在字符串列bigquery中查询json

Question

我想知道是否有可能查询一个json字符串列，该列将在不指定索引的情况下对产品进行解嵌。

示例

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

预期输出

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

enter image description here

但我希望获得此输出，而不必做一个产品索引的联合，但有一些东西，将重复和自动解嵌，因为有很多元素有产品

Answer 1

#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

带输出

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