0

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"}
1
  • 2
    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. Commented Jun 28, 2022 at 3:19

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.