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;