Scalar subquery produced more than one element - Standard SQL

Question

My query failed and showed the message "Scalar subquery produced more than one element"

  SELECT 
       EXTRACT (DATE FROM timestamp) AS Date
       ,jsonPayload.request.headers.origin AS URL
       ,jsonPayload.response.body.json.data.country.key AS Country
       ,(SELECT A.name FROM `pms_table_request_log.partners_request_log_*`, 
       UNNEST(jsonPayload.response.body.data.rows) AS A) AS Partner
       ,AVG((SELECT B.rating 
  FROM 
    `pms_table_request_log.partners_request_log_*`, 
     UNNEST(jsonPayload.response.body.data.rows) AS B)) AS Rating
    ,COUNT (*) AS Impression
    FROM 
       `pms_table_request_log.partners_request_log_*`
    GROUP BY 
        Date,URL,Country,Partner
    ORDER BY 
        Impression DESC;

Please guide what have I done wrong

Martin Weitzmann Martin Weitzmann · Accepted Answer · 2019-01-30T15:47:50

Your sub-query is resulting not in one element but in more than one element. BQ doesn't know what to do with more than one element when there is just room for one.

You can either feed the results into an array, which itself then again is only one element. You can aggregate or restrict the output of your sub-query. E.g.

(SELECT STRING_AGG(DISTINCT A.name) FROM `pms_table_request_log.partners_request_log_*`, 
       UNNEST(jsonPayload.response.body.data.rows) AS A) AS Partner

or

(SELECT AVG(B.rating) 
  FROM 
    `pms_table_request_log.partners_request_log_*`, 
     UNNEST(jsonPayload.response.body.data.rows) AS B)) AS Rating

as aggregation examples.

For limiting:

(SELECT A.name FROM `pms_table_request_log.partners_request_log_*`, 
       UNNEST(jsonPayload.response.body.data.rows) AS A LIMIT 1) AS Partner

The right choice depends on the meaning of your data, though. Hope that helps

Scalar subquery produced more than one element - Standard SQL

1 Answers