BigQuery集成中使用Stitch的简单连接错误

Question

使用Stitch数据集成在BigQuery中编写了下面的代码，以从Shopify中提取表。非常简单，但我不熟悉嵌套，我怀疑这可能是抛掉了我的查询。

任何帮助都将不胜感激。查询和错误如下：

查询

SELECT distinct
order_number
,so.created_at
,buyer_accepts_marketing
,currency
,value. sku
,value. vendor
,value. variant_title
,value. gift_card
,value. product_id
,customer. id
,shipping_address. province
,shipping_address. country
,shipping_address. city
,shipping_address. longitude
,shipping_address. country_code
,shipping_address. latitude
,sum(total_price_usd) as price_usd
,sum(total_price) as total_price
,sum(total_discounts) as total_discounts
FROM `shopifytest-272721.testconn.orders` as so
CROSS JOIN UNNEST(line_items)

left join

(SELECT distinct
created_at
,id
,product_type
,title
,value.sku
,value.fulfillment_service
,value.inventory_quantity
FROM `shopifytest-272721.testconn.products` 
CROSS JOIN UNNEST(variants)) as sp 

on so.product_id = sp.id 
and so.created_at = sp.created_at

group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

错误名称product_id在36:7中找不到

Answer 1

在第一个选择中，您有value.product_id，但您正在加入so.product_id = sp.id，在这个级别上不知道字段product_id，但是如果您设置别名，它可以工作：

value.product_id as product_id

另一个有噪声的字段是第一个查询中的so.created_at。让我为您的查询提供以下结构建议：

SELECT distinct
order_number
,created_at
,buyer_accepts_marketing
,currency
,value. sku
,value. vendor
,value. variant_title
,value. gift_card
,value. product_id as product_id
,customer. id
,shipping_address. province
,shipping_address. country
,shipping_address. city
,shipping_address. longitude
,shipping_address. country_code
,shipping_address. latitude
,sum(total_price_usd) as price_usd
,sum(total_price) as total_price
,sum(total_discounts) as total_discounts
FROM `shopifytest-272721.testconn.orders` as so --if line_items is contained into so:
CROSS JOIN UNNEST(so.line_items) as items

left join

(SELECT distinct
created_at
,id
,product_type
,title
,value.sku
,value.fulfillment_service
,value.inventory_quantity
FROM `shopifytest-272721.testconn.products` as p
CROSS JOIN UNNEST(p.variants)) as sp 

on items.product_id = sp.id 
and items.created_at = sp.created_at

group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

此外，由于您不熟悉嵌套，我建议您检查UNNEST、压扁阵列、连接类型。