Generating nested ROW struct in Presto

61 views Asked by At

I am trying to generate the following nested struct using ROW in Presto but am unable to get this work. I basically want something in the format below

{"workstream1":
{"dimension_1":
{"confidence":"2"
"value":"2"
},
"dimension_2":
{"confidence":"3",
"value":"3"
},
"dimension_3":
{"confidence":"2",
"value":"2"
},
"overall":
{"value":"4"}
}
}

Is it possible to generate this using ROW construct. If yes how.

I tried the following:

SELECT 
    CAST(ROW(
        CAST(ROW(
            CAST(ROW('2', '2') AS ROW(confidence VARCHAR, value VARCHAR))
            AS ROW(dimension_1 ROW(confidence VARCHAR, value VARCHAR))
        )),
        CAST(ROW(
            CAST(ROW('3', '3') AS ROW(confidence VARCHAR, value VARCHAR))
            AS ROW(dimension_2 ROW(confidence VARCHAR, value VARCHAR))
        )),
        CAST(ROW(
            CAST(ROW('2', '2') AS ROW(confidence VARCHAR, value VARCHAR))
            AS ROW(dimension_3 ROW(confidence VARCHAR, value VARCHAR))
        )),
        CAST(ROW(
            CAST(ROW('3') AS ROW(value VARCHAR))
            AS ROW(overall ROW(value VARCHAR))
        ))
    ) AS ROW(
        workstream1 ROW(
            dimension_1 ROW(confidence VARCHAR, value VARCHAR),
            dimension_2 ROW(confidence VARCHAR, value VARCHAR),
            dimension_3 ROW(confidence VARCHAR, value VARCHAR),
            overall ROW(value VARCHAR)
        )
    )) AS result;
0

There are 0 answers