Fetch second max date json object using the SQL query

Question

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

Here is jsonb column

--------
 value
--------
{
    "id": "90909",
    "records": [
        {
            "name":"john",
            "date": "2016-06-16"
        },
        {
             "name":"kiran",
            "date": "2017-06-16"
        },
        {
            "name":"koiy",
            "date": "2018-06-16"
        }
    ]
}

How to select the second maximum date json object..

expected output:-

 {
     "name":"kiran",
     "date": "2017-06-16"
 }

and if we have only one object inside the records means that will be the second max date

and any suggestions would also helpful..

Blue Star · Accepted Answer · 2021-02-04 06:10:41Z

1

My main suggestion would be this: If your data is structured, do not store it in a JSON. It will be much easier to work with it if you structure it as relational tables.

But anyhow, here's one way to get the second-latest-date object. First unpack the array, then sort by the date and take the second to last:

SELECT obj.* 
FROM your_table, jsonb_array_elements(value->'records') obj 
ORDER BY obj->'date' DESC
LIMIT 1 OFFSET 1;

                  value
-----------------------------------------
 {"date": "2017-06-16", "name": "kiran"}
(1 row)

answered Feb 4, 2021 at 6:10

Blue Star

1,9621 gold badge13 silver badges11 bronze badges

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

1 Comment

user330315 Over a year ago

"if your data is structured, do not store it in a JSON" - this

Collectives™ on Stack Overflow

Fetch second max date json object using the SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related