MySQL JSON 总结

MySQL JSON 官方文档阅读笔记与总结，以5.7版本为主，也会介绍8.0版本的一些需要注意的变化。
虽然个人还是建议用8.0+版本，但是实际工作还是5.7为主。

JSON Data Type

mysql 在 5.7.8 版本支持了RFC 7159 定义的 JSON原生数据类型。
JSON 数据类型相比于 String 类型增加了以下特性：

自动校验JSON格式，非法格式会报错。
优化JSON存储，内部存储格式允许快速读取JSON中的键值，不需要读取整个JSON串。

存储的JSON大小受到 max_allowed_packet 系统变量影响(5.7默认 4M ，8.0默认 64M)。
8.0.13版本之前 JSON类型的列不能有非NULL的默认值(默认值必须是NULL)。JSON类型属于二进制类型，所以不能直接为列建立索引，但是可以在JSON列中抽取的值上建立索引。

JSON类型的描述和String类型一样书写: '["a", "b", 1]' ,使用的是 utf8mb4 字符集。JSON类型的值可以被赋予至变量中

mysql> SET @j = JSON_OBJECT('key', 'value');
mysql> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+

不能直接赋予String类型的JSON串，需要通过JSON_OBJECT函数转换成JSON类型。JSON类型在是大小写敏感的

mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

null,true,false 这些字面量都必须是小写的 （和普通字面量不一样，比如MySQL中 null，Null，NULL都代表null）：

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |
+--------------------+--------------------+--------------------+
|                  1 |                  0 |                  0 |
+--------------------+--------------------+--------------------+

mysql> SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null                 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST('NULL' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:
"Invalid value." at position 0 in 'NULL'.

JSON Functions

介绍一些常用的JSON相关的函数。分为5大类

创建JSON函数
搜索JSON函数
修改JSON函数
JSON属性函数
JSON工具函数

创建JSON函数

Functions That Create JSON Values

JSON_ARRAY([val[, val] …])

参数为多个值(类型可以不同)，创建JSON array

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------+
| [1, "abc", null, true, "11:30:24.000000"]   |
+---------------------------------------------+

JSON_OBJECT([key, val[, key, val] …])

参数为多组key/value对，创建JSON object

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

JSON_QUOTE(string)

参数是String类型，返回通过双引号包裹形成json value值，通常用来创建JSON中的String类型的值。

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

搜索JSON函数

Functions That Search JSON Values

JSON_CONTAINS(target, candidate[, path])

判断目标JSON是否包含候选内容，返回 0、1。target和candidate非有效json或者path为非有效路径表达式或者包含*字符。
如果要判断path是否存在数据，可以使用 JSON_CONTAINS_PATH()函数。

是否包含的判定规则如下

判断是否包含是通过路径的值是否相等来实现的，值是否相等前提 JSON_TYPE() 必须相等（INTEGER 和 DECIMAL类型判断是例外，两种类型可以判断为相等）
如果target 和 candidate 都是array类型的时候，target必须完全包含candidate
candidate 是非 array 的时候，target中必须含有 candidate
candidate 是JSON Object的时候，target 中必须需包含 candidate 中相同的key与value

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

mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             0 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.c') |
+-------------------------------+
|                             1 |
+-------------------------------+

ps. 这里场景说明一下，当(任务、工单)处理人是多个的时候，可以使用json array 类型存储处理人，且查询的时候使用:

1	select * from ticket where json_contains(operate_user,JSON_QUOTE('userId'),'$') = 1;

注意字符串类型的userId需要是用 JSON_QUOTE 处理一下，或者直接加上双引号 "userId" 。

如果userId是数值类型可以省略双引号

1	select json_contains(JSON_ARRAY(111,222,333,444),'444','$') as ret

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)

判断JSON指定路径（支持多个）是否有值，返回 0、1. one_or_all 参数可选值只包含 one/all

one 只要有一个path存在的情况下返回1，否则返回0

all 所有path都存在的情况下返回1，否则返回0

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

JSON_EXTRACT(json_doc, path[, path] …)

在JSON中按path提取数据，支持多path，如果有多个返回，自动按path顺序包装成 array 类型。

mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 20                                         |
+--------------------------------------------+
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
+----------------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
+----------------------------------------------------+
| [20, 10]                                           |
+----------------------------------------------------+
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
+-----------------------------------------------+
| [30, 40]                                      |
+-----------------------------------------------+

column->path

新增于MySQL 5.7.9 版本，作为 JSON_EXTRACT函数两个参数的简写形式。

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
     > FROM jemp
     > WHERE JSON_EXTRACT(c, "$.id") > 1
     > ORDER BY JSON_EXTRACT(c, "$.name");
+-------------------------------+-----------+------+
| c                             | c->"$.id" | g    |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3"       |    3 |
| {"id": "4", "name": "Betty"}  | "4"       |    4 |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)

mysql> SELECT c, c->"$.id", g
     > FROM jemp
     > WHERE c->"$.id" > 1
     > ORDER BY c->"$.name";
+-------------------------------+-----------+------+
| c                             | c->"$.id" | g    |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3"       |    3 |
| {"id": "4", "name": "Betty"}  | "4"       |    4 |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)

mysql> CREATE TABLE tj10 (a JSON, b INT);
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO tj10
     > VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT a->"$[4]" FROM tj10;
+--------------+
| a->"$[4]"    |
+--------------+
| 44           |
| [22, 44, 66] |
+--------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tj10 WHERE a->"$[0]" = 3;
+------------------------------+------+
| a                            | b    |
+------------------------------+------+
| [3, 10, 5, 17, 44]           |   33 |
| [3, 10, 5, 17, [22, 44, 66]] |    0 |
+------------------------------+------+
2 rows in set (0.00 sec)

不仅用于select语句中，也可以用在update/delete语句中.

column->>path

新增于 MySQL 5.7.13 版本，在 -> 基础上除去了引号。下面操作是等价的：

JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path

mysql> SELECT c->'$.name' AS name
    ->     FROM jemp WHERE g > 2;
+----------+
| name     |
+----------+
| "Barney" |
| "Betty"  |
+----------+
2 rows in set (0.00 sec)

mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
    ->     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> SELECT c->>'$.name' AS name
    ->     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+

JSON_KEYS(json_doc[, path])

返回第一层的所有的JSON key组合成的array 。如果指定 path ，按path 的第一层来。

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

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

返回指定JSON中包含目标值的path , one_or_all 参数解释如下

one 找到第一个匹配的path后立即返回
all 搜索所有满足的path，自动包装成 array 类型

search_str 支持 % 和 _ 通配符，同 like 语句。默认的转移符是 \ 可以通过 escape_char 指定（如果是null 也是 \）。

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

修改JSON函数

Functions That Modify JSON Values

JSON_APPEND(json_doc, path, val[, path, val] …)

在JSON array追加一条记录，MySQL 5.7.9 版本后改名为 JSON_ARRAY_APPEND() ，MySQL 8.0 版本删除。

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

在JSON array中指定path末尾追加一条记录，返回追加后的JSON 。非array的时候会自动组成 array。

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

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)

在指定path插入一条记录，返回修改后的JSON

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, '$[2][1]', 'y');
+---------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') |
+---------------------------------------+
| ["a", {"b": [1, 2]}, [3, "y", 4]]     |
+---------------------------------------+

JSON_INSERT(json_doc, path, val[, path, val] …)

JSON中插入数据，如果path已经存在则忽略，不会更新值。

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

注意上面的例子如果需要插入JSON array, 必须做转移。否则就是字面量。

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

JSON_MERGE(json_doc, json_doc[, json_doc] …)

合并两个或者更多的JSON。JSON_MERGE_PRESERVE() 同义，在 MySQL 5.7.22 中弃用，并会在未来版本中删除。

mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
+---------------------------------------+
| JSON_MERGE('[1, 2]', '[true, false]') |
+---------------------------------------+
| [1, 2, true, false]                   |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'JSON_MERGE' is deprecated and will be removed in a future release. \
 Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead
1 row in set (0.00 sec)

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] …)

执行的逻辑同 RFC 7396 定义，返回合并后的结果JSON 。
这里不详细讲解了，不常用，且逻辑复杂，容易出错，使用这个函数一定要理解其中的含义。

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] …)

合并两个JSON 返回合并后的新JSON ，合并规则如下

多个 array 则合并成一个 array
多个 object 则合并成一个 object
单个标量则自动包装成array 然后按照 array的逻辑合并
array 和 object 则把 object 转成 array 按 array逻辑合并

mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');
+------------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2]', '[true, false]') |
+------------------------------------------------+
| [1, 2, true, false]                            |
+------------------------------------------------+

mysql> SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}');
+----------------------------------------------------+
| JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}') |
+----------------------------------------------------+
| {"id": 47, "name": "x"}                            |
+----------------------------------------------------+

mysql> SELECT JSON_MERGE_PRESERVE('1', 'true');
+----------------------------------+
| JSON_MERGE_PRESERVE('1', 'true') |
+----------------------------------+
| [1, true]                        |
+----------------------------------+

mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}');
+---------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}') |
+---------------------------------------------+
| [1, 2, {"id": 47}]                          |
+---------------------------------------------+

此函数在 MySQL 5.7.22版本加入，之前是使用的同义函数 JSON_MERGE().

JSON_REMOVE(json_doc, path[, path] …)

删除指定的JSON数据返回删除后的JSON。多个path从左向右依次执行。

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

JSON_REPLACE(json_doc, path, val[, path, val] …)

替换指定的JSON值，返回替换后的新JSON，如果path不存在则忽略。

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

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

插入或者更新指定path的JSON的值，并返回操作后的新JSON 。

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

JSON_SET(), JSON_INSERT(), JSON_REPLACE() 这三个函数的区别:

JSON_SET() 存在则替换，不存在则添加
JSON_INSERT() 只新增，存在则忽略
JSON_REPLACE() 值替换，不存在则忽略

JSON_UNQUOTE(json_val)

去除引号并返回JSON值的 utf8mb4 编码的String .

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

JSON属性函数

Functions That Return JSON Value Attributes

JSON_DEPTH(json_doc)

返回JSON的最大深度。所有的空 array和空object或者标量值最大深度都是1

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

JSON_LENGTH(json_doc[, path])

返回JSON最大长度，如果指定了 path按path对应的JSON来计算。长度规则如下

标量值长度都是1
array类型返回其对应包含的元素个数
object类型返回其包含的key个数
不计算嵌套array或者object

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

JSON_TYPE(json_val)

返回JSON值的JSON类型，使用 utf8mb4字符串表示。

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

返回的类型包含

JSON 类型:
- OBJECT: JSON objects
- ARRAY: JSON arrays
- BOOLEAN: The JSON true and false literals
- NULL: The JSON null literal
Numeric 类型:
- INTEGER: MySQL TINYINT, SMALLINT, MEDIUMINT and INT and BIGINT scalars
- DOUBLE: MySQL DOUBLE FLOAT scalars
- DECIMAL: MySQL DECIMAL and NUMERIC scalars
Temporal 类型:
- DATETIME: MySQL DATETIME and TIMESTAMP scalars
- DATE: MySQL DATE scalars
- TIME: MySQL TIME scalars
String 类型:
- STRING: MySQL utf8 character type scalars: CHAR, VARCHAR, TEXT, ENUM, and SET
Binary 类型:
- BLOB: MySQL binary type scalars: BINARY, VARBINARY, BLOB
- BIT: MySQL BIT scalars
其他类型:
- OPAQUE (raw bits)

JSON_VALID(val)

判断是否为有效的 JSON ，返回 0、1

JSON工具函数

JSON Utility Functions

JSON_PRETTY(json_val)

返回一个格式化后易于阅读的JSON 串，MySQL 5.7.22 及以后版本可用。

JSON_STORAGE_SIZE(json_val)

返回JSON存储的字节（bytes）数

mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO jtable VALUES
    ->     ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT
    ->     jcol,
    ->     JSON_STORAGE_SIZE(jcol) AS Size
    -> FROM jtable;
+-----------------------------------------------+------+
| jcol                                          | Size |
+-----------------------------------------------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |
+-----------------------------------------------+------+
1 row in set (0.00 sec)

JSON Index

JSON 类型的列不能直接创建索引，但是可以利用虚拟字段间接建立索引。
创建一个虚拟字段，代表JSON中的某一个属性，然后在这个列上建立索引即可：

mysql> CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     INDEX i (g)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES
     >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
     >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c->>"$.name" AS name
     >     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name
     >    FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

参考

5.7 json-functions

8.0 json-functions