如何将多层JSON API响应展平并存储至SQL Server表

要正确匹配维度与对应的指标值，核心是保留每个维度的索引位置——API返回的data数组是按header维度的索引顺序嵌套的，通过索引关联就能避免笛卡尔积，实现维度与值的精准匹配。

完整SQL代码 #

SELECT 
    [date_dimension_header].[date],
    [profile_dimension_header].[profile],
    [metrics_header].[metric],
    [metric_data].[metric_value]
FROM
    OPENJSON(@json, '$')
    WITH
    (
        [header_array] NVARCHAR(MAX) '$.header' AS JSON,
        [data_array] NVARCHAR(MAX) '$.data' AS JSON
    ) [api_response]
    -- 解析日期维度，保留索引与日期值
    CROSS APPLY (
        SELECT 
            CAST([key] AS INT) AS date_index,
            [value] AS [date]
        FROM OPENJSON([api_response].[header_array], '$[0].rows')
    ) [date_dimension_header]
    -- 解析Profile维度，保留索引与Profile值
    CROSS APPLY (
        SELECT 
            CAST([key] AS INT) AS profile_index,
            [value] AS [profile]
        FROM OPENJSON([api_response].[header_array], '$[1].rows')
    ) [profile_dimension_header]
    -- 解析指标维度，保留索引与指标名称
    CROSS APPLY (
        SELECT 
            CAST([key] AS INT) AS metric_index,
            [value] AS [metric]
        FROM OPENJSON([api_response].[header_array], '$[2].rows')
    ) [metrics_header]
    -- 解析data数组第一层（对应日期维度索引）
    CROSS APPLY (
        SELECT 
            CAST([key] AS INT) AS data_date_index,
            [value] AS profile_data
        FROM OPENJSON([api_response].[data_array])
    ) [date_data]
    WHERE [date_data].data_date_index = [date_dimension_header].date_index
    -- 解析data数组第二层（对应Profile维度索引）
    CROSS APPLY (
        SELECT 
            CAST([key] AS INT) AS data_profile_index,
            [value] AS metric_data
        FROM OPENJSON([date_data].profile_data)
    ) [profile_data]
    WHERE [profile_data].data_profile_index = [profile_dimension_header].profile_index
    -- 解析data数组第三层（对应指标维度索引与值）
    CROSS APPLY (
        SELECT 
            CAST([key] AS INT) AS data_metric_index,
            -- 根据实际指标值类型调整，比如INT/DECIMAL等
            CAST([value] AS DECIMAL(18,4)) AS metric_value
        FROM OPENJSON([profile_data].metric_data)
    ) [metric_data]
    WHERE [metric_data].data_metric_index = [metrics_header].metric_index;

关键说明 #

1. 保留维度索引：每个header维度解析时，提取OPENJSON返回的[key]字段（即维度项的顺序索引），转为整数用于后续关联。
2. 逐层匹配data数组：data数组是三层嵌套结构（日期→Profile→指标值），每层解析时同样保留索引，通过索引与header维度的索引精准对应。
3. 数据类型适配：metric_value的类型需根据API返回的实际值调整，比如字符串、整数或小数等。

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