title: Can you load JSON formatted data to a BigQuery table using Python and load_table_from_json()?
body: <h2>Issue:</h2>
  
  <p>I'm trying to load a JSON object to a BigQuery table using Python 3.7. </p>
  
  <p>From reading the Google documentation the <code>google-cloud-bigquery</code> module looks like it has a method that should do what I want: <a href="https://googleapis.dev/python/bigquery/latest/generated/google.cloud.bigquery.client.Client.html#google.cloud.bigquery.client.Client.load_table_from_json" rel="nofollow noreferrer"><code>load_table_from_json()</code></a>. However, when I try to implement this method in my Python script I get the following error returned in the Python shell: </p>
  
  <pre><code>BadRequest: 400 Error while reading data, error message: JSON table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[] collection for more details.
  </code></pre>
  
  <p>When I check the job history in BigQuery I have some additional information also:</p>
  
  <pre><code>Error while reading data, error message: JSON processing encountered too many errors, giving up. Rows: 1; errors: 1; max bad: 0; error percent: 0
  </code></pre>
  
  <p>And </p>
  
  <pre><code>Error while reading data, error message: JSON parsing error in row starting at position 0: Value encountered without start of object.
  </code></pre>
  
  <p>Here's the syntax of the Python script I'm running:</p>
  
  <pre><code>import pandas as pd
  import numpy as np
  from google.cloud import bigquery
  import os
  
  ### Converts schema dictionary to BigQuery's expected format for job_config.schema
  def format_schema(schema):
      formatted_schema = []
      for row in schema:
          formatted_schema.append(bigquery.SchemaField(row['name'], row['type'], row['mode']))
      return formatted_schema
  
  ### Create dummy data to load
  df = pd.DataFrame([[2, 'Jane', 'Doe']],
  columns=['id', 'first_name', 'last_name'])
  
  ### Convert dataframe to JSON object
  json_data = df.to_json(orient = 'records')
  
  os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = r"&lt;My_Credentials_Path&gt;\application_default_credentials.json"
  
  ### Define schema as on BigQuery table, i.e. the fields id, first_name and last_name   
  table_schema = {
            'name': 'id',
            'type': 'INTEGER',
            'mode': 'REQUIRED'
            }, {
            'name': 'first_name',
            'type': 'STRING',
            'mode': 'NULLABLE'
            }, {
            'name': 'last_name',
            'type': 'STRING',
            'mode': 'NULLABLE'
            }
  
  project_id = '&lt;my_project&gt;'
  dataset_id = '&lt;my_dataset&gt;'
  table_id = '&lt;my_table&gt;'
  
  client  = bigquery.Client(project = project_id)
  dataset  = client.dataset(dataset_id)
  table = dataset.table(table_id)
  
  job_config = bigquery.LoadJobConfig()
  job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
  job_config.schema = format_schema(table_schema)
  job = client.load_table_from_json(json_data, table, job_config = job_config)
  
  print(job.result())
  </code></pre>
  
  <p>As far as I can tell from the <a href="https://googleapis.dev/python/bigquery/latest/generated/google.cloud.bigquery.client.Client.html#google.cloud.bigquery.client.Client.load_table_from_json" rel="nofollow noreferrer">docs</a>, this should work - but it doesn't. </p>
  
  <p>I suspect the issue is with the JSON object <code>json_data</code> and that BigQuery might not like something about that value: <code>[{"id":2,"first_name":"Jane","last_name":"Doe"}]</code>. Even if I pass the <code>lines</code> parameter with a value of <code>True</code> it makes no difference - that JSON object has no square brackets: <code>{"id":2,"first_name":"Jane","last_name":"Doe"}</code></p>
  
  <p>I also tried using the alternate values for the <code>orient</code> parameter as described in the <code>to_json()</code> <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_json.html" rel="nofollow noreferrer">documentation</a>, but they all produce the same error as the <code>records</code> value in the above.</p>
  
  <p>I've also tried commenting out the line <code>job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON</code> but got the same error.</p>
  
  <h2>Workaround</h2>
  
  <p>Interestingly, in the <code>load_table_from_json()</code> <a href="https://googleapis.dev/python/bigquery/latest/generated/google.cloud.bigquery.client.Client.html#google.cloud.bigquery.client.Client.load_table_from_json" rel="nofollow noreferrer">documentation</a> there is a note which states: </p>
  
  <p><em>If your data is already a newline-delimited JSON string, it is best to wrap it into a file-like object and pass it to load_table_from_file():</em></p>
  
  <pre><code>import io
  from google.cloud import bigquery
  
  data = u'{"foo": "bar"}'
  data_as_file = io.StringIO(data)
  
  client = bigquery.Client()
  client.load_table_from_file(data_as_file, ...)
  </code></pre>
  
  <p>If I apply this to my script and attempt to load the data it all works. This indicates that the connection to BigQuery is working correctly and that the issue is indeed with loading the JSON in its original form. There is no mention of deprecation for <code>load_table_from_json()</code> in favour of <code>load_table_from_file()</code> so why isn't it working?</p>
  
  <h2>Working code:</h2>
  
  <p>For reference, here's a version of the script that uses the <code>load_table_from_file()</code> method to load the data to BigQuery:</p>
  
  <pre><code>import pandas as pd
  import numpy as np
  from google.cloud import bigquery
  import os
  import io
  
  def format_schema(schema):
      formatted_schema = []
      for row in schema:
          formatted_schema.append(bigquery.SchemaField(row['name'], row['type'], row['mode']))   
      return formatted_schema
  
  df = pd.DataFrame([[2, 'Jane', 'Doe']],
  columns=['id', 'first_name', 'last_name'])
  
  ### Additional parameter used to convert to newline delimited format
  json_data = df.to_json(orient = 'records', lines = True)
  stringio_data = io.StringIO(json_data)
  
  os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = r"&lt;My_Credentials_Path&gt;\application_default_credentials.json"
  
  table_schema = {
            'name': 'id',
            'type': 'INTEGER',
            'mode': 'REQUIRED'
            }, {
            'name': 'first_name',
            'type': 'STRING',
            'mode': 'NULLABLE'
            }, {
            'name': 'last_name',
            'type': 'STRING',
            'mode': 'NULLABLE'
            }
  
  project_id = '&lt;my_project&gt;'
  dataset_id = '&lt;my_dataset&gt;'
  table_id = '&lt;my_table&gt;'
  
  client = bigquery.Client(project = project_id)
  dataset = client.dataset(dataset_id)
  table = dataset.table(table_id)
  
  job_config = bigquery.LoadJobConfig()
  job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
  job_config.schema = format_schema(table_schema)
  
  job = client.load_table_from_file(stringio_data, table, job_config = job_config)
  print(job.result())
  </code></pre>