MySQL 5.7+, JSON_SET value in nested path
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!
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.
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.