Postgres JSON array contains given array element

Question

I have been searching for this answer for long on stackoverflow, but did not find anything useful. I have data in my_tbl as:

folder_id |  links                                                                                                     
--------------+----------------------------------------------------------------
   761 | [{"ids": "[82293,82292]", "index": "index_1"}, {"ids": "[82293,82292]", "index": "index_2"}]
   769 | [{"ids": "[82323,82324]", "index": "index_3"}]
   572 | [{"ids": "[80031,79674,78971]", "index": "index_4"}]
   785 | [{"ids": "[82367,82369]", "index": "index_5"}, {"ids": "[82368,82371]", "index": "index_6"}]
   768 | [{"ids": "[82292,82306]", "index": "index_7"}]

I want to get all the rows where links->>'ids' contain 82292. So in this case, it should return me folder_id 761 and 768.

I achieved so far is separating ids arrays from links by - jsonb_array_elements(links) ->> 'ids'

not sure how to proceed further.

Andomar · Accepted Answer · 2017-03-08 23:09:13Z

14

You can use jsonb_array_elements to convert a json array to a rowset. You can use this to at the value of "ids". Using the @> operator you can check if the array contains a value:

select  distinct folder_id
from    YourTable
cross join lateral
        jsonb_array_elements(links) ids(ele)      
where   (ele->>'ids')::jsonb @> '[82292]'

A tricky part is that "[82293,82292]" is stored as a string, not an array. The ele->>'ids' expression retrieves "[82293,82292]" as string using the ->> operator (the double > makes it return text instead of jsonb.) The content of the string is converted to an array by ::jsonb.

Example at rextester.com.

edited Mar 8, 2017 at 23:09

answered Mar 8, 2017 at 20:51

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

4 Comments

stack_d_code Over a year ago

Also, something I tried to get more out of the query -> search array of elements; something like, ele.id IN ('82292', '82324') that works too.

Andomar Over a year ago

Looks like you can do that with @>, check if the left-hand side array contains the right-hand side array

bobmarksie Over a year ago

Really like the use of the cross join lateral - nice solution.

inthedark72 Over a year ago

Hi Andomar. This solution is really helpful. If you are trying to check if links has multiple ids, then this seems to work in Postgres 11.10. where (ele->>'ids')::jsonb @> '[82292]' AND (ele->>'ids')::jsonb @> '[82306]' However, on my machine with Postgres 12.3 this results in 0 rows returned. Do you know why this may not be working on a later version?

Collectives™ on Stack Overflow

Postgres JSON array contains given array element

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related