UNPIVOT VARIANT data in SQL

[binsel]

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

[filipniziol · Accepted solution]

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:

[binsel]

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

[filipniziol · Accepted solution]

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;