postgresql - fetch max date json object in an array postgres

Question

I'm trying to fetch max date json data from an array..

Here is jsonb column

--------
 value
--------
{
    "id": "90909",
    "records": [
        {
            "name":"john",
            "date": "2016-06-16"
        },
        {
             "name":"koiy",
            "date": "2016-08-26"
        },
        {
            "name":"koiy",
            "date": "2016-01-06"
        }
    ]
}

How to select the maximum date json object in the jsonb column..

expected output:-

{
   "name":"koiy",
   "date": "2016-08-26"
}
        

and this selected json object date should less than the current date.. any suggestions would also helpful..

question from:https://stackoverflow.com/questions/65938908/fetch-max-date-json-object-in-an-array-postgres

Answer 1

CREATE TABLE test_table (a json);

INSERT INTO test_table (a) VALUES ('{"id":"90909","records":[{"name":"john","date":"2016-06-16"},{"name":"koiy","date":"2016-08-26"},{"name":"koiy","date":"2016-01-06"}]}');

WITH cte as (
    SELECT json_array_elements(a->'records') as record FROM test_table
)
SELECT record
FROM cte
ORDER BY (record->>'date')::date DESC
LIMIT 1;

->

+-------------------------------------+
| record                              |
|-------------------------------------|
| {"name":"koiy","date":"2016-08-26"} |
+-------------------------------------+
SELECT 1
Time: 0.003s