MYSQL JSON column change array order after saving

Question

I am using JSON column type in MySQL database table. When I try to save JSON values in table column, the JSON array automatically re-order(shuffle)

I have following JSON:

{"TIMER_HEADER": [{"XX!TIMERHDR": "XXTIMERHDR", "VER": " 7", "REL": " 0", "COMPANYNAME": "XXX", "IMPORTEDBEFORE": "N", "FROMTIMER": "N", "COMPANYCREATETIME": "12423426"}, {"XX!HDR": "XXHDR", "PROD": "Qics for Wnows", "VER": "Version 6.0", "REL": "Release R", "IIFVER": "1", "DATE": "2018-01-20", "TIME": "1516520267",   "ACCNTNT": "N", "ACCNTNTSPLITTIME": "0"}], "COLUMN_HEADER": ["!TIMEACT", "DATE", "JOB", "EMP", "ITEM", "PITEM", "DURATION", "PROJ", "NOTE", "BILLINGSTATUS"]}

After saving in JSON-column of MySql table, this becomes:

{"TIMER_HEADER": [{"REL": " 0", "VER": " 7", "FROMTIMER": "N", "COMPANYNAME": "XXX", "XX!TIMERHDR": "XXTIMERHDR", "IMPORTEDBEFORE": "N", "COMPANYCREATETIME": "12423426"}, {"REL": "Release R", "VER": "Version 6.0", "DATE": "2018-01-20", "PROD": "Qics for Wnows", "TIME": "1516520267", "IIFVER": "1", "XX!HDR": "XXHDR", "ACCNTNT": "N", "ACCNTNTSPLITTIME": "0"}], "COLUMN_HEADER": ["!TIMEACT", "DATE", "JOB", "EMP", "ITEM", "PITEM", "DURATION", "PROJ", "NOTE", "BILLINGSTATUS"]}

I need the same order as I have original, because there is an validation on 3rd party.

Please help. Thanks.

maybe this answer will help somebody [https://stackoverflow.com/a/61519255/10579735](https://stackoverflow.com/a/61519255/10579735) — Oleksandr Hrin, Apr 19 '21 at 12:35

score 15 · Answer 1 · edited Dec 17 '20 at 18:14

15

The items of arrays don't change order in MySQL JSON columns, but object key/value pairs might.

MySQL manual says:

To make lookups more efficient, it 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.

There is no standard that says it has to be in a certain order, so probably the best thing to do is to speak to someone on 3rd party to adjust validation.

edited Dec 17 '20 at 18:14

Matt Kieran

4,174
1
17
17

answered Jan 22 '18 at 13:31

kiks73

3,718
3
25
52

3

Damnit I was so excited to use this new column type but now I know the key-pair values might change order it's useless to my current needs. – Martin Joiner Jun 18 '18 at 13:31
2

I agree with Martin, this completely breaks a lot of use cases; particularly with a LIKE comparison. – Kevinleary.net Oct 04 '18 at 17:51
3

You can change type of the column to TEXT and still get power of JSON parsing, also, in this case, mysql doesn't change order. – dikirill Nov 21 '18 at 21:44
Is there any other way to overcome this order issue? like adding an id or order sequence ? – Mihir Patel Jun 21 '19 at 15:12
@KevinLeary I second that. – revo Feb 04 '20 at 08:07
maybe this answer will help somebody [https://stackoverflow.com/a/61519255/10579735](https://stackoverflow.com/a/61519255/10579735) – Oleksandr Hrin Apr 19 '21 at 12:36

score 0 · Answer 2 · answered Jul 24 '22 at 11:34

0

We are using blob for storage. JSON validation is handled by our application.

Using this blob column, keys are not sorted since it not optimized for JSON.

Please refer the blob docs for details. https://dev.mysql.com/doc/refman/8.0/en/blob.html

answered Jul 24 '22 at 11:34

marlo

6,998
5
28
34

MYSQL JSON column change array order after saving

2 Answers2

Linked