在SQL Server中使用OPENJSON解析非数组格式的JSON对象

完全可以用OPENJSON直接解析这种以ID为属性名的对象格式，无需手动逐个遍历处理，以下是具体实现方法：

1. 先定义存储JSON数据的变量（或直接从表字段读取）：

DECLARE @json NVARCHAR(MAX) = '{"1":{"id":"1","Name":"Charlie"}, "2":{"id":"2","Name":"Sally"}}'

2. 使用嵌套的OPENJSON配合CROSS APPLY解析：

SELECT 
    -- 可选：外层的key就是作为属性名的ID，和内层id字段一致时可省略
    j1.[key] AS ExternalId,
    j2.id,
    j2.Name
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON(j1.value) WITH (
    id VARCHAR(10),
    Name VARCHAR(50)
) j2

原理说明 #

• 外层OPENJSON(@json)会将整个对象拆分为键值对集合，返回的key列对应JSON中的ID属性名，value列是每个用户的JSON对象。
• 通过CROSS APPLY将每个value对象再次传入OPENJSON，并通过WITH子句指定要提取的字段和类型，最终得到结构化的查询结果。

如果不需要外层的key字段（内层已包含id），可以简化为：

SELECT 
    j2.id,
    j2.Name
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON(j1.value) WITH (
    id VARCHAR(10),
    Name VARCHAR(50)
) j2

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