CODEWITHSUNDEEP
google-bigquery
Gallory Knox
Gallory Knox

Reputation: 353

BigQuery FLATTEN clause equivalen in the new Standard SQL

I am trying to run a query in BigQuery using the new Standard SQL that BQ supports. My task for the moment is to try and run the following query (BQ syntax) using the Standard SQL.:

SELECT
    u0, u1
FROM
    (SELECT
        MIN(u) as u0, NEST(u) as u1
    FROM
        (FLATTEN((SELECT key, SPLIT(u2,',') as u FROM [dataset.table]),u))
    GROUP EACH BY key
    HAVING u0 <> u1)
GROUP EACH BY u0, u1

I'm mostly having problems with the FLATTEN operator. What I have so far, and it kind of works (not fully) is:

SELECT
  u0, u1
FROM
  (SELECT
    (SELECT MIN(uids) FROM UNNEST(u2_arr) u2) u0,
    u2_arr
  FROM
    (SELECT SPLIT(uids, ',') as u2_arr FROM `datase.table`)),
  UNNEST(u2_arr) u1
WHERE u0 <> u1
GROUP BY u0, u1

Any suggestions how to make the second query similar like the first one?

Thank you in advance! G

Upvotes: 1

Views: 1589

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Assuming your table looks like below (derived from code in your question) 

key u2   
1   2,6,3,1,4,5  
2   21,6,32,11,4,5

below BigQuery Standard SQL returns exactly same result as your original Legacy SQL 

SELECT 
  u0, u1
FROM (
  SELECT
    MIN(u) AS u0, ARRAY_AGG(u) AS u1
  FROM
    (SELECT key, u FROM `datase.table`, UNNEST(SPLIT(u2, ',')) AS u)
  GROUP BY key
), UNNEST(u1) AS u1
WHERE u0 <> u1

It might be not the best "translation" from legacy to standard sql - but w/o knowing exactly what you are trying to achieve - I wanted to leave code as close to your original code as possibble

From the other hand - i am not sure what you mean by it kind of works (not fully) as I would expect your version to work as is!

Upvotes: 2

Related Questions