PostgreSQL：将含ID的JSON数组替换为对应描述

嘿，我明白你想要实现的效果了——把EntitiesWithTags表中JSON数组里的标签ID，替换成Tags表中对应的描述文本对吧？这在PostgreSQL里可以通过JSON处理函数+关联查询轻松搞定，我给你详细说说怎么做：

核心思路 #

1. 先把JSON数组中的每个ID拆分成单独的行（展开JSON数组）
2. 关联Tags表，把ID映射成对应的Description
3. 再把映射后的描述重新聚合回JSON数组，对应到原表的每条记录

完整SQL查询 #

SELECT 
  ewt.id,
  json_agg(t.Description ORDER BY idx) AS jsonArray
FROM EntitiesWithTags ewt
-- 展开JSON数组，同时保留元素的原始索引（保证顺序和原数组一致）
JOIN json_array_elements_text(ewt.jsonArray) WITH ORDINALITY AS j(id_str, idx)
  ON TRUE
-- 关联Tags表，把字符串ID转成整数匹配
JOIN Tags t ON t.Id = j.id_str::INT
GROUP BY ewt.id
ORDER BY ewt.id;

代码解释 #

• json_array_elements_text(ewt.jsonArray)：把JSON数组里的每个元素以文本形式拆分成单独的行，WITH ORDINALITY会额外生成一个idx列，记录元素在原数组中的位置，这样聚合的时候能保持原顺序。
• j.id_str::INT：因为JSON数组里的ID是字符串类型，需要转成整数才能和Tags表的Id字段匹配。
• json_agg(t.Description ORDER BY idx)：把关联后的描述文本按原数组顺序聚合回JSON数组，替换原来的ID数组。

查询结果 #

执行上面的SQL后，你会得到期望的输出：

+----+---------------------+
| id | jsonArray           |
+----+---------------------+
| 1  | ["Test", "Hello"]   |
| 2  | ["Test", "Goodbye"] |
| 3  | ["Hello"]           |
+----+---------------------+

额外提示 #

如果你的JSON数组里可能存在Tags表中没有的ID，可以把JOIN Tags改成LEFT JOIN Tags，这样不存在的ID会被映射为null；如果想过滤掉这些无效ID，就保持INNER JOIN即可。

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