PostgreSQL searching value in inside JSON array -
i want search element inside jsonb in postgresql here json
create table test select jsondata::jsonb ( values ( '{"key1": 1, "keyset": [10, 20, 30]}' ), ( '{"key1": 1, "keyset": [10, 20]}' ), ( '{"key1": 1, "keyset": [30]}' ), ( '{"key1": 1 }' ), ( '{"key1": 1, "key2": 1}' ) ) t(jsondata);
in above table keyset
not exist in rows , query
select * test jsondata->>'keyset' = 10;
above query giving empty result, , expected output is
jsondata ------------------------------------ {"key1": 1, "keyset": [10, 20, 30]} {"key1": 1, "keyset": [10, 20]}
what want this
select jsondata @> '{"keyset": [10]}' foo;
so looks this
select jsondata, jsondata @> '{"keyset": [10]}' foo; jsondata | ?column? -------------------------------------+---------- {"key1": 1, "keyset": [10, 20, 30]} | t {"key1": 1, "keyset": [10, 20]} | t {"key1": 1, "keyset": [30]} | f {"key1": 1} | f {"key1": 1, "key2": 1} | f
the @>
operator checks containment in postgresql. put in select show evaluations..
select jsondata foo jsondata @> '{"keyset": [10]}';
Comments
Post a Comment