CODEWITHSUNDEEP
google-bigquery
GeorgeJerzy
GeorgeJerzy

Reputation: 81

How to change type of nested field in BigQuery via CAST?

I am trying to create a new table from a query, casting one of the nested fields on the way. Something like that:

#standardSQL    
SELECT * EXCEPT (bid_info),
  (SELECT AS STRUCT bid_info.* EXCEPT (clicks), CAST(bid_info.clicks AS NUMERIC) as clicks 
  FROM `testing.fb_ads.ads`) as bid_info
FROM `testing.fb_ads.ads`

I am getting error "Scalar subquery produced more than one element" and the query execution doesn't seem to end.

How can I fix this query?

Upvotes: 2

Views: 1326

Answers (1)

GeorgeJerzy
GeorgeJerzy

Reputation: 81

So, I've found the solution after all, here it goes:

SELECT * EXCEPT (bid_info),
(SELECT AS STRUCT bid_info.* EXCEPT (clicks), CAST(bid_info.clicks AS NUMERIC) as clicks ) as bid_info
FROM `testing.fb_ads.ads`

Upvotes: 6

Related Questions