BQ Scalar subquery produced more than one element

0
votes

Hi When I Run SQL Query I have next issue, why?

SELECT 
_raw.created_at,
_raw.name,
(select item.quantity from unnest (_raw.line_items) as item) as quantity

FROM `Orders` --as tb2 --,unnest (_raw.line_items) as item

where DATE(_raw.created_at)  = "2020-04-27"

enter image description here

1
google-bigquery
You have a low rate. Important on SO - you can mark accepted answer by using the tick on the left of the posted answer, below the voting. See meta.stackexchange.com/questions/5234/… for why it is important! Also important to vote on answer. Vote up answers that are helpful. ... You can check about what to do when someone answers your question - stackoverflow.com/help/someone-answers. Following these simple rules you increase your own reputation score and at the same time you keep us motivated to answer your questions :o) please consider! – Mikhail Berlyant

1 Answers

1
votes

I have next issue, why?

Below subquery produces more than one element 

select item.quantity from unnest (_raw.line_items) as item

this is obvious - the output here has as many elements (rows) as many items in line_items array

So, to make your query work - you can simply add ARRAY in front of it - as in below example 

SELECT 
_raw.created_at,
_raw.name,
ARRAY(select item.quantity from unnest (_raw.line_items) as item) as quantity

FROM `Orders` --as tb2 --,unnest (_raw.line_items) as item

where DATE(_raw.created_at)  = "2020-04-27"

OR make sure you limit output of subquery to just one element by adding LIMIT 1 as in below example 

SELECT 
_raw.created_at,
_raw.name,
(select item.quantity from unnest (_raw.line_items) as item LIMIT 1) as quantity

FROM `Orders` --as tb2 --,unnest (_raw.line_items) as item

where DATE(_raw.created_at)  = "2020-04-27"

OR ... any other ways to restrict output of that subquery to only one element - which really depends on your specific use-case and what it is that you are trying to achieve