Big Query, Error: Scalar subquery produced more than one element

Question

I have a query encounterying this error message: Error: Scalar subquery produced more than one element

I want to join tables representing behaviors happening in different time period, but normal join seems not working:

select a.g, count(*) from

(select user_id as user,

(select x.value.string_value from analytics_156934592.events_20180701, unnest(event_params) as x where x.key = 'group') as g,

(select x.value.string_value from analytics_156934592.events_20180701, unnest(event_params) as x where x.key = 'operation') as operation

from analytics_156934592.events_20180701 where event_name = 'book_now')a

join

(select user_id as user,

(select x.value.string_value from analytics_156934592.events_20180702, unnest(event_params) as x where x.key = 'operation') as operation

from analytics_156934592.events_20180702 where event_name = 'book_now')b

on a.user = b.user

where a.operation = 'no_car' and b.operation = 'confirm'

group by a.g

Not sure if it's a proper way to do joins with Big Query Standard Sql Also the solutions to previous similar questions didn't work for me.

Thanks for your help!

The error message is clear, one or more of your subqueries in the select clause are generating more than one value. There must be a better way to formulate your query than what you have now. — Tim Biegeleisen
You could use top 1 on your subqueries for solve the problem, but you need to check those sub queries in detail, because you could get wrong data — Cristina Carrasco

yimei yimei · Accepted Answer · 2018-07-26T01:45:02

Thanks all!

I got a solution from my friend, and it is feasible:

select count(distinct a.user), count(distinct b.user_id)
from

(select
user_id as user

from `analytics_156934592.events_20180707`
join unnest(event_params) as x
join unnest(event_params) as y
where event_name = 'book_now'
and x.key = 'group'
and x.value.string_value = 'a'
and y.key = 'operation'
and y.value.string_value = 'no_car')a 

left join

(select
user_id , event_date

from `analytics_156934592.events_*` 
join unnest(event_params) as y
where event_name = 'launcher'
and y.key = 'operation'
and y.value.string_value = 'show'
AND REGEXP_EXTRACT(_TABLE_SUFFIX, r'(\d+)') BETWEEN '20180708' and '20180720')b 

on a.user = b.user_id

Big Query, Error: Scalar subquery produced more than one element

1 Answers