How to format json string as MongoDB document style using spark/scala?

Question

I have a table with two columns id, json_string and need to convert json_string into a MongoDB document format. I'm sending data from Spark/Scala to MongoDB.

I tried using withColumn but I still don't get the desired format. This is what I have so far, so any help would be really appreciated.

Original json string sample (df)

val df=spark.sql("select id, json_string from mytable")

{"id":"0001","json_string":"{\"header\": {\"column1\":\"value1\",\"column2\":\"value2\"},\"tail\": [{\"column3\":\"value3\",\"column4\":\"value4\",\"column5\":\"value5\"}]}"}

Using withColumn (df2) I get this:

val df2=df.withColumn("json_string",from_json(col("json_string"),MapType(StringType,StringType)))

{"id":"0001","json_string":{"header":"{\"column1\":\"value1\",\"column2\":\"value2\"}","tail":"[{\"column3\":\"value3\",\"column4\":\"value4\",\"column5\":\"value5\"}]"}}

Desired format:

{"id":{"$id":"0001"},"header":{"column1":"value1","column2":"value2"},"tail":[{"column3":"value3","column4":"value4","column5":"value5"}]}

Desired format picture sample

koiralo · Accepted Answer · 2021-04-15 06:32:40Z

0

Instead of defining the schema manually, you can get it dynamically and use it with from_json

val json_schema = spark.read.json(df.select("json_string").as[String]).schema
val df2 = df.withColumn("json_string", from_json(col("json_string"), json_schema))
  .select("id", "json_string.*")

Result:

+----+----------------+--------------------------+
|id  |header          |tail                      |
+----+----------------+--------------------------+
|0001|{value1, value2}|[{value3, value4, value5}]|
+----+----------------+--------------------------+

Schema:

root
 |-- id: string (nullable = true)
 |-- header: struct (nullable = true)
 |    |-- column1: string (nullable = true)
 |    |-- column2: string (nullable = true)
 |-- tail: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- column3: string (nullable = true)
 |    |    |-- column4: string (nullable = true)
 |    |    |-- column5: string (nullable = true)

answered Apr 15, 2021 at 6:32

koiralo

23.2k6 gold badges57 silver badges77 bronze badges

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

1 Comment

haa Over a year ago

Awesome! It worked! I was struggling with this, trying many approaches without success. Thank you, greatly appreciate it.

Collectives™ on Stack Overflow

How to format json string as MongoDB document style using spark/scala?

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