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?
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
SELECT DISTINCT ON(events.id) events.id, filtered_logs, filtered_logs->>'recorded_at' AS recorded_at FROM "events" CROSS JOIN jsonb_array_elements(logs) AS filtered_logs WHERE (filtered_logs->>'state' IS NOT NULL) ORDER BY "events"."id" DESC, "recorded_at" DESC I had to add DISTINCT to return only single row