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

user_10268072 0
Viewed 626

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

aggregate sql-order-by mysql sql json
1 Answers

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.

user_11440350
Related