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;

How can we write query which will select row if "data" contains "city"

JSON structure of data is not consistent.

Sevanteri · Accepted Answer · 2016-03-24 09:44:00Z

30

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.

answered Mar 24, 2016 at 9:44

Sevanteri

4,0981 gold badge26 silver badges30 bronze badges

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

1 Comment

Bernhardt Scherer Over a year ago

This works on a JSONB column. The question is about a JSON column, so you have to do a type cast: SELECT * FROM details WHERE data::JSONB ? 'city';

TitanFighter · Accepted Answer · 2022-06-16 17:48:20Z

2

You can use ?:

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

edited Jun 16, 2022 at 17:48

answered Oct 2, 2021 at 20:38

TitanFighter

5,2044 gold badges55 silver badges81 bronze badges

Comments

Philip M · Accepted Answer · 2017-10-04 13:57:10Z

-3

You can convert your json to text and search for the key with LIKE

SELECT *
FROM "details"
WHERE "data"::text LIKE '%"city":%'

answered Oct 4, 2017 at 13:57

Philip M

211 bronze badge

3 Comments

Eugen Konkov Over a year ago

some json value may contain 'city' text. How you distinguish is this key name or just text?

Chau Loi Over a year ago

well this works for my case where value is a list like this ["abc", "edf"]

Priyam Over a year ago

This query will have a performance degradation as compared to accepted answer.

Collectives™ on Stack Overflow

Postgresql JSON column check key exists

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related