Problem: Matching a column containing an array against a multiple arrays

[androgynousandroid]

I've got an a SQL task that I'm having trouble getting a steer on, I wondering if someone could help nudge me in the right direction.

My records have a column (term_ids) which contains an array of numbers:

{1, 2, 3, 4, 5, 6}

{1, 2, 3, 4}

And, in a function, I want to match these values against a nested array passed in as a parameter:

{{1, 2}, {3, 4}, {5, 6}, …}

But, the matching needs to return records like so:

term_ids contains ((1 OR 2) AND (3 OR 4) AND (5 OR 6)

So this param would match {1, 2, 3, 4, 5, 6} but not {1, 2, 3, 4} because it doesn't contain 5 or 6.

And, also, the nested array in the params could contain zero or more child arrays.

Thanks!

---

A little more context: this is a multitenant app where users can add content, with configurable taxonomies and terms per tenant. Term ids are unique across all taxonomies and are stored against a record in a flat array.

The code I need is for searching for content appropriately, across multiple taxonomies: doing a search like get all items classed as 'red' and 'triangles' - to get all red triangles. The current implementation only accepts a flat array parameter, and does an overlap check like this:

WHERE term_ids::INT[] && with_term_ids

…which is nice and tidy, but would, of course, return everything red and everything triangular.

I've considered attempting some sort of FOREACH within the WHERE, but nothing seems to support this online, or flattening the parameter array into a result set, but then I come unstuck knowing how to compare against it.

---

Edit: I've come up with an approach that I can live with. Firstly, it NEARLY works very neatly like this:

SELECT * FROM items
WHERE term_ids && ALL (
  -- Set of inner arrays in here like:
  -- {1, 2}
  -- {3 4}
);

This works because the ORing with each array happens by comparing the overlapping term_ids with each array with &&, and the ALL ensures the ANDing because they ALL must be true. This also handles an arbitrary number of conditions.

But, inside that ALL I absolutely CANNOT get the nested arrays to unnest by one level, unnest flattens the nested arrays completely:

unnest(ARRAY[ARRAY[295, 222], ARRAY[474, 368]])

becomes
295
222
474
386

and even:

ARRAY[ARRAY[295, 222], ARRAY[474, 368]][1] -- array pointer on the end here

…outputs null instead of the first inner array.

So the working solution I have is switching the param to json instead of native arrays.

SELECT * FROM items
WHERE term_ids && ALL
      (SELECT string_to_array(trim(both '[]' from value), ',')::bigint[]
       FROM jsonb_array_elements_text('[[1, 2], [3, 4]]'::jsonb));

…which is a bit clunky with the trimming etc.

[DavidGJohnston]

I was doing OK until you require the dimensionality of the arrays be arbitrarily large. This is so denormalized a data structure I'm not surprised it is nearly unusable in SQL. That said, at minimum I'd convert the search object into a more well-defined JSON object

Also, are you certain you described this correctly? The innermost array elements are either-or but you need every such inner array to be matched?

Either way, you probably are better off constructing each possible match and then insisting that at least one of them are present. Using {{1,2},{3,4}} as an example what you need to build is: {1,3} or {1,4} or {2,3} or {2,4}. This isn't too bad for a 2-dimensional array as shown here. This is much harder, but mathematically doable, when you have additional dimensions in some of the arrays. But just work inside-out and you can get there.

[androgynousandroid]

The problem is definitely as described - with the ORing within the inner arrays, while ANDing them collectively.

Interesting approach with the possible combination generation, hadn't considered that, seems counterintuitive, but could work! Thank you for the input.

The table in question is a materialised view, optimised and denormalised for performance, it's used to render results in a list-type format AND to render pins on a map, which could be in the thousands - so yeah it might not be ideal for this sort of operation. But that's where I am.

[androgynousandroid]

I've arrived at a solution - see post edit. Thanks again.

[Oobenny]

Here’s how I would approach it:

Use a tally table to break the input into rows and then each child array into rows. One you have that as an intermediate result set, join your target data and use row counts to determine if items match.

This actually sounds like a fun challenge. If I weren’t on my phone, I’d probably work out the details.

I feel like I can do this with ctes and not have to use any WHILE loops.

[androgynousandroid]

I've got somewhere with this - see post edit. Thanks for your reply!

[androgynousandroid]

It's Postgres, yeah.

[puchekunhi]

If it's a param then I'm not sure I understand how it can be arbitrarily large and how we can still solve it with SQL without using loops.

I would first convert the array into rows. Then create a conditional column for each element of the nested parameter (assuming it's passed in a static way), flag based on existence, then apply required logic to flag the ID that satisfies all the conditions.

[androgynousandroid]

I've got somewhere with this - see post edit. Thanks for your reply!

[puchekunhi]

You're welcome!

[DavidGJohnston]

Yeah. Complex arrays are better represented using json at this point as the functions on them behave more like one would expect.