Postgres 9.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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 3


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

ERROR:  operator does not exist: jsonb @@ unknown
LINE 2: where jsonb_column @@ '$.key_1.type()=="array"'
                           ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.


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

ERROR:  operator does not exist: jsonb @? unknown
LINE 2: where jsonb_column @? '$.key_1[1]'
                           ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.


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


id
jsonb_column


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



SELECT 1


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


id
jsonb_column


1
{"key_1": "value1"}



SELECT 1


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

ERROR:  cannot subscript type jsonb because it is not an array