以下是 BigQuery 标准 SQL
#standardSQL
select order_id,
( select split(kv, ':')[offset(1)] from x.kvs kv where split(kv, ':')[offset(0)] = 'id') id,
( select split(kv, ':')[offset(1)] from x.kvs kv where split(kv, ':')[offset(0)] = 'qy') qy,
( select split(kv, ':')[offset(1)] from x.kvs kv where split(kv, ':')[offset(0)] = 'sum') sum
from `project.dataset.table`,
unnest(split(trim(line_items, ';'), ';')) items,
unnest([struct(split(items,'|') as kvs)]) x
-- order by order_id
如果适用于您的问题中的样本数据 - 输出是
上面的以下变体也很有用
#standardSQL
select order_id,
(select value from z.y where key = 'id') id,
(select value from z.y where key = 'qy') qy,
(select value from z.y where key = 'sum') sum
from `project.dataset.table`,
unnest(split(trim(line_items, ';'), ';')) items,
unnest([struct(split(items,'|') as kvs)]) x,
unnest([struct(array(
select as struct
split(kv, ':')[offset(0)] as key,
split(kv, ':')[offset(1)] value
from x.kvs kv
) as y)]) z
-- order by order_id