我们使用FiveTran从shopify中提取数据并将其存储在BigQuery中。字段"；属性"；orderline表中包含一个类似于键/值对数组的内容。在这种情况下，名称/值。字段类型为字符串。这里是内容的一个示例
order_line_id properties
9956058529877 [{"name":"_order_bump_rule_id","value":"4afx7cbw6"},{"name":"_order_bump_bump_id","value":"769d1996-b6fb-4bc3-8d41-c4d7125768c5"},{"name":"_source","value":"order-bump"}]
4467731660885 [{"name":"shipping_interval_unit_type","value":null},{"name":"charge_delay","value":null},{"name":"charge_on_day_of_week","value":null},{"name":"charge_interval_frequency","value":null},{"name":"charge_on_day_of_month","value":null},{"name":"shipping_interval_frequency","value":null},{"name":"number_charges_until_expiration","value":null}]
4467738738773 [{"name":"shipping_interval_unit_type","value":null},{"name":"charge_delay","value":null},{"name":"charge_on_day_of_week","value":null},{"name":"charge_interval_frequency","value":null},{"name":"charge_on_day_of_month","value":null},{"name":"shipping_interval_frequency","value":null},{"name":"number_charges_until_expiration","value":null}]
4578798600277 [{"name":"shipping_interval_unit_type","value":null},{"name":"charge_interval_frequency","value":null},{"name":"shipping_interval_frequency","value":null}]
我正试图编写一个查询，为每条记录生成一行，并为每个名称值生成一列：
- shipping_interval_unit_type
- 计费日期周
- 计费间隔频率
- 费用_天_月
- subscription_id
- number_charges_util_expiration
- 发货间隔频率
和相应的"；值"；。该字段"；属性"；可以包含许多不同的"；name"；值，并且它们每次可以按不同的顺序排列。"；name"；上述值并不总是存在于"；属性"；领域
我尝试过json函数，但它的格式似乎不适合json。我试过不测试它，但失败了，因为它是一个字符串。
考虑以下方法
select * from (
select order_line_id,
json_extract_scalar(property, '$.name') name,
json_extract_scalar(property, '$.value') value
from your_table, unnest(json_extract_array(properties)) property
)
pivot (min(value) for name in (
'shipping_interval_unit_type',
'charge_on_day_of_week',
'charge_interval_frequency',
'charge_on_day_of_month',
'subscription_id',
'number_charges_until_expiration',
'shipping_interval_frequency'
))