SQL Server 2014如何提取NVARCHAR列中JSON数组的所有值？

SQL Server 2014确实没有原生的JSON解析函数，不过咱们可以通过字符串预处理+递归CTE（或者数字表）的方式实现你要的效果——把myData里的JSON数组拆成单独的行，再去重得到唯一值。

核心思路 #

先把JSON数组的格式清理成普通的逗号分隔字符串（去掉首尾的[]、替换掉双引号），再把这个字符串拆分成单个元素，最后用DISTINCT去重。

方法一：递归CTE拆分（适合中小量数据） #

假设你的表名叫YourTable，直接用下面的代码就能实现：

WITH CleanedJson AS (
    -- 第一步：把JSON数组转成纯逗号分隔的字符串
    SELECT
        id,
        name,
        -- 去掉开头的[和结尾的]，再把所有双引号替换为空
        REPLACE(STUFF(STUFF(myData, 1, 1, ''), LEN(myData), 1, ''), '"', '') AS SplitReadyString
    FROM YourTable
    -- 过滤掉空数组或者null值（可选，根据你的数据情况调整）
    WHERE myData IS NOT NULL AND myData <> '[]'
),
RecursiveSplitter AS (
    -- 递归起始：提取第一个元素
    SELECT
        id,
        name,
        -- 取第一个逗号之前的内容作为当前元素
        CASE WHEN CHARINDEX(',', SplitReadyString) > 0
             THEN LEFT(SplitReadyString, CHARINDEX(',', SplitReadyString) - 1)
             ELSE SplitReadyString
        END AS Item,
        -- 剩下未拆分的字符串
        CASE WHEN CHARINDEX(',', SplitReadyString) > 0
             THEN RIGHT(SplitReadyString, LEN(SplitReadyString) - CHARINDEX(',', SplitReadyString))
             ELSE ''
        END AS RemainingText
    FROM CleanedJson

    UNION ALL

    -- 递归循环：继续拆分剩余的字符串
    SELECT
        id,
        name,
        CASE WHEN CHARINDEX(',', RemainingText) > 0
             THEN LEFT(RemainingText, CHARINDEX(',', RemainingText) - 1)
             ELSE RemainingText
        END AS Item,
        CASE WHEN CHARINDEX(',', RemainingText) > 0
             THEN RIGHT(RemainingText, LEN(RemainingText) - CHARINDEX(',', RemainingText))
             ELSE ''
        END AS RemainingText
    FROM RecursiveSplitter
    WHERE RemainingText <> ''
)
-- 最终获取去重后的唯一值
SELECT DISTINCT Item AS UniqueValue
FROM RecursiveSplitter
ORDER BY UniqueValue;

代码解释

• CleanedJson CTE：负责把["Fingers","Right-"]这种格式转换成Fingers,Right-，方便后续拆分。
• RecursiveSplitter CTE：通过递归的方式，每次从剩余字符串里抠出第一个元素，直到没有剩余内容为止，这样就把所有数组元素拆成了单独的行。
• 最后用DISTINCT去重，得到你需要的唯一值列表。

方法二：数字表拆分（适合大量数据，性能更好） #

如果你的表数据量很大，递归CTE可能会有性能瓶颈，这时候可以用数字表来替代递归：

-- 先生成一个足够大的数字序列（这里用系统表生成1到1000的数字，足够应对大部分数组长度）
WITH NumberSequence AS (
    SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SeqNum
    FROM sys.all_columns a
    CROSS JOIN sys.all_columns b
),
CleanedJson AS (
    SELECT
        id,
        name,
        REPLACE(STUFF(STUFF(myData, 1, 1, ''), LEN(myData), 1, ''), '"', '') AS SplitReadyString
    FROM YourTable
    WHERE myData IS NOT NULL AND myData <> '[]'
)
SELECT DISTINCT
    -- 提取第SeqNum个元素
    LTRIM(RTRIM(SUBSTRING(
        SplitReadyString,
        -- 元素的起始位置
        CASE WHEN SeqNum = 1 THEN 1 ELSE CHARINDEX(',', SplitReadyString, SeqNum - 1) + 1 END,
        -- 元素的长度
        CASE WHEN CHARINDEX(',', SplitReadyString, SeqNum) = 0 
             THEN LEN(SplitReadyString) + 1 
             ELSE CHARINDEX(',', SplitReadyString, SeqNum) 
        END - CASE WHEN SeqNum = 1 THEN 1 ELSE CHARINDEX(',', SplitReadyString, SeqNum - 1) + 1 END
    ))) AS UniqueValue
FROM CleanedJson
CROSS JOIN NumberSequence
-- 只处理数组中实际存在的元素数量
WHERE SeqNum <= LEN(SplitReadyString) - LEN(REPLACE(SplitReadyString, ',', '')) + 1
ORDER BY UniqueValue;

注意事项 #

• 如果你的JSON数组里有包含逗号的元素（比如["Hello,World","Test"]），上面的方法会失效，因为我们是按逗号拆分的。如果数据存在这种情况，需要先对数组中的逗号做转义处理，但如果你的原始数据没有这种场景，上面的代码就完全够用。
• 可以根据自己的数据量选择合适的方法，中小数据用递归CTE更简洁，大数据用数字表性能更好。

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