使用OPENJSON获取键值对列表：最优查询方案咨询

问题背景 #

给定结构动态变化的JSON数据，需要提取SearchParameters数组内每个对象的参数名与参数值键值对，且不能硬编码参数名。现有两个能得到结果的SQL查询，但第一个执行计划表现较差，需要判断这两个方案是否最优，或是否存在更优实现。

现有方案及问题分析 #

先看问题中的JSON和两个查询：

DECLARE @json NVARCHAR(MAX) = '{ "SearchParameters": [ { "LastName": "Smith" }, { "Org": "AAA" }, { "Postcode": "SW1" } ] }';

-- 方案1：多层嵌套OPENJSON
SELECT z.[key], z.value
FROM OPENJSON(@json) x
OUTER APPLY OPENJSON(x.value) y
OUTER APPLY OPENJSON(y.value) z

-- 方案2：子查询提取键值
SELECT 
    (SELECT [key] FROM OPENJSON([value])) AS KeyName,
    (SELECT [value] FROM OPENJSON([value])) AS KeyValue
FROM 
    (SELECT 
         [value]
     FROM 
         OPENJSON(@json,'$.SearchParameters') 
    ) x

方案1的问题

这个方案做了多余的顶层遍历：第一层OPENJSON(@json)会遍历整个JSON的顶级键（这里只有SearchParameters一个），后续两次APPLY才到数组元素和键值对。而且用了OUTER APPLY（实际场景中SearchParameters是必存在的数组，用CROSS APPLY更合适），执行计划会包含不必要的过滤和关联步骤，效率自然较差。

方案2的问题

这个方案依赖一个隐含前提：SearchParameters数组中的每个对象只能有一个键值对。如果后续某个对象出现多个参数（比如{"FirstName":"John", "Age":30}），子查询会返回多行，直接触发报错。另外，子查询的方式会导致多次独立调用OPENJSON，执行效率也不如直接关联的方式。

更优实现方案 #

直接定位到SearchParameters数组，再对每个数组元素展开键值对，逻辑简洁且执行效率最高：

DECLARE @json NVARCHAR(MAX) = '{ "SearchParameters": [ { "LastName": "Smith" }, { "Org": "AAA" }, { "Postcode": "SW1" }, { "FirstName": "John", "Age": 30 } ] }';

SELECT 
    sp.[key] AS ParameterName,
    sp.[value] AS ParameterValue
FROM OPENJSON(@json, '$.SearchParameters') AS arr
CROSS APPLY OPENJSON(arr.[value]) AS sp

方案优势

1. 精准定位：通过OPENJSON的第二个参数'$.SearchParameters'直接定位到目标数组，避免了顶层键的无效遍历。
2. 高效关联：用CROSS APPLY（如果数组可能为空，可替换为OUTER APPLY）关联每个数组元素的键值对展开，执行计划只有两次表值函数调用，步骤最少。
3. 扩展性强：兼容数组元素包含多个键值对的场景，完全符合参数动态变化的需求，不会出现方案2的报错问题。

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