Explode JSON key Value to new Rows

user_661900 0
Viewed 489

I'm looking to convert a nested JSON and explode it to new rows.

The current data:

+-----------+-----------+-----------------------------+
|         id|      site |                         sale|
+-----------------------------------------------------+
|        abc|          6|{"2019-05-08T00:00:00": null, "2019-05-09T00:00:00": [{"key1": 2, "key2": 0}]}         |
|        def|          5|{"2019-05-08T00:00:00": [{"key1": 22, "key2": 10}], "2019-05-09T00:00:00": null}       |
|        ghi|          4|{"2019-05-08T00:00:00": null, "2019-05-09T00:00:00": null}         |

Desired result

+-----------+-----------+------------------------+-----------+-----------+
|         id|      site |                saledate|       key1|       key2|
+-------------------------------------------------------------------------
|        abc|          6|     2019-05-08T00:00:00|       null|       null|
|        abc|          6|     2019-05-09T00:00:00|          2|          0|
|        def|          5|     2019-05-08T00:00:00|         22|         10|
|        def|          5|     2019-05-09T00:00:00|       null|       null|
|        ghi|          4|     2019-05-08T00:00:00|       null|       null|
|        ghi|          4|     2019-05-09T00:00:00|       null|       null|

What I have tried:

  • explode works only for Array type.
new_df = old_df.withColumn('saledate', explode('sale'))
  • I'm able to get properties of each date by doing the following. However, I would have to specify each date separately, which is not ideally.
new_df = old_df
  .withColumn('sale_collection', explode('sale.2019-05-08T00:00:00'))
  .withColumn('key1', col('sale_collection').getItem('key1')
  .withColumn('key2', col('sale_collection').getItem('key2')
pyspark apache-spark-sql apache-spark dataframe python
1 Answers

You can use from_json to convert the string into a Map and then explode:

from pyspark.sql.functions import from_json

df.withColumn('sale', from_json('sale', 'map<string,array<struct<key1:int,key2:int>>>')) \
  .selectExpr('*', 'explode_outer(sale) as (saledate, keys)') \
  .selectExpr('id', 'site', 'saledate', 'inline_outer(keys)') \
  .show()
+---+----+-------------------+----+----+
| id|site|           saledate|key1|key2|
+---+----+-------------------+----+----+
|abc|   6|2019-05-08T00:00:00|null|null|
|abc|   6|2019-05-09T00:00:00|   2|   0|
|def|   5|2019-05-08T00:00:00|  22|  10|
|def|   5|2019-05-09T00:00:00|null|null|
|ghi|   4|2019-05-08T00:00:00|null|null|
|ghi|   4|2019-05-09T00:00:00|null|null|
+---+----+-------------------+----+----+

Note: inline_outer and inline are SparkSQL builtin functions to explode an array of structs.

user_95107290
Related