CODEWITHSUNDEEP
sqlarraysgoogle-bigquery
Rachel
Rachel

Reputation: 21

Flattening an array in SQL

I am trying to flatten this array so that each neighbor has its own column.

How do I write a query that allows me to flatten this array when I don't know the elements in the array?

SELECT deviceid,
       neighbors
FROM
      `etl.routing_table_nodes`
WHERE
       Parent = 'QMI-YSK'

And results currently look like: 

Row deviceid    neighbors    
1    OHX-ZSI    DMR-RLE  
                WMI-YEK  
2    OHX-ZFI    DMR-RLE  
                QMI-YSK

Bigquery screenshot

Upvotes: 1

Views: 15794

Answers (1)

Lisa Yin
Lisa Yin

Reputation: 169

Try

SELECT
    deviceid, unnested_neighbors
FROM 
    `etl.routing_table_nodes` table,
    UNNEST(table.neighbors) as unnested_neighbors
WHERE
    unnested_neighbors = 'QMI-YSK'

Upvotes: 1

Related Questions