如何将SQL表中Name列按/拆分并返回数组形式结果？

现有数据表MyTable，包含Id和Name两列。其中Name列的值分为两种情况：一种是以/分隔的多值字符串（例如John/Warner/Jacob），另一种是单值字符串（例如Kol）。当前执行SELECT Id, Name FROM MyTable时，返回的Name为字符串格式，示例如下：

{
        "id": 1,
        "name": "John/Warner/Jacob",
},
{
        "id": 2,
        "name": "Kol",
},

需要编写SQL查询语句，让Name列返回数组形式的拆分结果，期望输出如下：

{
        "id": 1,
        "name": ["John", "Warner", "Jacob"],
},
{
        "id": 2,
        "name": ["Kol"] ,
},

针对不同数据库的实现方案 #

1. PostgreSQL #

直接使用内置的string_to_array函数即可完成转换：

SELECT 
    Id,
    string_to_array(Name, '/') AS name
FROM MyTable;

2. MySQL 8.0+ #

通过JSON_TABLE拆分字符串后，用JSON_ARRAYAGG聚合为JSON数组：

SELECT 
    t.Id,
    JSON_ARRAYAGG(j.name_part) AS name
FROM MyTable t
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(t.Name, '/', '","'), '"]'),
    '$[*]' COLUMNS(name_part VARCHAR(255) PATH '$')
) j
GROUP BY t.Id;

单值字符串也会被转为单个元素的数组，符合需求。

3. SQL Server #

用STRING_SPLIT拆分字符串，再通过STRING_AGG和JSON_QUERY生成标准JSON数组：

SELECT 
    Id,
    JSON_QUERY('["' + STRING_AGG(STRING_ESCAPE(value, 'json'), '","') + '"]') AS name
FROM MyTable
CROSS APPLY STRING_SPLIT(Name, '/')
GROUP BY Id;

STRING_ESCAPE用于处理特殊字符，避免JSON格式错误。

4. Oracle #

通过递归拆分字符串，再用JSON_ARRAYAGG聚合为数组：

SELECT 
    Id,
    JSON_ARRAYAGG(name_part) AS name
FROM (
    SELECT 
        t.Id,
        REGEXP_SUBSTR(t.Name, '[^/]+', 1, LEVEL) AS name_part
    FROM MyTable t
    CONNECT BY LEVEL <= REGEXP_COUNT(t.Name, '/') + 1
    AND PRIOR t.Id = t.Id
    AND PRIOR SYS_GUID() IS NOT NULL
)
GROUP BY Id;

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