How is it possible to order the array returned by json_arrayagg() in Mysql?

Question

I want to order the array returned by json_arrayagg(). My query is similar to this:-

select A, json_arrayagg(json_obj('X',value1, 'Y',value2)) AS RESULT
FROM (derived table)
GROUP BY A.

What I want is that I want the array that returns to be ordered by value2. I have tried adding order by clause at the end (like, order by value2: it is not working)

have tried adding order by inside json_arrayagg()..(like: json_arrayagg(json_obj() order by value2) It is not working.

Have tried using group_concat , but it is not reliable and don't know why it's not returning correct data. Have checked limits.

Please suggest me how to solve this? Thanks

Lothar

@Gordon Linoff, Can you please help here! – Lothar

. . Unfortunately, MySQL does not formally support that functionality -- even with an ORDER BY in the subquery. There is a hacky solution but no guarantee that that works. – Confederacy

@Gordon Linoff, There was no shortcut to do it, so I tried to pass the ordered values from the required derived table to the JSON_ARRAYAGG. But still, I would like to know the hacky solution ~ Thanks :) – Lothar

Answer 1

Apparently, there is a hack which might work:

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;

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

Confederacy

Answer 2

Im facing the same problem, in the end i use GROUP_CONCAT instead:

select 
    A, 
    CONCAT('[',
      GROUP_CONCAT(JSON_OBJECT('X',value1, 'Y',value2) ORDER BY value0)
    ,']') AS RESULT
FROM (derived table)
GROUP BY A.

Hatchel