postgres JSONB: check if all keys contained by an array

Question

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}

Hint: array(select jsonb_object_keys(my_jsonb_column)) and Array Functions and Operators — Abelisto
– Abelisto, Commented Nov 29, 2017 at 21:36
thanks for the reply! so i posted a solution based on your suggestion and it works, but i don't understand why it's necessary to have select inside the array function. apparently it doesn't work without it. would be great if you can point me to some resources for an explanation. thanks! — 732b
– 732b, Commented Nov 29, 2017 at 22:21
array() here is not regular function but Array Constructor. IMO It is not necessary to have two separate forms of constructor for subquery and for SRF results. — Abelisto
– Abelisto, Commented Dec 1, 2017 at 21:39

732b · Accepted Answer · 2017-11-29 22:16:11Z

3

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'];

answered Nov 29, 2017 at 22:16

732b

614 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres JSONB: check if all keys contained by an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related