title: Json file to pyspark dataframe
body: <p>I'm trying to work with JSON file on spark (pyspark) environment.</p>
  <p>Problem: Unable to convert JSON to expected format in Pyspark Dataframe</p>
  <p><strong>1st Input data set:</strong></p>
  <blockquote>
  <p><a href="https://health.data.ny.gov/api/views/cnih-y5dw/rows.json" rel="nofollow noreferrer">https://health.data.ny.gov/api/views/cnih-y5dw/rows.json</a></p>
  </blockquote>
  <p>In this file metadata is defined at start for of the file with tag &quot;meta&quot; and then followed by data with tag &quot;data&quot;.</p>
  <p>FYI: Steps taken to download data from web to local drive. 1. I've downloaded file to my local drive 2. then pushed to hdfs - from there I'm reading it to spark environment.</p>
  <pre><code>df=sqlContext.read.json(&quot;/user/train/ny.json&quot;,multiLine=True)
  df.count()
  out[5]: 1
  df.show()
  </code></pre>
  <p><a href="https://i.stack.imgur.com/c3V33.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/c3V33.png" alt="enter image description here" /></a></p>
  <p><code>df.printSchema()</code></p>
  <pre><code>root
   |-- data: array (nullable = true)
   |    |-- element: array (containsNull = true)
   |    |    |-- element: string (containsNull = true)
   |-- meta: struct (nullable = true)
   |    |-- view: struct (nullable = true)
   |    |    |-- attribution: string (nullable = true)
   |    |    |-- attributionLink: string (nullable = true)
   |    |    |-- averageRating: long (nullable = true)
   |    |    |-- category: string (nullable = true)
   |    |    |-- columns: array (nullable = true)
   |    |    |    |-- element: struct (containsNull = true)
   |    |    |    |    |-- cachedContents: struct (nullable = true)
   |    |    |    |    |    |-- average: string (nullable = true)
   |    |    |    |    |    |-- largest: string (nullable = true)
   |    |    |    |    |    |-- non_null: long (nullable = true)
   |    |    |    |    |    |-- null: long (nullable = true)
   |    |    |    |    |    |-- smallest: string (nullable = true)
   |    |    |    |    |    |-- sum: string (nullable = true)
   |    |    |    |    |    |-- top: array (nullable = true)
   |    |    |    |    |    |    |-- element: struct (containsNull = true)
   |    |    |    |    |    |    |    |-- count: long (nullable = true)
   |    |    |    |    |    |    |    |-- item: string (nullable = true)
   |    |    |    |    |-- dataTypeName: string (nullable = true)
   |    |    |    |    |-- description: string (nullable = true)
   |    |    |    |    |-- fieldName: string (nullable = true)
   |    |    |    |    |-- flags: array (nullable = true)
   |    |    |    |    |    |-- element: string (containsNull = true)
   |    |    |    |    |-- format: struct (nullable = true)
   |    |    |    |    |    |-- align: string (nullable = true)
   |    |    |    |    |    |-- mask: string (nullable = true)
   |    |    |    |    |    |-- noCommas: string (nullable = true)
   |    |    |    |    |    |-- precisionStyle: string (nullable = true)
   |    |    |    |    |-- id: long (nullable = true)
   |    |    |    |    |-- name: string (nullable = true)
   |    |    |    |    |-- position: long (nullable = true)
   |    |    |    |    |-- renderTypeName: string (nullable = true)
   |    |    |    |    |-- tableColumnId: long (nullable = true)
   |    |    |    |    |-- width: long (nullable = true)
   |    |    |-- createdAt: long (nullable = true)
   |    |    |-- description: string (nullable = true)
   |    |    |-- displayType: string (nullable = true)
   |    |    |-- downloadCount: long (nullable = true)
   |    |    |-- flags: array (nullable = true)
   |    |    |    |-- element: string (containsNull = true)
   |    |    |-- grants: array (nullable = true)
   |    |    |    |-- element: struct (containsNull = true)
   |    |    |    |    |-- flags: array (nullable = true)
   |    |    |    |    |    |-- element: string (containsNull = true)
   |    |    |    |    |-- inherited: boolean (nullable = true)
   |    |    |    |    |-- type: string (nullable = true)
   |    |    |-- hideFromCatalog: boolean (nullable = true)
   |    |    |-- hideFromDataJson: boolean (nullable = true)
   |    |    |-- id: string (nullable = true)
   |    |    |-- indexUpdatedAt: long (nullable = true)
   |    |    |-- metadata: struct (nullable = true)
   |    |    |    |-- attachments: array (nullable = true)
   |    |    |    |    |-- element: struct (containsNull = true)
   |    |    |    |    |    |-- assetId: string (nullable = true)
   |    |    |    |    |    |-- blobId: string (nullable = true)
   |    |    |    |    |    |-- filename: string (nullable = true)
   |    |    |    |    |    |-- name: string (nullable = true)
   |    |    |    |-- availableDisplayTypes: array (nullable = true)
   |    |    |    |    |-- element: string (containsNull = true)
   |    |    |    |-- custom_fields: struct (nullable = true)
   |    |    |    |    |-- Additional Resources: struct (nullable = true)
   |    |    |    |    |    |-- See Also: string (nullable = true)
   |    |    |    |    |-- Dataset Information: struct (nullable = true)
   |    |    |    |    |    |-- Agency: string (nullable = true)
   |    |    |    |    |-- Dataset Summary: struct (nullable = true)
   |    |    |    |    |    |-- Contact Information: string (nullable = true)
   |    |    |    |    |    |-- Coverage: string (nullable = true)
   |    |    |    |    |    |-- Data Frequency: string (nullable = true)
   |    |    |    |    |    |-- Dataset Owner: string (nullable = true)
   |    |    |    |    |    |-- Granularity: string (nullable = true)
   |    |    |    |    |    |-- Organization: string (nullable = true)
   |    |    |    |    |    |-- Posting Frequency: string (nullable = true)
   |    |    |    |    |    |-- Time Period: string (nullable = true)
   |    |    |    |    |    |-- Units: string (nullable = true)
   |    |    |    |    |-- Disclaimers: struct (nullable = true)
   |    |    |    |    |    |-- Limitations: string (nullable = true)
   |    |    |    |    |-- Local Data: struct (nullable = true)
   |    |    |    |    |    |-- County Filter: string (nullable = true)
   |    |    |    |    |    |-- County_Column: string (nullable = true)
   |    |    |    |-- filterCondition: struct (nullable = true)
   |    |    |    |    |-- children: array (nullable = true)
   |    |    |    |    |    |-- element: struct (containsNull = true)
   |    |    |    |    |    |    |-- metadata: struct (nullable = true)
   |    |    |    |    |    |    |    |-- includeAuto: long (nullable = true)
   |    |    |    |    |    |    |    |-- multiSelect: boolean (nullable = true)
   |    |    |    |    |    |    |    |-- operator: string (nullable = true)
   |    |    |    |    |    |    |    |-- tableColumnId: struct (nullable = true)
   |    |    |    |    |    |    |    |    |-- 583607: long (nullable = true)
   |    |    |    |    |    |    |-- type: string (nullable = true)
   |    |    |    |    |    |    |-- value: string (nullable = true)
   |    |    |    |    |-- metadata: struct (nullable = true)
   |    |    |    |    |    |-- advanced: boolean (nullable = true)
   |    |    |    |    |    |-- unifiedVersion: long (nullable = true)
   |    |    |    |    |-- type: string (nullable = true)
   |    |    |    |    |-- value: string (nullable = true)
   |    |    |    |-- jsonQuery: struct (nullable = true)
   |    |    |    |    |-- order: array (nullable = true)
   |    |    |    |    |    |-- element: struct (containsNull = true)
   |    |    |    |    |    |    |-- ascending: boolean (nullable = true)
   |    |    |    |    |    |    |-- columnFieldName: string (nullable = true)
   |    |    |    |-- rdfSubject: string (nullable = true)
   |    |    |    |-- renderTypeConfig: struct (nullable = true)
   |    |    |    |    |-- visible: struct (nullable = true)
   |    |    |    |    |    |-- table: boolean (nullable = true)
   |    |    |    |-- rowLabel: string (nullable = true)
   |    |    |-- name: string (nullable = true)
   |    |    |-- newBackend: boolean (nullable = true)
   |    |    |-- numberOfComments: long (nullable = true)
   |    |    |-- oid: long (nullable = true)
   |    |    |-- owner: struct (nullable = true)
   |    |    |    |-- displayName: string (nullable = true)
   |    |    |    |-- flags: array (nullable = true)
   |    |    |    |    |-- element: string (containsNull = true)
   |    |    |    |-- id: string (nullable = true)
   |    |    |    |-- profileImageUrlLarge: string (nullable = true)
   |    |    |    |-- profileImageUrlMedium: string (nullable = true)
   |    |    |    |-- profileImageUrlSmall: string (nullable = true)
   |    |    |    |-- screenName: string (nullable = true)
   |    |    |    |-- type: string (nullable = true)
   |    |    |-- provenance: string (nullable = true)
   |    |    |-- publicationAppendEnabled: boolean (nullable = true)
   |    |    |-- publicationDate: long (nullable = true)
   |    |    |-- publicationGroup: long (nullable = true)
   |    |    |-- publicationStage: string (nullable = true)
   |    |    |-- query: struct (nullable = true)
   |    |    |    |-- orderBys: array (nullable = true)
   |    |    |    |    |-- element: struct (containsNull = true)
   |    |    |    |    |    |-- ascending: boolean (nullable = true)
   |    |    |    |    |    |-- expression: struct (nullable = true)
   |    |    |    |    |    |    |-- columnId: long (nullable = true)
   |    |    |    |    |    |    |-- type: string (nullable = true)
   |    |    |-- rights: array (nullable = true)
   |    |    |    |-- element: string (containsNull = true)
   |    |    |-- rowsUpdatedAt: long (nullable = true)
   |    |    |-- rowsUpdatedBy: string (nullable = true)
   |    |    |-- tableAuthor: struct (nullable = true)
   |    |    |    |-- displayName: string (nullable = true)
   |    |    |    |-- flags: array (nullable = true)
   |    |    |    |    |-- element: string (containsNull = true)
   |    |    |    |-- id: string (nullable = true)
   |    |    |    |-- profileImageUrlLarge: string (nullable = true)
   |    |    |    |-- profileImageUrlMedium: string (nullable = true)
   |    |    |    |-- profileImageUrlSmall: string (nullable = true)
   |    |    |    |-- screenName: string (nullable = true)
   |    |    |    |-- type: string (nullable = true)
   |    |    |-- tableId: long (nullable = true)
   |    |    |-- tags: array (nullable = true)
   |    |    |    |-- element: string (containsNull = true)
   |    |    |-- totalTimesRated: long (nullable = true)
   |    |    |-- viewCount: long (nullable = true)
   |    |    |-- viewLastModified: long (nullable = true)
   |    |    |-- viewType: string (nullable = true)
  </code></pre>
  <p><em><strong>Problem:</strong></em>  All records getting wrapped up in single row and two column, i.e meta and data. Also with spark native JSON utility - Spark infers the schema (meatadata) automatically - and my expectation is it shouldn't explicitly as separate column on dataframe.</p>
  <p><strong>Expected Output</strong>
  JSON data set has following list of columns. It should show them in tabular format in dataframe where I can query them&quot;</p>
  <pre><code>FACILITY, ADDRESS, LAST INSPECTED, VIOLATIONS,TOTAL  CRITICAL VIOLATIONS, TOTAL CRIT.  NOT CORRECTED, TOTAL  NONCRITICAL VIOLATIONS, DESCRIPTION, LOCAL HEALTH DEPARTMENT, COUNTY, FACILITY ADDRESS, CITY, ZIP CODE, NYSDOH GAZETTEER (1980), MUNICIPALITY, OPERATION NAME, PERMIT EXPIRATION DATE, PERMITTED  (D/B/A), PERMITTED  CORP. NAME,PERM. OPERATOR LAST NAME, PERM. OPERATOR LAST NAME, PERM. OPERATOR FIRST NAME, NYS HEALTH OPERATION ID, INSPECTION TYPE, INSPECTION COMMENTS, FOOD SERVICE FACILITY STATE, Location1
  </code></pre>
  <p><strong>2nd Input DataSet:</strong>
  On site, it's first data set about funded projects by world bank</p>
  <blockquote>
  <p><a href="http://jsonstudio.com/resources/" rel="nofollow noreferrer">http://jsonstudio.com/resources/</a></p>
  <p>(On site, it's first data set about funded projects by world bank)</p>
  </blockquote>
  <p><em>It works all fine.</em></p>
  <pre><code>df=sqlContext.read.json(&quot;/user/train/wb.json&quot;)
  df.count()
  500 
  </code></pre>
  <p>2nd Input Data Sets works all but 1st Input dataset is not. My Observation is the way metadata defined is different for both Json files. In 1st. Meta data is defined first and then data however in 2nd file - meatadate is available with data on every line.</p>
  <p>Can you please guide me on 1st input JSON file format and how to handle situation while converting it into pyspark dataframe?</p>
  <p><strong>Updated Outcome:</strong> After initial analysis we found that format seems wrong but community member helped an alternative way to read format. Marking answer as right and closing this thread.</p>
  <p>Let me know if you need any further details, thanks in advance.</p>