Combining Arrays Big Query

Question

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

Mikhail Berlyant · Accepted Answer · 2019-07-18 14:04:20Z

3

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

answered Jul 18, 2019 at 14:04

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Manisha29 Over a year ago

Thank you so much Mikhail! This worked perfectly!!! I removed your first query and used my one as the product name/duration were created there and ended up with: ` WITH Test1 AS ( SELECT TransactionID, SPLIT ( c_ProductName, "|") as ProductName ,SPLIT ( c_ProductTrialDuration , "|") as ProductDuration FROM my.other.table ) SELECT TransactionID, ARRAY (SELECT CONCAT (name, '-', duration) FROM UNNEST(ProductName) name WITH OFFSET JOIN UNNEST(ProductDuration) duration WITH OFFSET USING(OFFSET) ) ProductName_Duration FROM Test1 `

Collectives™ on Stack Overflow

Combining Arrays Big Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related