如何将JSON数组格式的SHIFT_ID转换为关联班次名称并拼接？

需求说明 #

需要将以下两个表的数据转换，把Table1中SHIFT_ID字段的JSON数组替换为Table2中对应的SHIFT_NAME，并以逗号分隔拼接：

原始表结构及数据 #

Table 1：

ID | DATA_1 | SHIFT_ID
----------------------------
1  | 100    | ["1","2","3"]
2  | 150    | ["1","3"]
3  | 50     | ["3"]

Table 2：

ID | SHIFT_NAME
----------------
1  | Morning
2  | Afternoon
3  | Night

目标结果 #

ID | DATA_1 | SHIFT_ID
----------------------------
1  | 100    | Morning, Afternoon, Night
2  | 150    | Morning, Night
3  | 50     | Night

你的现有尝试 #

你写的SQL存在几个问题：CTE中T2引用自身会导致错误，关联条件T2.ID IN (T1.ID)逻辑错误，且缺少字符串聚合步骤。原始尝试代码如下：

WITH T2 AS
(
    SELECT ID, SHIFT_NAME FROM T2
)
SELECT T1.*, T2.*
FROM T1
INNER JOIN T2 ON T2.ID IN (T1.ID)

解决方案 #

实现这个需求的核心步骤是：拆分JSON数组为单行数据 → 关联Table2匹配名称 → 按ID聚合拼接名称。以下是不同数据库的具体实现：

1. PostgreSQL 版本 #

利用jsonb_array_text_elements拆分JSON数组，再用string_agg聚合：

SELECT
    t1.ID,
    t1.DATA_1,
    string_agg(t2.SHIFT_NAME, ', ') AS SHIFT_ID
FROM Table1 t1
CROSS JOIN jsonb_array_text_elements(t1.SHIFT_ID::jsonb) AS shift_ids(id)
JOIN Table2 t2 ON t2.ID = shift_ids.id::int
GROUP BY t1.ID, t1.DATA_1
ORDER BY t1.ID;

2. MySQL 版本 #

使用JSON_TABLE拆分JSON数组，GROUP_CONCAT聚合：

SELECT
    t1.ID,
    t1.DATA_1,
    GROUP_CONCAT(t2.SHIFT_NAME ORDER BY t2.ID SEPARATOR ', ') AS SHIFT_ID
FROM Table1 t1
JOIN JSON_TABLE(
    t1.SHIFT_ID,
    '$[*]' COLUMNS(id VARCHAR(10) PATH '$')
) AS shift_ids
JOIN Table2 t2 ON t2.ID = shift_ids.id
GROUP BY t1.ID, t1.DATA_1
ORDER BY t1.ID;

3. SQL Server 版本 #

通过OPENJSON拆分JSON数组，STRING_AGG聚合：

SELECT
    t1.ID,
    t1.DATA_1,
    STRING_AGG(t2.SHIFT_NAME, ', ') WITHIN GROUP (ORDER BY t2.ID) AS SHIFT_ID
FROM Table1 t1
CROSS APPLY OPENJSON(t1.SHIFT_ID) WITH (id VARCHAR(10) '$') AS shift_ids
JOIN Table2 t2 ON t2.ID = shift_ids.id
GROUP BY t1.ID, t1.DATA_1
ORDER BY t1.ID;

关键说明 #

• 拆分数组：不同数据库提供了不同的JSON数组拆分函数，需要根据你使用的数据库选择对应方法。
• 聚合拼接：使用数据库自带的字符串聚合函数（如string_agg/GROUP_CONCAT/STRING_AGG）来将多行名称合并为逗号分隔的字符串。
• 排序：如果需要保持Shift名称的顺序，可以在聚合时指定排序规则。

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