以下是 BigQuery Standard 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

以上的以下变体也很有用

Below variation of above can be useful too

#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