JSON_REMOVE
描述
JSON_REMOVE 函数用于从 JSON 文档中删除数据并返回结果。
语法
JSON_REMOVE (<json_object>, path[, path] ...)
参数
<json_object>JSON 类型表达式，被删除的目标。<path>String 类型表达式，路径参数按从左到右的顺序进行求值。对一个路径进行求值所产生的 JSON 文档成为下一个路径求值的新值。
返回值
Nullable(JSON)返回被删除后的 JSON 对象。
示例
- 路径不存在
SELECT JSON_REMOVE('{"a": 1, "b": 2, "c": 3}', '$.d');
+------------------------------------------------+
| JSON_REMOVE('{"a": 1, "b": 2, "c": 3}', '$.d') |
+------------------------------------------------+
| {"a":1,"b":2,"c":3} |
+------------------------------------------------+
<path>指向的值在 JSON 对象中删除
SELECT JSON_REMOVE('{"Name": "Jack", "Gender": "Male", "Age": 20}', '$.Age');
+-----------------------------------------------------------------------+
| JSON_REMOVE('{"Name": "Jack", "Gender": "Male", "Age": 20}', '$.Age') |
+-----------------------------------------------------------------------+
| {"Name":"Jack","Gender":"Male"} |
+-----------------------------------------------------------------------+
- 指定多个路径从 JSON 对象的多个位置删除数据
SELECT JSON_REMOVE('[1, 2, 3, 4, 5]', '$[3]'), JSON_REMOVE('[1, 2, 3, 4, 5]', '$[1]', '$[3]');
+----------------------------------------+------------------------------------------------+
| JSON_REMOVE('[1, 2, 3, 4, 5]', '$[3]') | JSON_REMOVE('[1, 2, 3, 4, 5]', '$[1]', '$[3]') |
+----------------------------------------+------------------------------------------------+
| [1,2,3,5] | [1,3,4] |
+----------------------------------------+------------------------------------------------+
- 更大的 JSON 对象
SELECT JSON_REMOVE('{"Person": {"Name": "Jack","Age": 20,"Hobbies": ["Eating", "Sleeping", "Base Jumping"]}}', '$.Person.Age', '$.Person.Hobbies[2]');
+------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_REMOVE('{"Person": {"Name": "Jack","Age": 20,"Hobbies": ["Eating", "Sleeping", "Base Jumping"]}}', '$.Person.Age', '$.Person.Hobbies[2]') |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| {"Person":{"Name":"Jack","Hobbies":["Eating","Sleeping"]}} |
+------------------------------------------------------------------------------------------------------------------------------------------------+
