title: Spark: How to parse multiple json with List of arrays of Struct?
body: <p>I am trying to get avg of ratings of all JSON objects in a file. I loaded the file and converted to data frame but getting error while parsing for avg. 
  Sample Request :</p>
  
  <pre class="lang-none prettyprint-override"><code>{
          "country": "France",
          "customerId": "France001",
          "visited": [
              {
                  "placeName": "US",
                  "rating": "2.3",
                  "famousRest": "N/A",
                  "placeId": "AVBS34"
  
              },
                {
                  "placeName": "US",
                  "rating": "3.3",
                  "famousRest": "SeriousPie",
                  "placeId": "VBSs34"
  
              },
                {
                  "placeName": "Canada",
                  "rating": "4.3",
                  "famousRest": "TimHortons",
                  "placeId": "AVBv4d"
  
              }        
      ]
  }
  </code></pre>
  
  <p>so for this JSON, US avg rating will be  (2.3 + 3.3)/2  = 2.8</p>
  
  <pre class="lang-none prettyprint-override"><code>{
          "country": "Egypt",
          "customerId": "Egypt009",
          "visited": [
              {
                  "placeName": "US",
                  "rating": "1.3",
                  "famousRest": "McDonald",
                  "placeId": "Dedcf3"
  
              },
                {
                  "placeName": "US",
                  "rating": "3.3",
                  "famousRest": "EagleNest",
                  "placeId": "CDfet3"
  
              },
  
  
  }
  
  {
          "country": "Canada",
          "customerId": "Canada012",
          "visited": [
              {
                  "placeName": "UK",
                  "rating": "3.3",
                  "famousRest": "N/A",
                  "placeId": "XSdce2"
  
              },
  
  
      ]
  }
  </code></pre>
  
  <p>for this avg for us= (3.3 +1.3)/2 = 2.3</p>
  
  <p>so over all, the average rating will be : (2.8 + 2.3)/2 = 2.55  (only two requests have 'US' in their visited list)</p>
  
  <p>My schema :</p>
  
  <pre class="lang-none prettyprint-override"><code>root
  |-- country: string(nullable=true)
  |-- customerId:string(nullable=true)
  |-- visited: array (nullable = true)
  |    |-- element: struct (containsNull = true)
  |    |   |-- placeId: string (nullable = true)
  |    |   |-- placeName: string (nullable = true) 
  |    |   |-- famousRest: string (nullable = true)
  |    |   |-- rating: string (nullable = true)
  
  val sqlContext = new org.apache.spark.sql.SQLContext(sc)
  val df = sqlContext.jsonFile("temp.txt")
  df.show() 
  </code></pre>
  
  <p>so basically I need to get average of ratings where placeName = 'US' in say for eg. AVG_RATING = sum of rating in each JSON object where placeName is US / count of such visited entry and FINAL_VALUE = Sum of all AVG_RATING in each JSON object with placeName 'US' / count of all JSON objects with placeName = 'US' . </p>
  
  <p>So far I tried :</p>
  
  <pre><code> df.registerTempTable("people")
     sqlContext.sql("select avg(expResults.rank) from people LATERAL VIEW explode(visited)people AS expResults where expResults.placeName = 'US' ").collect().foreach(println)
  
      val result = df.select("*").where(array_contains (df("visited.placeName"), "US"));  - gives the list where visited array contains US. But I am not sure how do parse through list of structs.
  </code></pre>
  
  <p>Can some one tell me how do I do this ? </p>