How to split a column into two columns, provided that this column is a dictionary?

Question

SQL

there is such a table

+----+--------------------------------+-----+----+
|id  |topping                         |type |name|
+----+--------------------------------+-----+----+
|0001|{5001, None}                    |donut|Cake|
|0001|{5002, Glazed}                  |donut|Cake|
|0001|{5005, Sugar}                   |donut|Cake|
|0001|{5007, Powdered Sugar}          |donut|Cake|
|0001|{5006, Chocolate with Sprinkles}|donut|Cake|
|0001|{5003, Chocolate}               |donut|Cake|
|0001|{5004, Maple}                   |donut|Cake|
+----+--------------------------------+-----+----+

how can a topping column be split into two columns key and value using spark.sql or spark.dataframe ?

Answer 1

1. Create a new column.
2. In PHP or another server-side language, loop through all the lines and assign the correct values.