topic UNPIVOT VARIANT data in SQL in Data Engineering

UNPIVOT VARIANT data in SQL

binsel — Mon, 02 Dec 2024 06:06:26 GMT

Hi All,

Have a VARIANT column with the following data;

CREATE TABLE unpivot_valtype AS SELECT parse_json( '{ "Id": 1234567, "Result": { "BodyType": "NG", "ProdType": "Auto", "ResultSets": [ { "R1": { "AInt": 1, "CRates": { "RateA": 11, "RateB": 12, "RateC": 13 } , "EffPrice": 14, "EffRate": 15 }, "R2": { "AInt": 2, "CRates": { "RateA": 21, "RateB": 22, "RateC": 23 } , "EffPrice": 24, "EffRate": 25 }, "R3": { "AInt": 3, "CRates": { "RateA": 31, "RateB":32, "RateC": 33 } , "EffPrice": 34, "EffRate": 35 } } ] } }') as rowData

and I want to convert "ResultSets" to rows as seen below:

Not sure how I should flatten it this way.

Thanks,

Burhan

Re: UNPIVOT VARIANT data in SQL

filipniziol — Mon, 02 Dec 2024 08:45:27 GMT

Hi @binsel ,

You need to use variant_explode function.
Here is the working code:

WITH first_explode AS ( SELECT uv.rowData:Id AS Id, uv.rowData:Result:BodyType AS BodyType, uv.rowData:Result:ProdType AS ProdType, v.value AS result_set FROM unpivot_valtype AS uv, LATERAL variant_explode(uv.rowData:Result:ResultSets) AS v ), second_explode AS ( SELECT fe.Id, fe.BodyType, fe.ProdType, vv.key AS R, vv.value AS r_value FROM first_explode fe, LATERAL variant_explode(fe.result_set) AS vv ) SELECT Id, BodyType, ProdType, R, r_value:AInt AS AInt, r_value:CRates:RateA AS RateA, r_value:CRates:RateB AS RateB, r_value:CRates:RateC AS RateC, r_value:EffPrice AS EffPrice, r_value:EffRate AS EffRate FROM second_explode;

The result:

Re: UNPIVOT VARIANT data in SQL

binsel — Mon, 02 Dec 2024 17:56:34 GMT

Thanks, @filipniziol

I realized CRates are arrays. I guess I have to use another function and add one more layer, right?

CREATE TABLE unpivot_valtype AS SELECT parse_json( '{ "Id": 1234567, "Result": { "BodyType": "NG", "ProdType": "Auto", "ResultSets": [ { "R1": { "AInt": 1, "CRates": [ { "RateA": 11, "RateB": 12, "RateC": 13 }, { "RateA": 111, "RateB": 112, "RateC": 113 }] , "EffPrice": 14, "EffRate": 15 }, "R2": { "AInt": 2, "CRates": [ { "RateA": 21, "RateB": 22, "RateC": 23 }, { "RateA": 221, "RateB": 222, "RateC": 223 }] , "EffPrice": 24, "EffRate": 25 }, "R3": { "AInt": 3, "CRates": [ { "RateA": 31, "RateB": 32, "RateC": 33 }, { "RateA": 331, "RateB": 332, "RateC": 333 }] , "EffPrice": 34, "EffRate": 35 } } ] } }') as rowData

Re: UNPIVOT VARIANT data in SQL

filipniziol — Tue, 03 Dec 2024 07:40:39 GMT

Hi @binsel ,

Yes, you need to unpack the CRates array as well

WITH first_explode AS ( SELECT uv.rowData:Id AS Id, uv.rowData:Result:BodyType AS BodyType, uv.rowData:Result:ProdType AS ProdType, v.value AS result_set FROM unpivot_valtype AS uv, LATERAL variant_explode(uv.rowData:Result:ResultSets) AS v ), second_explode AS ( SELECT fe.Id, fe.BodyType, fe.ProdType, vv.key AS R, vv.value AS r_value FROM first_explode fe, LATERAL variant_explode(fe.result_set) AS vv ), third_explode AS ( SELECT se.Id, se.BodyType, se.ProdType, se.R, se.r_value:AInt AS AInt, se.r_value:EffPrice AS EffPrice, se.r_value:EffRate AS EffRate, c.value AS c_rate FROM second_explode se, LATERAL variant_explode(se.r_value:CRates) AS c ) SELECT Id, BodyType, ProdType, R, AInt, c_rate:RateA AS RateA, c_rate:RateB AS RateB, c_rate:RateC AS RateC, EffPrice, EffRate FROM third_explode;