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.

Answer 2

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.

Answer 3

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!