How to create SQL query to sort JSON array using 1 attribute?

Question

I have a table with a column that contains a JSON body that has arrays that I want to sort based on a attribute associated with that array.

I have tried selecting the array name and displaying the attribute which will display the entire array

The column name is my_column and the JSON is formatted as follows -


{
    "num": "123",
    "Y/N": "Y",
    "array1":[
         {
             "name": "Bob",
             "sortNum": 123
         },
         {
             "name": "Tim Horton",
             "sortNum": 456
         }
    ]
}

I want the output to be based on the highest value of sortNum so the query should display the attributes for Tim Horton. The code I have played around with is below but get an error when trying to query based on sortNum.

SELECT my_column 
FROM 
    my_table,
    jsonb_array_elements(my_column->'array1') elem
WHERE elem->>'sortNum' = INT

sticky bit · Accepted Answer · 2019-04-04T22:50:13.700

Order by the filed 'sortNum' of the array element descending and use LIMIT 1 to only get the top record.

SELECT jae.e
       FROM my_table t
            CROSS JOIN LATERAL jsonb_array_elements(t.my_column->'array1') jae (e)
       ORDER BY jae.e->'sortNum' DESC
       LIMIT 1;

Edit:

If you want to sort numerically rather than lexicographically, get the element as text and cast it to an integer prior sorting on it.

SELECT jae.e
       FROM my_table t
            CROSS JOIN LATERAL jsonb_array_elements(t.my_column->'array1') jae (e)
       ORDER BY (jae.e->>'sortNum')::integer DESC
       LIMIT 1;

score 0 · Answer 2 · answered Apr 04 '19 at 22:30

This answer assumes that your table has a column (or maybe a combination of columns) that can be use to uniquely identify a record. Let's call it myid.

To start with, we can use json_array_elements to split the JSON array into rows, as follows:

select myid, x.value, x->>'sortNum' 
from 
    mytable, 
    json_array_elements(mycolumn->'array1') x
;

This returns:

myid  |  value                                 | sortnum
---------------------------------------------------------
1     |   {"name":"Bob","sortNum":123}         | 123
1     |   {"name":"Tim Horton","sortNum":456}  | 456

Now, we can turn this to a subquery, and use ROW_NUMBER() to filter in the array element with the highest sortNum attribute:

select value
from (
    select 
        x.value, 
        row_number() over(partition by myid order by x->>'sortNum' desc) rn
    from 
        mytable, 
        json_array_elements(mycolumn->'array1') x
) y 
where rn = 1;

Yields:

value
-----------------------------------
{"name":"Tim Horton","sortNum":456}

Demo on DB Fiddle

How to create SQL query to sort JSON array using 1 attribute?

2 Answers2