如何在bigquery标准sql中使结构扁平化？ [英] How to flatten a struct in bigquery standard sql?

问题描述

使用下面的标准sql查询，我可以返回包含a和b中所有字段的BigQuery中的结构表。

  SELECT a，b 
 FROM first_table a 
加入second_table b 
在a.key上= b.key; 
  

结果表格模式的 a 为RECORD， b 作为RECORD，其中a.field1，a.field2，b.field1，b.field2等都具有其原始类型。

如果我将此表保存为a_join_b，然后在legacy_sql中查询它：

  SELECT * 
 FROM a_join_b 
; 
  

它将压扁这些结构并提供一个表，其中包含名为a_field1，a_field2，...，b_field1 ，b_field2。请注意表名和字段名称之间的下划线，并且a和b可以具有相似的字段名称。

在BigQuery Standard SQL中是否有一种方法可以平整表格没有提及个人记录名称？我已经在看UNNEST如何将嵌套平铺转换为标准SQL ，但解决方案似乎需要连接必须命名的未命名字段。 解决方案

已确认的解决方案已由Mikhail Berlyant在提供的回复到是否有预先在BigQuery Standard SQL中加入表名的方法？问题在堆栈中。

With the below standard sql query, I can return a table of structs in BigQuery that contains all fields from both a and b.

SELECT a, b
FROM first_table a
JOIN second_table b
ON a.key = b.key;

The resulting table schema will have a as RECORD, and b as RECORD, with a.field1, a.field2, b.field1, b.field2, etc. all having their original types.

If I save this table as a_join_b and then query it in legacy_sql:

SELECT *
FROM a_join_b
;

it will flatten the structs and provide a table with fields named a_field1, a_field2, ..., b_field1, b_field2. Note the underscores between the table names and the field names, and that a and b can have similar field names.

Is there a way in BigQuery Standard SQL to flatten a table without referring to individual record names? I've looked at UNNEST in How to convert a nested flatten into Standard SQL but the solution seems to require joining unnested fields that must be named.

解决方案

The acknowledged solution has been provided by Mikhail Berlyant in his reply to the "Is there a way to prepend joined tablename in BigQuery Standard SQL?" question in stack.

这篇关于如何在bigquery标准sql中使结构扁平化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！