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

Popular posts from this blog

php - Permission denied. Laravel linux server -

google bigquery - Delta between query execution time and Java query call to finish -

python - Pandas two dataframes multiplication? -