Writing JSON array of strings with a blob element in Spark Scala

Question

I have a data frame with a schema that looks like this:

root
 |-- Id: integer (nullable = true)
 |-- FirstName: string (nullable = true)
 |-- LastName: string (nullable = true)
 |-- Age: integer (nullable = true)

I want to produce output like this (below) where each row is a JSON array

[1, "Smith", {"F": "John", "L":"Smith"}]
[2, "Doe", {"F": "Jane", "L":"Doe"}]

I tried it like this:

val df2 = df
  .withColumn("MyStruct", to_json(struct(col("FirstName").as("F"), col("LastName").as("L"))))
  .withColumn("Value", array($"Id",$"Last",$"MyStruct"))
  .select(to_json($"Value").as("output"))

df2.write
  .mode("overwrite")
  .option("header", false)
  .text("./df3")

This produces valid JSON, but the JSON blob/structure in each row is an encoded value. :(

 ["1","Smith","{\"F\":\"John\",\"L\":\"Smith\"}"]
 ["2","Doe","{\"F\":\"Jane\",\"L\":\"Doe\"}"]

Here's what I'm certain I am doing wrong: I am creating a struct wrapped with to_json, but I call to_json on this again (a second time), which makes it an an escaped JSON string.

When I call df2.write, I'm purposely writing text and not json. When I use JSON, then the ID and Last Name values, in what's meant to be a JSON array, are printed out as key/value pairs inside a JSON blob, which isn't what I want.

So then I tried to avoid the second parse_json call.

I swapped

val df2 = df
  .withColumn("MyStruct", to_json(struct(col("FirstName").as("F"), col("LastName").as("L"))))
  .withColumn("Value", array($"Id",$"Last",$"MyStruct"))
  .select(to_json($"Value").as("output"))

with

val df2 = df
  .withColumn("MyStruct", to_json(struct(col("FirstName").as("F"), col("LastName").as("L"))))
  .withColumn("Value", array($"Id",$"Last",$"MyStruct"))
  .select($"Value".cast("string").as("output"))

This looks a bit better:

[1, Smith, {"F":"John","L":"Smith"}]
[2, Doe, {"F":"Jane","L":"Doe"}]

The JSON blob is no longer escaped, but these records are no longer valid JSON. I could put quotation marks manually around the last name strings, but surely there's a cleaner, more idiomatic solution.

user_8593414 · Answer

In this case adding quotes is the only option, because Smith and Doe are actually a String data type & all String should be enclosed with quotes in Json.

Use concat function to quotes around Smith & Doe values. Check below code.

scala> df.show(false)
+---+---------+--------+---+
|id |firstname|lastname|age|
+---+---------+--------+---+
|1  |John     |Smith   |20 |
|2  |Jane     |Doe     |30 |
+---+---------+--------+---+

scala> :paste
// Entering paste mode (ctrl-D to finish)

df
.withColumn("data",struct(
    $"id",
    concat(lit("\""),$"firstname",lit("\"")), // Add quotes aournd firstname, to make valid json.
    to_json(struct($"firstname".as("F"),$"lastname".as("L")))).cast("string")
)
.select("data")
.repartition(1)
.write
.format("text")
.mode("overwrite")
.save("/tmp/data")

// Exiting paste mode, now interpreting.

scala> import sys.process._
import sys.process._

scala> "ls -ltr /tmp/data".!
-rw-r--r-- 1 root root 74 Oct 18 01:52 part-00000-cf540e79-d32e-413f-9d5d-1a08b6eb8d21-c000.txt
-rw-r--r-- 1 root root  0 Oct 18 01:52 _SUCCESS

scala> "cat /tmp/data/part-00000-cf540e79-d32e-413f-9d5d-1a08b6eb8d21-c000.txt".!
[1, "John", {"F":"John","L":"Smith"}]
[2, "Jane", {"F":"Jane","L":"Doe"}]

Writing JSON array of strings with a blob element in Spark Scala

1 Answers