Reduce a json string column into a key/val column

Question

i have a dataframe with the following structure :

| a        | b    |           c                                             |
-----------------------------------------------------------------------------
|01        |ABC   |    {"key1":"valueA","key2":"valueC"}                    |
|02        |ABC   |    {"key1":"valueA","key2":"valueC"}                    |
|11        |DEF   |    {"key1":"valueB","key2":"valueD", "key3":"valueE"}   |
|12        |DEF   |    {"key1":"valueB","key2":"valueD", "key3":"valueE"}   |

i would like to turn into something like :

| a        | b    |      key         |       value     |
--------------------------------------------------------
|01        |ABC   |    key1          |     valueA      |
|01        |ABC   |    key2          |     valueC      |
|02        |ABC   |    key1          |     valueA      |
|02        |ABC   |    key2          |     valueC      |
|11        |DEF   |    key1          |     valueB      |
|11        |DEF   |    key2          |     valueD      |
|11        |DEF   |    key3          |     valueE      |
|12        |DEF   |    key1          |     valueB      |
|12        |DEF   |    key2          |     valueD      |
|12        |DEF   |    key3          |     valueE      |

in an efficient way, as the dataset can be quite large.

Note, key1...keyXX are consistent only for a given col(b) value — Flow
– Flow, Commented Jul 27, 2020 at 13:26

notNull · Accepted Answer · 2020-07-27 13:55:02Z

3

Try using from_json function then explode the array.

Example:

import org.apache.spark.sql.types._
import org.apache.spark.sql.functions._
val df=Seq(("01","ABC","""{"key1":"valueA","key2":"valueC"}""")).toDF("a","b","c")
val Schema = MapType(StringType, StringType)
df.withColumn("d",from_json(col("c"),Schema)).selectExpr("a","b","explode(d)").show(10,false)
//+---+---+----+------+
//|a  |b  |key |value |
//+---+---+----+------+
//|01 |ABC|key1|valueA|
//|01 |ABC|key2|valueC|
//+---+---+----+------+

answered Jul 27, 2020 at 13:55

notNull

31.8k4 gold badges41 silver badges58 bronze badges

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

1 Comment

Flow Over a year ago

Brilliant... much appreciate. i was starting to look building an UDF to parse the json string into a map. sounds from_json is much more powerfull.

Collectives™ on Stack Overflow

Reduce a json string column into a key/val column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related