How to convert a nested flatten into Standard SQL

1
votes

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?

2
sqlgoogle-bigquery

2 Answers

3
votes

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;
1
votes

How can i convert this to Standard SQL query?
SELECT * FROM `DB.table1`
WHERE record.atr.value IN "1"

Below is for BigQuery Standard SQL 

#standardSQL
SELECT *
FROM `DB.table1`
WHERE (SELECT COUNT(1) 
        FROM UNNEST(record.atr) AS atr, 
              UNNEST(atr.value) AS val 
        WHERE val IN ('1')) > 0

You can test it with dummy data (partially borrowed from Elliott's example) 

#standardSQL
WITH `DB.table1` AS (
  SELECT 1 AS id, STRUCT([STRUCT<x STRING, value ARRAY<STRING>>('foo', ['1', '2']), ('bar', ['3', '4', '5'])] AS atr) AS record UNION ALL
  SELECT 2, STRUCT([STRUCT<x STRING, value ARRAY<STRING>>('baz', ['6', '7'])])
)
SELECT *
FROM `DB.table1`
WHERE (SELECT COUNT(1) 
        FROM UNNEST(record.atr) AS atr, 
              UNNEST(atr.value) AS val 
        WHERE val IN ('1')) > 0