BigQuery Arrays in Array to Proper Columns

Question

I'm trying to manipulate this;

id | data
1  | [[item1,2,1.99],[item2,1,4.99]]
2  | [[item1,2,1.99]]

into this:

id | A     | B | C    |
1  | item1 | 2 | 1.99 |
   | item2 | 1 | 4.99 |
2  | item1 | 2 | 1.99 |
...'

The closest I got is;

id | data |
1  | item1, 2, 1.99 |
   | item2, 1, 4.99 |
2  | item1, 2, 1.99 |

How do I split that array of strings into columns?

Here's my current query;

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

This query does the same;

SELECT 
   id,
   SPLIT(SUBSTR(line_items, 3 , LENGTH(line_items) - 4),'], [') AS line_items
FROM raw_data.sales

Solution

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

Mikhail Berlyant · Accepted Answer · 2019-11-14 19:55:41Z

Below is for BigQuery Standard SQL

#standardSQL
SELECT id, 
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(0)] FROM UNNEST(x.y) value) AS A,
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(1)] FROM UNNEST(x.y) value) AS B,
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(2)] FROM UNNEST(x.y) value) AS C
FROM `project.dataset.my_table`,
UNNEST([STRUCT(SPLIT(SUBSTR(DATA, 3, LENGTH(DATA) - 4),'],[') AS y)]) x

You can test, play with above using sample data from your question as in example below

#standardSQL
WITH `project.dataset.my_table` AS (
  SELECT 1 AS ID, '[[item1,2,1.99],[item2,1,4.99]]' AS DATA UNION ALL
  SELECT 2, '[[item1,2,1.99]]'
)
SELECT
  ID id, 
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(0)] FROM UNNEST(x.y) value) AS A,
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(1)] FROM UNNEST(x.y) value) AS B,
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(2)] FROM UNNEST(x.y) value) AS C
FROM `project.dataset.my_table`,
UNNEST([STRUCT(SPLIT(SUBSTR(DATA, 3, LENGTH(DATA) - 4),'],[') AS y)]) x

with result

Row id  A       B   C    
1   1   item1   2   1.99     
        item2   1   4.99     
2   2   item1   2   1.99

Teddy · Accepted Answer · 2019-11-14 19:36:56Z

1

Assuming your nested arrays have a constant length you should be able to use the below query.

EDIT: The returned value from the UNNEST() is a string, you simply need to use SPLIT() on the string returned to change its type to be an array. You can then use OFFSEET(n) to split it into different columns.

QUERY

-- CREATES A DUMMY TABLE WITH SAMPLE DATA 
WITH TABLE AS (
  SELECT
    1 AS ID,
    '[[item1,2,1.99],[item2,1,4.99]]' AS DATA
  UNION ALL
  SELECT
    2 AS ID,
    '[[item1,2,1.99]]' AS DATA
)

SELECT
  ID id,
  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

OUTPUT

edited Nov 14, 2019 at 19:36

answered Nov 14, 2019 at 17:27

Teddy

6651 gold badge8 silver badges25 bronze badges

Collectives™ on Stack Overflow

BigQuery Arrays in Array to Proper Columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related