CODEWITHSUNDEEP
jsonpostgresql
kaiserasif
kaiserasif

Reputation: 157

How to check if json keys are from an allowed set in PostgreSQL?

'{"a":1, "b":2, "c":3}'::jsonb ?& array['a', 'b'] checks if right hand side, an array of text, is contained by the top-level keys of the left hand side.

How can I check the opposite? That is, whether the keys of the json string are from a known set of texts.

Upvotes: 0

Views: 58

Answers (2)

Bergi
Bergi

Reputation: 664650

You can get the keys of your object with json(b)_object_keys and collect them into an array. Then use the array contains operator:

array(SELECT jsonb_object_keys('{"a":1, "b":2, "c":3}'::jsonb)) <@ array['a', 'b']

Alternatively, you could use a subquery on the keys and conjugate tests against the array (equivalent to the NOT EXISTS check by @GMB):

SELECT bool_and(k = ANY(array['a', 'b'])) FROM jsonb_object_keys(object) as k

(online demo)

Upvotes: 1

GMB
GMB

Reputation: 222512

You can use jsonb_object_keys() and ANY like so:

select 1
from jsonb_object_keys(t.js) o(x)
where not x = ANY(ar)

Here is how to use this in a query:

with t as (
    select '{"a":1, "b":2, "c":3}'::jsonb js, array['a', 'b', 'c'] ar
    union all select '{"a":1, "b":2, "z":3}'::jsonb js, array['a', 'b'] ar
)
select 
    js,
    ar,
    not exists(
        select 1
        from jsonb_object_keys(t.js) o(x)
        where not x = ANY(ar)
    ) res
from t

Yields:

js                       | ar      | res
:----------------------- | :------ | :-----
{"a": 1, "b": 2, "c": 3} | {a,b,c} | true  
{"a": 1, "b": 2, "z": 3} | {a,b}   | false

Upvotes: 0

Related Questions