PostgreSQL JSON: test if a value is contained in an array

Question

I have a table with a JSONB row which holds data in this form:

id	data
1	{"key_1": "value1"}
2	{"another_key": "some_value"}

Given another JSON structure like this {"key_1": ["value1", "value2"]}, what shall I use to get the first row to match a query?

I had no luck using the JSON operators of PostgreSQL in the doc https://www.postgresql.org/docs/9.5/functions-json.html

Answer 1

1. Unless you're really using long unsupported PostgreSQL 9.5, refer to a more recent version of the documentation. If you're really on 9.5, consider an update.
2. If first row to match a query means you want the first by id, then add order by id limit 1 at the end.
3. Version 12+ has JSONPath @@ predicate check and @? path exists operators. If you're looking for a row with a jsonb value resembling the one you showed (key_1 with an array under it): demo

create table my_table(id,jsonb_column) as values
  (1::int, '{"key_1": "value1"}'::jsonb     )
, (2     , '{"another_key": "some_value"}'  )
, (3     , '{"key_1": ["value1", "value2"]}') ;

select * from my_table 
where jsonb_column @@ '$.key_1.type()=="array"' 
order by id limit 1;

id	jsonb_column
3	{"key_1": ["value1", "value2"]}

select * from my_table where jsonb_column @? '$.key_1[1]' order by id limit 1;

id	jsonb_column
3	{"key_1": ["value1", "value2"]}

4. Between versions 9.4.0 and 11.22 you can use @> object containment operator instead: demo

select * from my_table where jsonb_column @> '{"key_1":[]}' order by id limit 1;

5. If you're looking for rows where there's key_1 on the top level, and under it, there's any of the values in your array, @Serg's example got it right. In version 14 and above, you can use [key] subscript

   select t1.*
   from my_table t1
   inner join jsonb_each(jsonb '{"key_1":["value1","value2"]}') as find(key,val)
     on find.val @> t1.jsonb_column[find.key]
   order by id limit 1;
   

   In earlier versions, you need the -> accessor:

   select t1.*
   from my_table t1
   inner join jsonb_each(jsonb '{"key_1":["value1","value2"]}') as find(key,val)
     on find.val @> (t1.jsonb_column -> find.key)
   order by id limit 1;
   

Answer 2

You can use <@ comparison operator

with tbl(id, dat) as (
  select 1, '{"key_1": "value1"}'::jsonb union all
  select 2, '{"key_1": "valueX"}'::jsonb union all
  select 3, '{"key_X": "value1"}'::jsonb union all
  select 4, '{"key_1": "value2"}'::jsonb

),
prmkv as (
  select *
  from jsonb_each('{"key_1": ["value1", "value2"]}'::jsonb) 
)
select tbl.*
from tbl
join prmkv on (tbl.dat -> prmkv.key) <@ prmkv.value

Answer 3

I would suggest to first re-format/flatten the search structure. Here is a bit more complex one for genericity, {"key_1":["value1","value2"], "key_2":["value21","value22","value23"]}

select l.j 
from jsonb_each('{"key_1":["value1","value2"],"key_2":["value21","value22","value23"]}'),
lateral (
  select jsonb_build_object(key, item) j 
  from jsonb_array_elements(value) item
 ) l;

result
{"key_1": "value1"}
{"key_1": "value2"}
{"key_2": "value21"}
{"key_2": "value22"}
{"key_2": "value23"}

and then use it in a more or less trivial query

select * from the_table where data @> any
(
 select l.j 
 from jsonb_each('{"key_1":["value1","value2"],"key_2":"value21","value22","value23"]}'),
 lateral (select jsonb_build_object(key, item) j from jsonb_array_elements(value) item) l
)
order by id limit 1;

DB Fiddle demo