SQL Server 使用T-SQL中的OPENJSON将Google API JSON文件解析为行和列

0md85ypi 于 2023-02-11 发布在 Go

关注(0)|答案(2)|浏览(120)

因此，我试图创建一个查询，可以处理一个json文件，我们从谷歌分析API 4的数据工厂Web请求获得，并将结果存储在Azure 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"
                        }
                    ]
                }
            ]
        }'
    DECLARE @jsonexample2 NVARCHAR(MAX) = (SELECT [value] FROM OPENJSON(@jsonexample) where [key]= 'rows' )
        
    
    SELECT *
    from OPENJSON(@jsonexample2)

这个博客似乎有一个很好的解释，但我仍然没有得到它的工作。https://levelup.gitconnected.com/how-to-easily-parse-and-transform-json-in-sql-server-c0b091a964de

sql-server

来源：https://stackoverflow.com/questions/75403118/parse-google-api-json-file-to-rows-and-columns-with-openjson-in-t-sql

2条答案

按热度按时间

e0uiprwp1#

你可以把它分解成这样：

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 cols as (
    select cast([key] as int) AS k, JSON_VALUE(value, '$.name') AS v
    from openjson(@jsonexample, '$.dimensionHeaders') x
       )
    , metrics as (
      select cast([key] as int) AS k, JSON_VALUE(value, '$.name') AS v
    from openjson(@jsonexample, '$.metricHeaders') x
      )
      select CAST(x.[key] AS INT) AS id, c.v AS dimName, JSON_VALUE(dim.value, '$.value') AS dimValue
      ,  m.v AS metName, JSON_VALUE(metr.value, '$.value') AS metValue
      from openjson(@jsonexample, '$.rows') x
      cross apply openjson(x.value, '$.dimensionValues') dim
      cross apply openjson(x.value, '$.metricValues') metr
      inner join cols c
         ON c.k = dim.[key]
      inner join metrics m
         ON m.k = metr.[key]

然后你就能解决剩下的问题了。

赞(0）回复(0）举报 2023-02-11

iugsix8n2#

下面是一段代码，可以动态解析Google Analytics的指标和维度。它应该会给予你一个很好的起点：）

SELECT TOP 1 @json = JSON_QUERY(RawJson, '$.reports[0].columnHeader')
FROM TableName
    
SET @WithClause =
(
    SELECT STRING_AGG(Line, ',')
    FROM
    (
        SELECT REPLACE(r.value, 'ga:', '')+' '+CASE
                                                    WHEN r.value = 'ga:DATE' THEN 'DATE'
                                                    ELSE 'NVARCHAR(255)'
                                                END+' '+'''$.dimensions['+r.[key]+']''' AS Line
        FROM OPENJSON(@json, '$.dimensions') AS r
        UNION ALL
        SELECT REPLACE(JSON_VALUE(r.value, '$.name'), 'ga:', '')+' '+CASE
                                                                            WHEN JSON_VALUE(r.value, '$.type') = 'TIME' THEN 'FLOAT'
                                                                            WHEN JSON_VALUE(r.value, '$.type') = 'CURRENCY' THEN 'DECIMAL(9,2)'
                                                                            ELSE JSON_VALUE(r.value, '$.type')
                                                                        END+' '+'''$.metrics[0].values['+r.[key]+']'''
        FROM OPENJSON(@json, '$.metricHeader.metricHeaderEntries') AS r
    ) AS a
);

SET @Query = '
SELECT d.*
INTO #temp_table
FROM TableNAme AS cm
CROSS APPLY OPENJSON(RawJson, ''$.reports[0].data.rows'') WITH ( '+@WithClause+ ') AS d';

--PRINT @Query;
EXECUTE (@Query);

赞(0）回复(0）举报 2023-02-11

我来回答

SQL Server 使用T-SQL中的OPENJSON将Google API JSON文件解析为行和列

2条答案

相关问题

热门标签

最新问答