MySQL 基于 JSON 实现 EXPLODE

1. 什么是 Explode

说明：

1. MySQL 不支持数组类型，但支持 JSON

Explode 是指将数组类型的元素分隔成多行，或者将 Map 类型的元素分隔成多行和多列。

示例 1：

Explode 后转换为：

示例 2：

Explode 后转换为：

2. JSON 表函数

说明：

1. 官方文档：https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

本节讲述将 JSON 数据转换成表数据的 JSON 函数。MySQL 8.0 支持这样的一个函数，JSON_TABLE()。

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

该函数从 JSON 文档中提取数据，并且将其作为具有指定列的关系表返回。该函数的完整语法如下所示：

JSON_TABLE(
    expr,
    path COLUMNS (column_list)
)   [AS] alias

column_list:
    column[, column][, ...]

column:
    name FOR ORDINALITY
    |  name type PATH string path [on_empty] [on_error]
    |  name type EXISTS PATH string path
    |  NESTED [PATH] path COLUMNS (column_list)

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR

expr 是返回 JSON 数据的表达式。可以是常量（'{"a":1}'），列（t1.json_data，它是在 FROM 子句中 JSON_TABLE() 前面指定的表 t1 中的列），或者函数调用（JSON_EXTRACT(t1.json_data,'$.post.comments')）。

path 是应用到数据源的 JSON 路径表达式。我们将与路径匹配的 JSON 值称为行源；它用于生成一行关系型数据。COLUMNS 子句计算行源，查找行源中特定的 JSON 值，并且将这些 JSON 值作为关系型数据行的独立列里的 SQL 值返回。

alias 是必需的。适用表别名的常用规则。

从 MySQL 8.0.27 开始，该函数以大小写不敏感的方式比较列名。

JSON_TABLE() 支持四种类型的列，如下所示：

• name FOR ORDINALITY：该类型枚举 COLUMNS 子句中的行，名为 name 的列是一个计数器，其类型是 UNSIGNED INT，初始值是 1。其等价于在 CREATE TABLE 语句中将该列指定为 AUTO_INCREMENT，并且可以用于区分为由 NESTED [PATH] 子句生成的多个行区分具有相同值的父行。
• name type PATH string_path [on_empty] [on_error]：该类型的列用于提取由 string_path 指定的值。type 是 MySQL 标量数据类型（不能是对象或数组）。JSON_TABLE() 将数据提取为 JSON，然后使用 MySQL 中适用于 JSON 数据的常规自动数据类型转换将其强制转换为列类型。缺失值将触发 on_empty 子句；保存对象或数组将触发可选的 on_error 子句；这同样发生在将保存为 JSON 的值强制转换为表列时发生错误，比如尝试将字符串 'asd' 保存到整数列中。
• name type EXISTS PATH path：如果由 path 指定的位置存在数据，那么该列返回 1，否则返回 0。type 可以是任何有效的 MySQL 数据类型，但通常应该指定为 INT 的某个变体。
• NESTED [PATH] path COLUMNS (column_list)：将 JSON 数据中的嵌套对象或数组与来自父对象或数组的 JSON 值一起平铺成一行。使用多个 PATH 选项可以将 JSON 值从多个嵌套级别投影到单行中。
  path 相对于 JSON_TABLE() 的父路径行路径。在嵌套路径的情况下，相对于父 NESTED [PATH] 子句的路径。

on_empty，如果指定，那么决定在缺失数据时 JSON_TABLE() 做什么（取决于类型）。当 NESTED PATH 子句中的列未匹配，并且为其生成 NULL 补充行时，也触发该子句。on_empty 接受以下值之一：

• NULL ON EMPTY：该列被设置为 NULL；这是默认行为。
• DEFAULT json_string ON EMPTY：提供的 json_string 被解析为 JSON，只要它有效，并且代替缺失的值。列类型规则也适用于默认值。
• ERROR ON EMPTY：抛出错误。

如果使用，on_error 接受以下值之一，其结果如下所示：

• NULL ON ERROR：该列被设置为 NULL；这是默认行为。
• DEFAULT json_string ON ERROR：json_string 被解析为 JSON（保证其有效），并且代替对象或数组。
• ERROR ON ERROR：抛出错误。

在 MySQL 8.0.21 之前的版本中，当传递给该函数的表达式和路径解析为 JSON null 时，JSON_TABLE() 将引发错误。在 MySQL 8.0.21 及以后的版本中，在这种情况下将返回 SQL NULL，以符合 SQL 标准，如下所示：

mysql> SELECT *
    ->   FROM
    ->     JSON_TABLE(
    ->       '[ {"c1": null} ]',
    ->       '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
    ->     ) as jt;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

以下查询展示 ON EMPTY 和 ON ERROR 的使用。对于路径 "$.a"，与 {"b":1} 对应的行是空的。并且尝试将 [1,2] 保存为标量将产生错误。

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
    ->     "$[*]"
    ->     COLUMNS(
    ->       rowid FOR ORDINALITY,
    ->       ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
    ->       aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
    ->       bx INT EXISTS PATH "$.b"
    ->     )
    ->   ) AS tt;

+-------+------+------------+------+
| rowid | ac   | aj         | bx   |
+-------+------+------------+------+
|     1 | 3    | "3"        |    0 |
|     2 | 2    | 2          |    0 |
|     3 | 111  | {"x": 333} |    1 |
|     4 | 0    | 0          |    0 |
|     5 | 999  | [1, 2]     |    0 |
+-------+------+------------+------+
5 rows in set (0.00 sec)

每个与 COLUMNS 关键字前的 path 匹配的项都映射到结果表中的单独一行。比如：

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    ->     "$[*]" COLUMNS(
    ->       xval VARCHAR(100) PATH "$.x",
    ->       yval VARCHAR(100) PATH "$.y"
    ->     )
    ->   ) AS  jt1;

+------+------+
| xval | yval |
+------+------+
| 2    | 8    |
| 3    | 7    |
| 4    | 6    |
+------+------+

NESTED PATH（或者简写为 NESTED；PATH 是可选的）为其所属的 COLUMNS 子句中的每个匹配生成一组记录。如果未匹配，那么嵌套路径的所有列都被置为 NULL。这实现了最顶层子句与 NESTED [PATH] 之间的外部连接。可以通过在 WHERE 子句中应用适当的条件模拟内部连接，如下所示：

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
    ->     '$[*]' COLUMNS(
    ->             a INT PATH '$.a',
    ->             NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
    ->            )
    ->    ) AS jt
    -> WHERE b IS NOT NULL;

+------+------+
| a    | b    |
+------+------+
|    1 |   11 |
|    1 |  111 |
|    2 |   22 |
|    2 |  222 |
+------+------+

兄弟嵌套路径 - 即同一 COLUMNS 子句中的 2 或多个 NESTED [PATH] 实例 - 一个接一个地处理，每次一个。当一个嵌套路径生成记录时，其它兄弟嵌套路径表达式的列被置为 NULL。这意味着单个 COLUMNS 子句内的单个匹配的记录总数是和，而非 NESTED [PATH] 修饰符生成的所有记录的乘积。如下所示：

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',
    ->     '$[*]' COLUMNS(
    ->         a INT PATH '$.a',
    ->         NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$'),
    ->         NESTED PATH '$.b[*]' COLUMNS (b2 INT PATH '$')
    ->     )
    -> ) AS jt;

+------+------+------+
| a    | b1   | b2   |
+------+------+------+
|    1 |   11 | NULL |
|    1 |  111 | NULL |
|    1 | NULL |   11 |
|    1 | NULL |  111 |
|    2 |   22 | NULL |
|    2 |  222 | NULL |
|    2 | NULL |   22 |
|    2 | NULL |  222 |
+------+------+------+

FOR ORDINALITY 枚举 COLUMNS 子句生成的记录，可用于区分嵌套路径的父记录，尤其是当父记录中的值相同时。如下所示：

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": "a_val",
    '>       "b": [{"c": "c_val", "l": [1,2]}]},
    '>     {"a": "a_val",
    '>       "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',
    ->     '$[*]' COLUMNS(
    ->       top_ord FOR ORDINALITY,
    ->       apath VARCHAR(10) PATH '$.a',
    ->       NESTED PATH '$.b[*]' COLUMNS (
    ->         bpath VARCHAR(10) PATH '$.c',
    ->         ord FOR ORDINALITY,
    ->         NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')
    ->         )
    ->     )
    -> ) as jt;

+---------+---------+---------+------+-------+
| top_ord | apath   | bpath   | ord  | lpath |
+---------+---------+---------+------+-------+
|       1 |  a_val  |  c_val  |    1 | 1     |
|       1 |  a_val  |  c_val  |    1 | 2     |
|       2 |  a_val  |  c_val  |    1 | 11    |
|       2 |  a_val  |  c_val  |    2 | 22    |
+---------+---------+---------+------+-------+

源文档是包含两个元素的数组；每个元素都产生两行。apath 和 bpath 的值在整个结果集中都相同；这意味着它们不能用于确定 lpath 值是否来自相同的或不同的父级。ord 列的值与 top_ord 等于 1 的记录集保持相同，因此这两个值来自单个对象。剩下的两个值来自不同的对象，因为它们在 ord 列中有不同的值。

通常，不能在同一 FROM 子句中连接依赖前面的表中的列的派生表。根据 SQL 标准，MySQL 对表函数进行例外处理；即使在不支持 LATERAL 关键字的 MySQL 版本（8.0.13 及更早版本）中，它们也被认为是横向派生表。在支持 LATERAL 的版本（8.0.14 及更高版本）中，它是隐式的，因此根据标准，不允许在 JSON_TABLE() 前面使用 LATERAL。

假设有一个表 t1，并且使用下面的语句填充：

DROP TABLE IF EXISTS t1;

CREATE TABLE IF NOT EXISTS t1
(
    c1 INT,
    c2 CHAR(1),
    c3 JSON
);

INSERT INTO t1 ()
VALUES
    ROW (1, 'z', JSON_OBJECT('a', 23, 'b', 27, 'c', 1)), ROW
        (1, 'y', JSON_OBJECT('a', 44, 'b', 22, 'c', 11)), ROW
        (2, 'x', JSON_OBJECT('b', 1, 'c', 15)), ROW
        (3, 'w', JSON_OBJECT('a', 5, 'b', 6, 'c', 7)), ROW
        (5, 'v', JSON_OBJECT('a', 123, 'c', 1111))
;

那么可以执行连接，比如下面这个，其中 JSON_TABLE() 作为派生表，同时它引用前面引用的表中的列：

SELECT c1, c2, JSON_EXTRACT(c3, '$.*')
FROM t1 AS m
         JOIN
     JSON_TABLE(
             m.c3,
             '$.*'
             COLUMNS (
                 at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY,
                 bt VARCHAR(10) PATH '$.b' DEFAULT '2' ON EMPTY,
                 ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY
                 )
     ) AS tt
     ON m.c1 > tt.at;

尝试在此查询中使用 LATERAL 关键字将引发 ER_PARSE_ERROR。

3. JSON 聚合函数

• JSON_ARRAYAGG()：返回单个 JSON 数组作为结果集
• JSON_OBJECTAGG()：返回单个 JSON 对象作为结果集

JSON_ARRAYAGG() 示例：

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
    -> FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.00 sec)

JSON_OBJECTAGG() 示例：

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
    -> FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value)      |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

4. Explode 示例

创建测试表及数据集：

DROP TABLE IF EXISTS explode;

CREATE TABLE IF NOT EXISTS explode
(
    id  BIGINT UNSIGNED,
    arr JSON,
    m   JSON
);

INSERT INTO explode
VALUES
    ROW (1, JSON_ARRAY(11, 12, 13), JSON_OBJECT('key1', 'value1', 'key2', 'value2')), ROW
        (2, JSON_ARRAY(21), JSON_OBJECT('key3', 'value3'));

4.1. Explode 数组

SELECT explode.id, eles.ele
FROM explode
         JOIN JSON_TABLE(explode.arr,
                         '$[*]' COLUMNS (
                             ele BIGINT PATH '$'
                             )
              ) AS eles;

4.2. Explode Map

首先通过自定义函数将 JSON Object 转换成由二元组组成的 JSON ARRAY；然后使用上一章节的方式 Explode。

DROP FUNCTION IF EXISTS JsonObjectToArray;

DELIMITER //

CREATE FUNCTION IF NOT EXISTS JsonObjectToArray(input_object JSON) RETURNS JSON
    DETERMINISTIC
BEGIN
    DECLARE res_array JSON DEFAULT '[]';

    SELECT JSON_ARRAYAGG(
                   JSON_ARRAY(ks.one_key, JSON_EXTRACT(input_object, CONCAT('$.', JSON_QUOTE(ks.one_key)))))
    INTO res_array
    FROM JSON_TABLE(
                 JSON_KEYS(input_object),
                 '$[*]' COLUMNS (
                     id FOR ORDINALITY,
                     one_key varchar(1024) PATH '$'
                     )
         ) AS ks;
    RETURN res_array;
END //

DELIMITER ;

SELECT explode.id, eles.k, eles.v
FROM explode
         JOIN JSON_TABLE(JsonObjectToArray(explode.m),
                         '$[*]' COLUMNS (
                             k VARCHAR(1024) PATH '$[0]',
                             v VARCHAR(1024) PATH '$[1]'
                             )
              ) AS eles;

参考文档

• https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html
• https://blog.csdn.net/acmman/article/details/131933738