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 — Oleksandr Hrin
– Oleksandr Hrin, Commented Apr 19, 2021 at 12:35

Matt Kieran · Accepted Answer · 2020-12-17 18:14:36Z

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, 2020 at 18:14

Matt Kieran

4,2201 gold badge21 silver badges17 bronze badges

answered Jan 22, 2018 at 13:31

kiks73

3,8383 gold badges30 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Martin Joiner Over a year ago

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.

Kevinleary.net Over a year ago

I agree with Martin, this completely breaks a lot of use cases; particularly with a LIKE comparison.

dikirill Over a year ago

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.

Mihir Patel Over a year ago

Is there any other way to overcome this order issue? like adding an id or order sequence ?

revo Over a year ago

@KevinLeary I second that.

|

marlo · Accepted Answer · 2022-07-24 11:34:58Z

1

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, 2022 at 11:34

marlo

8,3465 gold badges31 silver badges35 bronze badges

Comments

Hitesh Garg · Accepted Answer · 2024-09-02 08:38:15Z

I might be late to answer this but I have found a workaround that works in a paticular case in SQL:

Suppose your column type is longtext and you are storing JSON data in this column.

Now, there are 2 ways to manipulate data in this column, i.e. either using JSON_SET or utilising the fact that this is longtext column and not a JSON type column.

Suppose, our use case is to add a new key-value pair in this column, we can do it in 2 ways as below:

Method 1: Using JSON_SET but this will not preserve the order of existing keys in the JSON

UPDATE table_name SET column_name = JSON_SET(column_name, '$.key', value)

Method 2: Using Concat since our original column type is longtext and this will also preserve the order of existing keys in your JSON

UPDATE table_name SET column_name = 
CONCAT(LEFT(column_name, LENGTH(column_name) - 1),',"key": value}')

Collectives™ on Stack Overflow

MYSQL JSON column change array order after saving

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related