如何将嵌套平铺转换为标准SQL [英] How to convert a nested flatten into Standard SQL

问题描述

目前使用BigQuery从谷歌。我的遗留查询如下所示：

 选择* FROM FLATTEN（FLATTEN（[DB.table1]，record.atr））记录。 atr.value）
 WHERE record.atr.value IN1
  

我明白了< hQuery =https://chartio.com/resources/tutorials/how-to-flatten-data-using-google-bigquerys-legacy-vs-standard-sql/ =nofollow noreferrer> BigQuery现在自动变平，所以我认为这会做到这一点：

  SELECT * FROM`DB.table1` 
 WHERE record.atr.value IN1
  

然而，我得到一个错误错误：Can not在类型ARRAY，sv ARRAY，... >> at [2:17]上访问字段值

如何将其转换为标准SQL查询？ 在使用标准SQL时，BigQuery不会自动将数组展平; 您提供的链接表明相反。在你的情况下，查询将是这样的：

  #standardSQL 
 SELECT * EXCEPT（value），value 
 FROM`Db.table1` 
 CROSS JOIN UNNEST（record.atr）AS atr 
 CROSS JOIN UNNEST（atr.value）AS值; 
  

如果您想为空数组获得一行，可以使用 LEFT JOIN 改为：

  #standardSQL 
 SELECT * EXCEPT（value），value 
 FROM`Db.table1` 
 LEFT JOIN UNNEST（record.atr）AS atr 
 LEFT JOIN UNNEST（atr.value）AS值; 
  

以下是您可以尝试运行的示例查询：

  #standardSQL 
 WITH AS（
 SELECT STRUCT（ARRAY< STRUCT< x STRING，value ARRAY< INT64>>> [（' （'bar'，[3，4，5]）] AS atr）AS记录UNION ALL 
 SELECT STRUCT（ARRAY< STRUCT< x STRING，value ARRAY< INT64> >>> [（'baz'，[6，7]）]）
）
 SELECT * EXCEPT（value）
 FROM输入
 CROSS JOIN UNNEST（record.atr ）AS atr 
 CROSS JOIN UNNEST（atr.value）AS值; 
  

Currently using BigQuery From google. My legacy query looks like:

Select * FROM FLATTEN(FLATTEN([DB.table1], record.atr), record.atr.value) 
WHERE record.atr.value IN "1"

I understand BigQuery now automatically flattens tables so i thought this would do the trick:

SELECT * FROM `DB.table1`
WHERE record.atr.value IN "1" 

However i get an error Error: Cannot access field value on a value with type ARRAY, sv ARRAY, ...>> at [2:17]

How can i convert this to Standard SQL query?

解决方案

BigQuery does not automatically flatten arrays when using standard SQL; the link that you provided indicates the opposite. In your case, the query would be something like this:

#standardSQL
SELECT * EXCEPT(value), value
FROM `Db.table1`
CROSS JOIN UNNEST(record.atr) AS atr
CROSS JOIN UNNEST(atr.value) AS value;

If you want to get a row even for empty arrays, you can use LEFT JOIN instead:

#standardSQL
SELECT * EXCEPT(value), value
FROM `Db.table1`
LEFT JOIN UNNEST(record.atr) AS atr
LEFT JOIN UNNEST(atr.value) AS value;

Here is a sample query that you can try running:

#standardSQL
WITH Input AS (
  SELECT STRUCT(ARRAY<STRUCT<x STRING, value ARRAY<INT64>>>[('foo', [1, 2]), ('bar', [3, 4, 5])] AS atr) AS record UNION ALL
  SELECT STRUCT(ARRAY<STRUCT<x STRING, value ARRAY<INT64>>>[('baz', [6, 7])])
)
SELECT * EXCEPT (value)
FROM Input
CROSS JOIN UNNEST(record.atr) AS atr
CROSS JOIN UNNEST(atr.value) AS value;

这篇关于如何将嵌套平铺转换为标准SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！