CODEWITHSUNDEEP
postgresqljsonb
732b
732b

Reputation: 61

postgres JSONB: check if all keys *contained by* an array

the postgres doc says that i can use ?& to check if a JSONB object's keys contains all elements in an array. is there something for me to check if all keys in an JSONB objects are contained by a given array?

so doing a query like

select my_jsonb_column 
from my_table
where my_jsonb_column *contained_by* array['a', 'b', 'c'];

would yield results as the following where the keys are a subset of the given array.

{'a': 1, 'b': 2}
{'a': 1, 'b': 2, 'c': 3}

Upvotes: 2

Views: 1178

Answers (1)

732b
732b

Reputation: 61

thanks to @abelisto's suggestion, i came up with this and it seems to work

select *
from my_table
where array(select jsonb_object_keys(my_json_column)) <@ array['a', 'b', 'c'];

Upvotes: 3

Related Questions