title: After reading Multi-line JSON file in Apache Spark, how to get nested attributes as columns?
body: <p>I use Spark 2.3.2 and read a multiline JSON file. This is the output of <code>df.printSchema()</code>:</p>
  
  <pre><code>root
   |-- data: struct (nullable = true)
   |    |-- items: array (nullable = true)
   |    |    |-- element: struct (containsNull = true)
   |    |    |    |-- context: struct (nullable = true)
   |    |    |    |    |-- environment: struct (nullable = true)
   |    |    |    |    |    |-- tag: struct (nullable = true)
   |    |    |    |    |    |    |-- weather: string (nullable = true)
   |    |    |    |    |    |-- weather: struct (nullable = true)
   |    |    |    |    |    |    |-- clouds: double (nullable = true)
   |    |    |    |    |    |    |-- rain: long (nullable = true)
   |    |    |    |    |    |    |-- temp: long (nullable = true)
   |    |    |    |    |-- personal: struct (nullable = true)
   |    |    |    |    |    |-- activity: struct (nullable = true)
   |    |    |    |    |    |    |-- conditions: array (nullable = true)
   |    |    |    |    |    |    |    |-- element: string (containsNull = true)
   |    |    |    |    |    |    |-- kind: string (nullable = true)
   |    |    |    |    |    |-- status: struct (nullable = true)
   |    |    |    |    |    |    |-- speed: double (nullable = true)
   |    |    |    |    |-- timespace: struct (nullable = true)
   |    |    |    |    |    |-- geo: struct (nullable = true)
   |    |    |    |    |    |    |-- coordinates: array (nullable = true)
   |    |    |    |    |    |    |    |-- element: double (containsNull = true)
   |    |    |    |    |    |    |-- type: string (nullable = true)
   |    |    |    |    |    |-- tag: struct (nullable = true)
   |    |    |    |    |    |    |-- season: string (nullable = true)
   |    |    |    |    |    |-- timestamp: string (nullable = true)
   |    |    |    |-- passport: struct (nullable = true)
   |    |    |    |    |-- pid: string (nullable = true)
   |    |    |    |    |-- uid: string (nullable = true)
  </code></pre>
  
  <p>It can be seen that the JSON file has a nested structure and it's not so trivial to retrieve particular nested features, for example, season, speed, etc.</p>
  
  <p>This is how I read data:</p>
  
  <pre><code>SparkSession spark = SparkSession.builder()
                                   .config("spark.rdd.compress", "true")
                                   .appName("Test")
                                   .master("local[*]")
                                   .getOrCreate();
  df = spark
      .read()
      .option("multiLine", true).option("mode", "PERMISSIVE")
      .json(filePath);
  </code></pre>
  
  <p>How can I get <code>timestamp</code> and <code>weather</code> tag in a separate Dataset?</p>
  
  <pre><code>timestamp  weather
  ...        ...
  ...        ...
  </code></pre>
  
  <p>I tried this, but it did not worked:</p>
  
  <pre><code>df.registerTempTable("df");
  Dataset result = spark.sql("SELECT data.items.element.passport.uid FROM df");
  </code></pre>
  
  <p>or</p>
  
  <pre><code>Dataset result = df.withColumn("items",
                  org.apache.spark.sql.functions.explode(df.col("data.items")))
                  .select(df.col("items.context.environment.weather"));
  </code></pre>