CODEWITHSUNDEEP
arraysmergegoogle-bigquery
Zoey.L
Zoey.L

Reputation: 17

merge two array column and keep the distinct in BigQuery

I have a query output like that. I want to have N3 column is the distinct merge of N1 and N2. Query output example

My current query is this:

SELECT  rd.cId  
        , rd.dId 
        ,ARRAY_AGG(ei.IncentiveName IGNORE NULLS) AS N1
        ,ARRAY_AGG(ai.IncentiveName IGNORE NULLS) AS N2

FROM rd
join ei on...
join ai on ...
GROUP BY rd.cId, rd.dId

Upvotes: 1

Views: 936

Answers (3)

Gourav Garg
Gourav Garg

Reputation: 2911

We found the problem, in many of the records, there was null value in 2nd column which was returning overall outcome as null.

Fixed that by checking with help of IFNull(N1, []) which solved the problem.

SELECT  rd.cId  
    , rd.dId 
    ARRAY_CONCAT(
           IFNULL(ARRAY_AGG(DISTINCT ei.IncentiveName IGNORE NULLS),[]),
           IFNULL(ARRAY_AGG(DISTINCT ai.IncentiveName IGNORE NULLS),[])
    )
FROM rd
join ei on...
join ai on ...
GROUP BY rd.cId, rd.dId

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Consider below

with your_current_query as (
  SELECT  rd.cId  
    , rd.dId 
    ,ARRAY_AGG(ei.IncentiveName IGNORE NULLS) AS N1
    ,ARRAY_AGG(ai.IncentiveName IGNORE NULLS) AS N2
  FROM rd
  join ei on...
  join ai on ...
  GROUP BY rd.cId, rd.dId
)
select *, array(
    select * from t.N1 union distinct
    select * from t.N2
  ) as N3
from your_current_query  t

Upvotes: 1

Samuel
Samuel

Reputation: 3528

You can merge both arrays with array_concat. Then unnest the array and regroup to an arrray, but keep only distinct elements.

with tbl as 
(select 1 id, [1,2,2,3] N1, [1,5,7,8] N2
union all select 2 ,[],[5,5,5,6])

select *,
array_concat(N1,N2),
(select array_agg(distinct x) from unnest(array_concat(N1,N2))x)
from tbl

In case you want to group by id column:

with tbl as 
(select 1 id, [1,2,2,3] N1, [1,5,7,8] N2
union all select 2 ,[],[5,5,5,6]
union all select 2 ,[7,8],[9]),

helper as (
  select id, array_agg(N1_) N1, array_agg(N2_) N2
  from tbl,unnest(N1) N1_,unnest(N2) N2_
  group by 1
)
select *,
#array_concat(N1,N2),
(select array_agg(distinct x) from unnest(array_concat(N1,N2))x)
from helper

Upvotes: 0

Related Questions