How to get distinct values on GROUP_CONCAT using Google Big Query

Question

I'm trying to get distinct values when using GROUP_CONCAT in BigQuery.

I'll recreate the situation using a simpler, static example:

EDIT: I've modified the example to represent better my real situation: 2 columns with group_concat which needs to be distinct:

SELECT 
  category, 
  GROUP_CONCAT(id) as ids, 
  GROUP_CONCAT(product) as products
FROM 
 (SELECT "a" as category, "1" as id, "car" as product),
 (SELECT "a" as category, "2" as id, "car" as product),
 (SELECT "a" as category, "3" as id, "car" as product),
 (SELECT "b" as category, "4" as id, "car" as product),
 (SELECT "b" as category, "5" as id, "car" as product),
 (SELECT "b" as category, "2" as id, "bike" as product),
 (SELECT "a" as category, "1" as id, "truck" as product),
GROUP BY 
  category

This example returns:

Row category    ids products
1   a   1,2,3,1 car,car,car,truck
2   b   4,5,6   car,car,bike

I'd like to strip the duplicated values found, to return like:

Row category    ids products 
1   a   1,2,3   car,truck
2   b   4,5,6   car,bike

In MySQL, GROUP_CONCAT has a DISTINCT OPTION, but in BigQuery there isn't.

Any ideas?

Peti

possible duplicate of Syntax to run a distinct GROUP_CONCAT in Google Bigquery – Homeric

I think it's similar but not exactly the same, but thank you for pointing @Homeric – Peti

Answer 1

Here is solution which uses UNIQUE scope aggregation function to remove duplicates. Note, that in order to use it, first we need to build a REPEATED using NEST aggregation:

SELECT 
  GROUP_CONCAT(UNIQUE(ids)) WITHIN RECORD,
  GROUP_CONCAT(UNIQUE(products)) WITHIN RECORD 
FROM (
SELECT 
  category, 
  NEST(id) as ids, 
  NEST(product) as products
FROM 
 (SELECT "a" as category, "1" as id, "car" as product),
 (SELECT "a" as category, "2" as id, "car" as product),
 (SELECT "a" as category, "3" as id, "car" as product),
 (SELECT "b" as category, "4" as id, "car" as product),
 (SELECT "b" as category, "5" as id, "car" as product),
 (SELECT "b" as category, "2" as id, "bike" as product),
 (SELECT "a" as category, "1" as id, "truck" as product),
GROUP BY 
  category
)

Cranial

Perfect Mosha! I've never heard about UNIQUE function. It worked flawlessly! Thanks! – Peti

I don't think that you eve need to do the NEST subselect – Doldrums

I think this solution is in Legacy SQL syntax, would be good to mention this. This solution does not work with Standard SQL for BigQuery. Does anyone has a solution in Standard SQL? – Lothario

Answer 2

Removing duplicates before applying group_concat will achieve the result you want:

    SELECT 
      category, 
      GROUP_CONCAT(id) as ids
    FROM (  
    SELECT category, id
    FROM 
     (SELECT "a" as category, "1" as id),
     (SELECT "a" as category, "2" as id),
     (SELECT "a" as category, "3" as id),
     (SELECT "b" as category, "4" as id),
     (SELECT "b" as category, "5" as id),
     (SELECT "b" as category, "6" as id),
     (SELECT "a" as category, "1" as id),
    GROUP BY 
      category, id
    )
    GROUP BY 
      category

Capitulary

Thanks Ahmed, it works for a single column, but in my real situation I need 2 different distinct columns. I've edited the question to show the problem. – Peti

Answer 3

In Standard SQL (the preferred BigQuery dialect) the solution would be:

SELECT 
    string_agg(distinct(q.product), ', ') as products_distinct

FROM 
    (
        (SELECT "a" as category, "1" as id, "car" as product)
        union all
        (SELECT "a" as category, "2" as id, "car" as product)
        union all
        (SELECT "a" as category, "3" as id, "car" as product)
        union all
        (SELECT "b" as category, "4" as id, "car" as product)
        union all
        (SELECT "b" as category, "5" as id, "car" as product)
        union all
        (SELECT "b" as category, "2" as id, "bike" as product)
        union all
        (SELECT "a" as category, "1" as id, "truck" as product)
    ) as q

Lothario