Bug #84167 Managing deeply nested JSON documents with JSON_SET, JSON_MERGE
Submitted: 12 Dec 2016 16:33 Modified: 21 Mar 2018 11:17
Reporter: Geza Turi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:mysql Ver 14.14 Distrib 5.7.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: json_merge, json_set

[12 Dec 2016 16:33] Geza Turi
Description:
Updating deeply nested JSON documents is cumbersome.

The problem is that JSON_SET will not work on paths which have not been created before hand.

What works:

    UPDATE sometable
    SET some_json_column = JSON_SET(some_json_column, "$.somepath", "somevalue")
    WHERE ...

What does not work:

    UPDATE sometable
    SET some_json_column = JSON_SET(some_json_column, "$.some_none_existing_path.subpath", "somevalue")
    WHERE ...

JSON_MERGE almost comes to the rescue except unfortunately when it comes to scalar values it will not overwrite them but wraps them into an array and inserts the new value into that newly created array.
As documented here:
https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-mer...

Why is it important:

One of main reason one would consider using the new JSON column type is to be able to store/update data when the data structure is unknown and perhaps deeply nested. 

There are workarounds but they are far from ideal.
See this stackoverflow post:
http://stackoverflow.com/questions/38312370/mysql-5-7-json-set-value-in-nested-path 

How to repeat:
It is not a bug.

Suggested fix:
1. Make JSON_SET create default empty objects if the object does not exist. 
2. Offer an alternative to JSON_MERGE where the scalar values would be overwritten instead of how it works just now with JSON_MERGE whereby scalar value are wrapped into an array.
[14 Dec 2016 9:08] Bazard Shoxer
Fully agree with Geza Turi. This is a real pain when using the JSON column type for unknown data-structures / data-structures which can vary for every single record.

The workaround I posted in stackoverflow works, also the one from Fyrye, but these aren't the most elegant solutions. It would by nice if MySQL came up with a solution for this in a more constructive way.
[21 Mar 2018 11:17] Knut Anders Hatlen
Thanks for the enhancement request.

This use case should be covered now by the JSON_MERGE_PATCH function that was added in WL#9692 - https://dev.mysql.com/worklog/task/?id=9692.

    UPDATE sometable
    SET some_json_column = JSON_MERGE_PATCH(some_json_column, '{"some_none_existing_path":{"subpath":"somevalue"}}')
    WHERE ...