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

. . 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. — Gordon Linoff
– Gordon Linoff, Commented Aug 5, 2020 at 11:13
@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 :) — Harsh Raj
– Harsh Raj, Commented Aug 6, 2020 at 3:23

Gordon Linoff · Accepted Answer · 2020-08-06 10:58:33Z

4

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.

answered Aug 6, 2020 at 10:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben in CA Mar 20 at 22:36

About 5yrs later, is there any better way? BTW @Gordon Linoff - your profile has the old edition linked for your book, and your domain has expired.

Eri Indrawan · Accepted Answer · 2023-05-09 02:32:28Z

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.

answered May 9, 2023 at 2:32

Eri Indrawan

943 bronze badges

Comments

Marc DANJOU · Accepted Answer · 2025-09-02 17:42:17Z

1

I found out that on recent versions of MySQL (8+), the order can be set if a limit is explicitely provided in the recordset sent to json_arrayagg , otherwise this is not applied at all.

answered Sep 2 at 17:42

Marc DANJOU

12 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related