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

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

设置指定路径的数据（不管是否存在）。如果有参数为NULL，则返回NULL。

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

3.3.4 JSON_MERGE

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

merge多个json文档。规则如下：

mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]'); +---------------------------------------+ | JSON_MERGE('[1, 2]', '[true, false]') | +---------------------------------------+ | [1, 2, true, false] | +---------------------------------------+ mysql> SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}'); +-------------------------------------------+ | JSON_MERGE('{"name": "x"}', '{"id": 47}') | +-------------------------------------------+ | {"id": 47, "name": "x"} | +-------------------------------------------+ mysql> SELECT JSON_MERGE('1', 'true'); +-------------------------+ | JSON_MERGE('1', 'true') | +-------------------------+ | [1, true] | +-------------------------+ mysql> SELECT JSON_MERGE('[1, 2]', '{"id": 47}'); +------------------------------------+ | JSON_MERGE('[1, 2]', '{"id": 47}') | +------------------------------------+ | [1, 2, {"id": 47}] | +------------------------------------+

 

3.3.5 JSON_REMOVE

JSON_REMOVE(json_doc, path[, path] ...)
移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为NULL，则返回NULL。

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

3.3.6 JSON_UNQUOTE

JSON_UNQUOTE(val)

去掉val的引号。如果val为NULL，则返回NULL。

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

3.4 JSON特性查询 3.4.1 JSON_DEEPTH

 JSON_DEPTH(json_doc)

获取json文档的深度。如果参数为NULL，则返回NULL。

空的json array、json object或标量的深度为1。

mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'); +------------------+------------------+--------------------+ | JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') | +------------------+------------------+--------------------+ | 1 | 1 | 1 | +------------------+------------------+--------------------+ mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]'); +------------------------+------------------------+ | JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') | +------------------------+------------------------+ | 2 | 2 | +------------------------+------------------------+ mysql> SELECT JSON_DEPTH('[10, {"a": 20}]'); +-------------------------------+ | JSON_DEPTH('[10, {"a": 20}]') | +-------------------------------+ | 3 | +-------------------------------+

3.4.2 JSON_LENGTH

JSON_LENGTH(json_doc[, path])

获取指定路径下的长度。如果参数为NULL，则返回NULL。　

长度的计算规则：