title: How to convert Json array list with multiple possible values into columns in a dataframe using pyspark
body: <p>I am using the Google Admin Report API via the Python SDK in Databricks (Spark + Python 3.5).</p>
  
  <p>It returns data in the following format (Databricks pyspark code): </p>
  
  <pre><code>dbutils.fs.put("/tmp/test.json", '''{
      "userEmail": "rod@test.com", 
      "parameters": [
          {
              "intValue": "0",
              "name": "classroom:num_courses_created"
          },
          {
              "boolValue": true,
              "name": "accounts:is_disabled"
          },
          {
              "name": "classroom:role",
              "stringValue": "student"
          }
      ]
  }''', True)
  </code></pre>
  
  <p>There are 188 parameters and for each param it could be an int, bool, date or string. Depending on the field type the Api returns the value in the appropriate value (e.g. intValue for an int field and boolValue for a boolean).</p>
  
  <p>I am writing out this JSON untouched into my datalake and processing it later by loading it into a spark dataframe:</p>
  
  <pre><code>testJsonData = sqlContext.read.json("/tmp/test.json", multiLine=True)
  </code></pre>
  
  <p>This results in a dataframe with this schema:</p>
  
  <ul>
  <li>userEmail:string </li>
  <li>parameters:array 
  
  <ul>
  <li>element:struct 
  
  <ul>
  <li>boolValue:boolean</li>
  <li>intValue:string</li>
  <li>name:string</li>
  <li>stringValue:string</li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>If I display the dataframe it shows as</p>
  
  <p>{"boolValue":null,"intValue":"0","name":"classroom:num_courses_created","stringValue":null}
          {"boolValue":true,"intValue":null,"name":"accounts:is_disabled","stringValue":null}<br>
          {"boolValue":null,"intValue":null,"name":"classroom:role","stringValue":"student"}</p>
  
  <p>As you can see, it has inferred nulls for the typeValues that do not exist.</p>
  
  <p>The end state that I want is columns in a dataframe like:</p>
  
  <p><a href="https://i.stack.imgur.com/Q9CKC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Q9CKC.png" alt="required result"></a></p>
  
  <p>and the pivoted columns would be typed correctly (e.g classroom:num_courses_created would be of type int - see yellow columns above)</p>
  
  <p>Here is what I have tried so far:</p>
  
  <pre><code>from pyspark.sql.functions import explode
  tempDf = testJsonData.select("userEmail", explode("parameters").alias("parameters_exploded"))
  explodedColsDf = tempDf.select("userEmail", "parameters_exploded.*")
  </code></pre>
  
  <p>This results in a dataframe with this schema:</p>
  
  <ul>
  <li>userEmail:string</li>
  <li>boolValue:boolean</li>
  <li>intValue:string</li>
  <li>name:string</li>
  <li>stringValue:string</li>
  </ul>
  
  <p>I then pivot the rows into columns based on the Name field (which is ""classroom:num_courses_created", "classroom:role" etc (there are 188 name/value parameter pairs):</p>
  
  <pre><code>#turn intValue into an Int column
  explodedColsDf = explodedColsDf.withColumn("intValue", explodedColsDf.intValue.cast(IntegerType()))
  pivotedDf = explodedColsDf.groupBy("userEmail").pivot("name").sum("intValue")
  </code></pre>
  
  <p>Which results in this dataframe:</p>
  
  <ul>
  <li>userEmail:string</li>
  <li>accounts:is_disabled:long</li>
  <li>classroom:num_courses_created:long</li>
  <li>classroom:role:long</li>
  </ul>
  
  <p>which is not correct as the types for the columns are wrong.</p>
  
  <p>What I need to do is somehow look at all the typeValues for a parameter column (there is no way of knowing the type from the name or inferring it - other than in the original Json where it returns just the typeValue that is relevant) and whichever one is not null is the type of that column. Each param only appears once so the string, bool, int and date values just need to be outputed for the email key, not aggregated.</p>
  
  <p>This is beyond my current knowledge however I was thinking a simpler solution might be to go back all the way to the beginning and pivot the columns <em>before</em> I write out the Json so it would be in the format I want when I load it back into Spark, however I was reluctant to transform the raw data at all.  I also would prefer not to handcode the schema for 188 fields as I want to dynamically pick which fields I want so it needs to be able to handle that.</p>