Check if Postgres json array intersects with another array

Question

Is there a postgres jsonb operator that checks whether any one element of a set of elements is in another set of elements?

for example:

if ['a', 'b', 'c'] in ['c', 'd', 'e'] = True because c in 2nd set
if ['f', 'g', 'h'] in ['g', 'h', 'i'] = True because of g and h in 2nd set

Prolly answered by stackoverflow.com/questions/32812526/… and stackoverflow.com/questions/39480816/… — Ilja Everilä
– Ilja Everilä, Commented Jul 10, 2018 at 10:21
@IljaEverilä I guess the other questions you point to expect one of the sides to be a PostgreSQL array already. It's not clear from the pseudo-code in this question if both arrays come in jsonb type, or if one of them already is provided as text[]. (I was assuming the former.) — Bruno
– Bruno, Commented Jul 10, 2018 at 10:45
If you are specifying the other side as an array, then this is indeed a duplicate of the question linked to by @IljaEverilä. (Unclear why it being part of a join condition would make a difference, or why MongoDB would be better here, up to you.) — Bruno
– Bruno, Commented Jul 10, 2018 at 11:00

Bruno · Accepted Answer · 2018-07-10 10:02:09Z

You can expand the JSON array using jsonb_array_elements (when using jsonb) or json_array_elements (when using json), or directly to text using jsonb_array_elements_text (when using jsonb) or json_array_elements_text (when using json), and then turn them into a PostgreSQL array using array_agg.

WITH json_data(x, y) AS (
    VALUES ('["a", "b", "c"]'::jsonb, '["c", "d", "e"]'::jsonb),
           ('["a", "b", "c"]'::jsonb, '["g", "h", "i"]'::jsonb)
)
, array_data AS (
    SELECT x, (SELECT array_agg(e) AS arr FROM jsonb_array_elements_text(x) e) AS xarr,
           y, (SELECT array_agg(e) AS arr FROM jsonb_array_elements_text(y) e) AS yarr
    FROM json_data
)
SELECT x, y, xarr, yarr, xarr && yarr AS with_intersection FROM array_data

This produced these results:

        x        |        y        |  xarr   |  yarr   | with_intersection
-----------------+-----------------+---------+---------+-------------------
 ["a", "b", "c"] | ["c", "d", "e"] | {a,b,c} | {c,d,e} | t
 ["a", "b", "c"] | ["g", "h", "i"] | {a,b,c} | {g,h,i} | f
(2 rows)

Collectives™ on Stack Overflow

Check if Postgres json array intersects with another array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related