Need assistance with SQL query to parse JSON data from BigQuery table-CodePudding

I am using BigQuery Sessions table to query product fields. But I am able to query only first row with hardcoded index value. Can someone please assist me with the SQL query to parse JSON?

Query:

select visitId, hits[OFFSET(0)].product[OFFSET(0)].productSKU, hits[OFFSET(0)].product[OFFSET(0)].v2ProductName 
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
LIMIT 5

Sample sessions table data:

[
  {
    "visitId": "1501583974",
    "hits": [
      {
        "product": [
          {
            "productSKU": "GGOEGESB015199",
            "v2ProductName": "Test 1"
          },
          {
            "productSKU": "GGOEGESB015200",
            "v2ProductName": "Test 2"
          }
        ]
      }
    ]
  }
]

Expected outcome:

CodePudding user response：

I ran the following query :

select visitId, n.productSKU, n.v2productName from (select visitId, m.product
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`, unnest(hits) as m
LIMIT 5), unnest (product) as n

In the inner query I am unnesting the array of structures ‘hits’ and in the outer query I am unnesting the inner array of structures ‘product’.

Output :