Return an array(Repeated Field) from a query in BigQuery

3
votes

I am new to BigQuery and SQL. I have a table with following details

Schema

ID : String : Nullable
BCats : String : Repeated

ID can be repeated

Preview

   ID           BCats
|-----------------------|
|  ABCD     |   BCat25  | 
|           |   BCat24  |
|           |   BCat23  |
|_______________________|
|  PQRS     |   BCat8   |
|           |   BCat9   |
|_______________________|
|  ABCD     |   BCat23  |
|           |   BCat25  |
|           |   BCat24  |
|_______________________|
|  MNOP     |   BCat12  |
|           |   BCat13  |
|_______________________|
|  PQRS     |   BCat8   |
|           |   BCat9   |
|-----------------------|

I am trying to group the table based on ID using the following query

Query

SELECT BCats,ID
FROM (SELECT (GROUP_CONCAT(BCats)) as BCats,ID
      FROM(
        SELECT
        UNIQUE(BCats) as BCats,ID FROM
            my_table
        GROUP BY 
          ID
          )
    GROUP BY
          ID
    )

OUTPUT from the query in JSON Format is

Output from Query

{"BCats":"BCat25,BCat24,BCat23","ID":"ABCD"}

{"BCats":"BCat8,BCat9","ID":"PQRS"} 

{"BCats":"BCat12,BCat13","ID":"MNOP"}

My question is how can I output Array from the Query, like this

Expecting Output

{"BCats" : ["BCat25","BCat24","BCat23"],"ID":"ABCD"}

Currently I am getting BCats as a String.

I need to output this data into new table with BCats as Repeated.

Please Help.

Preview :

2
google-bigquery

2 Answers

2
votes

Try below. Note: in Web UI you need not only set Destination Table but also set/check-on Allow Large Results checkbox and uncheck Flatten Results checkbox

SELECT NEST(UNIQUE(BCats)) AS BCats, ID 
FROM my_table
GROUP BY ID
3
votes

You should instead use standard SQL. If you are familiar with legacy SQL, there is a migration guide that talks about the differences between the two dialects. After enabling standard SQL (uncheck "Use Legacy SQL" under "Show Options" in the UI) you can run e.g.:

WITH my_table AS (
  SELECT 'ABCD' AS ID, ['BCat25', 'BCat24', 'BCat23'] AS BCats UNION ALL
  SELECT 'PQRS', ['BCat8', 'BCat9'] UNION ALL
  SELECT 'ABCD', ['BCat23', 'BCat25', 'BCat24'] UNION ALL
  SELECT 'MNOP', ['BCat12', 'BCat13'] UNION ALL
  SELECT 'PQRS', ['BCat8', 'BCat9']
)
SELECT
  ID,
  ARRAY_AGG(DISTINCT BCat) AS BCats
FROM my_table, UNNEST(BCats) AS BCat
GROUP BY ID;