使用TSQL OPENJSON提取元数据与数据至表的技术求助

先明确场景示例 #

假设你的JSON结构如下：

{
  "customMetadata": {
    "id": "CFG-001",
    "name": "AppConfig"
  },
  "environments": {
    "Development": {
      "customs": "Debug mode enabled; timeout=30s"
    },
    "Test": {
      "customs": "Load testing mode; timeout=60s"
    },
    "Production": {
      "customs": "Performance optimized; timeout=10s"
    }
  }
}

目标临时表结构：

CREATE TABLE #TempAppConfig (
  MetadataID VARCHAR(50) PRIMARY KEY,
  MetadataName VARCHAR(100),
  DevCustoms NVARCHAR(MAX),
  TestCustoms NVARCHAR(MAX),
  ProdCustoms NVARCHAR(MAX)
)

方法1：直接用JSON_VALUE提取固定环境字段 #

如果环境名称（Development/Test/Production）是固定的，直接通过JSON路径提取对应值是最简洁的方案：

-- 插入完整数据（含customMetadata和环境customs）
INSERT INTO #TempAppConfig (MetadataID, MetadataName, DevCustoms, TestCustoms, ProdCustoms)
SELECT
  JSON_VALUE(raw_json.data, '$.customMetadata.id') AS MetadataID,
  JSON_VALUE(raw_json.data, '$.customMetadata.name') AS MetadataName,
  JSON_VALUE(raw_json.data, '$.environments.Development.customs') AS DevCustoms,
  JSON_VALUE(raw_json.data, '$.environments.Test.customs') AS TestCustoms,
  JSON_VALUE(raw_json.data, '$.environments.Production.customs') AS ProdCustoms
FROM (
  -- 替换为你的JSON数据源（可以是表字段或变量）
  SELECT '{
    "customMetadata": {
      "id": "CFG-001",
      "name": "AppConfig"
    },
    "environments": {
      "Development": {"customs": "Debug mode enabled; timeout=30s"},
      "Test": {"customs": "Load testing mode; timeout=60s"},
      "Production": {"customs": "Performance optimized; timeout=10s"}
    }' AS data
) AS raw_json;

如果已经插入了customMetadata数据，只想更新环境列：

UPDATE #TempAppConfig
SET
  DevCustoms = JSON_VALUE(raw_json.data, '$.environments.Development.customs'),
  TestCustoms = JSON_VALUE(raw_json.data, '$.environments.Test.customs'),
  ProdCustoms = JSON_VALUE(raw_json.data, '$.environments.Production.customs')
FROM #TempAppConfig tc
CROSS JOIN (
  SELECT '{你的JSON内容}' AS data
) AS raw_json
WHERE tc.MetadataID = JSON_VALUE(raw_json.data, '$.customMetadata.id');

方法2：动态环境场景（用OPENJSON+行转列） #

如果环境数量不固定，可先通过OPENJSON拆分环境节点，再用CASE转置为列：

INSERT INTO #TempAppConfig (MetadataID, MetadataName, DevCustoms, TestCustoms, ProdCustoms)
SELECT
  cm.MetadataID,
  cm.MetadataName,
  MAX(CASE WHEN env.EnvName = 'Development' THEN env.Customs END) AS DevCustoms,
  MAX(CASE WHEN env.EnvName = 'Test' THEN env.Customs END) AS TestCustoms,
  MAX(CASE WHEN env.EnvName = 'Production' THEN env.Customs END) AS ProdCustoms
FROM (
  -- 先提取customMetadata基础信息
  SELECT
    JSON_VALUE(raw_json.data, '$.customMetadata.id') AS MetadataID,
    JSON_VALUE(raw_json.data, '$.customMetadata.name') AS MetadataName,
    raw_json.data AS JsonContent
  FROM (SELECT '{你的JSON内容}' AS data) AS raw_json
) AS cm
-- 拆分environments节点为行数据
CROSS APPLY OPENJSON(cm.JsonContent, '$.environments')
WITH (
  EnvName NVARCHAR(50) '$',  -- 提取环境名称作为键
  Customs NVARCHAR(MAX) '$.customs'  -- 提取对应环境的customs值
) AS env
GROUP BY cm.MetadataID, cm.MetadataName;

为什么之前的CROSS APPLY没生效？ #

之前的尝试可能是直接拆分environments得到行级数据，但没有做行转列处理，导致每条环境数据生成一行而非合并到同一行的不同列。上述方法2的CASE+MAX组合就是解决行转列的核心逻辑。

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