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 the result. Returns NULL if any argument is NULL or path, if given, does not 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.

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!

After searching everywhere like many of you I've found the best possible solution listed here: https://forums.mysql.com/read.php?20,647956,647969#msg-647969

From the site: he nodes and subnodes, but doesn't contains any data... So in above example, the object will be like:

{"nodes": {}} 

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

UPDATE table SET attributes = JSON_MERGE(attributes, '{"nodes": {}'), attributes = JSON_SET(attributes, "$.nodes.node2", "Node 2") 

For now, this is working. But it's an weird workaround. Maybe this can be reviewed in previous MySQL versions, so JSON_SET also creates parent-nodes when subnodes are set ?