使用T-SQL的OPENJSON将Google API JSON文件解析为行列结构

要将Google Analytics 4 API返回的JSON转换为可插入Azure SQL的结构化表，需要同时解析维度表头、指标表头和行数据，让维度/指标值与表头一一对应。以下是完整的T-SQL实现：

完整代码示例 #

DECLARE @jsonexample NVARCHAR(MAX) = 
N'{
    "dimensionHeaders": [
        {
            "name": "date"
        },
        {
            "name": "country"
        }
    ],
    "metricHeaders": [
        {
            "name": "totalUsers",
            "type": "TYPE_INTEGER"
        }
    ],
    "rows": [
        {
            "dimensionValues": [
                {
                    "value": "20230207"
                },
                {
                    "value": "Netherlands"
                }
            ],
            "metricValues": [
                {
                    "value": "3"
                }
            ]
        },
        {
            "dimensionValues": [
                {
                    "value": "20230208"
                },
                {
                    "value": "Netherlands"
                }
            ],
            "metricValues": [
                {
                    "value": "2"
                }
            ]
        },
        {
            "dimensionValues": [
                {
                    "value": "20230208"
                },
                {
                    "value": "United States"
                }
            ],
            "metricValues": [
                {
                    "value": "1"
                }
            ]
        }
    ]
}';

-- 提取维度表头并保留顺序索引
WITH DimensionHeaders AS (
    SELECT 
        [name] AS DimName,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS DimIndex
    FROM OPENJSON(@jsonexample, '$.dimensionHeaders')
    WITH ([name] NVARCHAR(100) '$.name')
),
-- 提取指标表头并保留顺序索引
MetricHeaders AS (
    SELECT 
        [name] AS MetricName,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS MetricIndex
    FROM OPENJSON(@jsonexample, '$.metricHeaders')
    WITH ([name] NVARCHAR(100) '$.name')
),
-- 解析行数据，提取维度/指标值并标记位置索引
ParsedRows AS (
    SELECT 
        RowIndex = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        DimIndex = ROW_NUMBER() OVER (PARTITION BY j1.[key] ORDER BY (SELECT NULL)),
        DimValue = j2.value,
        MetricIndex = ROW_NUMBER() OVER (PARTITION BY j1.[key] ORDER BY (SELECT NULL)),
        MetricValue = j3.value
    FROM OPENJSON(@jsonexample, '$.rows') j1
    CROSS APPLY OPENJSON(j1.value, '$.dimensionValues') 
                WITH (value NVARCHAR(100) '$.value') j2
    CROSS APPLY OPENJSON(j1.value, '$.metricValues') 
                WITH (value NVARCHAR(100) '$.value') j3
)
-- 将行数据转换为结构化列
SELECT 
    MAX(CASE WHEN dh.DimName = 'date' THEN pr.DimValue END) AS date,
    MAX(CASE WHEN dh.DimName = 'country' THEN pr.DimValue END) AS country,
    CAST(MAX(CASE WHEN mh.MetricName = 'totalUsers' THEN pr.MetricValue END) AS INT) AS totalUsers
FROM ParsedRows pr
LEFT JOIN DimensionHeaders dh ON pr.DimIndex = dh.DimIndex
LEFT JOIN MetricHeaders mh ON pr.MetricIndex = mh.MetricIndex
GROUP BY pr.RowIndex
ORDER BY pr.RowIndex;

关键逻辑说明 #

• 表头索引匹配：用ROW_NUMBER()为每个维度/指标表头标记顺序，确保行数据里的第N个值对应第N个表头。
• 行数据解析：通过CROSS APPLY拆分每个行对象里的维度值和指标值，保留每行的唯一索引。
• 行列转换：使用CASE语句结合聚合函数，将按行存储的维度/指标值映射为对应的列，同时可根据指标类型（如TYPE_INTEGER）转换数据类型。
• 动态适配扩展：如果表头数量或名称不固定，可基于提取的表头生成动态SQL，避免硬编码列名。

插入Azure SQL表 #

将结果插入目标表时，只需将上述查询的SELECT部分替换为INSERT INTO YourTargetTable(date, country, totalUsers) SELECT ...，确保目标表列名与查询输出一致即可。

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