Append values from one column to another JSON column in the same dataframe

Question

I have some data in dataframe which looks like this:

+-----------+--------+-----------+--------------------------------+
|       Noun| Pronoun|  Adjective|Metadata                        |
+-----------+--------+-----------+--------------------------------+
|      Homer| Simpson|Engineer   |{"Age": "50", "Country": "USA"} | 
|      Elon | Musk   |King       |{"Age": "45", "Country": "RSA"} |
|      Bart | Lee    |Cricketer  |{"Age": "35", "Country": "AUS"} |
|      Lisa | Jobs   |Daughter   |{"Age": "35", "Country": "IND"} |
|      Joe  | Root   |Player     |{"Age": "31", "Country": "ENG"} |
+-----------+--------+-----------+--------------------------------+

I want to append values from another column(say Adjective) into the Metadata column. So that the final dataframe would look like this:

+-----------+--------+-----------+------------------------------------------------------------+
|       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"}      |
+-----------+--------+-----------+------------------------------------------------------------+

Please suggest how this can be implemented.

What is the data type of column Metadata?

blackbishop
– blackbishop

2022-01-03 09:22:52 +00:00
Commented Jan 3, 2022 at 9:22 — blackbishop
– blackbishop, Commented Jan 3, 2022 at 9:22
it is a json object.

UserASR
– UserASR

2022-01-03 09:24:15 +00:00
Commented Jan 3, 2022 at 9:24 — UserASR
– UserASR, Commented Jan 3, 2022 at 9:24
yes, String in dataframe.

UserASR
– UserASR

2022-01-03 09:35:10 +00:00
Commented Jan 3, 2022 at 9:35 — UserASR
– UserASR, Commented Jan 3, 2022 at 9:35

blackbishop · Accepted Answer · 2022-01-03 09:39:15Z

3

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.

edited Jan 3, 2022 at 9:39

answered Jan 3, 2022 at 9:33

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

UserASR Over a year ago

This answer worked but what if i want to add another json object within the Metadata column. so that the final value in Metadata will look like {"Age":"50","Country":"USA","Adjective":"Engineer", "Tags": {"t1": "v1", "t2": "v2" }}

Meeran0823 Over a year ago

@UserASR hey were you able to achieve this, I have very similar requirement. Could you please help me

Collectives™ on Stack Overflow

Append values from one column to another JSON column in the same dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related