query json in a string column bigquery

Question

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.

Example

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

Expected output

order_id    product_1
T12345  Triblend Android T-Shirt
T12345  Donut Friday Scented T-Shirt

enter image description here

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

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2020-02-10T15:01:38

#standardSQL
CREATE TEMP FUNCTION json2array(input STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(input).map(x=>JSON.stringify(x));
"""; 
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(product, "$.name") AS product_1
FROM mytable,
UNNEST(json2array(JSON_EXTRACT(eec, "$['ecommerce'].purchase.products"))) product

with output

Row order_id    product_1    
1   T12345      Triblend Android T-Shirt     
2   T12345      Donut Friday Scented T-Shirt

query json in a string column bigquery

1 Answers