Convert JSON file from google analytics to SQL table

Question

I'm trying to get data from google analytics v4 with ADF. The output are JSON files. Now I'm trying to convert the JSON files to an SQL table. Only the format of this JSON is different than I used to deal with. The columnheader are only shown above instead before every value. Here's an short example:

[
  {
    "columnHeader": {
      "dimensions": [
        "ga:source",
        "ga:medium",
        "ga:hasSocialSourceReferral",
        "ga:adDistributionNetwork"
      ],
      "metricHeader": {
        "metricHeaderEntries": [
          {
            "name": "ga:sessions",
            "type": "INTEGER"
          },
          {
            "name": "ga:sessionDuration",
            "type": "TIME"
          },
          {
            "name": "ga:bounces",
            "type": "INTEGER"
          },
          {
            "name": "ga:pageviews",
            "type": "INTEGER"
          }
        ]
      }
    },
    "data": {
      "rows": [
        {
          "dimensions": [
            "(direct)",
            "(none)",
            "No",
            "(not set)"
          ],
          "metrics": [
            {
              "values": [
                "66216",
                "8917838.0",
                "31584",
                "194820"
              ]
            }
          ]
        },
        {
          "dimensions": [
            "{{site_source_name}}",
            "mrwork_brands",
            "No",
            "(not set)"
          ],
          "metrics": [
            {
              "values": [
                "28",
                "150.0",
                "27",
                "31"
              ]
            }
          ]
        },
        {
          "dimensions": [
            "zeelandnet.nl",
            "referral",
            "No",
            "(not set)"
          ],
          "metrics": [
            {
              "values": [
                "1",
                "0.0",
                "1",
                "1"
              ]
            }
          ]
        }
      ],
      "totals": [
        {
          "values": [
            "263765",
            "2.3024278E7",
            "172853",
            "563754"
          ]
        }
      ],
      "rowCount": 295,
      "minimums": [
        {
          "values": [
            "1",
            "0.0",
            "0",
            "0"
          ]
        }
      ],
      "maximums": [
        {
          "values": [
            "66216",
            "8917838.0",
            "31584",
            "194820"
          ]
        }
      ],
      "samplesReadCounts": [
        "222617"
      ],
      "samplingSpaceSizes": [
        "263785"
      ]
    }
  }
]

Does anyone have a solution to convert this JSON dynamic to an SQL table?

Answer 1

Your sample data is incorrect, it is not a valid JSON.

I have attempted to fix it for you, but it would really help if you could post some correct sample data, with more than one row.

DECLARE @x VARCHAR(MAX)=
'{
   "reports":[
      {
         "columnHeader":{
            "dimensions":[
               "ga:source",
               "ga:medium",
               "ga:hasSocialSourceReferral",
               "ga:adDistributionNetwork"
            ]
         },
         "data":{
            "rows":[
               {
                  "dimensions":[
                     "(direct)",
                     "(none)",
                     "No",
                     "(not set)"
                  ],
                  "metrics":[
                     {
                        "values":[
                           "66216",
                           "8917838.0",
                           "31584",
                           "194820"
                        ]
                     }
                  ]
               }
            ]
         }
      }
   ]
}'

SELECT oj1.[Key], oj1.Value AS ColumnName, oj2.Value
FROM OPENJSON(@x,'$.reports[0].columnHeader.dimensions') oj1
INNER JOIN OPENJSON(@x,'$.reports[0].data.rows[0].metrics[0].values') oj2 ON oj2.[Key] = oj1.[Key]