CODEWITHSUNDEEP
google-bigquery
Roman
Roman

Reputation: 9451

Big query using two UNIQUE statements in query

My query looks something like this: 

SELECT
  id,
  unique(a) AS un_a,
  unique(b) AS un_b,
FROM x.y

GROUP BY id

I want unique values for a (un_a) and unique values for b (un_b).
This errs as follows: 

Cannot output multiple independently repeated fields at the same time. Found un_a and un_b

Running this query with only one UNIQUE statement works as intended. What's going on here? How do I fix it?

Upvotes: 0

Views: 76

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Actually it works!

Result look something like below: 

Row id  un_a    un_b     
1    1     1       1     
           2       2     
           3       3     
                   5     
2    2     5       5     
           6       6

Please note: BigQuery automatically flattens query results, so if you use your query as top level query, the results won't contain repeated fields.
Use it as subselect that produces intermediate results for immediate use by the same query or save this to table using respective options (Allow Large Results and UnFlatten Results) or you need to use it.

The error you are getting - is because you are trying to output two independent repeated fields and BigQuery doesnt handle this. See Flatten Operator for more insight

If for some reason this is not what you want to deal with - you can use below "workaround":

Below is potential workaround, assuming you need to present for each id list of all a in un_a and list of all b in un_b. 

SELECT id,   
  GROUP_CONCAT(STRING(UNIQUE(a))) AS un_a,
  GROUP_CONCAT(STRING(UNIQUE(b))) AS un_b,
FROM x.y
GROUP BY id

Result should like: 

Row id  un_a    un_b     
1    1  1,2,3   1,2,3,5  
2    2  5,6 5,6

Upvotes: 1

Related Questions