MySQL 5.7+, JSON_SET value in nested path

Question

For a recent development project, we're using MySQL 5.7, so we can take advantages of the latest JSON-functions...

I'm building an UPDATE-query, where an nested json-object should be inserted / added into the attributes-column, of type JSON, see query below.

UPDATE `table` SET `table`.`name` = 'Test',
    `table`.`attributes` = JSON_SET(
         `table`.`attributes`,
         "$.test1", "Test 1",
         "$.test2.test3", "Test 3"
     )

When I execute this query, the attributes-field contains the data

{"test1": "Test 1"} 

instead of the wanted

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

Also tried to use JSON_MERGE, but when I execute it multiple times, it creates an JSON-object like

{"test1": ["Test 1", "Test 1", "Test 1"... etc.], "test2": {"test3": ["Test 3", "Test 3", "Test 3"... etc.]}}

So, JSON_SET isn't working when nodes don't exist? JSON_MERGE merges till infinity?

The keys used in the JSON-object can be defined by the user, so it's not possible to create an empty JSON-object for all possible keys. Do we really need to execute an JSON_CONTAINS / JSON_CONTAINS_PATH query before each UPDATE query to determine if we need to use JSON_SET or JSON_MERGE / JSON_APPEND?

We're looking for a way to have a query which always works, so when "$.test4.test5.test6" is given, it will extend the current JSON-object, adding the full path... How can this be done?

Answer 1

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.