Bigquery - Return array of structs with specific fields of the struct

Question

I have an array of structs in bigquery. I would like to return an array of structs, but the struct of the array with less fields that the original struct.

Example:

I would like to have the same results but without Skills.Id.

I tried to do something like this but

  SELECT  [(select struct(dd.Level as Level, dd.TypeId as typeid)
                  from unnest(dd.Skills) as dd  )]  as skills    
   FROM tablee dd

But get an exception:

Scalar subquery produced more than one element

Sergey Geron · Accepted Answer · 2021-11-01 11:53:52Z

5

Try ARRAY instead of []:

SELECT ARRAY(select struct(dd.Level as Level, dd.TypeId as typeid) 
             from unnest(tablee.Skills) as dd) as skills    
FROM tablee

answered Nov 1, 2021 at 11:53

Sergey Geron

10.5k4 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bigquery - Return array of structs with specific fields of the struct

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related