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

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.

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