CODEWITHSUNDEEP
sqljsonpostgresqlselectjsonb
morgoth
morgoth

Reputation: 1451

Filtering out JSONB array elements containing specific condition in PostgreSQL

I have a table: "events" with jsonb column "logs".

Having events record with following logs:

[
{state: "something", recorded_at: "some-timestamp"},
{state: "other", recorded_at: "some-other-timestamp"},
{nothing: "interesting", recorded_at: "timestamp"}
]

I would like to perform this query:

I don't really want to construct WHERE query conditions, I just want to filter out "logs" in returned result.

How to do it?

Upvotes: 0

Views: 1108

Answers (1)

GMB
GMB

Reputation: 222482

To get records whose none of logs object has key 'state', you can use not exists and jsonb_array_elements():

select e.*
from events e
where not exists(select 1 from jsonb_array_elements(e.logs) x(obj) where obj ? 'state')

On the other hand, if you are not looking to filter out records, but instead want to filter out nested json objects that have key 'state':

select e.*, x.new_logs
from events e
cross join lateral (
    select jsonb_agg(x.obj order by x.ord) new_logs
    from jsonb_array_elements(e.obj) with ordinality x(obj, ord)
    where not obj ? 'state'
) x

Upvotes: 1

Related Questions