I want to check as a constraint if the root keys of a jsonb document consists of any text elements like array['likes','interests','activities','movies','music','television']
create table foo(
bar_json jsonb,
constraint baz check(jsonb_object_keys(bar_json)::text[] @> array['likes','interests','activities','movies','music','television'])
);
The one above doesn't work. Can you help me? Thank you!
This can be nicer achieved by using available JSON operators in Postgres.
Specifically for your use:
?|: Do any of these key/element strings exist?
create table foo(
bar_json jsonb,
constraint baz check(bar_json ?| array['likes','interests','activities','movies','music','television'])
);
The function jsonb_object_keys(jsonb) returns set, while an expression in check cannot return set.
Your try to cast jsonb_object_keys(jsonb)::text[] is incorrect, you need an aggregate.
Create a boolean function using the aggregate function bool_and() and use it as check expression:
create or replace function check_bar_json(val jsonb)
returns boolean language sql as $$
select
bool_and(elem = any(array['likes','interests','activities','movies','music','television']))
from
jsonb_object_keys(val) elem
$$;
create table foo(
bar_json jsonb,
constraint baz check(check_bar_json(bar_json))
);
