MySQL JSON_TABLE的详细介绍|极客笔记

MySQL JSON_TABLE的详细介绍

1. 简介

MySQL是一种常用的关系型数据库管理系统，在处理结构化数据方面有很高的效率和稳定性。然而，现实世界中的数据往往具有复杂的结构，例如JSON格式。为了能够更方便地处理JSON数据，MySQL从版本8.0开始引入了JSON_TABLE函数。

JSON_TABLE函数是一种用于将JSON格式的数据转换为关系表格的方法。它可以将JSON值的内容提取到表的行和列中，从而方便地对数据进行查询、分析和处理。

在本文中，我们将详细介绍MySQL JSON_TABLE函数的使用方法。

2. JSON_TABLE函数的语法

JSON_TABLE函数的基本语法如下：

JSON_TABLE(json_doc, "$[*]" COLUMNS(...)) AS table_alias

json_doc：要处理的JSON文档。
“$[*]”：JSON路径，表示要处理的是JSON文档中的所有元素。
COLUMNS(…)：定义要提取的列和其数据类型。

3. 示例代码

下面是几个示例代码，展示了JSON_TABLE函数的用法及其运行结果。

3.1 示例1：提取根对象的属性

假设我们有一个名为”employees”的JSON文档，它包含多个员工的信息。每个员工有”ID”、”Name”和”Salary”三个属性。我们可以使用JSON_TABLE函数将这些属性提取到表中。

SELECT *
FROM JSON_TABLE('[
    {"ID": 1, "Name": "John", "Salary": 5000},
    {"ID": 2, "Name": "Jane", "Salary": 6000}
]' , " $[*]" COLUMNS ( ID INT PATH "$ .ID",
    Name VARCHAR(255) PATH " $.Name", Salary INT PATH "$ .Salary"
)) AS t;

运行结果：

+------+-------+--------+
| ID   | Name  | Salary |
+------+-------+--------+
|    1 | John  |   5000 |
|    2 | Jane  |   6000 |
+------+-------+--------+

3.2 示例2：提取嵌套对象的属性

假设我们有一个名为”departments”的JSON文档，它包含多个部门的信息，每个部门又包含多个员工。我们可以使用JSON_TABLE函数将部门名称、员工ID和员工姓名提取到表中。

SELECT d.Name AS Department, e.ID, e.Name
FROM JSON_TABLE('[
    {"Name": "HR", "Employees": [
        {"ID": 1, "Name": "John"},
        {"ID": 2, "Name": "Jane"}
    ]},
    {"Name": "Finance", "Employees": [
        {"ID": 3, "Name": "Mike"},
        {"ID": 4, "Name": "Emily"}
    ]}
]' , " $[*]" COLUMNS ( Department VARCHAR(255) PATH "$ .Name",
    NESTED PATH " $.Employees[*]" COLUMNS ( ID INT PATH "$ .ID",
        Name VARCHAR(255) PATH "$.Name"
    )
)) AS t

运行结果：

+------------+----+-------+
| Department | ID | Name  |
+------------+----+-------+
| HR         |  1 | John  |
| HR         |  2 | Jane  |
| Finance    |  3 | Mike  |
| Finance    |  4 | Emily |
+------------+----+-------+

3.3 示例3：使用ON条件进行连接

我们可以在JSON_TABLE函数中使用ON条件将多个JSON数组连接起来。

假设我们有两个名为”departments”和”salaries”的JSON文档，分别包含部门信息和薪水信息。我们可以使用JSON_TABLE函数将这两个数组连接起来。

SELECT d.Name AS Department, s.Name AS Name, s.Salary
FROM JSON_TABLE('[
    {"Name": "HR", "Employees": [
        {"ID": 1, "Name": "John"},
        {"ID": 2, "Name": "Jane"}
    ]},
    {"Name": "Finance", "Employees": [
        {"ID": 3, "Name": "Mike"},
        {"ID": 4, "Name": "Emily"}
    ]}
]' , " $[*]" COLUMNS ( Department VARCHAR(255) PATH "$ .Name",
    NESTED PATH " $.Employees[*]" COLUMNS ( ID INT PATH "$ .ID",
        Name VARCHAR(255) PATH " $.Name" ) )) AS d JOIN JSON_TABLE('[ {"Name": "John", "Salary": 5000}, {"Name": "Jane", "Salary": 6000}, {"Name": "Mike", "Salary": 7000}, {"Name": "Emily", "Salary": 8000} ]' , "$ [*]" COLUMNS (
    Name VARCHAR(255) PATH " $.Name", Salary INT PATH "$ .Salary"
)) AS s ON d.Name = s.Name;

运行结果：

+------------+------+--------+
| Department | Name | Salary |
+------------+------+--------+
| HR         | John |   5000 |
| HR         | Jane |   6000 |
| Finance    | Mike |   7000 |
| Finance    | Emily|   8000 |
+------------+------+--------+

3.4 示例4：使用预定义格式

JSON_TABLE函数还支持预定义的格式，例如RFC4180格式的CSV文件。我们可以指定预定义的格式对JSON文档进行解析。

SELECT t.*
FROM JSON_TABLE(
    '"
ID,Name,Salary
1,John,5000
2,Jane,6000
"',
    "$"
    COLUMNS (
        ID INT FORMAT JSON,
        Name VARCHAR(255) FORMAT JSON,
        Salary INT FORMAT JSON
    )
) AS t;

运行结果：

+------+-------+--------+
| ID   | Name  | Salary |
+------+-------+--------+
|    1 | John  |   5000 |
|    2 | Jane  |   6000 |
+------+-------+--------+

3.5 示例5：使用缺省值

当JSON文档中缺少某些属性时，我们可以使用缺省值来填充空值。

SELECT t.*
FROM JSON_TABLE('[
    {"ID": 1, "Name": "John"},
    {"ID": 2}
]' , " $[*]" COLUMNS ( ID INT PATH "$ .ID",
    Name VARCHAR(255) PATH " $.Name", Salary INT PATH "$ .Salary" DEFAULT 0
)) AS t;

运行结果：

+------+-------+--------+
| ID   | Name  | Salary |
+------+-------+--------+
|    1 | John  |      0 |
|    2 | NULL  |      0 |
+------+-------+--------+