| 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: | |
| 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 | ||
[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 ...
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.