Spark Unnesting Nested Schema from DynamoDB Json

user_2843331 0
Viewed 257

I am dealing with with DynamoDB JSON that is similar to this:

{ 
  "name" : {"S" : "John"},
  "birthday": {
    "M" : {
       "month" : {"N": 1},
       "year" : {"N": 2000},
       "day" : {"N": 2} 
    }
  }
}

When I read this in spark

val df = spark.read.json("s3://path")

I get a complicated schema:

name : structType ( S : String),
birthday: structType (
  M : StructType (
    month : structType (N : int),
    year : structType (N : int),
    day : structType (N : int),
  )
)

Instead, I want to change the schema to something like

name : String
birthday : structType (
  month : int
  year : int
  day : int
)

Is there a way to do this?

My schema is in fact much larger than this example, with many deeply nested structs. I wonder also if there is dynamic way of "normalizing" the schema.

apache-spark-sql apache-spark
2 Answers
.selectExpr("name", "birthday.M as birthday")

Or you could even flatten this totally up to the root level

.selectExpr("name", "birthday.M.*")
user_4289440

I was able to use named_struct function:

df.selectExpr("""
named_struct (
  'name', name.S,
  'birthday', named_struct(
    'month', birthday.M.month.N as decimal,
    'year', birthday.M.year.N as decimal,
    'day', birthday.M.day.N as decimal,
  )
) as items
""")

This worked out well for me.

user_28433310
Related