| Bug #93010 | JSON_SET() does not create intermediate objects | ||
|---|---|---|---|
| Submitted: | 30 Oct 2018 14:40 | Modified: | 30 Oct 2018 20:22 |
| Reporter: | Alfredo Kojima | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 8.0.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[30 Oct 2018 14:42]
Alfredo Kojima
A workaround is to use JSON_MERGE_PATCH() instead of JSON_SET():
mysql> select json_merge_patch(cast('{}' as json), cast('{"a":{"b":42}}' as json));
+----------------------------------------------------------------------+
| json_merge_patch(cast('{}' as json), cast('{"a":{"b":42}}' as json)) |
+----------------------------------------------------------------------+
| {"a": {"b": 42}} |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_merge_patch(cast('{"a":"hello"}' as json), cast('{"a":{"b":42}}' as json));
+---------------------------------------------------------------------------------+
| json_merge_patch(cast('{"a":"hello"}' as json), cast('{"a":{"b":42}}' as json)) |
+---------------------------------------------------------------------------------+
| {"a": {"b": 42}} |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Description: Calling JSON_SET() with a multi-level path will not create intermediate objects if they're missing. For example: select JSON_SET(cast('{}' as json), '$.a.b', 42); returns {}, instead of {"a": {"b": 42}}. select JSON_SET(cast('{"a": 0}' as json), '$.a.b', 42); also fails: returns {}, instead of {"a": {"b": 42}}. How to repeat: mysql> select JSON_SET(cast('{}' as json), '$.a', 42); +-----------------------------------------+ | JSON_SET(cast('{}' as json), '$.a', 42) | +-----------------------------------------+ | {"a": 42} | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> select JSON_SET(cast('{"a":{}}' as json), '$.a', 42); +-----------------------------------------------+ | JSON_SET(cast('{"a":{}}' as json), '$.a', 42) | +-----------------------------------------------+ | {"a": 42} | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql> select JSON_SET(cast('{"a":{}}' as json), '$.a.b', 42); +-------------------------------------------------+ | JSON_SET(cast('{"a":{}}' as json), '$.a.b', 42) | +-------------------------------------------------+ | {"a": {"b": 42}} | +-------------------------------------------------+ 1 row in set (0.00 sec) mysql> select JSON_SET(cast('{"a":{"b":1}}' as json), '$.a.b', 42); +------------------------------------------------------+ | JSON_SET(cast('{"a":{"b":1}}' as json), '$.a.b', 42) | +------------------------------------------------------+ | {"a": {"b": 42}} | +------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select JSON_SET(cast('{}' as json), '$.a.b', 42); +-------------------------------------------+ | JSON_SET(cast('{}' as json), '$.a.b', 42) | +-------------------------------------------+ | {} | +-------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: Introduce a new function that behaves as expected in these cases. mysql> select JSON_SET_OVERWRITE(cast('{}' as json), '$.a.b', 42); and mysql> select JSON_SET_OVERWRITE(cast('{"a": "hello"}' as json), '$.a.b', 42); Should both return: {"a": {"b": 42}}