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.

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 — Vao Tsun
– Vao Tsun, Commented Jan 18, 2018 at 12:55
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
– gerritg, Commented Jan 18, 2018 at 16:07

Vao Tsun · Accepted Answer · 2018-01-18 13:00:59Z

12

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

answered Jan 18, 2018 at 13:00

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gerritg Over a year ago

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 :)

ardmont · Accepted Answer · 2020-02-22 02:07:27Z

4

You can also do this:

select translate('[1,2,3]', '[]','{}')::int[] && ARRAY[1,3]

answered Feb 22, 2020 at 2:07

ardmont

1358 bronze badges

1 Comment

nesk May 5 at 13:10

In case someone wonders about performance: I didn't any performance penalties, even on very large datasets. Thank you @ardmont for this suggestion!

coladict · Accepted Answer · 2018-01-18 13:50:24Z

0

You could use jsonb_array_elements, cast to text, then to int and combine with array_agg.

WITH j AS (SELECT array_agg(h), othercol, thirdcol a FROM
  (SELECT jsonb_array_elements('[53,22,41,23]')::text::int h, othercol,
    thirdcol) AS j GROUP BY othercol, thirdcol)
SELECT othercol, thirdcol FROM j WHERE j.a && ARRAY[44,13,12,41];

Not exactly elegant, especially when you consider the group by statement

answered Jan 18, 2018 at 13:50

coladict

5,1351 gold badge20 silver badges31 bronze badges

1 Comment

gerritg Over a year ago

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

macieks · Accepted Answer · 2025-01-23 13:07:40Z

0

This is jsonb value in DB

column config contains {"topicId": [1, 2, 4]}

Now i search for any row with numbers: 1 and 4

select * from table where  (config->>'topicId')::jsonb@>'[1,4]'

answered Jan 23 at 13:07

macieks

4918 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related