0

I am working with a row that contains JSON data. the column is like this,

log 
[{{"status":"orderderd","date":"2021-10-13T16:30:57.134Z"},{"status":"deivered","date":"2021-10-13T16:30:57.134Z"}}]

now I want to get the time when the status is delivered. How can I write a query for this?

noob_developer
  • 183
  • 3
  • 11
  • 2
    That is not a valid JSON object. – Laurenz Albe Oct 14 '21 at 06:51
  • Your first `'{'` and last `'}'` must be `'['` and `']'`, if that is an array. In addition, you could start trying to search in previous answers. https://stackoverflow.com/questions/42918348/postgresql-json-like-query – James Oct 14 '21 at 06:54
  • Which Postgres version are you using? –  Oct 14 '21 at 09:28

1 Answers1

0

If you are using Postgres 12 or later, you can do this quite easily using a JSON path query:

select jsonb_path_query_first(log, '$[*] ? (@.status == "delivered").date') #>> '{}' as delivery_date
from the_table;

jsonb_path_query_first returns a jsonb value and the expression #>> '{}' turns that into a text value.

If you are using an older version, you need to unnest the array:

select (select l.element ->> 'date'
        from jsonb_array_elements(t.log) as l(element)
        where l.element ->> 'status' = 'delivered'
        limit 1) as delivery_date
from the_table t;

Both queries assume that the column is defined as jsonb (which it should be). If it's not you need to cast it (e.g. log::jsonb)