score:2

Accepted answer

assuming your column metadata contains json strings, you can first convert it to maptype with from_json function, then add the columns you want using map_concat and finally convert again to json string using to_json:

val df2 = df.withcolumn(
    "metadata",
    from_json(col("metadata"), lit("map<string,string>"))
).withcolumn(
    "metadata",
    to_json(map_concat(col("metadata"), map(lit("adjective"), col("adjective"))))
)

df2.show(false)

//+-----+-------+---------+----------------------------------------------------+
//|noun |pronoun|adjective|metadata                                            |
//+-----+-------+---------+----------------------------------------------------+
//|homer|simpson|engineer |{"age":"50","country":"usa","adjective":"engineer"} |
//|elon |musk   |king     |{"age":"45","country":"rsa","adjective":"king"}     |
//|bart |lee    |cricketer|{"age":"35","country":"aus","adjective":"cricketer"}|
//|lisa |jobs   |daughter |{"age":"35","country":"ind","adjective":"daughter"} |
//|joe  |root   |player   |{"age":"31","country":"eng","adjective":"player"}   |
//+-----+-------+---------+----------------------------------------------------+

this also can be done using a conversion to structtype instead of maptype but map is more generic in this case.


Related Query

More Query from same tag