MySQL JSON数组转行

MySQL JSON数组转行

在MySQL数据库中，通常我们会将数据以JSON格式存储在表的某个字段中。有时候我们需要将这些JSON数组进行转行，即将数组中的每个元素作为一行数据来处理。本文将详细介绍如何实现这一功能。

JSON数组在MySQL中的存储

在MySQL 5.7以及更新的版本中，支持JSON数据类型，我们可以创建一列存储JSON格式的数据。例如：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    emails JSON
);

INSERT INTO users VALUES (1, 'Alice', '["alice@example.com", "alice@work.com"]');

在上面的示例中，我们创建了一个名为users的表，其中包含一个emails字段，用于存储用户的邮箱地址。邮箱地址以JSON数组的形式存储。

使用JSON_TABLE函数将JSON数组转行

MySQL提供了JSON_TABLE函数，可以将JSON数据转换为关系数据。我们可以使用JSON_TABLE将JSON数组转行，例如：

SELECT id, name, email
FROM users,
JSON_TABLE(emails, "[*]" COLUMNS (
    email VARCHAR(255) PATH ""
)) AS email_table;

以上示例查询将返回如下结果：

| id | name  | email               |
|----|-------|---------------------|
| 1  | Alice | alice@example.com    |
| 1  | Alice | alice@work.com       |

在JSON_TABLE函数中，我们指定了列名email和路径$，$[*]表示获取JSON数组中的所有元素。使用JSON_TABLE函数可以方便地将JSON数组转换为行数据。

使用CTE（通用表达式）将JSON数组转行

除了使用JSON_TABLE函数，我们还可以使用CTE（通用表达式）来实现将JSON数组转行的功能。例如：

WITH RECURSIVE cte AS (
    SELECT id, name, JSON_EXTRACT(emails, CONCAT('[', 0, ']')) AS email
    FROM users
    WHERE JSON_EXTRACT(emails, CONCAT('[', 0, ']')) IS NOT NULL
    UNION ALL
    SELECT id, name, JSON_EXTRACT(emails, CONCAT('[', cte.r+1, ']')) AS email
    FROM cte
    JOIN users ON cte.id = users.id
    CROSS JOIN
    (SELECT MAX(JSON_LENGTH(emails)) AS r FROM users) AS m
    WHERE JSON_EXTRACT(emails, CONCAT('[', cte.r+1, ']')) IS NOT NULL
)
SELECT id, name, email
FROM cte;

以上示例中，我们使用CTE实现了将JSON数组转行的功能。通过递归的方式，从JSON数组中依次取出每个元素，并生成对应的行数据。

结论

在实践中，我们经常会遇到需要将JSON数组转行的情况。在MySQL中，我们可以使用JSON_TABLE函数或CTE实现这一功能。无论采用哪种方式，都能够方便地将JSON数组转换为行数据进行进一步处理。