MySQL如何将JSON提取的labelKey数组展平为单行单值？

不同数据库的JSON数组行转列语法略有差异，以下是主流数据库的实现方式：

MySQL 8.0+ 版本 #

MySQL 8.0 提供了JSON_TABLE函数专门处理JSON数组转多行的需求，直接嵌套在原查询的FROM子句中即可：

SELECT 
  labelKey
FROM d_json j,
JSON_TABLE(
  j.data->'$.options[*].labelKey',
  '$[*]' COLUMNS (labelKey VARCHAR(255) PATH '$')
) AS t;

你原来的双层JSON_EXTRACT写法也可以简化为j.data->'$.options[*].labelKey'，效果完全一致。如果是MySQL 5.x版本没有JSON_TABLE，可以借助辅助数字序列的方式实现：

• 先构建一个包含连续数字的临时序列，数字范围要覆盖你单个options数组的最大长度
• 用数字作为下标逐个提取数组元素
  示例代码：

-- 假设你的options数组最多不超过10个元素
SELECT 
  JSON_UNQUOTE(JSON_EXTRACT(j.data, CONCAT('$.options[', n.idx, '].labelKey'))) AS labelKey
FROM d_json j
JOIN (
  SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
  UNION ALL SELECT 8 UNION ALL SELECT 9
) n ON n.idx < JSON_LENGTH(j.data->'$.options')
WHERE JSON_EXTRACT(j.data, CONCAT('$.options[', n.idx, '].labelKey')) IS NOT NULL;

PostgreSQL 版本 #

PostgreSQL 可以用jsonb_array_elements_text函数直接展开数组：

SELECT 
  jsonb_array_elements_text(j.data->'options'->'labelKey') AS labelKey
FROM d_json j;

如果你的data字段是json类型而非jsonb，把jsonb_array_elements_text替换成json_array_elements_text即可。

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