CODEWITHSUNDEEP
google-bigquery
Gigaxel
Gigaxel

Reputation: 1068

How to STRING_AGG rows from UNNEST?

Basically I am new to BigQuery, and I've tried retrieving data by using STRING_AGG and UNNEST.

I have an object:

What I am trying to retrieve is for each PricingModel the PriceOptions in the following format: $m/nm where n stands for the PriceOption duration, and m for the PriceOption price.

However I want to concatenate the results i.e. the $m/nm of each PriceOption for each PricingModel, to get an output like:

Row     PriceOptions
__________________________
1       $1/5m,$2/10m,$3/20m
2       $5/25m,$6/40m,$7/60m

What I've tried is the following query:

SELECT STRING_AGG((select concat(concat(concat('$', price), '/'), concat(duration, 'm')) from unnest(pricingmodel.priceoptions))) as priceOptions
from `mytable.event` limit 100;

But I receive the following error:

Scalar subquery produced more than one element

Upvotes: 0

Views: 816

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Use below instead (very light adjustment to your original query) 

#standardSQL
SELECT 
  ( SELECT STRING_AGG(CONCAT(CONCAT(CONCAT('$', price), '/'), CONCAT(duration, 'm'))) 
    FROM UNNEST(pricingmodel.priceoptions)
  ) AS priceOptions
FROM `mytable.event` 
LIMIT 100

Also, try to use shortened version of all these CONCATs as 

STRING_AGG('$' || price || '/' || duration || 'm')

Upvotes: 2

Related Questions