MySQL 5.7+, JSON_SET value in nested path

As of MySQL version 5.7.13, assuming you desire an end result of

{"test1": "Test 1", "test2": {"test3": "Test 3"}}

In your example the attributes column that is being updated is set to {"test1": "Test 1"}

Looking at your initial UPDATE query, we can see $.test2.test3 does not exist.So it can not be set as

JSON_SET() Inserts or updates data in a JSON document and returns theresult. Returns NULL if any argument is NULL or path, if given, doesnot locate an object.

Meaning MySQL can add $.test2, but since $.test2 is not an object, MySQL can not add on to $.test2.test3.

So you would need to define $.test2 as a json object by doing the following.

mysql> SELECT * FROM testing;+----+---------------------+| id | attributes          |+----+---------------------+|  1 | {"test1": "Test 1"} |+----+---------------------+1 row in set (0.00 sec)

mysql> UPDATE testing    -> SET attributes = JSON_SET(    ->     attributes,    ->     "$.test1", "Test 1",    ->     "$.test2", JSON_OBJECT("test3", "Test 3")    -> );Query OK, 1 row affected (0.03 sec)Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM testing;+----+---------------------------------------------------+| id | attributes                                        |+----+---------------------------------------------------+|  1 | {"test1": "Test 1", "test2": {"test3": "Test 3"}} |+----+---------------------------------------------------+1 row in set (0.00 sec)

So instead of relying on the MySQL dot notation, you would need to explicitly tell MySQL that the key exists as a JSON object.

This is similar to how PHP also defines non-existent object property values.

$a = (object) ['test1' => 'Test 1'];$a->test2->test3 = 'Test 3';//PHP Warning:  Creating default object from empty value

To get rid of the error, you would need to first define $a->test2 as an object.

$a = (object) ['test1' => 'Test 1'];$a->test2 = (object) ['test3' => 'Test 3'];

Alternatively you could test and create the objects prior to using the dot notation, to set the values. Though with larger datasets this may be undesirable.

mysql> UPDATE testing    -> SET attributes = JSON_SET(    ->     attributes, "$.test2", IFNULL(attributes->'$.test2', JSON_OBJECT())    -> ),    -> attributes = JSON_SET(    ->     attributes, "$.test4", IFNULL(attributes->'$.test4', JSON_OBJECT())    -> ),    -> attributes = JSON_SET(    ->     attributes, "$.test4.test5", IFNULL(attributes->'$.test4.test5', JSON_OBJECT())    -> ),    -> attributes = JSON_SET(    ->     attributes, "$.test2.test3", "Test 3"    -> );Query OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM testing;+----+---------------------------------------------------------------------------+| id | attributes                                                                |+----+---------------------------------------------------------------------------+|  1 | {"test1": "Test 1", "test2": {"test3": "Test 3"}, "test4": {"test5": {}}} |+----+---------------------------------------------------------------------------+1 row in set (0.00 sec)

Though in either case if the original data is not provided the JSON_OBJECT function call will empty out the nested object's property value(s). But as you can see from the last JSON_SET query, $.test1 was not provided in the definition of attributes, and it remained intact, so those properties that are unmodified can be omitted from the query.

Now, as of MySQL version 5.7.22 the easiest way is to use JSON_MERGE_PATCH like this:

UPDATE `table` SET `attributes` = JSON_MERGE_PATCH(`attributes`, '{"test2": {"test3": "Test 3"}, "test4": {"test5": {}}}')

which gives the expected result of {"test1": "Test 1", "test2": {"test3": "Test 3"}, "test4": {"test5": {}}} as in your example.

Fyrye, thanks for the awnser, appreciate it a lot! Because of the data hasn't a fixed structure and can be different for every single record, I needed a solution where I could generate a query which would automatically generate the total JSON-object in a single query.

I really like your solution using the JSON_SET(attributes, "$.test2", IFNULL(attributes->'$.test2',JSON_OBJECT())) method. Because I continued my search, I also figured out a solution myself using JSON_MERGE function.

When i'm executing an update, i'm using JSON_MERGE to merge an empty JSON-object onto the field in the database, for all keys with subnodes, so the're available in the JSON-field in the database and after that, using JSON_SET to update values. So the complete query looks like this:

UPDATE table SET    -> attributes = JSON_MERGE(    -> attributes, '{"test2": {}, "test4": {"test5": {}}}'),    -> attributes = JSON_SET(attributes, "$.test2.test3", "Test 3");

After executing this query, the result will look something like this:

 mysql> SELECT * FROM testing; +----+---------------------------------------------------------------------------+ | id | attributes                                                                | +----+---------------------------------------------------------------------------+ |  1 | {"test1": "Test 1", "test2": {"test3": "Test 3"}, "test4": {"test5": {}}} | +----+---------------------------------------------------------------------------+ 1 row in set (0.00 sec)

I don't know which method is better at this time, both work for now. Will do some speed tests in the future to check how they preform when 1 update 10.000 rows!