JSON路径属性找不到：为何能获取total_pages却无法返回id值？

错误提示：指定的JSON路径上找不到属性。
我无法理解为何无法返回JSON中的"id"值。既然total_pages的查询能正常返回，我遗漏了什么引用？

用户提供的SQL代码：

DECLARE @json NVARCHAR(MAX);

SET @json = N'{
  "data": [
    {
      "id": 1450
    },
    {
      "id": 1451
    }
],
  "total_pages": 1
 }';

SELECT 
    *
FROM 
    OPENJSON(@json) WITH (
      id INT 'strict $.total_pages'
)

SELECT 
    *
FROM 
    OPENJSON(@json) WITH (
      id INT 'strict $.data.id'
)

原因与解决方法 #

第一个查询能正常运行，是因为$.total_pages是JSON顶层的单个属性，路径指向明确。但第二个查询里的$.data是数组类型，直接写$.data.id无效——JSON路径不知道要取数组里的哪一个元素的id，所以会提示找不到属性。

方法1：获取数组中所有的id值 #

先定位到data数组，再逐个提取每个元素里的id：

SELECT *
FROM OPENJSON(@json, '$.data')
WITH (
  id INT 'strict $.id'
)

这段代码会返回两行结果，分别是1450和1451。

方法2：获取数组中特定位置的id值 #

如果只需要数组中某一个元素的id（比如第一个），可以用数组索引（JSON数组索引从0开始）：

SELECT *
FROM OPENJSON(@json)
WITH (
  id INT 'strict $.data[0].id'
)

这段代码会返回第一个元素的id：1450。

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