Scala - How to Convert String Column to Array of Json

user_13162034 0
Viewed 1164

Using Below DataFrame I am getting a Array of Json but Data Type is String, where I am looking for help to convert this String to Array of JSON.

val rawDF = spark.sql("select 1").withColumn("parent_id", lit("Parent_12345")).withColumn("jsonString", lit("""[{"First":{"Info":"ABCD123","Res":"5.2"}},{"Second":{"Info":"ABCD123","Res":"5.2"}},{"Third":{"Info":"ABCD123","Res":"5.2"}}]"""))
        rawDF.show(false)

Input and Output DataFrame:

Input DataFrame :

+----------+-------+-----------------------------------------------------------------------------------------------------------------------------------+
|item_id   |s_tag  |jsonString                                                                                                                         |
+----------+-------+-----------------------------------------------------------------------------------------------------------------------------------+
|Item_12345|S_12345|[{"First":{"Info":"ABCD123","Res":"5.2"}},{"Second":{"Info":"ABCD123","Res":"5.2"}},{"Third":{"Info":"ABCD123","Res":"5.2"}}]      |
+----------+-------+-----------------------------------------------------------------------------------------------------------------------------------+


Output DataFrame :
+----------+-------+-----------------------------------------+
|item_id   |s_tag  |jsonString                               |
+----------+-------+-----------------------------------------+
|Item_12345|S_12345|{"First":{"Info":"ABCD123","Res":"5.2"}} |
+----------+-------+-----------------------------------------+
|Item_12345|S_12345|{"Second":{"Info":"ABCD123","Res":"5.2"}}|
+----------+-------+-----------------------------------------+
|Item_12345|S_12345|{"Third":{"Info":"ABCD123","Res":"5.2"}} |
+----------+-------+-----------------------------------------+

Problem Statement :

jsonString is String data but looks like Array of json, I want to cast/convert this column as Array of Json to split into possible number of rows as output DataFrame.

What I have tried so far :

val jsonArray = udf((value: String) => new JSONArray(value)) // Or how to convert as Array of json.

val strToJsonArray = rawDF.withColumn("arrJson", jsonArray(rawDF("jsonString"))).drop("jsonString") //This is not working.

//If We can convert To Array then using below code I can Split the Json Column in expected Output.
val splittedDF = strToJsonArray.withColumn("splittedJson", explode(strToJsonArray.col("arrJson"))).drop("arrJson")

How do I covert my String to an Array of JSON values?

apache-spark explode scala arrays json
1 Answers

No need to have UDF for this case, we can use spark inbuilt functions split,regexp_replace,explode for this case.

Example:

//sample data
val rawDF = spark.sql("""select string("Item_12345") as item_id""").withColumn("s_tag", lit("S_12345")).withColumn("jsonString", lit("""[{"First":{"Info":"ABCD123","Res":"5.2"}},{"Second":{"Info":"ABCD123","Res":"5.2"}},{"Third":{"Info":"ABCD123","Res":"5.2"}}]"""))

//to make valid array we first replace (},) with (}},) then remove ("[|]") and split on (},) it results array finally we explode on the array. 
rawDF.
selectExpr("item_id","s_tag","""explode(split(regexp_replace(regexp_replace(jsonString,'(\\\},)','}},'),'(\\\[|\\\])',''),"},")) as jsonString""").
show(false)

//+----------+-------+-----------------------------------------+
//|item_id   |s_tag  |jsonString                               |
//+----------+-------+-----------------------------------------+
//|Item_12345|S_12345|{"First":{"Info":"ABCD123","Res":"5.2"}} |
//|Item_12345|S_12345|{"Second":{"Info":"ABCD123","Res":"5.2"}}|
//|Item_12345|S_12345|{"Third":{"Info":"ABCD123","Res":"5.2"}} |
//+----------+-------+-----------------------------------------+
user_76326950
Related