SQL Server中使用带WITH的OPENJSON时如何获取数组项的key索引

问题原因 #

带WITH子句的OPENJSON中，WITH块内定义的列只会从当前解析的JSON对象的内部属性取值，而代表数组下标索引的key是OPENJSON函数默认返回的元数据列，不属于JSON对象的内部字段，因此在WITH块中定义[key] INT会因为找不到对应属性返回NULL。

正确实现方式 #

不需要在WITH子句中定义索引列，直接引用OPENJSON返回的原生key列即可，示例代码如下：

DECLARE @json NVARCHAR(MAX) = '[{"name":"Alpha"},{"name":"Bravo"},{"name":"Charlie"}]'

SELECT j.[key], j.name
FROM OPENJSON(@json)
WITH (
  name NVARCHAR(MAX) '$.name'
) AS j

运行后返回结果符合预期：

如果需要自定义索引列的名称，直接在SELECT语句中设置别名即可，例如：

SELECT j.[key] AS 数组项索引, j.name
FROM OPENJSON(@json)
WITH (
  name NVARCHAR(MAX) '$.name'
) AS j

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