Mysql JSON相关函数与操作

最新推荐文章于 2024-04-24 17:52:26 发布

nbskycity

最新推荐文章于 2024-04-24 17:52:26 发布

阅读量675

点赞数

分类专栏： Mysql 文章标签： mysql mysql JSON 数据库JSON操作 Mysql JSON操作

本文链接：https://blog.csdn.net/nbskycity/article/details/107426735

版权

Mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

Mysql JSON相关函数与操作

JSON相关函数概览

名称	描述
->	评估路径后从JSON列返回值；等效于JSON_EXTRACT（）。
->>	评估路径并取消引用结果后，从JSON列返回值；等效于JSON_UNQUOTE（JSON_EXTRACT（））。
JSON_ARRAY()	创建JSON数组
JSON_ARRAY_APPEND()	将数据附加到JSON文档
JSON_ARRAY_INSERT()	插入JSON数组
JSON_CONTAINS()	JSON文档是否在路径中包含特定对象
JSON_CONTAINS_PATH()	JSON文档是否在路径中包含任何数据
JSON_DEPTH()	JSON文档的最大深度
JSON_EXTRACT()	从JSON文档返回数据
JSON_INSERT()	将数据插入JSON文档
JSON_KEYS()	JSON文档中的键数组
JSON_LENGTH()	JSON文档中的元素数
JSON_MERGE()（已弃用）	合并JSON文档，保留重复的键。JSON_MERGE_PRESERVE（）的已弃用同义词
JSON_MERGE_PATCH()	合并JSON文档，替换重复键的值
JSON_MERGE_PRESERVE()	合并JSON文档，保留重复的键
JSON_OBJECT()	创建JSON对象
JSON_OVERLAPS()（8.0.17引入）	比较两个JSON文档，如果它们具有相同的键值对或数组元素，则返回TRUE（1），否则返回FALSE（0）
JSON_PRETTY()	以易于阅读的格式打印JSON文档
JSON_QUOTE()	引用JSON文档
JSON_REMOVE()	从JSON文档中删除数据
JSON_REPLACE()	替换JSON文档中的值
JSON_SCHEMA_VALID()（8.0.17引入）	根据JSON模式验证JSON文档；如果文档针对架构进行验证，则返回TRUE / 1；否则，则返回FALSE / 0
JSON_SCHEMA_VALIDATION_REPORT()（8.0.17引入）	根据JSON模式验证JSON文档；以JSON格式返回有关验证结果的报告，包括成功或失败以及失败原因
JSON_SEARCH()	JSON文档中值的路径
JSON_SET()	将数据插入JSON文档
JSON_STORAGE_FREE()	部分更新后，JSON列值的二进制表示形式中的可用空间
JSON_STORAGE_SIZE()	用于存储JSON文档的二进制表示的空间
JSON_TABLE()	从JSON表达式返回数据作为关系表
JSON_TYPE()	JSON值类型
JSON_UNQUOTE()	取消引用JSON值
JSON_VALID()	JSON值是否有效
JSON_VALUE()（8.0.21引入）	在提供的路径指向的位置从JSON文档中提取值；以VARCHAR（512）或指定的类型返回此值
MEMBER OF()（8.0.17引入）	如果第一个操作数与作为第二个操作数传递的JSON数组的任何元素匹配，则返回true（1），否则返回false（0）

主要函数解析与示例

1. JSON_EXTRACT() （->）

获取JSON字段中的值，可以获取JSONArray，也可以获取JSONObject。

列入有以下字段：

+---------------------------------------------------------------------------------------------+
| device_value                                                                                |
+---------------------------------------------------------------------------------------------+
| {"bty": 36, "r_c": 0.0, "r_t": 25.35, "r_v": 0, "sign": 191, "time": "2020-07-18 17:43:51"} |
| {"bty": 36, "r_c": 0.0, "r_t": 25.91, "r_v": 0, "sign": 193, "time": "2020-07-18 17:43:51"} |
+---------------------------------------------------------------------------------------------+

可以如下获取JSON中value：

SELECT device_value -> '$.bty' FROM {tableName} ;

+-------------------------+
| device_value -> '$.bty' |
+-------------------------+
| 36                      |
| 36                      |
+-------------------------+

JSONArray同样也可以操作：

+---------------------------------------------------------------------------------------------+
| device_id_array                                                                             |
+---------------------------------------------------------------------------------------------+
| ["id_001", "id_002"] |
| ["id_101", "id_102"] |
+---------------------------------------------------------------------------------------------+

SELECT device_value -> '$[0]' FROM {tableName} ;

+-------------------------+
| device_value -> '$[0]' |
+-------------------------+
| id_001                  |
| id_101                  |
+-------------------------+

2. JSON_CONTAINS(target, candidate[, path])

查询JSON字段是否包含，包含会返回1，不包含返回0.

[, path]，可选项，可指定路径比较

例：

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS(@j, '1', '$.a');            //  注意，如果验证数值包含，必须用`''`；如果验证字符串包含，必须`""`
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             1 |
+-------------------------------+

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

mysql> SELECT JSON_CONTAINS(@j2, "a", '$[2]');     //验证数值2位置是否有"a"
+-------------------------------+
| JSON_CONTAINS(@j2, "a", '$[2]') |
+-------------------------------+
|                             0 |
+-------------------------------+

3. JSON_REMOVE(json_doc, path[, path] …)

移除JSON字段某个位置的值，现在还不支持移除JSON字段指定值，不过我们可以结合JSON_SEARCH()来实现移除指定值，在下文应用中会讲解。

path不可以是$或包含*或** 的通配符

例：

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

4. JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)

将值附加到JSON字段中指定数组的末尾并返回结果。

指定位置（path）的末尾插入（val），如果指定位置不是数组，将会被转为数组

例：

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

mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
+-------------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
+-------------------------------------+
| ["a", [["b", 3], "c"], "d"]         |
+-------------------------------------+

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

mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
+--------------------------------------+
| JSON_ARRAY_APPEND(@j, '$.c', 'y')    |
+--------------------------------------+
| {"a": 1, "b": [2, 3], "c": [4, "y"]} |
+--------------------------------------+

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

如果你想在JSONArray数组末尾插入，可以使用通配符'$'进行操作：

mysql> SET @j = '[1, 2, 3]';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', '4');
+---------------------------------+
| JSON_ARRAY_APPEND(@j, '$', '4') |
+---------------------------------+
| [1, 2, 3, 4]                 |
+---------------------------------+

5. JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])

返回JSON字段中给定字符串的路径；如果不存在，将返回NULL。

‘one’：搜索在第一个匹配项后终止，并返回一个路径字符串。未定义首先考虑哪个匹配。

‘all’：搜索将返回所有匹配的路径字符串，以便不包括重复的路径。如果有多个字符串，它们将自动包装为一个数组。数组元素的顺序是不确定的。

例：

mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]"                        |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'ghi') |
+-------------------------------+
| NULL                          |
+-------------------------------+

注意：搜索的JSON字段必须是字符串类型，数值类型可能导致匹配失败（Ver 8.0.20 for Win64 on x86_64 下测试）

mysql> SET @j = '[1, 2, 3]';

//以下用数值与字符串都无法匹配到
mysql> SELECT JSON_SEARCH(@j, 'one', '1');
+-----------------------------+
| JSON_SEARCH(@j, 'one', '1') |
+-----------------------------+
| NULL                        |
+-----------------------------+
mysql> SELECT JSON_SEARCH(@j, 'one', "1");
+-----------------------------+
| JSON_SEARCH(@j, 'one', "1") |
+-----------------------------+
| NULL                        |
+-----------------------------+

mysql> SET @j = '["1", "2", "3"]';

mysql> SELECT JSON_SEARCH(@j, 'one', '1');
+-----------------------------+
| JSON_SEARCH(@j, 'one', '1') |
+-----------------------------+
| "$[0]"                      |
+-----------------------------+
mysql> SELECT JSON_SEARCH(@j, 'one', "1");
+-----------------------------+
| JSON_SEARCH(@j, 'one', "1") |
+-----------------------------+
| "$[0]"                      |
+-----------------------------+

应用

1. 删除JSONArray中指定值

正如上文所说，Mysql现在还不支持删除JSON字段中指定的值，但是我们可以利用JSON_SEARCH()和JSON_REMOVE()两个方法来达到目的。

1) 使用`JSON_SEARCH()`定位需要删除值的位置

// 将字段存储都设置为字符串，便于匹配查找，数值类型可能导致匹配失败
mysql> SET @j = '["1", "2", "3"]';
mysql> SELECT JSON_SEARCH(@j, 'one', "1");
+-----------------------------+
| JSON_SEARCH(@j, 'one', "1") |
+-----------------------------+
| "$[0]"                      |
+-----------------------------+

2) 使用`JSON_REMOVE()`删除指定位置的值

SELECT JSON_REMOVE(@j, REPLACE(JSON_SEARCH(@j, 'one', '1'), '"', ''));  //JSON_SEARCH() 查询结果是带""的下标信息，需要将""去掉
+----------------------------------------------------------------+
| JSON_REMOVE(@j, REPLACE(JSON_SEARCH(@j, 'one', '1'), '"', '')) |
+----------------------------------------------------------------+
| ["2", "3"]                                                     |
+----------------------------------------------------------------+