I want to sort a JSON array, in mysql, based on my date_requested field, order would be ascending. Can I achieve that?

Array is like:

SET @nonSorted= '[
{
    "id": "119",
    "date_end": "2022-01-04 18:30:00",
    "occupied": "0",
    "date_begin": "2022-01-04 16:00:00",
    "idCategory": "2",
    "date_requested": "2022-01-01 11:14:39"
},
{
    "id": "134",
    "date_end": "2022-01-06 11:30:00",
    "occupied": "0",
    "date_begin": "2022-01-06 08:00:00",
    "idCategory": "4",
    "date_requested": "2022-01-02 16:15:49"
},
{
    "id": "142",
    "date_end": "2022-01-09 11:00:00",
    "occupied": "1",
    "date_begin": "2022-01-09 08:30:00",
    "idCategory": "4",
    "date_requested": "2022-01-01 18:11:20"
}
]';

Would love to get the array sorted by the field date_requested, I've also tried some solution I found on stackoverflow but it appears that doens't work in my case. The output should be like thtis:

'[{
    "id": "119",
    "date_end": "2022-01-04 18:30:00",
    "occupied": "0",
    "date_begin": "2022-01-04 16:00:00",
    "idCategory": "2",
    "date_requested": "2022-01-01 11:14:39"
},{
    "id": "142",
    "date_end": "2022-01-09 11:00:00",
    "occupied": "1",
    "date_begin": "2022-01-09 08:30:00",
    "idCategory": "4",
    "date_requested": "2022-01-01 18:11:20"
},{
    "id": "134",
    "date_end": "2022-01-06 11:30:00",
    "occupied": "0",
    "date_begin": "2022-01-06 08:00:00",
    "idCategory": "4",
    "date_requested": "2022-01-02 16:15:49"
}
]'

CodePudding user response：

Possible solution for MySQL 8:

SELECT JSON_ARRAYAGG(object)
FROM (
    SELECT object
    FROM JSON_TABLE(@nonSorted,
                    '$[*]' COLUMNS (object JSON PATH '$',
                                    date_requested DATETIME PATH '$.date_requested')) jsontable
    ORDER BY date_requested LIMIT 18446744073709551615
) parsed;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b4d7a5ffa690becda0fad27cc9d0a326

CodePudding user response：

This will return the 3 parts in correct order, you will only have to merge them back to 1 JSON:

select 
   JSON_EXTRACT(jdoc, CONCAT("$[",t2.i,"]")) B
from t1 
cross join (select 0 as i union select 1 union select 2) t2
order by JSON_EXTRACT(jdoc, CONCAT("$[",t2.i,"].date_requested")) ASC;

output: