How to query nested unamed jsonb objects and order?

Question

have an audits column (jsonb) seems like:

{{"status": "pending", "updated_at": "2022-06-20T15:58:54-03:00"},{"status": "active", "updated_at": "2022-06-27T21:30:41-03:00"}}

want to order by updated_at in the object that has status = "active".

{"status": "active", "updated_at": "2022-06-27T21:30:41-03:00"}

Please provide a valid JSON sample. The current one is not valid. I suppose it should really be a JSON array? And always your version of Postgres. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 28, 2022 at 3:19

Erwin Brandstetter · Accepted Answer · 2022-06-28 03:37:05Z

1

Assuming your values are really JSON arrays, jsonb_path_query_first() with this path expression would do it:

SELECT *
FROM   tbl
ORDER  BY jsonb_path_query_first(audits, '$[*] ? (@.status == "active").updated_at')::timestamp

Also assuming the key updated_at contains valid timestamp literals.

answered Jun 28, 2022 at 3:29

187k28 gold badges465 silver badges639 bronze badges

1 Answer 1