Convert spark dataframe to json using scala

Question

I have a dataframe in the following format

ID  currency   account name    principal   interest
123    USD     Principal       1000        100
123    EUR     Principal       2000        50
123    USD     Interest        2000        100

I would like the json output in the following format :

{ 
       "id":"123",
       "principal_type":{ 
          "USD":1000,
          "EUR":2000
       },
       "interest_type":{ 
          "USD":100
       }
    }

Since the first two rows have account type of Principal it gets added in the principal type while the third row is of type Interest hence it gets added to interest_type with key being currency and the value being Principal or Interest depending on the type

Seems like a "group by" of sorts - might be able to find other questions / examples from such. — user2864740
– user2864740, Commented Oct 29, 2019 at 5:51

Mahesh Gupta · Accepted Answer · 2019-10-29 06:27:08Z

2

you can try this way spark

scala> var dfdd = Seq((123,"USD","Principal" ,1000,100),(123,"EUR","Principal",2000,50),(123,"USD","Interest",2000,100)).toDF("ID","currency","account_name","principal","interest")

scala> dfdd.show()
+---+--------+------------+---------+--------+
| ID|currency|account_name|principal|interest|
+---+--------+------------+---------+--------+
|123|     USD|   Principal|     1000|     100|
|123|     EUR|   Principal|     2000|      50|
|123|     USD|    Interest|     2000|     100|
+---+--------+------------+---------+--------+
scala> var dfdd2 = dfdd.groupBy("ID","account_name").pivot("currency").agg(collect_list("principal"))
+---+------------+------+------+
| ID|account_name|   EUR|   USD|
+---+------------+------+------+
|123|    Interest|    []|[2000]|
|123|   Principal|[2000]|[1000]|
+---+------------+------+------+
//added .show() only for understanding purpose
scala> var dfdd3 = dfdd2.withColumn("account_type",struct($"account_name",$"EUR",$"USD")).drop("EUR","USD","account_name").groupBy("id").agg(collect_list("account_type").as("test"))


scala> dfdd3.toJSON.show(false)
+----------------------------------------------------------------------------------------------------------------------------+
|value                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------+
|{"id":123,"test":[{"account_name":"Interest","EUR":[],"USD":[2000]},{"account_name":"Principal","EUR":[2000],"USD":[1000]}]}|
+----------------------------------------------------------------------------------------------------------------------------+

equal JSON format as your desired output

have look do let me know if you have any question related to same

answered Oct 29, 2019 at 6:27

Mahesh Gupta

1,90214 silver badges17 bronze badges

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

2 Comments

amrit Over a year ago

instead of the array structure is it possible to get the structure that i mentioned above in the comment so that the key is currency and value is numerical number

Mahesh Gupta Over a year ago

ya got you questions, but I am using here collect_set so it will always give result as an Array.

Collectives™ on Stack Overflow

Convert spark dataframe to json using scala

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