MySQL如何将JSON数组类型列拆分为多行数据

单独用JSON_EXTRACT做不到你要的逐行拆分数组效果。这个函数的作用是按JSON路径提取匹配的值，不会自动把数组里的每个元素拆成独立行，要实现这个需求需要搭配对应数据库的JSON数组展开（常称炸裂/unnest）类表函数，关联原表字段同时保留空值行即可。

假设你的原表名为user_food_preferences，以下是几种主流数据库的可直接运行的写法：

MySQL 8.0 及以上版本 #

使用JSON_TABLE函数解析JSON数组为关系表，通过左连接保证空值行不丢失：

SELECT
  t.user_id,
  item.food
FROM user_food_preferences t
LEFT JOIN JSON_TABLE(
  t.favorite_foods,
  '$[*]' COLUMNS (food VARCHAR(32) PATH '$')
) item ON TRUE;

语法说明：$[*]是JSON路径中匹配数组所有下标的通配写法，LEFT JOIN会让favorite_foods为NULL的用户行保留，对应展开的food字段自动返回NULL，完全匹配你要的输出规则。

PostgreSQL #

如果字段是jsonb类型，用jsonb_array_elements_text展开，用LATERAL关联原表字段：

SELECT
  t.user_id,
  item.food
FROM user_food_preferences t
LEFT JOIN LATERAL jsonb_array_elements_text(
  t.favorite_foods -- 字段为json类型就替换为json_array_elements_text
) item(food) ON TRUE;

Spark/Hive SQL #

用EXPLODE函数配合OUTER关键字保留空值：

SELECT
  t.user_id,
  item.food
FROM user_food_preferences t
LATERAL VIEW OUTER EXPLODE(from_json(t.favorite_foods, 'array<string>')) item AS food;

运行以上对应版本的SQL后，返回结果和你给出的预期完全一致：

内容的提问来源于stack exchange，提问作者Anthony