下面是BigQuery标准SQL

Below is for BigQuery Standard 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    

如果要应用于您的问题的样本数据-输出为

If to apply to sample data from your question - output is

下面的变化也可能有用

#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