如何去除SQL Server表中JSON Array的重复值（保留首次出现）

我在SQL Server表的一个字段中存储了如下JSON数组：

["I3","I23","B1","B3","B2","B4","B6","I1","I11","I4","I14","I24","I34","I5","I15","I25","I35","I6","I16","I26","I36","I21","B5","I31","I11","I3","I1","I31","I21","I21","I5","I4","I3","I21","I4","I23","B1","I23","I3","B1","B2","B3","I15","I15","B2","I13","I2"]

该数组存在重复值，我需要去除重复值并保留首次出现的条目，预期结果如下：

["I3","I23","B1","B3","B2","B4","B6","I1","I11","I4","I14","I24","I34","I5","I15","I25","I35","I6","I16","I26","I36","I21","B5","I31","I13","I2"]

我尝试过多种解决方案，目前最接近的结果是对象数组而非值数组，输出如下：

[
     {
        "c": "B1"
     },
     {
        "c": "B2"
     },
     {
        "c": "B3"
     },
     {
        "c": "B4"
     },
     {
        "c": "B5"
     },
     {
        "c": "B6"
     },
     {
        "c": "I1"
     },
     {
        "c": "I11"
     },
     {
        "c": "I13"
     },
     {
        "c": "I14"
     },
     {
        "c": "I15"
     },
     {
        "c": "I16"
     },
     {
        "c": "I2"
     },
     {
        "c": "I21"
     },
     {
        "c": "I23"
     },
     {
        "c": "I24"
     },
     {
        "c": "I25"
     },
     {
        "c": "I26"
     },
     {
        "c": "I3"
     },
     {
        "c": "I31"
     },
     {
        "c": "I34"
     },
     {
        "c": "I35"
     },
     {
        "c": "I36"
     },
     {
        "c": "I4"
     },
     {
        "c": "I5"
     },
     {
        "c": "I6"
     }
  ]

请问该如何修正以得到预期的JSON值数组？

通用版本（兼容SQL Server 2017及以上） #

假设你的表名为YourTable，存储JSON数组的字段名为JsonColumn，可以用以下SQL语句实现需求：

SELECT 
    JSON_QUERY('[' + STRING_AGG(JSON_QUOTE(value), ',') + ']') AS DistinctJsonArray
FROM (
    SELECT 
        value,
        CAST([key] AS INT) AS elementIndex,
        ROW_NUMBER() OVER (PARTITION BY value ORDER BY CAST([key] AS INT)) AS rn
    FROM YourTable
    CROSS APPLY OPENJSON(JsonColumn)
) t
WHERE rn = 1
ORDER BY elementIndex;

关键逻辑说明：

1. OPENJSON(JsonColumn)：将JSON数组拆分成行，每行包含数组元素的value（值）和key（原始数组中的索引，字符串类型）。
2. 去重并保留顺序：通过ROW_NUMBER() OVER (PARTITION BY value ORDER BY CAST([key] AS INT))为每个重复元素标记行号，只保留首次出现的条目（rn=1），同时用elementIndex记录原始位置。
3. 重组为纯值数组：用STRING_AGG(JSON_QUOTE(value), ',')将去重后的元素拼接成合法的JSON字符串片段，再通过JSON_QUERY包装成JSON数组，避免被转义为字符串类型。

简洁版本（SQL Server 2022及以上） #

如果你的SQL Server版本支持JSON_ARRAYAGG函数，可以用更简洁的写法：

SELECT 
    JSON_ARRAYAGG(value ORDER BY elementIndex) AS DistinctJsonArray
FROM (
    SELECT 
        value,
        MIN(CAST([key] AS INT)) AS elementIndex
    FROM YourTable
    CROSS APPLY OPENJSON(JsonColumn)
    GROUP BY value
) t
ORDER BY elementIndex;

该写法通过MIN(CAST([key] AS INT))获取每个元素首次出现的索引，再按索引排序后用JSON_ARRAYAGG直接生成纯值JSON数组。

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