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.
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...