SQL Server未知嵌套JSON列的WHERE子句查询难题

核心问题原因 #

JSON_VALUE()必须指定明确的JSON路径才能取值，而你的JSON结构未知、层级不确定，所以直接写固定路径$.kref自然找不到结果。下面是几个可行的查询方案：

方案1：递归遍历所有键值对（推荐） #

利用OPENJSON的递归特性，把整个JSON拆解为所有层级的键值对，再筛选目标条件，这是最可靠的方法。

单条件查询（找值为'12345'的记录） #

SELECT *
FROM YourTable
WHERE EXISTS (
    SELECT 1
    -- 第一层解析
    FROM OPENJSON(YourTable.JSONUDFS)
    WITH (
        Value NVARCHAR(MAX) '$.Value' AS JSON,
        KeyName NVARCHAR(100) '$.Key'
    ) AS j1
    -- 递归处理所有嵌套层级
    CROSS APPLY (
        SELECT [Key], Value FROM OPENJSON(j1.Value)
        UNION ALL
        SELECT j2.[Key], j2.Value
        FROM OPENJSON(j1.Value)
        CROSS APPLY OPENJSON(Value) j2
    ) AS j2
    -- 匹配目标值，如需指定键名则加 j2.[Key] = 'kref'
    WHERE j2.Value = '12345'
)

多条件AND查询（同时满足kref='12345'和status='active'） #

SELECT *
FROM YourTable
-- 第一个条件：存在kref=12345
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(YourTable.JSONUDFS)
    WITH (
        Value NVARCHAR(MAX) '$.Value' AS JSON,
        KeyName NVARCHAR(100) '$.Key'
    ) AS j1
    CROSS APPLY (
        SELECT [Key], Value FROM OPENJSON(j1.Value)
        UNION ALL
        SELECT j2.[Key], j2.Value
        FROM OPENJSON(j1.Value)
        CROSS APPLY OPENJSON(Value) j2
    ) AS j2
    WHERE j2.[Key] = 'kref' AND j2.Value = '12345'
)
-- 第二个条件：存在status=active
AND EXISTS (
    SELECT 1
    FROM OPENJSON(YourTable.JSONUDFS)
    WITH (
        Value NVARCHAR(MAX) '$.Value' AS JSON,
        KeyName NVARCHAR(100) '$.Key'
    ) AS j1
    CROSS APPLY (
        SELECT [Key], Value FROM OPENJSON(j1.Value)
        UNION ALL
        SELECT j2.[Key], j2.Value
        FROM OPENJSON(j1.Value)
        CROSS APPLY OPENJSON(Value) j2
    ) AS j2
    WHERE j2.[Key] = 'status' AND j2.Value = 'active'
)

多条件OR查询（满足kref='12345'或status='active'） #

把两个条件放在同一个EXISTS的WHERE子句里用OR连接即可：

SELECT *
FROM YourTable
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(YourTable.JSONUDFS)
    WITH (
        Value NVARCHAR(MAX) '$.Value' AS JSON,
        KeyName NVARCHAR(100) '$.Key'
    ) AS j1
    CROSS APPLY (
        SELECT [Key], Value FROM OPENJSON(j1.Value)
        UNION ALL
        SELECT j2.[Key], j2.Value
        FROM OPENJSON(j1.Value)
        CROSS APPLY OPENJSON(Value) j2
    ) AS j2
    WHERE (j2.[Key] = 'kref' AND j2.Value = '12345')
       OR (j2.[Key] = 'status' AND j2.Value = 'active')
)

方案2：字符串模糊匹配（临时排查用） #

如果JSON结构不复杂，且目标值不会和其他键名/值混淆，可以直接用字符串匹配，缺点是容易误判：

-- 找包含值12345的记录
SELECT *
FROM YourTable
WHERE JSONUDFS LIKE '%12345%'

-- 更精准：找包含"kref":"12345"的键值对
SELECT *
FROM YourTable
WHERE JSONUDFS LIKE '"kref":%12345%'

方案3：自定义递归函数（小数据集用） #

创建一个标量函数递归遍历所有层级的键值对，返回拼接后的字符串，再基于这个函数查询：

CREATE FUNCTION dbo.GetAllJsonKeyValuePairs(@json NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX) = ''
    DECLARE @key NVARCHAR(100), @value NVARCHAR(MAX), @isJson BIT

    DECLARE jsonCursor CURSOR FOR
    SELECT [Key], Value, ISJSON(Value) AS IsJson
    FROM OPENJSON(@json)

    OPEN jsonCursor
    FETCH NEXT FROM jsonCursor INTO @key, @value, @isJson

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @result += @key + ':' + @value + ';'
        -- 如果当前值是JSON，递归处理
        IF @isJson = 1
        BEGIN
            SET @result += dbo.GetAllJsonKeyValuePairs(@value)
        END
        FETCH NEXT FROM jsonCursor INTO @key, @value, @isJson
    END

    CLOSE jsonCursor
    DEALLOCATE jsonCursor

    RETURN @result
END
GO

-- 使用函数查询包含kref:12345的记录
SELECT *
FROM YourTable
WHERE dbo.GetAllJsonKeyValuePairs(JSONUDFS) LIKE '%kref:12345%'

注意：标量函数在大数据量下性能较差，仅适合小数据集使用。

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