How to order by, a json_object in mysql

I've a problem sorting a JSON_OBJECT:

How can I sort the objects as they are in the JSON OBJECT?

Solution 1:

You can't control the order of the elements in the array that json_arrayagg() generates, even in MySQL 8.0, as explained in the documentation:

Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined.

An ugly and not scalable work around is to use group_concat() to manually generate the json array:

select 
    dashboard,
    widget,
    ...
    concat(
        '[',
        group_concat(
            json_object('color_mode', color_mode, ...)
            order by <your_ordering_clumn>
        ),
        ']'
    ) js_array
from datadog_wigets_markers 
group by dashboard, widget, ...

This will fail on long json strings. I would rather try and live with json_arrayagg() and unordered arrays.

Side note: you should enumerate all the non-aggregated columns in the group by clause; it's a requirement in most databases, and a good coding practice.

Solution 2:

@Gordon Linoff's answer from this post worked for me on MySQL 8.0, but I didn't test it for scalability.

As written in @Simas Jonelinuas's comment, here is the answer from the post in case the link ever becomes invalid.

SELECT A, json_arrayagg(json_obj('X',value1, 'Y',value2)) AS RESULT
FROM (SELECT . . .,
             ROW_NUMBER() OVER (ORDER BY value2) as seqnum
      FROM . . . 
      . . . 
     ) x
GROUP BY A;

Quote: "The ROW_NUMBER() -- apparently -- manages to order the result set, even though ORDER BY does not work."