Convert SQL output to JSON using Spark

Question

I have a Spark SQL query (using Scala as the language) which gives output as the following table where {name, type, category} is unique. Only type has limited values (due to 5-6 unique types).

name	type	category	value
First	type1	cat1	value1
First	type1	cat2	value2
First	type1	cat3	value3
First	type2	cat1	value1
First	type2	cat5	value4
Second	type1	cat1	value5
Second	type1	cat4	value5

I'm looking for a way to convert it into a JSON with Spark such that output is something like this, basically get the output for every name & type combination.

[
  {
    "name": "First",
    "type": "type1",
    "result": {
      "cat1": "value1",
      "cat2": "value2",
      "cat3": "value3"
    }
  },
  {
    "name": "First",
    "type": "type2",
    "result": {
      "cat1": "value1",
      "cat5": "value4"
    }
  },
  {
    "name": "Second",
    "type": "type1",
    "result": {
      "cat1": "value5",
      "cat4": "value5"
    }
  }
]

Is this possible via Spark scala? Any pointers or references would be really helpful. Eventually I have to write the JSON output to S3, so if this is possible during write then it will also be okay.

Does this answer your question? Spark Row to JSON

mazaneicha
– mazaneicha

2022-08-28 18:53:43 +00:00
Commented Aug 28, 2022 at 18:53 — mazaneicha
– mazaneicha, Commented Aug 28, 2022 at 18:53

vilalabinot · Accepted Answer · 2022-08-28 18:14:57Z

2

You can groupBy, collect_set then finally map_from_entries as below:

df = df
  .groupBy("name", "type")
  .agg(collect_set(struct("category", "value")).as("result"))
  .withColumn("result", map_from_entries(col("result")))

Exporting as JSON, however, will not give you the result as you expect. To get the expected result, you can use:

df.toJSON.collect.mkString("[", "," , "]" )

Final result:

[
  {
    "name": "First",
    "type": "type1",
    "result": {
      "cat3": "value3",
      "cat1": "value1",
      "cat2": "value2"
    }
  },
  {
    "name": "First",
    "type": "type2",
    "result": {
      "cat1": "value1",
      "cat5": "value4"
    }
  },
  {
    "name": "Second",
    "type": "type1",
    "result": {
      "cat1": "value5",
      "cat4": "value5"
    }
  }
]

Good luck!

answered Aug 28, 2022 at 18:14

vilalabinot

1,6216 silver badges21 bronze badges

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

2 Comments

Haris Nadeem Over a year ago

I'm curious, why wouldn't df.write.format("json").save("output.json") work?

vilalabinot Over a year ago

Because Spark writes to JSON in a format that it can read (it can infer schema)

Collectives™ on Stack Overflow

Convert SQL output to JSON using Spark

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related