Parse Google API JSON file to rows and columns with OPENJSON in T-SQL

Question

So I am trying to create a query than can handle a json file that we get with a data factory web request from the Google Analytics API 4 and store the result in an Azure sql table. The following query is the closest I got.

The dimension and metric headers seems to be column names and the values in the rows part should be the rows.

 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)

This blog seemed to have a good explanation but I still not got it working. https://levelup.gitconnected.com/how-to-easily-parse-and-transform-json-in-sql-server-c0b091a964de

score 1 · Accepted Answer · answered Feb 09 '23 at 22:40

You can shred it down to something like this:

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]

Then you can probably figure out the rest.

Thanks, works perfect. A pivot function fixed the rest – Rogier Wijsman Feb 13 '23 at 12:34 — Rogier Wijsman, Feb 13 '23 at 12:34

score 0 · Answer 2 · answered Feb 09 '23 at 22:47

Here is a bit code that will dynamically parse the metrics and dimension for Google Analytics. It should give you a good starting point :)

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);

We use this code for the ga reporting api, in a stored procedure. The dynamic SQL with the with clause make the pipeline bullet proof. — Simon P, Feb 13 '23 at 18:03

Parse Google API JSON file to rows and columns with OPENJSON in T-SQL

2 Answers2