Search code examples
sqlsql-servert-sqlazure-sql-databaseopen-json

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


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


Solution

  • 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.