MySQL的`JSON_TABLE`函数：如何利用它将`JSON`数组或对象转换为关系型数据？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，我们开始今天的讲座，主题是MySQL的JSON_TABLE函数，以及如何利用它将JSON数组或对象转换为关系型数据。

引言：JSON与关系型数据的鸿沟

在现代应用程序开发中，JSON（JavaScript Object Notation）作为一种轻量级的数据交换格式被广泛使用。然而，关系型数据库（如MySQL）仍然是存储结构化数据的基石。因此，经常需要在JSON数据和关系型数据之间进行转换。 MySQL 5.7.22引入了JSON_TABLE函数，为我们提供了一种强大的工具，可以直接在SQL查询中将JSON数据转换为关系型数据，从而弥合了这两种数据格式之间的鸿沟。

JSON_TABLE函数的基本语法

JSON_TABLE函数的基本语法如下：

JSON_TABLE(
    json_doc,
    path,
    COLUMNS (
        column_name data_type PATH json_path [ERROR ON EMPTY | DEFAULT value]
        [, column_name data_type PATH json_path [ERROR ON EMPTY | DEFAULT value]]
        ...
    )
) AS alias

让我们逐一解析这些参数：

json_doc: 包含JSON数据的表达式，可以是JSON类型的列、字符串字面量，或者其他返回JSON值的函数。
path: 一个JSON路径表达式，用于指定从JSON文档中提取哪些元素作为行。通常，这用于指定数组的路径，以便将数组中的每个元素转换为一行。
COLUMNS: 定义了结果表的列及其数据类型和JSON路径。
- column_name: 结果表中列的名称。
- data_type: 列的数据类型（例如，INT、VARCHAR、DATE）。
- PATH json_path: 用于从JSON文档中提取列值的JSON路径表达式。
- ERROR ON EMPTY: 如果指定的JSON路径在JSON文档中找不到匹配项，则引发错误。
- DEFAULT value: 如果指定的JSON路径在JSON文档中找不到匹配项，则使用提供的默认值。
alias: 结果表的别名，用于在查询中引用它。

示例1：将JSON数组转换为表格

假设我们有一个名为products的表，其中包含一个名为details的JSON列，它存储了产品信息的数组。数组中的每个元素都是一个JSON对象，包含name和price字段。

CREATE TABLE products (
    id INT PRIMARY KEY,
    details JSON
);

INSERT INTO products (id, details) VALUES
(1, '[{"name": "Laptop", "price": 1200}, {"name": "Mouse", "price": 25}]'),
(2, '[{"name": "Keyboard", "price": 75}, {"name": "Monitor", "price": 300}]');

现在，我们使用JSON_TABLE函数将details列中的JSON数组转换为表格：

SELECT
    p.id,
    jt.name,
    jt.price
FROM
    products p,
    JSON_TABLE(
        p.details,
        '$[*]' COLUMNS (
            name VARCHAR(255) PATH '$.name',
            price DECIMAL(10, 2) PATH '$.price'
        )
    ) AS jt;

在这个查询中：

p.details 是JSON文档。
'$[*]' 是JSON路径，表示数组中的所有元素。
COLUMNS 定义了两个列：name (VARCHAR(255)) 和 price (DECIMAL(10, 2))。
'$.name' 和 '$.price' 是JSON路径，用于从每个JSON对象中提取name和price值。
jt 是结果表的别名。

查询结果如下：

id	name	price
1	Laptop	1200.00
1	Mouse	25.00
2	Keyboard	75.00
2	Monitor	300.00

示例2：处理嵌套的JSON对象

假设我们的JSON数据包含嵌套的对象。例如，products表中的details列现在包含一个包含dimensions对象的JSON对象，该对象具有width和height字段。

ALTER TABLE products MODIFY COLUMN details JSON;

UPDATE products SET details = JSON_SET(details, '$.[0].dimensions', JSON_OBJECT('width', 30, 'height', 20)) WHERE id = 1;
UPDATE products SET details = JSON_SET(details, '$.[1].dimensions', JSON_OBJECT('width', 10, 'height', 5)) WHERE id = 1;
UPDATE products SET details = JSON_SET(details, '$.[0].dimensions', JSON_OBJECT('width', 40, 'height', 25)) WHERE id = 2;
UPDATE products SET details = JSON_SET(details, '$.[1].dimensions', JSON_OBJECT('width', 24, 'height', 12)) WHERE id = 2;

现在，我们可以使用JSON路径来访问嵌套对象中的字段：

SELECT
    p.id,
    jt.name,
    jt.price,
    jt.width,
    jt.height
FROM
    products p,
    JSON_TABLE(
        p.details,
        '$[*]' COLUMNS (
            name VARCHAR(255) PATH '$.name',
            price DECIMAL(10, 2) PATH '$.price',
            width INT PATH '$.dimensions.width',
            height INT PATH '$.dimensions.height'
        )
    ) AS jt;

查询结果如下：

id	name	price	width	height
1	Laptop	1200.00	30	20
1	Mouse	25.00	10	5
2	Keyboard	75.00	40	25
2	Monitor	300.00	24	12

示例3：处理缺失的字段

如果JSON文档中缺少某些字段，JSON_TABLE函数默认会返回NULL。可以使用ERROR ON EMPTY或DEFAULT子句来处理这种情况。

例如，如果某些产品缺少price字段，我们可以使用DEFAULT子句来提供默认值：

SELECT
    p.id,
    jt.name,
    jt.price
FROM
    products p,
    JSON_TABLE(
        p.details,
        '$[*]' COLUMNS (
            name VARCHAR(255) PATH '$.name',
            price DECIMAL(10, 2) PATH '$.price' DEFAULT 0.00
        )
    ) AS jt;

在这个查询中，如果price字段缺失，则jt.price将返回0.00。

如果希望在缺少字段时引发错误，可以使用ERROR ON EMPTY子句：

SELECT
    p.id,
    jt.name,
    jt.price
FROM
    products p,
    JSON_TABLE(
        p.details,
        '$[*]' COLUMNS (
            name VARCHAR(255) PATH '$.name',
            price DECIMAL(10, 2) PATH '$.price' ERROR ON EMPTY
        )
    ) AS jt;

如果price字段缺失，则此查询将引发错误。

示例4：将JSON对象转换为表格

JSON_TABLE也可以用于将JSON对象转换为表格，而不仅仅是JSON数组。在这种情况下，path参数通常设置为'$'，表示整个JSON对象。

假设我们有一个名为config的表，其中包含一个名为settings的JSON列，它存储了配置设置。

CREATE TABLE config (
    id INT PRIMARY KEY,
    settings JSON
);

INSERT INTO config (id, settings) VALUES
(1, '{"theme": "dark", "font_size": 12}');

现在，我们可以使用JSON_TABLE函数将settings列中的JSON对象转换为表格：

SELECT
    c.id,
    jt.theme,
    jt.font_size
FROM
    config c,
    JSON_TABLE(
        c.settings,
        '$' COLUMNS (
            theme VARCHAR(255) PATH '$.theme',
            font_size INT PATH '$.font_size'
        )
    ) AS jt;

在这个查询中：

c.settings 是JSON文档。
'$' 是JSON路径，表示整个JSON对象。

查询结果如下：

id	theme	font_size
1	dark	12

示例5：更复杂的JSON结构

假设我们的JSON数据更加复杂，包含嵌套的数组和对象。例如，products表中的details列现在包含一个包含多个options对象的数组，每个options对象都有color和size字段。

ALTER TABLE products MODIFY COLUMN details JSON;

UPDATE products SET details = JSON_SET(details, '$.[0].options', JSON_ARRAY(JSON_OBJECT('color', 'red', 'size', 'M'), JSON_OBJECT('color', 'blue', 'size', 'L'))) WHERE id = 1;
UPDATE products SET details = JSON_SET(details, '$.[1].options', JSON_ARRAY(JSON_OBJECT('color', 'green', 'size', 'S'), JSON_OBJECT('color', 'yellow', 'size', 'XL'))) WHERE id = 1;
UPDATE products SET details = JSON_SET(details, '$.[0].options', JSON_ARRAY(JSON_OBJECT('color', 'purple', 'size', 'M'), JSON_OBJECT('color', 'orange', 'size', 'L'))) WHERE id = 2;
UPDATE products SET details = JSON_SET(details, '$.[1].options', JSON_ARRAY(JSON_OBJECT('color', 'pink', 'size', 'S'), JSON_OBJECT('color', 'black', 'size', 'XL'))) WHERE id = 2;

为了提取这些options，我们需要使用嵌套的JSON_TABLE函数。首先，我们提取options数组，然后将数组中的每个元素转换为一行。

SELECT
    p.id,
    jt1.name,
    jt1.price,
    jt2.color,
    jt2.size
FROM
    products p,
    JSON_TABLE(
        p.details,
        '$[*]' COLUMNS (
            name VARCHAR(255) PATH '$.name',
            price DECIMAL(10, 2) PATH '$.price',
            options JSON PATH '$.options'
        )
    ) AS jt1,
    JSON_TABLE(
        jt1.options,
        '$[*]' COLUMNS (
            color VARCHAR(255) PATH '$.color',
            size VARCHAR(255) PATH '$.size'
        )
    ) AS jt2;

在这个查询中：

第一个JSON_TABLE函数提取name、price和options字段。
第二个JSON_TABLE函数将options数组转换为表格，提取color和size字段.

查询结果如下：

id	name	price	color	size
1	Laptop	1200.00	red	M
1	Laptop	1200.00	blue	L
1	Mouse	25.00	green	S
1	Mouse	25.00	yellow	XL
2	Keyboard	75.00	purple	M
2	Keyboard	75.00	orange	L
2	Monitor	300.00	pink	S
2	Monitor	300.00	black	XL

性能考虑

虽然JSON_TABLE函数非常强大，但在处理大型JSON文档时可能会影响性能。在可能的情况下，尽量避免在WHERE子句中使用JSON路径表达式，因为这可能会导致全表扫描。考虑使用索引来优化JSON查询。 MySQL 8.0 引入了对JSON列的索引支持，可以显著提高查询性能。此外，只提取需要的字段，避免提取整个JSON文档，可以减少I/O开销。

结论：JSON_TABLE 的应用场景

JSON_TABLE函数在以下场景中非常有用：

将JSON数据导入到关系型数据库中。
在SQL查询中分析JSON数据。
将JSON数据与关系型数据连接起来。
创建基于JSON数据的视图。
构建灵活的数据模型，其中某些属性可以存储为JSON，而其他属性存储为关系型数据。

总结：灵活处理JSON，简化数据转换

JSON_TABLE函数提供了一种强大而灵活的方式，用于将JSON数据转换为关系型数据，从而简化了JSON数据和关系型数据之间的转换过程。掌握这个函数，可以更有效地处理JSON数据，并在SQL查询中充分利用JSON数据的价值。

发表回复 取消回复

发表回复取消回复