Howto user JSON_SET to update json in multiple records including nested data...

[Fape Spold]

Hi there,

we're currently using MySQL 5.7.12 for our latest development. The JSON-type columns are a nice addition to the MySQL-features. Unfortunately, we're having some problems using the JSON_SET function...

For example: We're having a table like this:

id | name | attributes
1 | foo | {"foo": "bar", "nodes": {"node1": "Node 1"}}
2 | bar | {"foo": "bar", "nodes": {"node2": "Node 2"}}
3 | foobar | {"foo": "bar"}

So, we have 3 records, all of them have a name and we use the column 'attributes' for 'free data'.

Now, we want to add "Node 2" to ALL of these items, so we execute the following query:

UPDATE table SET attributes = JSON_SET(attributes, "$.nodes.node2", "Node 2") WHERE 1

After executing the query, we're getting the message that 1 row is affected. The first row is edited and "Node 2" is added. The second row is still the same, because "Node 2" was already there, the third row is still the same, no "Node 2" is added because... because why? Because there was no "nodes" object found? The table now looks like this:

id | name | attributes
1 | foo | {"foo": "bar", "nodes": {"node1": "Node 1", "node2": "Node 2"}}
2 | bar | {"foo": "bar", "nodes": {"node2": "Node 2"}}
3 | foobar | {"foo": "bar"}

I already searched for solutions online and found commands like JSON_MERGE and JSON_APPEND. I tried a query like:

UPDATE table SET attributes = JSON_MERGE(attributes, '{"nodes": {"node2": "Node 2"}}') WHERE 1

This doesn't give the wanted result, because that will make the second item look like this:

id | name | attributes
2 | bar | {"foo": "bar", "nodes": {"node2": ["Node 2", "Node 2"]}}

In this case, we have 3 items, so we can create a loop, check all items with
JSON_CONTAINS / JSON_CONTAINS_PATH() and check if the "nodes" element is available and based on that, use a JSON_SET or JSON_MERGE, but what if we've got thousands of rows?

How to add/append/update a sub-node (and create if not exsits) in multiple rows? And without having to select, analyse and change them item by item...?