MySQL Bugs: #100974: JSON_OBJECT() performs key sort on output

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

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

Hi,

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

all best
Bogdan

Thanks for your prompt reply.

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

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

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!

Posted by developer:
 
I can confirm that the behaviour is intended. It is also documented in the reference manual under "Normalization, Merging, and Autowrapping of JSON Values" here:
https://dev.mysql.com/doc/refman/8.0/en/json.html#json-normalization

The last paragraph says: "To make lookups more efficient, MySQL also sorts the keys of a JSON object. You should be aware that the result of this ordering is subject to change and not guaranteed to be consistent across releases."

Fixed in mysqldoc rev 71133-71134; added a note to the JSON Functions section regarding this.

Closed.

Thanks for bug fixing