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?

Will B. · Answer 1 · 2021-03-25T21:57:17.603

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.

Does this not just makes dealing with JSON documents a real pain ? I mean in the case when the structure is unknown and nesting is deep. Which exactly when one would turn to JSON IMO. — Geza Turi, Dec 12 '16 at 14:46

score 8 · Accepted Answer · answered Jul 22 '20 at 13:20

8

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.

answered Jul 22 '20 at 13:20

boryn

726
9
10

JSON_MERGE_PATCH will remove keys from the results, for which the key value in the second object is JSON null literal. [doc](https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-merge-patch) – Vedant Jan 18 '22 at 06:58

Bazardshoxer · Answer 3 · 2016-07-14T10:08:24.093

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!

This is real pain IMO. I have submitted a feature request http://bugs.mysql.com/bug.php?id=84167. Maybe if you have minute you could support it by clicking the effects me button : ) Or comment. — Geza Turi, Dec 12 '16 at 16:35

score 0 · Answer 4 · edited Nov 09 '18 at 00:15

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 ?

Hi Gregor, This post is already 2 years old. After reporting this issue / problem at MySQL (see: bugs.mysql.com/bug.php?id=84167), the've made some changed / created new functions for JSON in MySQL. Have a look at the JSON_MERGE_PATCH (Availabline since MySQL 5.7.22), this might be a better solutions for your problem, see: https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-merge-patch — Bazardshoxer, Nov 11 '18 at 11:32

MySQL 5.7+, JSON_SET value in nested path

4 Answers4