Postgres Json(b) int array contains any of array values

Question

TLDR: How do I find every record that contains a json number array which contains one number of the given array/list.

Ok, I tried a lot, read a lot of other Threads but I didn't find a working solution yet. I have some JSON Objects in Postgres I'd like to find. They contain the index of a multiple choice select.

Objects:

[{"id": 5, "list": [1, 2, 3]}, {"id": 6, "list": [4, 5, 6]}]

And I need so select all objects which contain the Items 1 OR 5. For now, I can only check for one value

SELECT '[1, 2, 3, 4]'::jsonb @> '3'::jsonb as result;

or if both are present:

SELECT '[1,2,3,4]'::jsonb @> '[1, 2]'::jsonb as result;

Or, the interesting thing, if I have an array of strings, I can do what I need:

SELECT '["1","2","3","4"]'::jsonb ?| array['1', '5'] as result;

How do I apply this to JSON number arrays? I tried casting to int[] and a lot of other stuff. Nothing worked. I either need to convert the number array to a text array to work with it or find the right cast for the right side. This also didn't work: ?| array[1, 5]::text[]

Solution 1:

Well, the first thing that came to my and Mao Tsuns mind, to use OR, still looks like the fastest and simplest solution.

Solution 2:

I finally found a way to do it shorter (like I thought it should work) but it's a casting hell (also 9.4+) and not pretty to look at:

SELECT array_to_json(translate('[1,2,3,4]'::jsonb::text, '[]', '{}')::text[])::jsonb ?| array['1','5'] as result;

Which basically transforms the integers to strings.

Solution

you can use OR:

t=# SELECT '[1, 2, 3, 4]'::jsonb @> '3'::jsonb OR '[1, 2, 3, 4]'::jsonb @> '5'::jsonb as result;
 result
--------
 t
(1 row)

or aggregate unnested jsonb array to int array to compare if it intersects:

t=# with c(j) as (values('[1, 2, 3, 4]'::jsonb))
, a as (select j,jsonb_array_elements(j) ja from c)
select array_agg(ja), j, array_agg(ja::text::int) && array[5,4] from a group by j;
 array_agg |      j       | ?column?
-----------+--------------+----------
 {1,2,3,4} | [1, 2, 3, 4] | t
(1 row)

because all jsonb operators behave as documented in your examples. And I don't see some elegant shorter solution...

Comments

[Vao Tsun]

of course using OR is not an option?.. SELECT '[1, 2, 3, 4]'::jsonb @> '3'::jsonb OR '[1, 2, 3, 4]'::jsonb @> '5'::jsonb as result

[gerritg]

OR was the first solution that came to my mind but with a lot of selected options it surely blows up the query. Which is, of course, only a part of a really complicated query :) But yes, for now, it seams like the cleanest and fastest solution.

[gerritg]

That's really something I haven't thought about but as you stated: The group by isn't ideal. But thanks!

[gerritg]

Yes, the OR Solution is the first thing I had in mind but I thought there would be something shorter/elegant. I accepted this since it's still the fastest & easiest solution available. For now :)