title: Spark unable to Explode column
body: <p>For the given JSON Response:</p>
  
  <pre><code>{
      "id": "1575972348068_1649088229",
      "results": [
          {
              "rows_count": 53,
              "runtime_seconds": 0.004000000189989805,
              "columns": [
                  "ROLE_ID",
                  "ROLE_NAME"
              ],
              "columns_type": [
                  "number",
                  "string"
              ],
              "limit": 2000000000,
              "index": 0,
              "rows": [
                  [
                      "6",
                      "Incentive Plan Advisor                                                                              "
                  ],
                  [
                      "7",
                      "Security Admin                                                                                      "
                  ]
              ],
              "command": "&lt;an sql command&gt;"
          }
      ],
      "status": "completed"
  }
  </code></pre>
  
  <p>I want to get the <code>rows</code> in this JSON as a Spark Dataframe. For this, I am trying to <code>explode</code> the <code>results</code> entry using:</p>
  
  <pre><code>response.show()
  val flattened = response.select($"results", explode($"results").as("results_flat1")).select($"results_flat1")
          flattened.show()
  </code></pre>
  
  <p>I get this response:</p>
  
  <pre><code>+--------------------+--------------------+---------+
  |                  id|             results|   status|
  +--------------------+--------------------+---------+
  |1575972687102_374...|[[[ROLE_ID, ROLE_...|completed|
  +--------------------+--------------------+---------+
  
  +--------------------+
  |       results_flat1|
  +--------------------+
  |[[ROLE_ID, ROLE_N...|
  +--------------------+
  </code></pre>
  
  <p>While trying to do an <code>explode</code> more, I get this error:</p>
  
  <pre><code>flattened.select($"results_flat1", explode($"results_flat1").as("results_flat2"))
  </code></pre>
  
  <pre><code>Exception in thread "main" org.apache.spark.sql.AnalysisException: cannot resolve 'explode(`results_flat1`)' due to data type mismatch: input to function explode should be array or map type, not struct&lt;columns:array&lt;string&gt;,columns_type:array&lt;string&gt;,command:string,index:bigint,limit:bigint,rows:array&lt;array&lt;string&gt;&gt;,rows_count:bigint,runtime_seconds:double&gt;;;
  'Project [results_flat1#91, explode(results_flat1#91) AS results_flat2#99]
  +- Project [results_flat1#91]
     +- Project [results#75, results_flat1#91]
        +- Generate explode(results#75), false, [results_flat1#91]
           +- LogicalRDD [id#74, results#75, status#76], false
  </code></pre>
  
  <p>From what I can analyse, I can see that for explode, we need to have a string or an array of string for explode to work. For that, I tried:</p>
  
  <pre><code>val x = spark.read.json(Seq(flattened.first().get(0).asInstanceOf[String]).toDS())
  x.show()
  </code></pre>
  
  <p>Trying this, gives another error:</p>
  
  <pre><code>Exception in thread "main" java.lang.ClassCastException: org.apache.spark.sql.catalyst.expressions.GenericRowWithSchema cannot be cast to java.lang.String
      at org.apache.spark.sql.Row$class.getString(Row.scala:255)
      at org.apache.spark.sql.catalyst.expressions.GenericRow.getString(rows.scala:166)
      at com.ibm.cmdwcloud.operations.SelectOperations.getRoleListFromEntitlement(SelectOperations.scala:23)
      at com.ibm.cmdwcloud.Main$.main(Main.scala:22)
      at com.ibm.cmdwcloud.Main.main(Main.scala)
  </code></pre>
  
  <p>I am not aware of any methods that can help me get the rows object directly and convert it to a DataFrame. Please help on this.</p>
  
  <p>EDIT:</p>
  
  <p>I'm able to see this schema though:</p>
  
  <pre><code>root
   |-- results_flat1: struct (nullable = true)
   |    |-- columns: array (nullable = true)
   |    |    |-- element: string (containsNull = true)
   |    |-- columns_type: array (nullable = true)
   |    |    |-- element: string (containsNull = true)
   |    |-- command: string (nullable = true)
   |    |-- index: long (nullable = true)
   |    |-- limit: long (nullable = true)
   |    |-- rows: array (nullable = true)
   |    |    |-- element: array (containsNull = true)
   |    |    |    |-- element: string (containsNull = true)
   |    |-- rows_count: long (nullable = true)
   |    |-- runtime_seconds: double (nullable = true)
  </code></pre>
  
  <p>But unable to explode on this..</p>
  
  <p>EDIT 2:</p>
  
  <p>Thanks to the response below, I've gotten closer to what I'm trying to achieve. I performed this:</p>
  
  <pre><code>val flattened = response.select($"results", explode($"results").as("results_flat1"))
              .select("results_flat1.*")
              .select($"rows", explode($"rows").as("rows_flat"))
              .select($"rows_flat")
  
  flattened.show()
  </code></pre>
  
  <p>And got this output:</p>
  
  <pre><code>+--------------------+
  |           rows_flat|
  +--------------------+
  |[6, Incentive Pla...|
  |[7, Security Admi...|
  +--------------------+
  </code></pre>
  
  <p>Is it now possible to explode this and map it to a schema further so that I can get something like:</p>
  
  <pre><code>+--------------------+--------------------+
  |             role_id|           role_name|
  +--------------------+--------------------+
  |                   6| Incentive Plan Ad..|
  |                   7|      Security Admin|
  +--------------------+--------------------+
  </code></pre>