google-bigquery - 在记录字段中获取问题

Question

My Schema is mongoid STRING NULLABLE
order_vfid STRING NULLABLE
order_storeid STRING NULLABLE
order_filterdate DATETIME NULLABLE
order_sortdate DATETIME NULLABLE
order_time DATETIME NULLABLE
order_shopify_name STRING NULLABLE
order_shopify_id STRING NULLABLE
shipping_address RECORD REPEATED
customer_details RECORD REPEATED
order_payment_status STRING NULLABLE
order_fulfillment_status STRING NULLABLE
order_status STRING NULLABLE
order_currency STRING NULLABLE
order_total_paid FLOAT NULLABLE
is_cod INTEGER NULLABLE
line_items RECORD REPEATED
lineitem_id STRING NULLABLE
vfsku STRING NULLABLE
product_shopifyid STRING NULLABLE
variant_shopifyid STRING NULLABLE
quantity FLOAT NULLABLE
name STRING NULLABLE
catalogname STRING NULLABLE
item_total_sold_price FLOAT NULLABLE
discount FLOAT NULLABLE
confirmation_date STRING NULLABLE
item_vforderstatus STRING NULLABLE
product_status STRING NULLABLE
type STRING NULLABLE
total_shipping2 FLOAT NULLABLE
shipping_perquantity2 FLOAT NULLABLE
vfprodid STRING NULLABLE

现在我正在使用查询获取数据，但出现错误。请帮帮我。我的查询是 SELECT * FROM testdata.orders where line_items.vfsku = 'VFPQ123_VT1'

标签： google-bigquery

Answer 1

查看如何扫描满足条件的 STRUCT 字段值：

SELECT *
FROM testdata.orders
WHERE EXISTS(SELECT 1 FROM UNNEST(line_items) WHERE vfsku = 'VFPQ123_VT1');