PostgreSQL JSONB overlaps operator on multiple JSONB columns-CodePudding

I have a table that contains two jsonb columns both consisting of jsonb data that represent an array of strings. These can be empty arrays too. I am now trying to query this table and retrieve the rows where either (or both) jsonb arrays contain at least one item of an array I pass, I managed to figure out a working query

SELECT *
FROM TABLE T
WHERE (EXISTS (SELECT *
               FROM JSONB_ARRAY_ELEMENTS_TEXT(T.DATA1) AS DATA1
               WHERE ARRAY[DATA1] && ARRAY['some string','some other string']))
  OR (EXISTS (SELECT *
              FROM JSONB_ARRAY_ELEMENTS_TEXT(T.DATA2) AS DATA2
              WHERE ARRAY[DATA2] && ARRAY['random string', 'another random string']));

But i think this is not optimal at all, I am trying to do it with a cross join but the issue is that this data1 and data2 in the jsonb columns can be an empty array, and then the join will exclude these rows, while maybe the other jsonb column does satisfy the overlaps && condition.

I tried other approaches too, like:

SELECT DISTINCT ID
FROM table,
    JSONB_ARRAY_ELEMENTS_TEXT(data1) data1,
    JSONB_ARRAY_ELEMENTS_TEXT(data2) data2
WHERE data1 in ('some string', 'some other string')
    OR data2 in ('random string', 'string');

But this one also does not include rows where data1 or data2 is an empty string. So I thought of a FULL OUTER JOIN but because this is a lateral reference it does not work: The combining JOIN type must be INNER or LEFT for a LATERAL reference.

CodePudding user response：

You don't need to unnest the JSON array. The JSONB operator ?| can do that directly - it checks if any of the array elements of the argument on the right hand side is contained as a top-level element in the JSON value on the left hand side.

SELECT *
FROM the_table t
WHERE t.data1 ?| ARRAY['some string','some other string']))
   OR t.data2 ?| ARRAY['random string', 'another random string']));

This will not return rows where both array are empty (or if neither of the columns contains the searched keys)