How to query nested unamed jsonb objects and order?

Question

have an audits column (jsonb) seems like:

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

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

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

• Erwin Brandstetter: 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.

Answer 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.