Bug #79007 JSON_INSERT and JSON_SET do not add properties in order of addition
Submitted: 28 Oct 2015 21:09 Modified: 29 Oct 2015 6:53
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:5.7, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[28 Oct 2015 21:09] Roland Bouman
Description:
JSON_SET and JSON_INSERT do not add keys in order of addition, but seem to add keys in alphabetical order.

While the JSON specification does not specify anything with regard to order of the properties in an object, there is a well established convention across javascript engines that keys should be placed in order of insertion/addition. 

Early v8 had a similar issue in object property iteration, and changed their implementation after overwhelming feedback form the community.

https://code.google.com/p/v8/issues/detail?id=164 

How to repeat:
mysql> SELECT JSON_SET('{}', '$.aa', 1, '$.a', 2);
+-------------------------------------+
| JSON_SET('{}', '$.aa', 1, '$.a', 2) |
+-------------------------------------+
| {"a": 2, "aa": 1}                   |
+-------------------------------------+
1 row in set (0.01 sec)

Expected result:

{"aa": 1, "a": 2}

mysql> SELECT JSON_insert('{}', '$.aa', 1, '$.a', 2);
+----------------------------------------+
| JSON_insert('{}', '$.aa', 1, '$.a', 2) |
+----------------------------------------+
| {"a": 2, "aa": 1}                      |
+----------------------------------------+
1 row in set (0.00 sec)

Expected result:

{"aa": 1, "a": 2}

Suggested fix:
Please adhere to the de-facto standard practice common in all major javascript engines to add keys in order of insertion / addition. Do not attempt any other property ordering scheme.
[29 Oct 2015 6:53] Umesh Shastry
Hello Roland,

Thank you for the report.

Thanks,
Umesh
[29 Oct 2015 6:55] Umesh Shastry
// 5.7.10

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT JSON_SET('{}', '$.aa', 1, '$.a', 2);
+-------------------------------------+
| JSON_SET('{}', '$.aa', 1, '$.a', 2) |
+-------------------------------------+
| {"a": 2, "aa": 1}                   |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_insert('{}', '$.aa', 1, '$.a', 2);
+----------------------------------------+
| JSON_insert('{}', '$.aa', 1, '$.a', 2) |
+----------------------------------------+
| {"a": 2, "aa": 1}                      |
+----------------------------------------+
1 row in set (0.00 sec)
[5 Nov 2015 9:46] Knut Anders Hatlen
I'm reclassifying this report as a feature request. RFC 7159 says "An object is an unordered collection", so it's not a bug, even though it might be unexpected.

The reordering is done in order to get better performance for key lookups, as one can do a binary search instead of scanning the keys linearly.

FWIW, PostgreSQL does a similar reordering of keys in their JSONB format:

> select '{"aa":1,"a":2,"bb":3,"b":4}'::jsonb;
               jsonb                
------------------------------------
 {"a": 2, "b": 4, "aa": 1, "bb": 3}
(1 row)

PostgreSQL does however have an alternative, non-binary storage format which preserves whitespace and ordering from the original JSON text, at the expense of performance.