SQL Server查询嵌套数组JSON列：获取plans数组及字段

问题背景 #

将JSON数据存入SQL Server单列后，使用OPENJSON查询无法正确读取嵌套的plans数组，关联字段返回NULL，需实现两个目标：

• 获取完整的plans数组
• 提取plans中的单个字段

示例数据 #

假设表YourTable包含JsonColumn列，JSON示例如下：

{
  "id": "123",
  "name": "Test",
  "plans": [
    {
      "planId": "p1",
      "planName": "Basic",
      "price": 9.99
    },
    {
      "planId": "p2",
      "planName": "Premium",
      "price": 19.99
    }
  ]
}

现有问题查询（返回NULL） #

常见错误查询示例：

SELECT 
  JSON_VALUE(JsonColumn, '$.id') AS Id,
  JSON_VALUE(JsonColumn, '$.plans') AS Plans, -- 数组无法用JSON_VALUE提取，返回NULL
  JSON_VALUE(JsonColumn, '$.plans[0].planName') AS SinglePlanName -- 若路径解析不当也会返回NULL
FROM YourTable

解决方案 #

1. 获取完整plans数组 #

使用JSON_QUERY提取数组（JSON_VALUE仅支持标量值，数组/对象需用JSON_QUERY）：

SELECT 
  JSON_VALUE(JsonColumn, '$.id') AS Id,
  JSON_QUERY(JsonColumn, '$.plans') AS FullPlansArray
FROM YourTable

2. 提取plans中的单个字段（遍历数组） #

通过OPENJSON结合CROSS APPLY拆分数组为行，逐个提取字段：

SELECT 
  JSON_VALUE(t.JsonColumn, '$.id') AS ParentId,
  j.planId,
  j.planName,
  j.price
FROM YourTable t
CROSS APPLY OPENJSON(t.JsonColumn, '$.plans')
WITH (
  planId VARCHAR(50) '$.planId',
  planName VARCHAR(100) '$.planName',
  price DECIMAL(10,2) '$.price'
) j

若仅需提取数组中特定索引的字段（如第一个计划名称）：

SELECT 
  JSON_VALUE(JsonColumn, '$.id') AS Id,
  ISNULL(JSON_VALUE(JsonColumn, '$.plans[0].planName'), '无计划') AS FirstPlanName
FROM YourTable

关键说明 #

• JSON_VALUE：仅能提取JSON中的标量值（字符串、数字、布尔等），提取数组/对象会返回NULL
• JSON_QUERY：用于提取JSON中的对象或数组，返回JSON格式字符串
• OPENJSON + CROSS APPLY：将JSON数组拆分为多行，方便遍历提取每个元素的字段

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