SQL使用OpenJSON查询时如何获取JSON顶层键名作为用户名列

你观察到的结论是正确的，当前JSON结构中顶级键（key）就是你要的用户名，由于key是SQL Server的保留关键字，使用时需要加方括号包裹。你需要结合两层OPENJSON调用实现需求，第一层获取用户名，第二层解析对应用户的属性字段，完整查询语句如下：

SELECT 
    [key] AS username,
    user_type,
    user_enabled,
    last_login
FROM OPENJSON(@json)
CROSS APPLY OPENJSON([value])
WITH (
    user_type NVARCHAR(100) '$.user_type',
    user_enabled NVARCHAR(100) '$.user_enabled',
    last_login NVARCHAR(100) '$.last_login'
)

原理解释 #

• 第一层OPENJSON(@json)返回的结果中，[key]列存储顶级键（即bob、claire这类用户名），[value]列存储对应用户的嵌套JSON对象
• CROSS APPLY会为每一行的[value]内容再次调用OPENJSON，通过WITH子句解析出用户的三个属性字段
• 如果你不想用CROSS APPLY，也可以直接在字段中用JSON_VALUE取值，写法如下：

SELECT 
    [key] AS username,
    JSON_VALUE([value], '$.user_type') AS user_type,
    JSON_VALUE([value], '$.user_enabled') AS user_enabled,
    JSON_VALUE([value], '$.last_login') AS last_login
FROM OPENJSON(@json)

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