Is there a way to sort JSON entries, in mysql, based on a field?

Question

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"
}
]'

Why do you use JSON? You have an array where each element is an object with an identical set of fields. This should be another normal table, with six columns. Then all the troubles with JSON vanish. You can search and sort efficiently, and it's easy to write the query to do that. — Bill Karwin, Jan 26 '22 at 18:11

score 1 · Answer 1 · answered Jan 26 '22 at 17:23

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:

B
{"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"}

score 1 · Accepted Answer · answered Jan 26 '22 at 17:31

1

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

answered Jan 26 '22 at 17:31

Akina

39,301
5
14
25

seeing this question on SO ([Why is (18446744073709551615 == -1) true?](https://stackoverflow.com/questions/40608111/why-is-18446744073709551615-1-true)), I wonder why there is a LIMIT on above query ... (/me is just curious) – Luuk Jan 27 '22 at 18:29
@Luuk LIMIT is used for to forbid ORDER BY ignoring. The parameter of LIMIT clause is BIGINT UNSIGNED, and used value is maxvalue for this datatype. – Akina Jan 27 '22 at 19:48
Ah, a trick/feature to solve this problem " JSON_ARRAYAGG cannot currently be used as a window function." – Luuk Jan 28 '22 at 08:03
@Luuk Yes, JSON_ARRAYAGG does not implement ORDER BY clause (like in GROUP_CONCAT). – Akina Jan 28 '22 at 08:06

Is there a way to sort JSON entries, in mysql, based on a field?

2 Answers2