why is mysql saving json in reverse order ? how to fix it

Question

this is the code to insert json in my sql

function insert_ema($json){

$a= new Sql();
$b=$a->connection;
$sql = "INSERT INTO ema (ema_ten) VALUES ('$json')";

if ($b->query($sql) === TRUE) {
echo PHP_EOL." New record created successfully \n";
} else {
echo PHP_EOL." Error: " . $sql . "<br>" . $b->error;
}
$b->close();


;}

insert_ema('{"firstName":"John", "lastName":"Doe","3":"Jo", "4":"Do"}');

+----------------------------------------------------------------+----+
| ema_ten                                                        | id |
+----------------------------------------------------------------+----+
| {"3": "Jo", "4": "Do", "lastName": "Doe", "firstName": "John"} |  1 |
| {"3": "Jo", "4": "Do", "lastName": "Doe", "firstName": "John"} |  2 |
+----------------------------------------------------------------+----+

the sql saved above is in reverse order!! how can i fix it

The reason why I want to persevere order is, I want to be able to convert the json to an array and use pop .

I think MySQL should save arrays and also sort this issue.

What difference does it make? Mysql makes storage decisions about your json data for faster lookup/retrieval just like it would when you insert rows (row insert order is not preserved). If you need to preserve order, then store the json as a string. — JNevill, Aug 01 '22 at 20:59
By the way, don't interpolate variables into your SQL statement like you show above. Use query parameters. — Bill Karwin, Aug 01 '22 at 21:04
Putting variables inside strings. In this example: `"INSERT INTO ema (ema_ten) VALUES ('$json')"` the variable `$json` is interpolated, so the value of that variable becomes part of the string. This is dangerous when the string becomes an SQL statement, because it leads to SQL injection security vulnerabilities. If you want to read more about this, see my answer here: https://stackoverflow.com/questions/601300/what-is-sql-injection/601524#601524 — Bill Karwin, Aug 03 '22 at 05:32
Oh it’s ok I normally use pdo but this is a closed system so no chance of an injection, but thanks — amar essa, Aug 03 '22 at 05:35
SQL injection is not just a problem with malicious attackers. It can occur by accident in a closed system. For example, what if your `$json` variable contains some string content that has an apostrophe (`'`)? — Bill Karwin, Aug 03 '22 at 15:29
No chance because I’m building the json string and know what’s going into it , but thanks again — amar essa, Aug 04 '22 at 16:23

Bill Karwin · Answer 1 · 2022-08-04T16:25:32.867

https://dev.mysql.com/doc/refman/8.0/en/json.html 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.

This mean you should not depend on any particular sort order of the keys in a JSON object. JSON arrays have order, but the keys of JSON objects don't.

JSON objects are equal if their keys and respective values are the same, regardless of order:

mysql> select cast('{"firstName":"John", "lastName":"Doe","3":"Jo", "4":"Do"}' as json) 
  = cast('{"3": "Jo", "4": "Do", "lastName": "Doe", "firstName": "John"}' as json) 
  as is_equal;
+----------+
| is_equal |
+----------+
|        1 |
+----------+

Re your comment:

The point of the above example is that you can't make MySQL store keys in your intended order. MySQL's implementation of JSON doesn't do that. It rearranges JSON object keys to make it more efficient for lookups. You don't get a say in this.

JSON arrays can be ordered. So your only option to preserve order is to use an array, where each element of the array is an object with a single key:

[{"firstName":"John"}, {"lastName":"Doe"}, {"3":"Jo"}, {"4":"Do"}]

I understand this is not what you asked for, but what you asked for cannot be achieved in MySQL.

Read about the [CAST() function](https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast). It typecasts to a data type. If I were to compare the strings without casting, they would compare as string types, not as JSON types, and the strings are not equal. But as JSON documents, they are equal even if the order of their keys is different. — Bill Karwin, Aug 03 '22 at 15:31
How is that going to solve the problem? Like I said I want the json in the same order so I can pop the json after retrieving it — amar essa, Aug 04 '22 at 16:21

why is mysql saving json in reverse order ? how to fix it

1 Answers1