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:

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;