iis服务器助手广告广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >Mysql中Json相关的函数使用
  • 489

0
分享到

Mysql中Json相关的函数使用

MysqlJson函数MysqlJson 2023-06-09 10:06:50 489人浏览 薄情痞子
摘要

目录一、前言二、创建JSON文本的函数2.1.jsON_ARRAY（转换json数组）2.2.JSON_OBJECT（转换json对象）2.3.JSON_QUOTE（转义字符串）三、搜索JSON文本的函数3.1.JSON

一、前言

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

Mysql中Json相关的函数使用

二、创建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 中常见的转义字符如下表所示：

Mysql中Json相关的函数使用

三、搜索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函数：

Mysql中Json相关的函数使用

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

Mysql中Json相关的函数使用

取对象嵌套的value

Mysql中Json相关的函数使用

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

Mysql中Json相关的函数使用

取指定数组当中的value值

Mysql中Json相关的函数使用

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

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.00"}' -> '$.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

Mysql中Json相关的函数使用

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

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

Mysql中Json相关的函数使用

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

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

Mysql中Json相关的函数使用

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

Mysql中Json相关的函数使用

– 完整格式: 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_doc 用于指定 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}');
+---------------------------------------------+
| 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 |
+------------------------+
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函数内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: Mysql中Json相关的函数使用

本文链接: https://www.lsjlt.com/news/255573.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • Mysql中Json相关的函数使用
    目录一、前言二、创建jsON文本的函数2.1.JSON_ARRAY（转换json数组）2.2.JSON_OBJECT（转换json对象）2.3.JSON_QUOTE（转义字符串）三、搜索JSON文本的函数3.1.JSON...
    99+
    2023-06-09
    Mysql Json函数 Mysql Json
  • Mysql当中Json相关的函数详解
    目录 一、前言二、创建JSON文本的函数2.1.JSON_ARRAY（转换json数组）2.2.JSON_OBJECT（转换json对象）2.3.JSON_QUOTE（转义字符串） 三、搜索JSON文本的函数3.1.JSON_...
    99+
    2023-08-18
    json mysql 数据库
  • 详解mysql中的concat相关函数
    一、concat()函数 功能：将多个字符串连接成一个字符串 语法：concat(str1,str2,…) 其中的字符串既可以是数据表字段，也可以是指定的字符串 返回结果为连接参数产生的...
    99+
    2024-04-02
  • MySQL日期相关函数
    MySQL中，字符串的年月日时分秒按照 yyyy-mm-dd HH:mm:ss 这种格式排列，那这个字符串就是日期 1、 curdate() ：获取年月日 select curdate(); -- 2023-02-04 curtime()...
    99+
    2023-08-30
    mysql 数据库 sql
  • MySQL如何使用JSON函数
    这篇文章主要为大家展示了“MySQL如何使用JSON函数”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL如何使用JSON函数”这篇文章吧。JSON函数(...
    99+
    2024-04-02
  • PHP中垃圾回收相关函数的使用
    目录再谈循环引用以及强制清理循环引用开启、关闭及查看循环引用垃圾回收状态强制回收Zend引擎内存管理器使用的内存获取垃圾收集器的信息测试代码：相关文章之前我们已经学习过 PHP 中的...
    99+
    2024-04-02
  • MySQL中常用的日期相关函数有哪些
    这篇文章主要介绍了MySQL中常用的日期相关函数有哪些，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。目录第一部分：时间差函数 timesta...
    99+
    2024-04-02
  • numpy相关函数如何使用
    本篇内容介绍了“numpy相关函数如何使用”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！np.where首先强调一下，wher&#...
    99+
    2023-06-27
  • Python OpenCV的基本使用及相关函数
    目录1、图像的读取2、图像保存3、图像展示4、获取图像属性5、图像缩放（宽，高）6、在原图像中获取某一区域7、彩色图像通道分解8、图像加法9、图像反转10、图像金字塔11、直方图12...
    99+
    2024-04-02
  • PHP中垃圾回收相关函数的使用案例
    这篇文章主要介绍PHP中垃圾回收相关函数的使用案例，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！php的框架有哪些php的框架：1、Laravel，Laravel是一款免费并且开源的PHP应用框架。2、Phalcon...
    99+
    2023-06-14
  • VB.NET计算数字相关函数的使用方法
    这篇文章将为大家详细讲解有关VB.NET计算数字相关函数的使用方法，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。VB.NET编程语言一经出现，就凭借其强大的功能受到广大开发人员的青睐。它的应...
    99+
    2023-06-17
  • js日历相关函数使用详解
    本文实例为大家分享了js日历相关函数的具体代码，供大家参考，具体内容如下 1、获取某年某月第一天是周几 getMonthStartDay(year, month, index) { ...
    99+
    2024-04-02
  • Python函数使用的相关练习题分享
    目录1.写函数计算传入数字参数的和（动态传参）2.写函数用户传入修改的文件名3.写函数检查用户传入的对象4.写函数检查传入字典的每一个value的长度5.闭包6.写函数返回一个扑克牌...
    99+
    2024-04-02
  • MySql中怎么使用json_extract函数处理json字段
    这篇“MySql中怎么使用json_extract函数处理json字段”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“MySq...
    99+
    2023-06-30
  • SQL Server中的JSON函数怎么使用
    这篇文章主要介绍“SQL Server中的JSON函数怎么使用”，在日常操作中，相信很多人在SQL Server中的JSON函数怎么使用问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”SQL...
    99+
    2023-06-30
  • Mysql数学函数相关知识点整理
    本篇内容介绍了“Mysql数学函数相关知识点整理”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！所有的数学函...
    99+
    2024-04-02
  • 【MySQL】——常用接口API即相关函数说明
       目录    1、MySQL结构体的说明 1、MYSQL结构体 2.MYSQL_RES结构体 3. MYSQL_FIELD  2. 接口的使用步骤 3、mysql_init()——MYSQL对象初始化  4、mysql_real_con...
    99+
    2023-10-25
    mysql 数据库
  • 一文详解C语言中文件相关函数的使用
    目录一、文件和流1、程序文件2、数据文件3、流二、文件组成三、文件的打开和关闭1、文件的打开fopen2、文件关闭fclose四、文件的顺序读写1、使用fputc和fgetc写入/读...
    99+
    2024-04-02
  • 使用opencv相关函数确定图片中的直线问题
    目录使用opencv相关函数确定图片中的直线OpenCV：直线检测使用opencv相关函数确定图片中的直线 #pip install opencv-python==4.4.0.4...
    99+
    2022-11-13
    使用opencv opencv函数 opencv图片中直线
  • MySql中使用JSON_TABLE 函数来实现json类型的数据查询
    使用 MySQL 8.0.4 及以上版本提供的 JSON_TABLE 函数来实现查询 id 在 3、4、5… 中的数据，示例如下： SELECT *FROM table_nameCROSS J...
    99+
    2023-10-06
    mysql json 数据库
软考高级职称资格查询
iis服务器助手广告
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作