在 Google bigquery 中转换数据 - 提取文本、将其拆分为多列并旋转数据

我在大查询中有一些博客数据，我需要对其进行转换以使其更易于使用和查询。数据如下：

我想提取并转换数据在 Results{…..} 之后的大括号内（蓝色）。数据的形式为‘(\d+((PQ)|(KL))+\d+)’结果数组中可以有 1-20 多个条目。我只对前 16 个条目感兴趣。

我已经能够使用 Substr 和 regext_extract 将大括号内的数据提取到新列中。但我无法将其拆分为列（有时只有 1 个结果，因此缺少分隔符“，”。我是正则表达式新手，也许我可以使用类似 '(\d+((PQ)| (KL))+\d+){1}’等将数据拆分为多个列，然后对其进行透视。

在我的例子中，理想的输出是将其转换为如下形式：

在上述解决方案中，原始表中的每一行都会重复 1 到 16 次，具体取决于结果数组中的项目数。

我不完全确定是否可以在大查询中执行此操作。如果有人能在这里帮助我，我将不胜感激。

如果这是不可能的，那么对于结果数组中条目少于 16 个的情况，我可以为每个事件设置 16 行，Event_details 中的值为 NULL。

In case both of these are not possible, the last solution would be to have it transformed into something like:

我想要转换数据的原因是，在大多数情况下，我需要找到哪些结果数组项出现以及出现的顺序。

看一下这个：使用 bigquery 将字符串拆分为多列 https://stackoverflow.com/questions/22154275/split-string-into-multiple-columns-with-bigquery/22166014#22166014。 在他们的例子中，它是由空格分隔的。将 \s 替换为 ','

就像是：

SELECT  
Regexp_extract(StringToParse,r'^*{(?:[^,]*,){0}(\d+(?:(?:PQ)|(?:KL))+\d+)\s?') as Word0,
Regexp_extract(StringToParse,r'^*{(?:[^,]*,){1}(\d+(?:(?:PQ)|(?:KL))+\d+)\s?') as Word1,
Regexp_extract(StringToParse,r'^*{(?:[^,]*,){2}(\d+(?:(?:PQ)|(?:KL))+\d+)\s?') as Word2,
Regexp_extract(StringToParse,r'^*{(?:[^,]*,){3}(\d+(?:(?:PQ)|(?:KL))+\d+)\s?') as Word3,
FROM
(SELECT 'bla{1234PQ5,6789KL0,1234PQ5,6789KL0,123' as StringToParse)