Access jsonb value[] by key

Question

I am currently storing data in a jsonb column on a postgres db in the following format: {"ids": [4, 3, 2, 1]} where the value is an array of integers.

I wish to do the following:

Locate rows where the column contains the value of 4 in the value
array for the 'ids' key.
Locate rows where the column contains the values 2,3, & 1 in the value array for the 'ids' key.

My most recent attempt use the following

select * from tablename WHERE (column_name -> ids)::integer[] && ARRAY[1,3]

Which gave me a type casting error at ::integer[] ERROR: cannot cast type jsonb to integer[]

I suspect I was trying to cast the 'ids' portion and needed to go a level deeper, but I'm unsure how (if that is even the right approach).

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2020-08-29T18:56:58

Use the contains operator @>

select * 
from tablename 
WHERE column_name @> '{"ids":[4]}'

This also works for multiple values:

select * 
from tablename 
WHERE column_name @> '{"ids":[1,2,3]}'

Online example

Access jsonb value[] by key

2 Answers