MySQL 8 中使用 JSON_TABLE 创建 JSON 临时表进行 JSON 复杂查询
MySQL 8 中使用 JSON_TABLE 创建 JSON 临时表进行 JSON 复杂查询
比如有个用户表信息表，使用 plans 字段存储了用户的套餐信息，这个表长这样:
mysql> SELECT id,email,plans from users ;
+-------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------+
| id | email | plans |
+-------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------+
| 10000 | t0@t0.com | [{"id": 101, "name": "套餐1", "expired_at": "2024-05-01 10:12:31"}, {"id": 102, "name": "套餐2", "expired_at": "2024-08-05 07:41:16"}] |
| 10001 | t1@t1.com | [{"name": "套餐2", "expired_at": "2023-12-11 05:07:11"}, {"name": "套餐3", "expired_at": "2023-11-08 16:02:51"}] |
| 10002 | t2@t2.com | [{"name": "套餐4", "expired_at": "2024-01-20 17:24:33"}] |
+-------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
想要根据 plans 字段 JSON 数组中的 name 字段获取指定套餐的用户及到期时间，但这里试图使用 JSON_EXTRACT(plans, '$[n].expired_at') 提取 JSON 中的字段时却无法知道索引 n 的值的。
这种情况可以使用 JSON_TABLE 将 JSON 数组中的值创建临时表来解决：
mysql> SELECT
-> u.id,
-> u.email,
-> json_tab.plan_name,
-> json_tab.plan_expired_at
-> FROM
-> users AS u
-> CROSS JOIN JSON_TABLE(
-> `plans`, '$[*]'
-> COLUMNS (
-> plan_id INT PATH '$.id' ERROR ON ERROR,
-> plan_name VARCHAR(40)PATH '$.name',
-> plan_expired_at datetime PATH '$.expired_at'
-> )
-> ) AS json_tab
-> WHERE
-> plan_name = '套餐2';
+-------+-----------+-----------+---------------------+
| id | email | plan_name | plan_expired_at |
+-------+-----------+-----------+---------------------+
| 10000 | t0@t0.com | 套餐2 | 2024-08-05 07:41:16 |
| 10001 | t1@t1.com | 套餐2 | 2023-12-11 05:07:11 |
+-------+-----------+-----------+---------------------+
2 rows in set (0.00 sec)
