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

Answer 1

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.