title: BigQuery Arrays in Array to Proper Columns
body: <p>I'm trying to manipulate this;</p>
  
  <pre><code>id | data
  1  | [[item1,2,1.99],[item2,1,4.99]]
  2  | [[item1,2,1.99]]
  </code></pre>
  
  <p>into this:</p>
  
  <pre><code>id | A     | B | C    |
  1  | item1 | 2 | 1.99 |
     | item2 | 1 | 4.99 |
  2  | item1 | 2 | 1.99 |
  ...'
  </code></pre>
  
  <p>The closest I got is;</p>
  
  <pre><code>id | data |
  1  | item1, 2, 1.99 |
     | item2, 1, 4.99 |
  2  | item1, 2, 1.99 |
  </code></pre>
  
  <p>How do I split that array of strings into columns?</p>
  
  <p>Here's my current query;</p>
  
  <pre><code>SELECT 
     id,
     ARRAY(SELECT * FROM UNNEST(SPLIT(SUBSTR(line_items, 3 , LENGTH(line_items) - 4),'], [')) AS line_items) AS line_items
  FROM raw_data.sales 
  </code></pre>
  
  <p>This query does the same;</p>
  
  <pre><code>SELECT 
     id,
     SPLIT(SUBSTR(line_items, 3 , LENGTH(line_items) - 4),'], [') AS line_items
  FROM raw_data.sales
  </code></pre>
  
  <p><strong>Solution</strong></p>
  
  <pre><code>SELECT
    ID id,
    ARRAY_AGG(STRUCT(SPLIT(A, ',')[OFFSET(0)] AS A,
    SPLIT(A, ',')[OFFSET(1)] AS B,
    SPLIT(A, ',')[OFFSET(2)] AS C))
  FROM
    TABLE t,
    UNNEST(SPLIT(SUBSTR(DATA, 3 , LENGTH(DATA) - 4),'],[')) A
  GROUP BY id
  </code></pre>