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](https://stackoverflow.com/questions/36157810/spark-row-to-json) — mazaneicha, Aug 28 '22 at 18:53

score 2 · Answer 1 · answered Aug 28 '22 at 18:14

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!

I'm curious, why wouldn't `df.write.format("json").save("output.json")` work? — Haris Nadeem, Aug 29 '22 at 03:05
Because Spark writes to JSON in a format that it can read (it can infer schema) — vilalabinot, Aug 29 '22 at 06:31

Convert SQL output to JSON using Spark

1 Answers1