Search code examples
jsonpostgresqljsonb

How to perform order by limit 1 inside a jsonb cell in postgres?

I have a table in postgres

CREATE TABLE my_table (
    id serial primary key,
    col_1 jsonb,

    ....

Inside col_1, I have a structure like this

[{"date": "2018-10-13", "val_1": 90.8, "val_2": 87.9},
 {"date": "2018-10-03", "val_1": 90.2, "val_2": 83.2},
 {"date": "2018-10-11", "val_1": 92.8, "val_2": 88.9},
 ...
]

Now I need to query something like this

SELECT "latest date from the jsonb" WHERE id = {some_id};

In order to do that, I should be able to order/sort the array in col_1 by date in descending order (convert the date string first using the to_date function) then get the first element of that sorted array. How can I do this in postgres?

Solution

  • You shoud unnest the json array using the function jsonb_array_elements():

    select (jsonb_array_elements(col_1)->>'date')::date as date
from my_table
where id = 1
order by date desc
limit 1

    date    
------------
 2018-10-13
(1 row)