Nested Json extract the value with unknown key in the middle

user_10167321 0
Viewed 177

I have a Json column(colJson) in a dataframe like this

{
    "a": "value1",
    "b": "value1",
    "c": true,
    "details": {
        "qgiejfkfk123": { //unknown value
            "model1": {
                "score": 0.531,
                "version": "v1"
            },
            "model2": {
                "score": 0.840,
                "version": "v2"
            },
            "other_details": {
                "decision": false,
                "version": "v1"

            }
        }
    }
}

Here 'qgiejfkfk123' is dynamic value and changes with each row. However I need to extract model1.score as well as model2.score.

I tried

sourceDf.withColumn("model1_score",get_json_object(col("colJson"), "$.details.*.model1.score").cast(DoubleType))
.withColumn("model2_score",get_json_object(col("colJson"), "$.details.*.model2.score").cast(DoubleType))

but did not work.

apache-spark-sql apache-spark scala-collections scala json
1 Answers

I managed to get your solution by using from_json, parsing the dynamic value as Map<String, Struct> and exploding the values from it:

val schema = "STRUCT<`details`: MAP<STRING, STRUCT<`model1`: STRUCT<`score`: DOUBLE, `version`: STRING>, `model2`: STRUCT<`score`: DOUBLE, `version`: STRING>, `other_details`: STRUCT<`decision`: BOOLEAN, `version`: STRING>>>>"

val fromJsonDf = sourceDf.withColumn("colJson", from_json(col("colJson"), lit(schema)))
val explodeDf = fromJsonDf.select($"*", explode(col("colJson.details")))
// +----------------------------------------------------------+------------+--------------------------------------+
// |colJson                                                   |key         |value                                 |
// +----------------------------------------------------------+------------+--------------------------------------+
// |{{qgiejfkfk123 -> {{0.531, v1}, {0.84, v2}, {false, v1}}}}|qgiejfkfk123|{{0.531, v1}, {0.84, v2}, {false, v1}}|
// +----------------------------------------------------------+------------+--------------------------------------+

val finalDf = explodeDf.select(col("value.model1.score").as("model1_score"), col("value.model2.score").as("model2_score"))
// +------------+------------+
// |model1_score|model2_score|
// +------------+------------+
// |       0.531|        0.84|
// +------------+------------+
user_60802760
Related