MySQL 8.0 新增 JSON_TABLE 函数的使用

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

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)

-- 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;

-- 查询结果
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)