Reputation: 53
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
Reputation: 23766
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
date
field, convert into timestamp and use it to order all array elements with most recent timestamp firstUpvotes: 3