I am currently trying to work through this and I'm unsure as to how to proceed. I have the below data

I would like to turn it into | ID | properties.name | properties.value | |:---- |:------: | -----: | | One | a | 10 | | | b | 20 | | | c | 30 |

The below query looked like it was working but instead of having an array it created a nested record with 2 array fields.

SELECT ID
  name
  , value
  , array (
          select as struct
            split(name, ',') as name
            , split(value, ',') as value
        ) as properties
        
   
FROM `orders` 

CodePudding user response：

Consider below approach

select id, array(
    select as struct name, value
    from unnest(split(name)) name with offset
    join unnest(split(value)) value with offset
    using(offset)
  ) as properties
from `orders`           

if applied to sample data in your question - output is