title: How can get a flatten dataframe from a nested json containing arrays
body: <p>I just started to code Spark with Scala, I'm trying to flatten a json as dataframe in order to writh in hadoop, I had some problem dealing with nested json cointaining arrays. Anybody as some suggestion in order to flat a json in a dataframe?</p>
  
  <p>this is my schema:</p>
  
  <pre><code>root
   |-- Destination: struct (nullable = true)
   |    |-- DestinationId: string (nullable = true)
   |    |-- Type: string (nullable = true)
   |-- Header: struct (nullable = true)
   |    |-- MessageTime: string (nullable = true)
   |    |-- MessageVersion: string (nullable = true)
   |-- Payload: struct (nullable = true)
   |    |-- Sensors: array (nullable = true)
   |    |    |-- element: struct (containsNull = true)
   |    |    |    |-- Location: struct (nullable = true)
   |    |    |    |    |-- Lat: string (nullable = true)
   |    |    |    |    |-- Lng: string (nullable = true)
   |    |    |    |-- Measures: array (nullable = true)
   |    |    |    |    |-- element: struct (containsNull = true)
   |    |    |    |    |    |-- PropertyId: string (nullable = true)
   |    |    |    |    |    |-- Time: string (nullable = true)
   |    |    |    |    |    |-- Value: string (nullable = true)
   |    |    |    |-- SensorId: string (nullable = true)
   |    |-- TransactionId: string (nullable = true)
   |    |-- Type: string (nullable = true)
   |-- Source: struct (nullable = true)
   |    |-- Location: struct (nullable = true)
   |    |    |-- Lat: string (nullable = true)
   |    |    |-- Lng: string (nullable = true)
   |    |-- SourceId: string (nullable = true)
   |    |-- Type: string (nullable = true)
  </code></pre>
  
  <p>I tried the flattan function provided as a solution of several question </p>
  
  <p>This is what i did:</p>
  
  <pre><code>val df2 = spark.read.json("message.json").toDF()
  
  val dfPayload = df2.select(df2("Payload")).select("Payload.Type","Payload.TransactionId", "Payload.Sensors" )
  val dfSensors = dfPayload.select(explode(dfPayload("Sensors"))).toDF("Sensors").select("Sensors.SensorId", "Sensors.Location.Lat", "Sensors.Location.Lng", "Sensors.Measures")
        //output
        dfSensors.show()
  Sensors
  +--------+----------+----------+--------------------+
  |SensorId|       Lat|       Lng|            Measures|
  +--------+----------+----------+--------------------+
  |  S02001|43.5356278|10.2977402|[[DISPL, 2019-07-...|
  |  S02002|43.1237824|10.0984567|[[DISPL, 2019-06-...|
  |  S02003|43.0598432|10.9152361|[[TEMP, 2019-07-1...|
  +--------+----------+----------+--------------------+
  
        val dfMeasures = dfSensors.select(explode(dfSensors("Measures"))).toDF("Measures").select("Measures.Time", "Measures.PropertyId", "Measures.Value")
        //output
        dfMeasures.show()
  Measures
  +--------------------+----------+-----+
  |                Time|PropertyId|Value|
  +--------------------+----------+-----+
  |2019-07-17T10:43:...|     DISPL|00.87|
  |2019-07-17T10:43:...|      ACCX|00.02|
  |2019-07-17T10:43:...|      ACCY|00.09|
  |2019-07-17T10:43:...|      ACCZ|00.03|
  |2019-07-17T10:43:...|     DISPL|00.92|
  |2019-07-17T10:43:...|      ACCX|00.69|
  |2019-07-17T10:43:...|      ACCY|00.21|
  |2019-06-26T08:25:...|      ACCZ|00.95|
  |2019-06-26T08:16:...|     DISPL|00.71|
  |2019-07-17T10:43:...|     DISPL|10.43|
  |2019-07-17T10:43:...|     DISPL|10.36|
  |2019-07-17T10:43:...|      ACCX|10.22|
  |2019-07-17T10:43:...|      ACCY|00.69|
  |2019-07-17T10:43:...|      ACCZ|00.81|
  |2019-07-17T10:43:...|      ACCX|00.22|
  |2019-07-17T10:43:...|      ACCY|00.49|
  |2019-07-17T10:43:...|      ACCZ|00.74|
  |2019-07-17T10:43:...|      ACCX|00.23|
  |2019-07-17T10:43:...|      ACCY|00.95|
  |2019-07-17T10:43:...|      ACCZ|00.90|
  +--------------------+----------+-----+
  </code></pre>
  
  <p>I can explode the array but i loss the column of the father</p>
  
  <p>I need a flatten dataframe containing exploded array as record and father columns, like this</p>
  
  <pre><code>+--------+----------+----------+--------------------+----------+-----+
  |SensorId|       Lat|       Lng|                Time|PropertyId|Value|
  +--------+----------+----------+--------------------+----------+-----+
  |  S02001|43.5356278|10.2977402|2019-07-17T10:43:...|     DISPL|00.87|
  |  S02001|43.5356278|10.2977402|2019-07-17T10:43:...|      ACCX|00.02|
  |  S02001|43.5356278|10.2977402|2019-07-17T10:43:...|      ACCY|00.09|
  |  S02001|43.5356278|10.2977402|2019-07-17T10:43:...|      ACCZ|00.03|
  |  S02002|43.1237824|10.0984567|2019-06-26T08:25:...|      ACCZ|00.95|
  |  S02003|43.0598432|10.9152361|2019-06-26T08:16:...|     DISPL|00.71|
  +--------+----------+----------+--------------------+----------+-----+
  </code></pre>
  
  <p>Anybody as encountered the same problem;</p>