append values from one column to another json column in the same dataframe

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(
    from_json(col("Metadata"), lit("map<string,string>"))
    to_json(map_concat(col("Metadata"), map(lit("Adjective"), col("Adjective"))))

//|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.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top