Postgresql JSON column check key exists

Question

I have wrote query like this to check json column has key

SELECT *
FROM "details" 
where ("data"->'country'->'state'->>'city') is not null; 

but i need to write query which will select row if "data" contains "city"

json structure of data is not consistent.

Answer 1

You can check the top-level keys of data with ? as it is said in the documentation.

For example

SELECT * FROM details
WHERE data ? 'city';

Checking every key in all nested objects from the json column requires a recursive CTE

select * from details
where 'city' in (
    WITH RECURSIVE t(k,j) as (
        select jsonb_object_keys(details.data), details.data
    UNION ALL
        select jsonb_object_keys(t.j->t.k), t.j->t.k
        FROM t WHERE jsonb_typeof(t.j->t.k) = 'object'
    )
    select k from t
);

This of course is not very efficient.

Answer 2

You can use ?:

SELECT *
FROM "details" 
WHERE data->'country'->'state' ? 'city';