从SQL表读取JSON列，提取并拼接数组中的电话号码为字符串

针对不同SQL数据库，提供以下直接可行的解决方案：

SQL Server #

使用OPENJSON解析JSON数组，配合STRING_AGG函数实现字符串拼接：

SELECT
    Id,
    Column1 AS [Column 1],
    Column2 AS [Column 2],
    STRING_AGG(pn.PhoneNumber, ', ') AS [JSON Column]
FROM YourTable
CROSS APPLY OPENJSON(JsonData, '$.PhoneNumbers')
WITH (PhoneNumber VARCHAR(20) '$.PhoneNumber') AS pn
GROUP BY Id, Column1, Column2

MySQL（8.0+） #

通过JSON_TABLE拆分JSON数组，再用GROUP_CONCAT完成拼接：

SELECT
    Id,
    Column1 AS `Column 1`,
    Column2 AS `Column 2`,
    GROUP_CONCAT(pn.PhoneNumber SEPARATOR ', ') AS `JSON Column`
FROM YourTable
JOIN JSON_TABLE(
    JsonData,
    '$.PhoneNumbers[*]' COLUMNS (PhoneNumber VARCHAR(20) PATH '$.PhoneNumber')
) AS pn
GROUP BY Id, Column1, Column2

PostgreSQL #

利用jsonb_array_elements（或json_array_elements）展开数组，结合string_agg拼接结果：

SELECT
    Id,
    Column1 AS "Column 1",
    Column2 AS "Column 2",
    string_agg((pn->>'PhoneNumber'), ', ') AS "JSON Column"
FROM YourTable,
     jsonb_array_elements(JsonData->'PhoneNumbers') AS pn
GROUP BY Id, Column1, Column2

注：如果JSON列是json类型，将jsonb_array_elements替换为json_array_elements即可。

内容的提问来源于stack exchange，提问作者Bruno Gonzalez Torres