title: query json in a string column bigquery
body: <p>I would like to know if it is possible to query a json string column that will unnest the products without having to specify the index.</p>
  
  <p>Example</p>
  
  <pre><code>with mytable as (
  select 
  '{"ecommerce":{"purchase":{"actionField":{"id":"T12345","affiliation":"Online Store","revenue":"35.43","tax":"4.90","shipping":"5.99","coupon":"SUMMER_SALE"},"products":[{"name":"Triblend Android T-Shirt","id":"12345","price":"15.25","brand":"Google","category":"Apparel","variant":"Gray","quantity":1,"coupon":""},{"name":"Donut Friday Scented T-Shirt","id":"67890","price":"33.75","brand":"Google","category":"Apparel","variant":"Black","quantity":1}]}}}' as eec
  )  
  select 
  JSON_EXTRACT_SCALAR(eec, "$['ecommerce'].purchase.actionField.id") AS order_id,
  JSON_EXTRACT_SCALAR(eec, "$['ecommerce'].purchase.products[0].name") AS product_1
  from mytable
  union all
  select
  JSON_EXTRACT_SCALAR(eec, "$['ecommerce'].purchase.actionField.id") AS order_id,
  JSON_EXTRACT_SCALAR(eec, "$['ecommerce'].purchase.products[1].name") AS product_1
  from mytable
  
  </code></pre>
  
  <p>Expected output </p>
  
  <pre><code>order_id    product_1
  T12345  Triblend Android T-Shirt
  T12345  Donut Friday Scented T-Shirt
  
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/Q1FVH.png" rel="nofollow noreferrer">enter image description here</a></p>
  
  <p>But i would like to obtain this output without having to do a union of product[index] but have something that will repeat and unnest automatically as much element that there is inside product</p>