Convert Nested Array into Columns in Snowflake-CodePudding

I have a table column with nested arrays in a Snowflake database. I want to convert the nested array into columns in the manner shown below in Snowflake SQL.

Table Name: SENSOR_DATA

The RX column is of data type VARIANT. The nested arrays will not always be 3 as shown below. There are cases where there are 20,000 nested arrays, and other cases where there are none.

| ID |             RX              |
|----|-----------------------------|
| 1  |[[0, 15], [1, 50], [2, 34.2]]|
| 2  |[[0, 20], [1, 25]]           |
| 3  |[[0, 100], [1, 42], [2, 76]] |

I want to achieve something like this from the table above:

| ID |Col0 | Col1| Col2|
|----|-----|-----|-----|
| 1  |  15 |  50 | 34.2|
| 2  |  20 |  25 | NULL|
| 3  | 100 |  42 | 76  |

CodePudding user response：

Using [] to access array elements:

SELECT ID, RX[0][1] AS col1,  RX[1][1] AS col1, RX[2][1] AS col2
FROM SENSOR_DATA;

CodePudding user response：

Not exactly what you asked for but this is close

with sensor_data as (
  select column1 id, parse_json(column2) rx
  from values (1, '[[0, 15], [1, 50], [2, 34.2]]')
             ,(2, '[[0, 20], [1, 25]]')
             ,(3, '[[0, 100], [1, 42], [2, 76]]')
       as vals
),
flat as (
select id, val.value[1] arrvalue
  from sensor_data,
  lateral flatten(input => sensor_data.rx, outer => true) val
 )
select
     id
    ,listagg(arrvalue, ',') rx_list
from flat
group by id
order by id
  ;

ID  RX_LIST
1   15,50,34.2
2   20,25
3   100,42,76