How do you combine data in a Scala dataframe and output it as JSON objects?

Question

This is slightly hard to explain for me so I'll do my best. Here is the given data set:

Name	Car Brand	Car Model	Car Color	Year Bought
Tom	Toyota	Corolla	Black	2009
Tom	Hyundai	Kona	Blue	2010
Tom	Kia	Soul	Red	2011
Bob	Mazda	CX-30	Red	2008
Bob	BMW	X1	Blue	2014

With the given data set, I want to condense it based on name and just put all the cars into a list and output it out as JSON objects on separated lines in file. For the above data set, the output should look like this:

{
    "name": "Tom",
    "Cars": [{
        "CarSpecifications": {
            "Brand": "Toyota",
            "Model": "Corolla",
            "Color": "Black"
        },
        "YearBought":2009
     }, 
     {
        "CarSpecifications": {
            "Brand": "Hyundai",
            "Model": "Kona",
            "Color": "Blue"
        },
        "YearBought":2010
     },
     {
        "CarSpecifications": {
            "Brand": "Hyundai",
            "Model": "Kona",
            "Color": "Blue"
        },
        "YearBought":2011
    }]
}

{
    "name": "Bob",
    "Cars": [{
        "CarSpecifications": {
            "Brand": "Mazda",
            "Model": "CX-30",
            "Color": "Red"
        },
        "YearBought":2008
     }, 
     {
        "CarSpecifications": {
            "Brand": "BMW",
            "Model": "X1",
            "Color": "Blue"
        },
        "YearBought":2014
     }]
}

How could I accomplish these transformations using Scala and Scala Dataframes?

Test Mirror · Accepted Answer · 2021-08-04 23:41:11Z

You can aggregate the dataset using groupBy & collect_list and generate JSON strings with toJSON:

df.groupBy("Name").agg(collect_list(
    struct(
      struct(
        $"Car Brand".as("Brand"),
        $"Car Model".as("Model"),
        $"Car Color".as("Color")
      ).as("CarSpecifications"),
      $"Year Bought".as("YearBought")
    ).as("CarSpecifications")
  ).as("Cars"))
  .toJSON
  .show(false)

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|value                                                                                                                                                                                                                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|{"Name":"Tom","Cars":[{"CarSpecifications":{"Brand":"Toyota","Model":"Corolla","Color":"Black"},"YearBought":"2009"},{"CarSpecifications":{"Brand":"Hyundai","Model":"Kona","Color":"Blue"},"YearBought":"2010"},{"CarSpecifications":{"Brand":"Kia","Model":"Soul","Color":"Red"},"YearBought":"2011"}]}|
|{"Name":"Bob","Cars":[{"CarSpecifications":{"Brand":"Mazda","Model":"CX-30","Color":"Red"},"YearBought":"2008"},{"CarSpecifications":{"Brand":"BMW","Model":"X1","Color":"Blue"},"YearBought":"2014"}]}                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Collectives™ on Stack Overflow

How do you combine data in a Scala dataframe and output it as JSON objects?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related