CODEWITHSUNDEEP
sqlgoogle-bigquery
Manisha29
Manisha29

Reputation: 1

Combining Arrays Big Query

I have an array where I have 2 fields (product name and product duration) and want to combine them in to one row.

By using SPLIT (which I need to do to get to this stage as transactionID is unique and want it split by this), it has automatically created an array for me which looks like this: 

Transaction ID    ProductName       ProductDuration

1234               Ents                14D
                   Movies               7D
                   Sports              NONE 
---------------------------------------------------------
5678               Movies                7D
                   Kids                 14D

This is the code I have used to get to my current stage: 

SELECT
TransactionID
,SPLIT ( c_ProductName, "|") as ProductName 
,SPLIT ( c_ProductTrialDuration , "|") as ProductDuration

Ideally as per example below, I would like something like 

Transaction ID     ProductName_Duration
---------------------------------------------------------
1234               Ents-14D
                   Movies-7D
                   Sports-NONE
---------------------------------------------------------
5678               Movies-7D
                   Kids-14D

I've tried ARRAY_CONCAT but that's only added duration to product name below (a bit like a union).

If anyone knows how to fix this that would be great!!

Thanks

Upvotes: 0

Views: 284

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below example for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1234 id, ['Ents','Movies','Sports'] ProductName, ['14D','7D','NONE'] ProductDuration UNION ALL
  SELECT 5678, ['Movies','Kids'], ['7D','14D']
)
SELECT id,
  ARRAY(SELECT CONCAT(name, '-', duration) 
    FROM UNNEST(ProductName) name WITH OFFSET
    JOIN UNNEST(ProductDuration) duration WITH OFFSET
    USING(OFFSET)
  ) ProductName_Duration
FROM `project.dataset.table`

with result 

Row id      ProductName_Duration     
1   1234    Ents-14D     
            Movies-7D    
            Sports-NONE  
2   5678    Movies-7D    
            Kids-14D

Upvotes: 3

Related Questions