How to write a postgres query on json data to check for specific conditions

Question

I'm working on a real estate application that uses a postgres database. I need to query a json field for a specific condition and I'm not sure how to do that.

The json data is stored in a column called "conditions". For example, a buyer might have the following conditions:

{
    "subject to financing": {"date": "2020-05-30", "time": "2100", "done": true},
    "inspection": {"date": "2020-05-30", "time": "2100", "done": true},
    "be my friend": {"date": "2020-05-30", "time": "2100", "done": true}
}

I want to write query that checks if all conditions are met. In the above case, that means cycling through all conditions and checking that their "done" property is true.

How does one write a query like that with json data? Thanks.

user330315 · Accepted Answer · 2020-05-27 19:09:03Z

1

You can do that with a NOT EXISTS condition.

select t.*
from the_table t
where not exists (select *
                  from jsonb_each(t.conditions) as c(k,val)
                  where (c.val -> 'done')::boolean = false)

The subquery iterates over all key/value pairs (of each row in the outer query) and checks if none is set to false

answered May 27, 2020 at 19:09

user330315

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

2 Comments

gib65 Over a year ago

Thanks a_horse... that seems to work. If I were to refine it a little to detect when 'done' doesn't exist in the json object, how would I add that?

user330315 Over a year ago

Use the ? operator, not c.val ? 'done'

Collectives™ on Stack Overflow

How to write a postgres query on json data to check for specific conditions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related