如何将指定JSON数据导入SQL Server临时表并生成目标结构？

你可以利用SQL Server的OPENJSON函数解析这种多层嵌套且含动态键的JSON数据，展开后插入临时表，具体步骤如下：

1. 创建目标临时表 #

先创建匹配你需求结构的临时表：

CREATE TABLE #TempPricing (
    OfferName NVARCHAR(100),
    Region NVARCHAR(100),
    Price DECIMAL(10,3),
    PricingType NVARCHAR(50)
)

2. 解析JSON并插入数据 #

假设JSON数据存储在变量中（也可替换为从表字段读取），执行以下语句完成解析与插入：

DECLARE @json NVARCHAR(MAX) = N'{
  "offers": {
    "burstableenablement": {
      "prices": {
        "australia-central": {
          "value": 35.635,
          "pricingType": "WebDirect"
        },
        "australia-central-2": {
          "value": 35.635,
          "pricingType": "WebDirect"
        }
      }
    },
    "burstable-enablement1": {
      "prices": {
        "australia-central": {
          "value": 35.635,
          "pricingType": "WebDirect"
        },
        "australia-central-2": {
          "value": 35.635,
          "pricingType": "WebDirect"
        }
      }
    },
    "burstable-enablement2": {
      "prices": {
        "australia-central": {
          "value": 35.635,
          "pricingType": "WebDirect"
        },
        "australia-central-2": {
          "value": 35.635,
          "pricingType": "WebDirect"
        }
      }
    }
  }
}'

INSERT INTO #TempPricing (OfferName, Region, Price, PricingType)
SELECT
    offers.[key] AS OfferName,
    regions.[key] AS Region,
    JSON_VALUE(regions.[value], '$.value') AS Price,
    JSON_VALUE(regions.[value], '$.pricingType') AS PricingType
FROM OPENJSON(@json, '$.offers') AS offers
CROSS APPLY OPENJSON(offers.[value], '$.prices') AS regions

3. 验证结果 #

执行查询查看临时表数据：

SELECT * FROM #TempPricing

会得到你期望的输出：

OfferName             | Region             | Price  | PricingType
----------------------|--------------------|--------|------------
burstableenablement   | australia-central  | 35.635 | WebDirect
burstableenablement   | australia-central-2| 35.635 | WebDirect
burstable-enablement1 | australia-central  | 35.635 | WebDirect
burstable-enablement1 | australia-central-2| 35.635 | WebDirect
burstable-enablement2 | australia-central  | 35.635 | WebDirect
burstable-enablement2 | australia-central-2| 35.635 | WebDirect

核心逻辑说明 #

• OPENJSON(@json, '$.offers')：解析最外层offers对象，返回每个offer的名称（[key]）和对应子JSON（[value]）。
• CROSS APPLY OPENJSON(offers.[value], '$.prices')：针对每个offer的子JSON，进一步解析prices对象，返回区域名称（[key]）和价格详情JSON（[value]）。
• JSON_VALUE：从价格详情JSON中提取value和pricingType字段的值。

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