将SPLIT键/值对分配到Google BigQuery中的列

本文介绍了将SPLIT键/值对分配到Google BigQuery中的列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我对Google BigQuery还是陌生的，肯定很挣扎.

I am quite new to Google BigQuery and definitely struggling.

我的表具有以下内容:

+----------+----------------------------------------+ | order_id | line_items | +----------+----------------------------------------+ | 123 | id:1|qy:1|sum:1.00;id:2|qy:6|sum:4.50; | +----------+----------------------------------------+ | 456 | id:1|qy:3|sum:3.00;id:3|qy:4|sum:3.20; | +----------+----------------------------------------+

我需要看起来像这样:

+----------+----+----+------+ | order_id | id | qy | sum | +----------+----+----+------+ | 123 | 1 | 1 | 1.00 | | 123 | 2 | 6 | 4.50 | | 456 | 1 | 3 | 3.00 | | 456 | 3 | 4 | 3.20 | +----------+----+----+------+

我在line_items中拥有的键值对的数量是任意的(并且有不止三个，但是我需要提取这三个).

The amount of key value pairs I have in line_items is arbitrary (and there are much more than those 3, but I would need to extract those three).

我能够使以下UNNEST和SPLIT查询正常工作，但不幸的是，我仍然具有这些键值对...

I was able to get the following UNNEST and SPLIT query working, but unfortunately I still have these key-value pairs...

此

SELECT order_id, line_items FROM `myTable`, UNNEST(SPLIT(line_items,"|")) line_items

带我来这里

+----------+------------+ | order_id | line_items | +----------+------------+ | 123 | id:1 | | 123 | qy:1 | | 123 | sum:1.00 | | 123 | id:2 | | 123 | qy:6 | | 123 | sum:4.50; | | 456 | id:1 | | 456 | qy:3 | | 456 | sum:3.00 | | 456 | id:3 | | 456 | qy:4 | | 456 | sum:3.20 | +----------+------------+

因此，我仍然不是很能干，如何将这些键提取到列标题，并将值提取到列内容.

So I am still not really able, how to extract these keys to column headlines and the value to the column content.

如果有人指出我正确的方向，我将不胜感激.

I would highly appreciate if someone pointed me in the right direction.

已经非常感谢！

推荐答案

下面是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