Mongo export to JSON does not load in BigQuery

[Sanjay Kumar]

Error while reading data, error message: Failed to parse JSON: No active field found.; ParsedString returned false; Could not parse value; Parser terminated before end of string (error code: invalid)

used compass to export a collection to JSON

stored the exported JSON on google storage (nearly 300 MB)

loaded the exported JSON in Google BigQuery

------------------------------------------

To debug copied 3 documents from compass and assembled them in an editor and loaded them in BigQuery and it worked.

-----------------------------------------

[Wan Bachtiar]

Error while reading data, error message: Failed to parse JSON: No active field found.; ParsedString returned false; Could not parse value; Parser terminated before end of string

Hi Sanjay,

From the Google BigQuery manual, based on the error code 400:

This error returns when there is any kind of invalid input other than an invalid query, such as missing required fields or an invalid table schema

It’s not clear whether you utilise BigQuery schema auto-detection or not. However, this error is likely due to a mismatch of documents with the table schema. The format of JSON documents exported from MongoDB Compass is in Extended JSON, and if the schema is defined is not may create some issue. For example:

{"a": 1}

In your exported JSON file may look as below:

{"a":{"$numberInt":"1"}}

If you have further question I would recommend to post a question on StackOverflow: google-bigquery to reach wider audience with the related expertise.

Regards,
Wan.

​

[Sanjay Kumar]

Yes, I had selected auto schema detection in BQ.

Yes, the mongo export looks like {"a":{"$numberInt":"1"}}

In the copy paste of a few documents of the same collection from compass (which loads into BQ) it looks like {"a": 1}

[Sanjay Kumar]

The mongoexport from command line formats JSON files a bit differently than the compass export.  But neither does this load into BigQuery. 

"Error while reading data, error message: Failed to parse JSON: No active field found.; ParsedNumber returned false; Could not parse value; Could not parse value; Could not parse value; Parser terminated before end of string (error code: invalid)"

Here too auto schema selection was selected.

[Wan Bachtiar]

Yes, the mongo export looks like {“a”:{“$numberInt”:”1”}}

Hi Sanjay,

As mentioned previously, this may be the cause of the issue. The schema is expecting an integer 1 after field a instead of a document object {"$numberInt": "1"}. Depending on your use case, you may want to alter the JSON export format file from extended to standard JSON. For example, using MongoDB Python driver:

#!/usr/bin/env python 
import sys 
import json
from bson import json_util 

def main(): 
    for line in sys.stdin:
        doc = json_util.loads(line)
        # Convert to string if you're using ObjectId, as there is no JSON seriazable form. 
        doc["_id"] = str(doc["_id"])
        print(json.dumps(doc))

if __name__ == "__main__":
    main()

You could see the output of the non-extended JSON format example using below command in *nix :

mongoexport --db dbname -c collname  | python ./file_snippet_above.py

Or alternatively, just store the extended JSON format in BigQuery by changing the schema to accept the extended JSON format. 

If you have further question regarding Google BigQuery I would recommend to post a question on StackOverflow: google-bigquery to reach wider audience with the related expertise.

Regards,
Wan.

​