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?

possible duplicate of [Syntax to run a distinct GROUP\_CONCAT in Google Bigquery](http://stackoverflow.com/questions/28324533/syntax-to-run-a-distinct-group-concat-in-google-bigquery) — Pentium10, Feb 20 '15 at 21:50
I think it's similar but not exactly the same, but thank you for pointing @Pentium10 — Leonardo Naressi, Feb 23 '15 at 12:56

score 3 · Answer 1 · answered Feb 21 '15 at 00:41

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

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. — Leonardo Naressi, Feb 23 '15 at 14:35

score 3 · Accepted Answer · edited Aug 10 '17 at 13:37

3

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
)

edited Aug 10 '17 at 13:37

kupendra

1,002
1
15
37

answered Feb 23 '15 at 19:16

Mosha Pasumansky

13,206
5
32
55

Perfect Mosha! I've never heard about UNIQUE function. It worked flawlessly! Thanks! – Leonardo Naressi Feb 24 '15 at 20:50
I don't think that you eve need to do the NEST subselect – Roman Mar 30 '16 at 09:22
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? – Hedge92 Dec 16 '21 at 13:37

Hedge92 · Answer 3 · 2021-12-16T14:39:22.450

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

How to get distinct values on GROUP_CONCAT using Google Big Query

3 Answers3