MySQL 8.0 新增 JSON_TABLE 函数的使用

通过 JSON_TABLE 函数，可以将 JSON 文档中的数据提取出来，并以表格的形式返回客户端。


基本用法：

JSON_TABLE(
    json_doc,
    path COLUMNS (column_list)
) [AS] alias
  • 1.
  • 2.
  • 3.
  • 4.

其中：

  • json_doc 是 JSON 文档（可以是一个 JSON 列或 JSON 字符串）
  • path 是用于选择 JSON 文档中元素的路径表达式
  • column_list 定义了结果表的列


测试用例

mysql> select * from people;
+------+---------------------------------------------------+
| id   | info                                              |
+------+---------------------------------------------------+
|    1 | {"age": 25, "city": "Bei Jing", "name": "张三"}   |
|    2 | {"age": 27, "city": "He Bei", "name": "李四"}     |
+------+---------------------------------------------------+
2 rows in set (0.00 sec)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.


JSON_TABLE 函数使用

-- JSON_TABLE 函数使用
SELECT p.id, jt.name, jt.age, jt.city
FROM people p
JOIN JSON_TABLE(
    p.info,
    '$' COLUMNS (
        name VARCHAR(50) PATH '$.name',
        age INT PATH '$.age',
        city VARCHAR(50) PATH '$.city'
    )
) AS jt ON TRUE;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
-- 查询结果
mysql> -- JSON_TABLE 函数使用
mysql> SELECT p.id, jt.name, jt.age, jt.city
    -> FROM people p
    -> JOIN JSON_TABLE(
    ->     p.info,
    ->     '$' COLUMNS (
    ->         name VARCHAR(50) PATH '$.name',
    ->         age INT PATH '$.age',
    ->         city VARCHAR(50) PATH '$.city'
    ->     )
    -> ) AS jt ON TRUE
    -> ;
+------+--------+------+----------+
| id   | name   | age  | city     |
+------+--------+------+----------+
|    1 | 张三   |   25 | Bei Jing |
|    2 | 李四   |   27 | He Bei   |
+------+--------+------+----------+
2 rows in set (0.00 sec)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.


总结

JSON_TABLE 函数非常灵活，可以处理复杂的 JSON 结构，包括数组、嵌套对象等。它使得在 SQL 查询中处理 JSON 数据变得更加简单和直观，特别是在需要将 JSON 数据与关系数据结合使用的场景中。