Mysql 5.7 native json support - control keys order in json_insert function

Question

I am facing issues with json key ordering while inserting using json_insert function.

I have the sorted records as below

"AIR", "AIR Express"

"CFR", "Cost and Freight"

"FH", "Free house"

"UN", "United Nations"

"UPS", "United Parcel Service"

After converting the above records into json using json_insert function, I am seeing the results as below

{"FH":"Free house", "UN": "United Nations", "AIR": "AIR Express", "CFR": "Cost and Freight", "UPS": "United Parcel Service"}

but I want the json to maintain the keys in the order I am inserting. Though I could sense that it is trying to maintain the order based on the character length of keys. But how can I get rid to get the keys aligned in the order.

Botvinnik

I can confirm it's ordering by the length of the keys short-to-long. See this screenshot for a more fun example "pretty triangular pattern" : twitter.com/harry_wood/status/1283511514591383553 – Comte

Answer 1

That's sad, but at now is no way to keep original keys order.

Normalization, Merging, and Autowrapping of JSON Values

The normalization performed by MySQL also sorts the keys of a JSON object (for the purpose of making lookups more efficient). The result of this ordering is subject to change and not guaranteed to be consistent across releases. In addition, extra whitespace between keys, values, or elements in the original document is discarded.

Appel

Answer 2

i had the same problem, the right way to solve this problem i found by talking with the community,is to adding an extra value for each key on the object,it is the most right way. The shorter example based on your code, you can do

{"1:FH":"Free house", "2:UN": "United Nations", "3:AIR": "AIR Express", "4:CFR": "Cost and Freight", "5:UPS": "United Parcel Service"}

Then by these extra values, you sort the positions of the keys, you can do this in many other ways, if you or someone else are interested in this way of key sortering, let me know, and i will show how, on JS, or PHP. Cheers!

Frae