CODEWITHSUNDEEP
jsonpostgresql
Alex
Alex

Reputation: 53

Postgres array json recent date

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

Upvotes: 1

Views: 1325

Answers (1)

S-Man
S-Man

Reputation: 23766

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
  1. Extract all array elements into one row
  2. Read datetime string from date field, convert into timestamp and use it to order all array elements with most recent timestamp first
  3. Just return the very first (= most recent) array element.

Upvotes: 3

Related Questions