Postgres array json recent date

Question

i have a json array like this:

[
  {
    "date": "26/11/2020 23:27",
    "note": "test1"
  },
  {
    "date": "22/11/2020 22:59",
    "note": "test2"
  },
  {
    "date": "18/11/2020 17:08",
    "note": "test3"
  }
]

I would like to take the element that has the most recent data.

My old query to get the first element was like that:

(notes\:\:json->0)\:\:json->>'note' as note,
(notes\:\:json->0)\:\:json->>'date' as date_note

S-Man · Accepted Answer · 2020-12-07 15:45:53Z

3

step-by-step demo:db<>fiddle

SELECT 
    elem.value ->> 'date' as thedate,
    elem.value ->> 'note' as note
FROM t,
    json_array_elements(data) elem                                  -- 1 
WHERE id = 4123
ORDER BY to_timestamp(elem ->> 'date', 'DD/MM/YYYY HH24:MI') DESC   -- 2
LIMIT 1                                                             -- 3

Extract all array elements into one row
Read datetime string from date field, convert into timestamp and use it to order all array elements with most recent timestamp first
Just return the very first (= most recent) array element.

edited Dec 7, 2020 at 15:45

answered Dec 7, 2020 at 15:07

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Over a year ago

Is there any way to change this query?select (tb.notes::json->0)::json->>'note' as note, (tb.notes::json->0)::json->>'date' as date_note from table as tb where id = 4545

Alex Over a year ago

i need to directly extract the array with the most recent date

S-Man Over a year ago

dbfiddle.uk/… Of course. Just add the WHERE clause to filter the id and extract the elements from the value

Collectives™ on Stack Overflow

Postgres array json recent date

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related