how to build postgres json query for missing property in json?

Question

I have a table A with a column b as jsonb

        b
------------------
 {"c": 1}
 {"c": 1, "d": 2}

how to build a query for rows where d is missing?

SELECT * FROM A WHERE b@>'{"c":1}';

returns all rows while

SELECT * FROM A WHERE b@>'{"c":1,"d":null}';

returns none ( due d is not null in first row );

user330315 · Accepted Answer · 2017-10-30 08:17:34Z

5

You can use the ? operator to test for the presence of a key. To find those where the key does not exist, you can negate the expression:

select *
from a 
where not b ? 'd';

answered Oct 30, 2017 at 8:17

user330315

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

1 Answer 1