Bug #100974 JSON_OBJECT() performs key sort on output
Submitted: 28 Sep 2020 13:52 Modified: 30 Sep 2020 16:19
Reporter: GANESA KUMAR Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S1 (Critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: JSON_OBJECT

[28 Sep 2020 13:52] GANESA KUMAR
Description:
Query : JSON format output from table

SELECT JSON_OBJECT('user_id', 1,
'Name', 'Ganesh',
'DOB', '1980-04-27'
) AS OUTPUT

Expected output should be:
{
"user_id" : 1,
"Name" : "Ganesh",
"DOB" : "1980-04-27"

}

But what i got

{
"DOB" : "1980-04-27",
"Name" : "Ganesh",
"user_id" : 1
}

Problem is column order. Column order in JSON should User_id,name and DOB.
But am getting like DOB,Name and User_id.

Anyone can help me to order the output as expected.

Note : Here i have given simple example, actually my query will have multiple array objects also.. so i did not tried any user defined procedure to create JSON output.

If anybody can help to order the column in JSON_OBJECT would be very greatful for me instead of writing any other script.

How to repeat:
I have given detail in Description box. Please try to repeat to see the bug

Suggested fix:
JSON_OBJECT function should maintain the column order
[28 Sep 2020 14:40] MySQL Verification Team
Hi,

This is not a bug, there is no specification that requires order of named values in JSON format.

all best
Bogdan
[28 Sep 2020 14:54] GANESA KUMAR
Thanks for your prompt reply.

Will there be any idea to implement this feature in future version?
[28 Sep 2020 15:34] MySQL Verification Team
Hi,

> Will there be any idea to implement this feature in future version?

No. The JSON format is made so that named values are accessed by name, not by their order so that's how your client must handle them. You cannot expect named values in order. The array values have order but named values / nodes are accessed only by name.

All best
Bogdan
[30 Sep 2020 16:19] Jon Stephens
The issue here is that JSON_OBJECT() performs a sort by key when printing the object:

mysql> SELECT JSON_OBJECT('user_id', 1,
-> 'Name', 'Ganesh',
-> 'DOB', '1980-04-27'
-> ) AS output;
+-------------------------------------------------------+
| output |
+-------------------------------------------------------+
| {"DOB": "1980-04-27", "Name": "Ganesh", "user_id": 1} |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OBJECT('user_id', 1,
-> 'DOB', '1980-04-27',
-> 'Name', 'Ganesh'
-> ) AS output;
+-------------------------------------------------------+
| output |
+-------------------------------------------------------+
| {"DOB": "1980-04-27", "Name": "Ganesh", "user_id": 1} |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OBJECT('name', 'carrot', 'id', 87) AS output;
+------------------------------+
| output |
+------------------------------+
| {"id": 87, "name": "carrot"} |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot') AS output;
+------------------------------+
| output |
+------------------------------+
| {"id": 87, "name": "carrot"} |
+------------------------------+
1 row in set (0.00 sec)

Please check with Development and verify that this is intentional behaviour. If they say that it is, then please change the bug category to Docs and assign to me.

Thanks!