如何在SQL Server中用OPENJSON解析嵌套JSON并获取GoActionFile行

示例JSON结构 #

{
  "Root": {
    "Action": {
      "GoActionFiles": [
        {
          "FileName": "file1.txt",
          "Size": 1024,
          "Pools": ["poolA", "poolB"]
        },
        {
          "FileName": "file2.txt",
          "Size": 2048,
          "Pools": ["poolC"]
        }
      ]
    }
  }
}

基础解析：每个GoActionFile返回一行 #

如果只需要提取GoActionFiles数组中每个对象的基础字段，无需展开Pools数组，使用以下SQL语句：

DECLARE @json NVARCHAR(MAX) = N'{
  "Root": {
    "Action": {
      "GoActionFiles": [
        {
          "FileName": "file1.txt",
          "Size": 1024,
          "Pools": ["poolA", "poolB"]
        },
        {
          "FileName": "file2.txt",
          "Size": 2048,
          "Pools": ["poolC"]
        }
      ]
    }
  }
}';

SELECT 
  gaf.FileName,
  gaf.Size,
  gaf.Pools -- 以JSON字符串形式返回Pools数组
FROM OPENJSON(@json, '$.Root.Action')
WITH (
  GoActionFiles NVARCHAR(MAX) AS JSON
) AS actionData
CROSS APPLY OPENJSON(actionData.GoActionFiles)
WITH (
  FileName NVARCHAR(100),
  Size INT,
  Pools NVARCHAR(MAX) AS JSON
) AS gaf;

进阶解析：同时展开Pools数组 #

如果需要将Pools数组中的每个元素也拆分为单独行，添加一层CROSS APPLY展开数组：

DECLARE @json NVARCHAR(MAX) = N'{
  "Root": {
    "Action": {
      "GoActionFiles": [
        {
          "FileName": "file1.txt",
          "Size": 1024,
          "Pools": ["poolA", "poolB"]
        },
        {
          "FileName": "file2.txt",
          "Size": 2048,
          "Pools": ["poolC"]
        }
      ]
    }
  }
}';

SELECT 
  gaf.FileName,
  gaf.Size,
  p.PoolName
FROM OPENJSON(@json, '$.Root.Action')
WITH (
  GoActionFiles NVARCHAR(MAX) AS JSON
) AS actionData
CROSS APPLY OPENJSON(actionData.GoActionFiles)
WITH (
  FileName NVARCHAR(100),
  Size INT,
  Pools NVARCHAR(MAX) AS JSON
) AS gaf
CROSS APPLY OPENJSON(gaf.Pools)
WITH (
  PoolName NVARCHAR(50) '$'
) AS p;

关键逻辑说明 #

• 第一层OPENJSON定位到Root>Action节点，将GoActionFiles标记为JSON类型以便后续解析
• 使用CROSS APPLY配合OPENJSON展开GoActionFiles数组，实现每个数组元素对应一行数据
• 若需处理嵌套的Pools数组，重复上述CROSS APPLY + OPENJSON的模式即可

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