CODEWITHSUNDEEP
snowflake-cloud-data-platform
user2555515
user2555515

Reputation: 1033

How to tranpose snowflake table with arrays

I have a result of a query that looks like this:

|KEY |VALUE                 |
----------------------------
|C1| |{1,2,3,4,5}           |
|C2| |{ 'a','b','c','d','e'}|
|C3| |{1.0,2.0,3.0,4.0,5.0} |

How to convert it into:

|C1|C2 |C3 |
-----------
|1 |'a'|1.0|
|2 |'b'|2.0|
|3 |'c'|3.0|
|4 |'d'|4.0|
|5 |'d'|5.0|

Upvotes: 0

Views: 205

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25938

SELECT Record.Key, Record.Value 
FROM (
    SELECT parse_json('{"C1":[1,2,3,4,5],"C2":[ \'a\',\'b\',\'c\',\'d\',\'e\'],"C3":[1.0,2.0,3.0,4.0,5.0]}') as json
) , LATERAL FLATTEN (input=> json, MODE=>'OBJECT' ) as Record ;

gives:

KEY VALUE
C1  [    1,    2,    3,    4,    5  ]
C2  [    "a",    "b",    "c",    "d",    "e"  ]
C3  [    1,    2,    3,    4,    5  ]

so if you have a single object of JSON that you want the equal sized arrays of values:

SELECT 
     json:C1[r.index] as C1 
    ,json:C2[r.index] as C2 
    ,json:C3[r.index] as C3 
FROM (
    SELECT parse_json('{"C1":[1,2,3,4,5],"C2":[ \'a\',\'b\',\'c\',\'d\',\'e\'],"C3":[1.0,2.0,3.0,4.0,5.0]}') as json
) , LATERAL FLATTEN (input=> json:C1, MODE=>'ARRAY' ) as r ;

gives

C1  C2    C3
1   "a"   1
2   "b"   2
3   "c"   3
4   "d"   4
5   "e"   5

where-as if you have many batches of the above object types in an array:

SELECT 
    r.index as batch
    ,r.value:C1[q.index] as C1 
    ,r.value:C2[q.index] as C2 
    ,r.value:C3[q.index] as C3 
FROM (
    SELECT parse_json('[{"C1":[1,2,3,4,5],"C2":[ \'a\',\'b\',\'c\',\'d\',\'e\'],"C3":[1.0,2.0,3.0,4.0,5.0]},{"C1":[6,7,8,9,10],"C2":[ \'a\',\'b\',\'c\',\'d\',\'e\'],"C3":[11.0,12.0,13.0,14.0,15.0]}]') as json
) , LATERAL FLATTEN (input=> json, MODE=>'ARRAY' ) as r
  , LATERAL FLATTEN (input=> r.value:C1, MODE=>'ARRAY' ) as q;

gives:

BATCH   C1  C2  C3
0       1   "a" 1
0       2   "b" 2
0       3   "c" 3
0       4   "d" 4
0       5   "e" 5
1       6   "a" 11
1       7   "b" 12
1       8   "c" 13
1       9   "d" 14
1       10  "e" 15

Upvotes: 1

Related Questions