MySQl Modify JSON Values

方法	描述
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
JSON_INSERT(json_doc, path, val[, path, val] ...)
JSON_MERGE(json_doc, json_doc[, json_doc] ...)
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
JSON_REMOVE(json_doc, path[, path] ...)
JSON_REPLACE(json_doc, path, val[, path, val] ...)
JSON_SET(json_doc, path, val[, path, val] ...)
JSON_UNQUOTE(json_val)

1.JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

SET @j = '["a", ["b", "c"], "d"]';
SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1); -- ["a", ["b", "c", 1], "d"];
SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2); -- [["a", 2], ["b", "c"], "d"]
SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3); -- ["a", [["b", 3], "c"], "d"]

SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x'); -- {"a": 1, "b": [2, 3, "x"], "c": 4}
SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y'); -- {"a": 1, "b": [2, 3], "c": [4, "y"]}

SET @j = '{"a": 1}';
SELECT JSON_ARRAY_APPEND(@j, '$', 'z'); -- [{"a": 1}, "z"]

2.JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x'); -- ["a", "x", {"b": [1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x'); -- ["a", {"b": [1, 2]}, [3, 4], "x"]
SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x'); -- ["a", {"b": ["x", 1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y'); -- ["a", {"b": [1, 2]}, [3, "y", 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y'); -- ["x", "a", {"b": [1, 2]}, [3, 4]]

3.JSON_INSERT(json_doc, path, val[, path, val] ...)

SET @j = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]'); -- {"a": 1, "b": [2, 3], "c": "[true, false]"} //注意a节点没有更新
SELECT JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON)); -- {"a": 1, "b": [2, 3], "c": [true, false]} 

4.JSON_MERGE(json_doc, json_doc[, json_doc] ...)

SELECT JSON_MERGE('[1, 2]', '[true, false]');

5.JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]'); -- [true, false]
SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}'); -- {"id": 47, "name": "x"}
SELECT JSON_MERGE_PATCH('1', 'true');  -- true
SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');  -- {"id": 47}
SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }'); -- {"a": 5, "b": 2, "c": 4, "d": 6}
SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}'); -- {"a": 1}
SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}'); -- {"a": {"x": 1, "y": 2}}  

6.JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)

SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]'); -- [1, 2, true, false]
SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}'); -- {"id": 47, "name": "x"}
SELECT JSON_MERGE_PRESERVE('1', 'true'); -- [1, true]
SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}'); -- [1, 2, {"id": 47}]
SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }'); -- {"a": [1, 3], "b": 2, "c": 4}
SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }','{ "a": 5, "d": 6 }'); -- {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}

7.JSON_REMOVE(json_doc, path[, path] ...)

SET @j = '["a", ["b", "c"], "d"]';
SELECT JSON_REMOVE(@j, '$[1]'); -- ["a", "d"]

8.JSON_REPLACE(json_doc, path, val[, path, val] ...)

SET @j = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3]}

9.JSON_SET(json_doc, path, val[, path, val] ...)

SET @j = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');  -- {"a": 10, "b": [2, 3], "c": "[true, false]"}
SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]'); -- {"a": 1, "b": [2, 3], "c": "[true, false]"}
SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3]}

10.JSON_UNQUOTE(json_val)

SET @j = '"abc"';
SELECT @j, JSON_UNQUOTE(@j);  -- "abc" | abc
SET @j = '[1, 2, 3]';
SELECT @j, JSON_UNQUOTE(@j); -- [1, 2, 3] | [1, 2, 3]