Scala spark aggregate a set of columns in a data frame to a JSON string

Question

Given a data frame,

+-----------------------------+
| id|  name| payable| strategy|
+-----------------------------+
|  0|   Joe|     100|     st-1|
|  1|   Tom|     200|     st-2|
|  2|  John|     300|     st-1|
+-----------------------------+

What would be the most efficient way to convert each row to a JSON string such as follows,

{
  "payload": {
     "name": "Joe",
     "payments": [
         {
            "strategy": "st-1",
            "payable": 100
         }
     ]
  }
}

Currently I have UDF to manually stringify the provided columns, but I'm wondering whether there is a better way to achieve this. The to_json method is the best alternative I found so far but that takes only one column as an input.

Do you also want to aggregate/group by above data using id, name because payments is an array. — hagarwal
– hagarwal, Commented Feb 20, 2020 at 9:15
You can use single select with struct for generating the desired complex type — abiratsis
– abiratsis, Commented Feb 20, 2020 at 10:11

vdep · Accepted Answer · 2020-02-20 10:09:21Z

3

Using to_json() is the correct approach, but the contents need to be passed as struct as appropriate:

val df = Seq((0,"Joe",100,"st-1"), (1,"Tom",200,"st-2")).toDF("id","name","payable","strategy")

val result = df.select(
  to_json(struct(
    struct($"name",
      array(struct($"strategy",$"payable")) as "payments"
    ) as "payload")
  ) as "jsonValue"
 )

result.show(false)
+-------------------------------------------------------------------------+
|jsonValue                                                                |
+-------------------------------------------------------------------------+
|{"payload":{"name":"Joe","payments":[{"strategy":"st-1","payable":100}]}}|
|{"payload":{"name":"Tom","payments":[{"strategy":"st-2","payable":200}]}}|
+-------------------------------------------------------------------------+

edited Feb 20, 2020 at 10:09

answered Feb 20, 2020 at 9:59

vdep

3,6005 gold badges30 silver badges56 bronze badges

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

1 Comment

Rukshan Hassim Over a year ago

Tried it out and works perfectly. I didn't think of using the struct function that way.

Collectives™ on Stack Overflow

Scala spark aggregate a set of columns in a data frame to a JSON string

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