Mysql中Json相关的函数使用

目录

• 一、前言
• 二、创建jsON文本的函数
• 2.1.JSON_ARRAY（转换json数组）
• 2.2.JSON_OBJECT（转换json对象）
• 2.3.JSON_QUOTE（转义字符串）
• 三、搜索JSON文本的函数
• 3.1.JSON_CONTAINS（json当中是否包含指定value）
• 3.2.JSON_CONTAINS_PATH（是否包含某个PATH）
• 3.3.JSON_EXTRACT 和 -> （根据key取值）
• 3.4.JSON_UNQUOTE(JSON_EXTRACT()) 和 ->> （无引号提取）
• 3.5.JSON_KEYS（获取json当中key数组）
• 3.6.JSON_OVERLAPS（判断两个json是否存在同样的的key value）
• 3.8.JSON_VALUE（根据key取值，如果没找到可以给默认值，如果找到了可以转换想要的数据类型）
• 3.9.MEMBER OF（查看数组是否有某个元素）
• 四、修改JSON文本的函数
• 4.1.JSON_ARRAY_APPEND（在指定的数组位置末尾追加元素，假如指定的位置不是数组追加完过后就变成了数组）
• 4.2.JSON_ARRAY_INSERT（在指定的数组位置后追加元素）
• 4.3.JSON_INSERT（向指定path添加元素，path有值不覆盖，没值就添加）
• 4.5.JSON_MERGE（多个json合并）
• 4.6.JSON_MERGE_PATCH
• 4.7.JSON_MERGE_PRESERVE
• 4.8.JSON_REMOVE（根据path移除）
• 4.9.JSON_REPLACE（替换指定path的值）
• 5.0.JSON_SET（有则覆盖，没有则新增）
• 五、返回JSON文本属性的函数
• 5.1.JSON_DEPTH（返回 JSON 文档的最大深度）
• 5.2.JSON_LENGTH（返回长度）
• 5.3.JSON_TYPE（获取json的类型）
• 5.4.JSON_VALID（判断是否是有效json）
• 六、JSON工具函数
• 6.1.JSON_PRETTY（json格式化输出）
• 6.2.JSON_STORAGE_SIZE（计算占用的存储空间，单位字节）
• 七、JSON聚合函数
• 7.1.JSON_ARRAYAGG（配合GROUP BY进行分组，得到的是数组）
• 7.2.JSON_OBJECTAGG（配合GROUP BY进行分组，得到的是对象）

一、前言

从5.7开始，mysql开始支持json类型，用于存储JSON数据。提供数据类型的同时也提供了很多关于json的函数供我们使用，本篇文章基本上都来源于官网;https://dev.mysql.com/doc/refman/8.0/ja/json-function-reference.html

二、创建JSON文本的函数

2.1.JSON_ARRAY（转换json数组）

• 描述：JSON_ARRAY()接受一个值列表(可能为NULL)，并返回一个包含这些值的JSON数组
• 语法：JSON_ARRAY([val[, val] ...])

参数 val 表示输入的值。

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------+
| [1, "abc", null, true, "10:48:25.000000"]   |
+---------------------------------------------+
1 row in set (0.05 sec)
mysql> SELECT JSON_ARRAY(null);
+------------------+
| JSON_ARRAY(null) |
+------------------+
| [null]           |
+------------------+
1 row in set (0.00 sec)

2.2.JSON_OBJECT（转换json对象）

• 描述：JSON_OBJECT()接受一个键值对(可以为空)列表，并返回一个包含这些键值对的JSON对象
• 语法：JSON_OBJECT([key, val[, key, val] ...])

key, val 表示一个键值对。如果参数数量为奇数，则会发生报错。

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------+

2.3.JSON_QUOTE（转义字符串）

• 描述：JSON_QUOTE()通过用双引号字符包裹 字符串 并转义内部引号和其他字符，将字符串引用为 JSON 值，然后将结果作为 utf8mb4 字符串返回。如果参数为NULL则返回NULL。
• 语法：JSON_QUOTE(string)

string 表示要引用的字符串，参数要用单引号括住，如果参数为 NULL，则返回 NULL。

mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
+--------------------+----------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"null"') |
+--------------------+----------------------+
| "null"             | "\"null\""           |
+--------------------+----------------------+
mysql> SELECT JSON_QUOTE('[1, 2, 3]');
+-------------------------+
| JSON_QUOTE('[1, 2, 3]') |
+-------------------------+
| "[1, 2, 3]"             |
+-------------------------+

转义字符一般以反斜杠符号\开头，用来说明后面的字符不是字符本身的含义，而是表示其它的含义。MySQL 中常见的转义字符如下表所示：

三、搜索JSON文本的函数

3.1.JSON_CONTAINS（json当中是否包含指定value）

• 描述：该函数用于检验指定 JSON 文档是否包含在目标 JSON 文档中，或者是否在目标文档的指定路径上找到指定元素（如果提供了 path参数）。如果指定 JSON 文档包含在目标 JSON 文档中，该函数返回 1，否则返回 0。
• 语法：JSON_CONTAINS(target, candidate[, path])

参数 target 表示目标 JSON 文档，参数 candidate 用于指定 JSON 文档。

如果任意参数为 NULL，或者 path 参数未被识别为目标文档的一部分，则返回 NULL。

注意：target和candidate参数要使用单引号括住

以下情况都会发生报错：

• candidate 或 target 参数不是有效的 JSON 文档。
• path 参数不是有效的路径表达式。
• path 参数包含 * 或 ** 通配符。

比较的对象：

• 如果两个json都是数值类型，可以直接进行比较。
• 对于数组，当 candidate 数组的所有元素都包含在 target 数组中时，才会返回1
• 对于对象，当 candidate 对象的所有 key 都需要包含在 target 对象中，而且每个 key 对应的 value 也要和目标对象对应的 key 相同时，candidate 对象才算包含在 target 对象中。

mysql> SELECT JSON_CONTAINS( '{ "a" : 1, "b" : "2", "c" : { "d" : 4 }}', '1', '$.a' ) as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS( '{ "a" : 1, "b" : "2", "c" : { "d" : 4 }}', '"2"', '$.b' ) as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS( '{ "a" : 1, "b" : 2, "c" : { "d" : 4 }}', '{ "d" : 4 }', '$.c' ) as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

判断json数据类型当中的数组，是否包含某个元素：

实战当中可使用：SELECT json_contains( CAST( declaration_info -> '$.intellectual.intellectualProperty' AS JSON ), json_array( 2 ) from 表;

3.2.JSON_CONTAINS_PATH（是否包含某个PATH）

• 描述：该函数用于检验一个或多个指定的路径（假如json是对象，那这里的路径就是指的json当中的key值）是否包含于目标 JSON 文档中。如果包含在目标 JSON 文档中，该函数返回 1，否则返回 0。
• 语法：JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

参数 json_doc 用于指定 JSON 文档，path 为路径参数。如果任何参数为 NULL，则返回 NULL。

对于 one_or_all 参数，可选值如下：

• ‘one’：如果文档中至少存在一个路径，则返回 1，否则返回 0。
• ‘all’：如果文档中存在所有路径，则返回 1，否则返回 0。

以下情况都会发生报错：

• json_doc 参数不是有效的 JSON 文档。
• 任意 path 参数不是有效的路径表达式。
• one_or_all 参数不为 ‘one’ 或者 ‘all’

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+

3.3.JSON_EXTRACT 和 -> （根据key取值）

• 描述：该函数用于从 JSON 文档中指定的路径返回数据。如果任何参数为 NULL 或在文档中没有定位到路径，则返回 NULL。
• 语法：JSON_EXTRACT(json_doc, path[, path] ...)
• -> 跟 JSON_EXTRACT 函数 都可以用来根据key值取value，他两个其实就是等价的。
• -> 语法：（5.7.9版本开始支持的）：json列名->"path" （这个好像是不能直接使用json的，只能使用存储好json的列名）
• path 参数，还可通过 .* 获取对象中的所有元素。

参数 json_doc 用于指定 JSON 文档，path 为路径参数。

如果参数 json_doc 不是有效的 JSON 文档或任意 path 参数不是有效的路径表达式，则会发生错误。

返回值由 path 参数的所有匹配值组成。如果这些参数返回多个值，则匹配值将自动包裹为一个数组，顺序与生成它们的路径相对应。否则，返回单个匹配值。

这个是实际开发当中重点要使用的，所以我着重记录一下这块，下面我会频繁使用该json数据作为测试：

{"testFee": "3.00", "developList": [{"indicators": "的地方北方热", "annualTarget": "发热的随访人", "annualCompletion": "发VG热娃娃"}, {"indicators": "1111", "annualTarget": "2222", "annualCompletion": "3333"}], "fundBalance": {"totalFunds": "3.00", "totalProject": "3.00", "totalExpenses": "3.00"}}

然后新增的一个test表，有两个字段，json1一个是json类型的，另一个是longtext类型的，但是存储的数据都是上面那个json，主要也是想看一下，两个字段都存json是否都可以使用json函数：

取普通的value（事实证明longtext类型存储的json数据也是可以使用json函数的）

取对象嵌套的value

根据数组下标取值，如果不带下标[0]就是取的整个数组，下标是从0开始的,[0]就是取的数组第一条数据

取指定数组当中的value值

多维数组（实际开发当中应该很少会用到）

obclient> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][1]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][1]') |
+-----------------------------------------------+
| 40                                            |
+-----------------------------------------------+
1 row in set

以上都是对查询做的测试，实际上不仅仅可以用于查询，还可以作为条件，这里就不测试了哈

注意：这种写法是错误的

mysql> SELECT '{"testFee": "3.0php0"}' -> '$.testFee';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-> '$.testFee'' at line 1

3.4.JSON_UNQUOTE(JSON_EXTRACT()) 和 ->> （无引号提取）

通过上面的取值会发现我们取出来的值都带有双引号：

mysql也给我们提供了专门的函数用于去除引号：

• ->> （5.7.13版本开始支持的）语法：json列名->>"path"
• 语法：JSON_UNQUOTE(JSON_EXTRACT(json_doc, path[, path] ...)
• ->>等同于JSON_UNQUOTE(JSON_EXTRACT())
• JSON_UNQUOTE不仅仅是配合JSON_EXTRACT使用，他的作用主要是去除双引号

那会刚用的时候不知道有这个函数，于是便是用了trim()函数来去除的引号：trim函数用于删除字符串str头或尾的指定字符串remstr，通常用来移除字符串首尾两端的空格

– 完整格式: TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

– testxx (删除指定的首字符 x)

SELECT TRIM(LEADING ‘x’ FROM ‘xxtestxx’);

– test (删除指定的首尾字符 x)

SELECT TRIM(BOTH ‘x’ FROM ‘xxtestxx’);

– xxtest (删除指定的尾字符 x)

SELECT TRIM(TRAILING ‘x’ FROM ‘xxtestxx’);

– testx (删除指定的尾字符 xyz)

SELECT TRIM(TRAILING ‘xyz’ FROM ‘testxxyz’);

3.5.JSON_KEYS（获取json当中key数组）

• 描述：该函数从 JSON 对象的顶层值开始搜索，返回对应路径的 Key 的数组。
• 语法：JSON_KEYS(json_doc[, path])

参数 json_doc 用于指定 JSON 文档，path 为路径参数。如果任何参数为 NULL，则返回 NULL。

如果 json_doc 参数指定的不是对象（指的是json数据类型当中的 对象），或者 path（如果给出）不对应任何路径，返回 NULL。

以下情况都会发生报错：

• json_doc 参数 不是有效的 JSON 文档。
• path 参数不是有效的路径表达式。
• path 参数包含 * 或 ** 通配符。

如果选定的对象为空，则结果为空。如果顶层值嵌套了子对象，则返回值不包括来自这些子对象的 Key。

mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"]                            |
+---------------------------------------+
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"]                                        |
+----------------------------------------------+

3.6.JSON_OVERLAPS（判断两个json是否存在同样的的key value）

• 描述：该函数用于比较两个 JSON 文档。如果两个文档具有共同的键值对（key-value）或数组元素（不要求全部一样，只要一个键值对一样就可以），则返回 1，否则返回 0。
• 语法：JSON_OVERLAPS(json_doc1, json_doc2)

参数 json_doc1 和 json_doc2 分别指定两个用于比较的 JSON 文档。如果两个参数都是标量，则函数执行简单的相等性测试。

该函数与 JSON_CONTAINS() 对应，它要求搜索的数组的所有元素都存在于被搜索的数组中。因此，JSON_CONTAINS() 对搜索键执行 AND 运算，而 JSON_OVERLAPS() 执行 OR 运算。

在比较两个数组时，如果它们共享一个或多个数组元素，则 JSON_OVERLAPS() 返回 1，否则返回 0。

在 WHERE 子句中使用 JSON_OVERLAPS() 对 InnoDB 表的 JSON 列的查询，可以使用多值索引进行优化。

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]") |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]") |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');
+-----------------------------------------------------------------------+
| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}') |
+-----------------------------------------------------------------------+
|                                                                     1 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

3.7.JSON_SEARCH（通过内容找path，支持模糊查）

• 描述：该函数返回 JSON 文档中指定字符串的路径。
• 语法：JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

参数 json_djsoc 用于指定 JSON 文档，search_str 为搜索字符串参数（支持%字符串%模糊查询），escape_char 参数指定常量表达式，path 为路径参数。

如果任何参数为 NULL，则返回 NULL；如果文档中不存在路径或未找到指定字符串，也会返回 NULL。

对于 one_or_all 参数，可选值如下：

• ‘one’：在第一次匹配后搜索终止并返回一个路径字符串。
• ‘all’：搜索返回所有匹配的路径字符串，不会包含重复的路径。如果有多个字符串，它们会自动包裹为一个数组。数组元素的顺序不保证是有序的。

对于 search_str 参数，% 和 _ 字符与 LIKE 运算符的作用相同，含义如下：

• % 表示匹配任意数量的字符（包括零个字符）。
• _ 表示正好匹配一个字符。

如果要在搜索字符串中指定 % 或 _ 字符，请在其前面加上转义字符。如果缺少 escape_char 参数或者为NULL，则转义字符默认值为 \。否则，escape_char 必须是一个空的常量或字符。

无论数据库的编码值为多少，search_str 和 path 总是作为 utf8mb4 字符串使用。

以下情况会发生报错：

• json_doc 参数指定的不是有效的 JSON 文档。
• path 参数不是有效的路径表达式。
• one_or_all 参数不为 ‘one’ 或者 ‘all’。
• escape_char 不是常量表达式。

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                          |
+-------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10');
+------------------------------+
| JSON_SEARCH(@j, 'all', '10') |
+------------------------------+
| "$[1][0].k"                  |
+------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
+-----------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$') |
+-----------------------------------------+
| "$[1][0].k"                             |
+-----------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
+---------------------------------------------+
| "$[1][0].k"                                 |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
+-------------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
+-------------------------------------------------+
| "$[1][0].k"                                     |
+-------------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
+-----------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
+-----------------------------------------------+
| "$[1][0].k"                                   |
+-----------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%a%') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%b%') |
+-------------------------------+
| ["$[0]", "$[2].x", "$[3].y"]  |
+-------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
+---------------------------------------------+
| "$[0]"                                      |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
+-------------------------------------------+
| "$[3].y"                                  |
+-------------------------------------------+

3.8.JSON_VALUE（根据key取值，如果没找到可以给默认值，如果找到了可以转换想要的数据类型）

• 描述：该函数从指定路径中提取 JSON 文档中的值，并返回提取的值，还可以选择将其转换为所需数据类型。
• 语法：

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])
on_empty:
    {NULL | ERROR | DEFAULT value} ON EMPTY
on_error:
    {NULL | ERROR | DEFAULT value} ON ERROR

相关参数解释如下：

• json_doc 参数指定有效的 JSON 文档。
• path 参数是指向文档中某个位置的 JSON 路径，必须是字符串。
• type 参数是支持如下数据类型：
  • FLOAT
  • DOUBLE
  • DECIMAL
  • SIGNED
  • UNSIGNED
  • DATE
  • TIME
  • DATETIME
  • YEAR (不支持 YEAR(1) 和 YEAR(2))
  • CHAR
  • JSON

如果没有使用 RETURNING 子句，则该函数的返回类型为 VARCHAR(512)。

当没有为返回类型指定字符集时，JSON_VALUE() 使用 utf8mb4 和二进制排序规则，并区分大小写； 如果将 utf8mb4指定为结果的字符集，则服务器使用此字符集的默认排序规则，不用区分大小写。

当在指定路径中未找到数据时，on_empty 子句用于指定 JSON_VALUE() 的行为。该子句取值如下：

• NULL ON EMPTY：JSON_VALUE() 返回 NULL。这是 ON EMPTY 的默认行为。
• DEFAULT value ON EMPTY’：返回所提供的 value 值，该值的类型必须与返回类型相匹配。
• ERROR ON EMPTY：函数抛出错误。

当发生错误时，on_error 取值如下：

• NULL ON ERROR：JSON_VALUE() 返回 NULL；如果未使用 ON ERROR 子句，则这是默认行为。
• DEFAULT value ON ERROR：返回所提供的 value 值，该值的类型必须与返回类型的值相匹配。
• ERROR ON ERROR：抛出错误。
• ON EMPTY 的位置（如果使用）必须在所有 ON ERROR 子句之前，错误的顺序会导致语法错误。

通常，JSON_VALUE() 会检查所有 JSON 输入（文档和路径）的有效性。如果其中任何一个为无效，则会抛出 SQL 错误，而不触发 ON ERROR 子句。

如果发生以下某一事件，就会触发 ON ERROR：

• 尝试提取对象或数组时，输入的路径在 JSON 文档中被解析为多个路径。
• 转换错误。例如，尝试将 ‘asdf’ 转换为 UNSIGNED 值。
• 数据截断

mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.item');
+-------------------------------------------------------------+
| json_value('{"item": "shoes", "price": "49.95"}', '$.item') |
+-------------------------------------------------------------+
| shoes                                                       |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.price' returning decimal(4,2)) as price;
+-------+
| price |
+-------+
| 49.95 |
+-------+
1 row in set (0.00 sec)
mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.price1' error on empty);
ERROR 3966 (22035): No value was found by 'json_value' on the specified path.
mysql> select json_value('[1, 2, 3]', '$[1 to 2]' error on error);
ERROR 3967 (22034): More than one value was found by 'json_value' on the specified path.
mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.item' returning unsigned error on error) as price;
ERROR 1690 (22003): UNSIGNED value is out of range in 'json_value'

3.9.MEMBER OF（查看数组是否有某个元素）

• 描述：如果被检索的元素和 JSON 数组中的任意一个元素相同，则该函数返回 1，否则返回 0。
• 语法：元素 value MEMBER OF(json_array)

如果 value 是 json_array 的元素，则返回 1，否则返回 0。

value 必须是标量或 JSON 文档；如果它是标量，则运算符会将其视为 JSON 数组的元素。

mysql> SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');
+---------------------------------------------+
| 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT
    -> 17 MEMBER OF('[23, "abc", "17", "ab", 10]'),
    -> "17" MEMBER OF('[23, "abc", 17, "ab", 10]')\G
*************************** 1. row ***************************
17 MEMBER OF('[23, "abc", "17", "ab", 10]'): 0
"17" MEMBER OF('[23, "abc", 17, "ab", 10]'): 0
1 row in set (0.00 sec)
mysql> SELECT cast( '{ "indicators" : "的地方北方热", "annualTarget" : "发热的随访人", "annualCompletion" : "发VG热娃娃" }' AS json ) MEMBER OF ('[{
    '> "indicators": "的地方北方热",
    '> "annualTarget": "发热的随访人",
    '> "annualCompletion": "发VG热娃娃"
    '> }, {
    '> "indicators": "1111",
    '> "annualTarget": "2222",
    '> "annualCompletion": "3333"
    '> }]') as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

要对本身为数组的值使用此操作符，必须将其显式转换为JSON数组。你可以用CAST (…)为JsoN):

mysql> SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]');
+--------------------------------------------------+
| CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]') |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+
1 row in set (0.00 sec)

也可以使用JSON ARRAY()函数执行必要的强制转换，如下所示:

mysql> SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]');
+--------------------------------------------+
| JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]') |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)

任何用作要测试的值或出现在目标数组中的JSON对象都必须使用CAST强制转换或JSON_OBJECT()。

此外，包含JSON对象的目标数组本身必须使用JSON_ARRAY进行强制转换，下面的语句序列演示了这一点:

mysql> SELECT'{
    '> "indicators": "的地方北方热",
    '> "annualTarget": "发热的随访人",
    '> "annualCompletion": "发VG热娃娃"
    '> }' MEMBER OF ( '[{
    '> "indicators"编程客栈: "的地方北方热",
    '> "annualTarget": "发热的随访人",
    '> "annualCompletion": "发VG热娃娃"
    '> }, {
    '> "indicators": "1111",
    '> "annualTarget": "2222",
    '> "annualCompletion": "3333"
    '> }]' ) AS result;
+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OBJECT( 'indicators', "的地方北方热", "annualTarget", "发热的随访人", "annualCompletion", "发VG热娃娃" ) MEMBER OF ( '[{
    '> "indicators": "的地方北方热",
    '> "annualTarget": "发热的随访人",
    '> "annualCompletion": "发VG热娃娃"
    '> }, {
    '> "indicators": "1111",
    '> "annualTarget": "2222",
    '> "annualCompletion": "3333"
    '> }]' ) AS result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

四、修改JSON文本的函数

4.1.JSON_ARRAY_APPEND（在指定的数组位置末尾追加元素，假如指定的位置不是数组追加完过后就变成了数组）

• 描述：该函数用于将指定的值附加到 JSON 文档中指定数组的末尾并返回结果。
• 语法：JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

json_doc 参数用于指定 JSON 文档，path 为路径参数。如果任何参数为 NULL，则返回 NULL。

以下情况都会发生报错：

• json_doc 参数不是有效的 JSON 文档。
• path 参数不是有效的路径表达式。
• path 参数包含 * 或 ** 通配符。

如果路径对应的值是一个标量或对象值，则该值将自动包裹在数组中，并将新值添加到该数组中。

数组：

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, '$[0]', 2);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[0]', 2) |
+----------------------------------+
| [["a", 2], ["b", "c"], "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"]                 |
+---------------------------------+
mysql> SELECT JSON_ARRAY_APPEND('{"a": 1}','$',JSON_OBJECT( 'z', 2 ));
+---------------------------------------------------------+
| JSON_ARRAY_APPEND('{"a": 1}','$',JSON_OBJECT( 'z', 2 )) |
+---------------------------------------------------------+
| [{"a": 1}, {"z": 2}]                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

4.2.JSON_ARRAY_INSERT（在指定的数组位置后追加元素）

• 描述：该函数用于将数组插入文档中并返回修改后的文档。
• 语法：JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

json_doc 参数用于指定 JSON 文档，path 为路径参数。如果任何参数为 NULL，则返回 NULL。

以下情况都会发生报错：

• json_doc 参数不是有效的 JSON 文档。
• path 参数不是有效的路径表达式。
• path 参数包含 * 或 ** 通配符。

如果路径识别了某一数组元素，则在该元素位置插入相应的值，将所有后续值向右移动。如果路径识别的数组位置超出数组末尾的位置，则将值插入到数组末尾。

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

假如数组元素是对象，这时候要插入一定要使用cast进行转换一下，不然直接以字符串进行插入会出现问题，他会以为你让他追加字符串！

mysql> SELECT JSON_ARRAY_INSERT( '[{
    '> "indicators": "的地方北方热",
    '> "annualTarget": "发热的随访人",
    '> "annualCompletion": "发VG热娃娃"
    '> }, {
    '> "indicators": "1111",
    '> "annualTarget": "2222",
    '> "annualCompletion": "3333"
    '> }]', '$[3]', cast( '{"indicators":"999","annualTarget":"999","annualCompletion":"999"}' AS json ) ) AS result FROM test;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| result                                                                                                                                                                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"indicators": "的地方北方热", "annualTarget": "发热的随访人", "annualCompletion": "发VG热娃娃"}, 
{"indicators": "1111", "annualTarget": "2222", "annualCompletion": "3333"}, 
{"indicators": "999", "annualTarget": "999", "annualCompletion": "999"}] |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_INSERT( '[{
    '> "indicators": "的地方北方热",
    '> "annualTarget": "发热的随访人",
    '> "annualCompletion": "发VG热娃娃"
    '> }, {
    '> "indicators": "1111",
    '> "annualTarget": "2222",
    '> "annualCompletion": "3333"
    '> }]', '$[3]',  '{"indicators":"999","annualTarget":"999","annualCompletion":"999"}' ) AS result FROM test;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| result                                                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"indicators": "的地方北方热", "annualTarget": "发热的随访人", "annualCompletion": "发VG热娃娃"}, 
{"indicators": "1111", "annualTarget": "2222", "annualCompletion": "3333"},
"{\"indicators\":\"999\",\"annualTarget\":\"999\",\"annualCompletion\":\"999\"}"] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

4.3.JSON_INSERT（向指定path添加元素，path有值不覆盖，没值就添加）

• 描述：该函数将数据插入 JSON 文档并返回结果。
• 语法：JSON_INSERT(json_doc, path, val[, path, val] ...)

json_doc 参数用于指定 JSON 文档，path 为路径参数。如果任何参数为 NULL，则返回 NULL。

以下情况都会发生报错：

• json_doc 参数不是有效的 JSON 文档。
• path 参数不是有效的路径表达式。
• path 参数包含 * 或 ** 通配符。

mysql> SELECT JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
+--------------------------------------------------------------------------+
| JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
+--------------------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}                              |
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT JSON_INSERT( '{ "a": 1, "b": [2, 3],"c":666}', '$.a', 10, '$.c', 999 );
+------------------------------------------------------------------------+
| JSON_INSERT( '{ "a": 1, "b": [2, 3],"c":666}', '$.a', 10, '$.c', 999 ) |
+------------------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": 666}                                        |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.5.JSON_MERGE（多个json合并）

• 描述：该函数用于合并多个 JSON 文档。该函数是 JSON_MERGE_PRESERVE() 的同义词。
• 语法：JSON_MERGE(json_doc, json_doc[, json_doc] ...)

obclient> SELECT JSON_MERGE('[1, 2, 3]', '[true, false]', '{"a":100}');
+-------------------------------------------------------+
| JSON_MERGE('[1, 2, 3]', '[true, false]', '{"a":100}') |
+-------------------------------------------------------+
| [1, 2, 3, true, false, {"a": 100}]                    |
+-------------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE('{"1": 2}', '[true, false]', '{"a":100}');
+------------------------------------------------------+
| JSON_MERGE('{"1": 2}', '[true, false]', '{"a":100}') |
+------------------------------------------------------+
| [{"1": 2}, true, false, {"a": 100}]                  |
+------------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE('{"1": 2}',  '{"a":100}');
+--------------------------------------+
| JSON_MERGE('{"1": 2}',  '{"a":100}') |
+--------------------------------------+
| {"1": 2, "a": 100}                   |
+--------------------------------------+
1 row in set

4.6.JSON_MERGE_PATCH

• 描述：该函数依据 RFC 7396 标准对两个或多个 JSON文档执行合并，并返回合并结果，结果不保留具有重复键的成员。如果某一个参数所传递的文档无效，则会引发错误。
• 语法：JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

SON_MERGE_PATCH() 执行合并的规则如下：

如果第一个参数不是对象，则将其视为空对象与第二个参数合并作为合并结果。

如果第二个参数不是对象，则合并结果是第二个参数。

如果两个参数都是对象，则合并结果是具有以下成员的对象：

• 第一个对象的所有成员在第二个对象中没有具有相同键的对应成员。
• 第二个对象的所有成员在第一个对象中没有对应的键，并且其值不是 JSON 的 null 字面量。
• 所有成员的键都存在于第一个和第二个对象中，并且在第二个对象中的值不是 JSON 的 null 字面量。 这些成员的值是递归合并第一个对象中的值与第二个对象中的值的结果。

obclient> SELECT JSON_MERGE_PATCH('[1, 2, 3]', '[true, false]');
+------------------------------------------------+
| JSON_MERGE_PATCH('[1, 2, 3]', '[true, false]') |
+------------------------------------------------+
| [true, false]                                  |
+------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 23}');
+-------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "x"}', '{"id": 23}') |
+-------------------------------------------------+
| {"id": 23, "name": "x"}                         |
+-------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('1', 'true');
+-------------------------------+
| JSON_MERGE_PATCH('1', 'true') |
+-------------------------------+
| true                          |
+-------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('[1, 2, 3]', '{"id": 45}');
+----------www.devze.com-----------------------------------+
| JSON_MERGE_PATCH('[1, 2, 3]', '{"id": 45}') |
+---------------------------------------------+
| {"id": 45}                                  |
+---------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');
+-----------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |
+-----------------------------------------------------------+
| {"a": 3, "b": 2, "c": 4}                                  |
+-----------------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }',NULL);
+--------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }',NULL) |
+--------------------------------------------+
| NULL                                       |
+--------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }');
+--------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }') |
+--------------------------------------------------------------------------------+
| {"a": 5, "b": 2, "c": 4, "d": 6}                                               |
+--------------------------------------------------------------------------------+
1 row in set

4.7.JSON_MERGE_PRESERVE

• 描述：合并两个或多个 JSON 文档并返回合并结果。
• 语法：JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)

如果任何参数为 NULL，则返回 NULL。如果任何参数不是有效的 JSON 文档，则会报错。

JSON_MERGE_PRESERVE() 合并规则如下：

• 相邻数组合并为一个数组。
• 相邻的对象合并为一个对象。
• 标量值自动包裹为数组并且合并为数组。
• 通过将对象自动包裹为数组来合并相邻的数组和对象。

obclient> SELECT JSON_MERGE_PRESERVE('[1, 2, 3]', '[true, false]');
+---------------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2, 3]', '[true, false]') |
+---------------------------------------------------+
| [1, 2, 3, true, false]                            |
+---------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PRESERVE('{"name": "apple"}', '{"id": 56}');
+--------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"name": "apple"}', '{"id": 56}') |
+--------------------------------------------------------+
| {"id": 56, "name": "apple"}                            |
+--------------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PRESERVE('1', 'true');
+----------------------------------+
| JSON_MERGE_PRESERVE('1', 'true') |
+----------------------------------+
| [1, true]                        |
+----------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PRESERVE('[1, 2, 3]', '{"id": 56}');
+------------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2, 3]', '{"id": 56}') |
+------------------------------------------------+
| [1, 2, 3, {"id": 56}]                          |
+------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2, "c": 5}',  '{ "a": 3, "c": 4 }');
+-------------------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{ "a": 1, "b": 2, "c": 5}',  '{ "a": 3, "c": 4 }') |
+-------------------------------------------------------------------------+
| {"a": [1, 3], "b": 2, "c": [5, 4]}                                      |
+-------------------------------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }', '{ "a": 5, "d": 6 }');
+--------------------------------------------------------------------------------------+
| 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}                                             |
+--------------------------------------------------------------------------------------+
1 row in set

4.8.JSON_REMOVE（根据path移除）

• 描述：该函数用于从 JSON 文档中删除数据并返回结果。
• 语法：JSON_REMOVE(json_doc, path[, path] ...)

json_doc 参数用于指定 JSON 文档，path 为路径参数。如果任何参数为 NULL，则返回 NULL。

以下情况都会发生报错：

• json_doc 参数不是有效的 JSON 文档。
• path 参数不是有效的路径表达式。
• path 参数是 $。
• path 参数包含 * 或 ** 通配符。

如果要移除的元素在文档中不存在，则不会产生错误。在这种情况下，路径不会影响文档。

obclient> SET @jn = '["a", ["b", "c"], "d"]';
Query OK, 0 rows affected
obclient> SELECT JSON_REMOVE(@jn, '$[1]');
+--------------------------+
| JSON_REMOVE(@jn, '$[1]') |
+--------------------------+
| ["a", "d"]               |
+--------------------------+
1 row in set
obclient> SELECT JSON_REMOVE(@jn, '$[7]');
+--------------------------+
| JSON_REMOVE(@jn, '$[7]') |
+--------------------------+
| ["a", ["b", "c"], "d"]   |
+--------------------------+
1 row in set

4.9.JSON_REPLACE（替换指定path的值）

• 描述：该函数用于替换 JSON 文档中的现有值并返回结果。
• 语法：JSON_REPLACE(json_doc, path, val[, path, val] ...)

以下情况都会发生报错：

• json_doc 参数不是有效的 JSON 文档。
• path 参数不是有效的路径表达式。
• path 参数包含 * 或 ** 通配符。

文档中现有路径的 path-value 对会用新值覆盖现有文档值。文档中不存在路径的 path-value 将被忽略且无效。

obclient> SET @jn = '{ "a": 1, "b": [2, 3, 4]}';
Query OK, 0 rows affected
obclient> SELECT JSON_REPLACE(@jn, '$.a', 10, '$.c', '[true, false]');
+------------------------------------------------------+
| JSON_REPLACE(@jn, '$.a', 10, '$.c', '[true, false]') |
+------------------------------------------------------+
| {"a": 10, "b": [2, 3, 4]}                            |
+------------------------------------------------------+
1 row in set

5.0.JSON_SET（有则覆盖，没有则新增）

• 描述：该函数用于在 JSON 文档中插入或更新数据并返回结果。
• 语法：JSON_SET(json_doc, path, val[, path, val] ...)

json_doc 参数用于指定 JSON 文档，path 为路径参数。如果任何参数为 NULL 或 path（如果给定）未定位到对象，则返回 NULL。

以下情况都会发生报错：

• json_doc 参数不是有效的 JSON 文档。
• path 参数不是有效的路径表达式。
• path 参数包含 * 或 ** 通配符。

文档中现有路径的 path-value 对会用新值覆盖现有文档值。如果路径标识的对象不存在，则文档中不存在路径的路径值对会按照如下规则添加到文档中：

• 现有对象中不存在的成员。该成员被添加到对象并与新值相关联。
• 超出现有数组末尾的位置。此时使用新值扩展数组。如果现有值不是数组，则将其自动包裹为数组，然后使用新值进行扩展。

obclient> SET @jn = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected
obclient> SELECT JSON_SET(@jn, '$.a', 10, '$.c', '[true, false]');
+--------------------------------------------------+
| JSON_SET(@jn, '$.a', 10, '$.c', '[true, false]') |
+--------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"}     |
+--------------------------------------------------+
1 row in set

五、返回JSON文本属性的函数

5.1.JSON_DEPTH（返回 JSON 文档的最大深度）

• 描述：返回 JSON 文档的最大深度。
• 语法：JSON_DEPTH(json_doc)

json_doc 参数用于指定 JSON 文档。如果参数为 NULL，则返回 NULL。

如果参数不是有效的 JSON 文档，则会发生错误。

空数组、空对象或标量值的深度为 1。仅包含深度为 1 的元素的非空数组深度为 2，仅包含深度为 1 的成员值的非空对象的深度为 2。否则，JSON 文档的深度大于 2。

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 |
+-------------------------------+

5.2.JSON_LENGTH（返回长度）

• 描述：该函数返回 JSON 文档的长度。如果给出了 path 参数，则返回由路径标识的文档中值的长度。
• 语法：JSON_LENGTH(json_doc[, path])

json_doc 参数用于指定 JSON 文档，path 为路径参数。如果任何参数为 NULL 或 path 参数未标识文档中的值，则返回 NULL。

以下情况都会发生报错：

• json_doc 参数不是有效的 JSON 文档。
• path 参数不是有效的路径表达式。
• path 参数包含 * 或 ** 通配符。

JSON 文档的长度定义如下：

• 标量的长度为 1。
• 数组的长度是数组元素的数量。
• 对象的长度是对象成员的数量。
• 不计算嵌套数组或对象的长度。

mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
+---------------------------------+
| JSON_LENGTH('[1, 2, {"a": 3}]') |
+---------------------------------+
|                               3 |
+---------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
|                                       2 |
+-----------------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
+------------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

5.3.JSON_TYPE（获取json的类型）

• 描述：该函数返回代表 JSON 值类型的 utf8mb4 字符串。
• 语法：JSON_TYPE(json_val)

参数 json_val 可以是对象、数组或标量类型。

如果参数为 NULL，则返回 NULL。如果参数不是有效的 JSON 值，则会报错。

mysql> SET @j = '{"a": [10, true]}';
mysql> SELECT JSON_TYPE(@j);
+---------------+
| JSON_TYPE(@j) |
+---------------+
| OBJECT        |
+---------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));
+------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a')) |
+------------------------------------+
| ARRAY                              |
+------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));
+---------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) |
+---------------------------------------+
| INTEGER                               |
+---------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]'));
+---------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')) |
+---------------------------------------+
| BOOLEAN                               |
+---------------------------------------+

5.4.JSON_VALID（判断是否是有效json）

• 描述：该函数用于判断输入值是否为有效的 JSON，如果是有效的 JSON 返回 1，否则返回 0。
• 语法：JSON_VALID(val)

obclient> SELECT JSON_VALID('{"a": 1}');
+------------------------+
| JSON_VALID('{"a": 1}') |
+------------------------+
|                      1 |
+-----python-------------------+
1 row in set
obclient> SELECT JSON_VALID('oceanbase'), JSON_VALID('"oceanbase"');
+-------------------------+---------------------------+
| JSON_VALID('oceanbase') | JSON_VALID('"oceanbase"') |
+-------------------------+---------------------------+
|                       0 |                         1 |
+-------------------------+---------------------------+
1 row in set

六、JSON工具函数

6.1.JSON_PRETTY（json格式化输出）

• 描述：该函数对 JSON 值按照格式打印。类似于 php 等。
• 语法：JSON_PRETTY(json_val)

参数 json_val 必须是 JSON 值或 JSON 值的有效字符串表示形式。此值中存在的无关空格和换行符对输出没有影响。如果该值不是 JSON 文档，或者无法进行解析，则该函数将执行失败并显示错误。

obclient> SELECT JSON_PRETTY('{"a":"10","b":"20","c":"30"}');
+---------------------------------------------+
| JSON_PRETTY('{"a":"10","b":"20","c":"30"}') |
+---------------------------------------------+
| {
  "a": "10",
  "b": "20",
  "c": "30"
}   |
+---------------------------------------------+
1 row in set

6.2.JSON_STORAGE_SIZE（计算占用的存储空间，单位字节）

• 描述：此函数返回用于存储 JSON 文档的二进制字节数。当参数是一个 JSON 列时，会用于存储 JSON 文档。JSON的部分更新不会改变该函数的返回值。
• 语法：JSON_STORAGE_SIZE(json_val)

json_val 参数必须是有效的 JSON 文档或可以解析为一个字符串。在 json_val 是字符串的情况下，该函数将字符串解析为 JSON 并将其转换为二进制后返回存储空间的二进制字节数。

obclient> CREATE TABLE jtbl (jcol JSON);
Query OK, 0 rows affected
obclient> INSERT INTO jtbl VALUES  ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
Query OK, 1 row affected
obclient> SELECT jcol,JSON_STORAGE_SIZE(jcol) AS Size FROM jtbl;
+-----------------------------------------------+------+
| jcol                                          | Size |
+-----------------------------------------------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   41 |
+-----------------------------------------------+------+
1 row in set

七、JSON聚合函数

7.1.JSON_ARRAYAGG（配合GROUP BY进行分组，得到的是数组）

描述：该函数将结果集聚合为单个 JSON 数组，其元素由行组成。此数组中元素的顺序未定义。

该函数作用于计算为单个值的列或表达式。

语法：JSON_ARRAYAGG(col_or_expr)[over_clause]

参数 col_or_expr 为列或表达式。如果结果不包含行，或者出现错误，则返回 NULL。

obclient> CREATE TABLE tbl1 (oid INT, attr VARCHAR(100), value VARCHAR(100));
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES (2, 'color', 'red'),(2, 'fabric', 'silk'),(3,'color','green'),(3,'shape','square');
Query OK, 4 rows affected 
Records: 4  Duplicates: 0  Warnings: 0
obclient> SELECT oid, JSON_ARRAYAGG(attr) AS attributes FROM tbl1 GROUP BY oid;
+------+---------------------+
| oid  | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set
obclient> INSERT INTO tbl1 SELECT * FROM tbl1;
Query OK, 4 rows affected 
Records: 4  Duplicates: 0  Warnings: 0
obclient> SELECT oid, JSON_ARRAYAGG(attr) AS attributes FROM tbl1 GROUP BY oid;
+------+----------------------------------------+
| oid  | attributes                             |
+------+----------------------------------------+
|    2 | ["color", "fabric", "color", "fabric"] |
|    3 | ["color", "shape", "color", "shape"]   |
+------+----------------------------------------+
2 rows in set

7.2.JSON_OBJECTAGG（配合GROUP BY进行分组，得到的是对象）

• 描述：该函数将两个列名或表达式作为参数，第一个用作键，第二个用作值，并返回包含键值对的 JSON 对象。
• 语法：JSON_OBJECTAGG(key, value) [over_clause]

key, value 表示键值对。如果结果不包含行，或者出现错误，则返回 NULL。如果任何键名称为 NULL 或参数数量不等于 2，则会发生错误。

obclient> SELECT oid, JSON_OBJECTAGG(attr, value) AS attributes FROM tbl1 GROUP BY oid;
+------+---------------------------------------+
| oid  | attributes                            |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set
obclient> CREATE TABLE tbl2(c VARCHAR(10), i INT);
Query OK, 0 rows affected
obclient>  INSERT INTO tbl2 VALUES ('key', 3), ('key', 4), ('key', 5);
Query OK, 3 rows affected 
Records: 3  Duplicates: 0  Warnings: 0
obclient> SELECT c, i FROM tbl2;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    4 |
| key  |    5 |
+------+------+
3 rows in set
obclient> SELECT JSON_OBJECTAGG(c, i) FROM tbl2;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 5}           |
+----------------------+
1 row in set

到此这篇关于Mysql中Json相关的函数使用的文章就介绍到这了,更多相关Mysql Json函数内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！