BigQuery Error - Scalar Subquery produced more than one element

Question

I am trying to update a column in bigquery with the below query. But it is generating BigQuery Error - Scalar Subquery produced more than one element, that also specifically for this sub-query(Select * from unnest(array(Select to_json_String(JSON_EXTRACT_SCALAR(c.raw,'$.C360ServiceError.serviceName'),true) from tableabc as c where c.trace_id=a.trace_id). Can you please help

The complete query is:-

UPDATE tableabc a SET a.Link=CONCAT("a",(Select split(TopicName, '/')[OFFSET(1)] from tableabc As b where b.trace_id=a.trace_id ), 'String abc', (Select split(TopicName, '/')[OFFSET(1)] from tableabc As b where b.trace_id=a.trace_id ),'""', 'String def', ((Select * from unnest(array(Select to_json_String(JSON_EXTRACT_SCALAR(c.raw,'$.C360ServiceError.serviceName'),true) from tableabc as c where c.trace_id=a.trace_id)))), '"%0A"') WHERE DATE(a.logDate) Between CURRENT_DATE("Asia/Kolkata")-3 And CURRENT_DATE("Asia/Kolkata")

Roberto Armenta Ahumada Roberto Armenta Ahumada · Accepted Answer · 2021-05-27T22:28:42

The UNNEST function is returning more than one value and that is not allowed, I believe because you are unnesting the array, in my experience use unnest for the table and then array in order to avoid the error.

Try the below, and please let me now the outcome.

UPDATE tableabc a
SET    a.link=concat("a",
       (
              SELECT Split(topicname, '/')[OFFSET(1)]
              FROM   tableabc AS b
              WHERE  b.trace_id=a.trace_id ), 'String abc',
       (
              SELECT Split(topicname, '/')[OFFSET(1)]
              FROM   tableabc AS b
              WHERE  b.trace_id=a.trace_id ),'""', 'String def', (
       (
              SELECT *
              FROM   array
                     (
                            select to_json_string(json_extract_scalar(c.raw,'$.C360ServiceError.serviceName'),true)
                            FROM   (unnest tableabc AS c WHERE c.trace_id=a.trace_id)))), '"%0A"')
WHERE  date(a.logdate) BETWEEN CURRENT_DATE("Asia/Kolkata")-3 AND    CURRENT_DATE("Asia/Kolkata")

Regards :)

BigQuery Error - Scalar Subquery produced more than one element

1 Answers