I have a table that look like this: How can I get it back to this? The schema is: EDIT This is how I'm getting the output now Answer

How can I transform all row nested rows back into normal rows bigquery?

I have a table that look like this:

Row id  edad    nombre     sector          contacto.telefono    contacto.correo 
1   275  18   dozolme      El Golf         5555555          xxxxx@xxxxxx.com
                                           123456789        yyyyy@yyyyy.com
                                           35161631

How can I get it back to this?

Row id  edad    nombre     sector          telefono             correo  
1   275  18   dozolme      El Golf         5555555          xxxxx@xxxxxx.com
2   275  18   dozolme      El Golf         123456789        yyyyy@yyyyy.com
3   275  18   dozolme      El Golf         35161631         null --> ? maybe null?

The schema is:

id                  INTEGER NULLABLE    
edad                INTEGER NULLABLE    
nombre              STRING  NULLABLE    
sector              STRING  NULLABLE    
contacto            RECORD  REPEATED    
contacto.telefono   STRING  REPEATED    
contacto.correo     STRING  REPEATED

EDIT

This is how I’m getting the output now

Answer

select * except(contacto, telefono, correo),
  telefono, correo
from `project.dataset.table`,
unnest(contacto), 
unnest(telefono) as telefono with offset
join unnest(correo) as correo with offset
using(offset)

Advertisement

Answer