Unable to read json from S3. "Invalid Json...ObjectId" error

P_Vivek · 2022-12-17T12:02:43.642Z

Hello guys, I’m unable to query json file from s3 bucket, that I have integrated through Data Federation. Getting this error

parse error on JSON document 1 from “s3://Drip/DripJson.json?delimiter=%2F&region=”: parse error at ObjectId("...: invalid character, correlationID =

How to resolve this? Can’t we upload json with ObjectId to s3 and access through federated queries?

chris · 2022-12-17T14:18:30.325Z

ObjectId("xxxxx") is not JSON. For a proper MongoDB extended JSON with ObjectID I’d expect to see:

"_id":{"$oid":"5ca4bbc7a2dd94ee5816238c"}

How are you generating your files ?

P_Vivek · 2022-12-17T16:18:03.026Z

Hi thanks for ur reply. Have exported the json file from Studio3T from the existing mongodb collection we already have and uploaded it to S3. It usually exports as _id:ObjectId(""), and long datatype as “age”: NumberLong(10) etc… Even from Mongodb compass it exports in the same way.

chris · 2022-12-17T16:30:23.877Z

My Compass install (1.33.1) and mongodb-database-tools(100.6.0) exporting as expected

What OSes, MongoDB version,compass and Studio3T are you using?

Yilmaz_Durmaz · 2022-12-17T21:00:59.033Z

Hi @P_Vivek, there is an unfortunate misunderstanding about JSON file structure versus json content we use in mongodb queries. It is actually eJSON when we want to use ObjectId, NumberLong, and other data types.

MongoDB Extended JSON (v2) — MongoDB Manual

I don’t know how S3 sends the file to your program, but when parsing you need to use EJSON parser.

P_Vivek · 2022-12-18T16:44:58.134Z

Hi Chris, I’m using Mongodb V5, compass 1.34.2, Windows 11, Studio3T latest.

P_Vivek · 2022-12-18T16:51:22.469Z

Hi @Yilmaz_Durmaz thanks for ur clarification. The issues is studio3t is generating ejson while exporting, I’m uploading that json file to s3, when reading that as s3 object I’m getting above error.

P_Vivek · 2022-12-18T17:14:17.721Z

Hi @chris there is another option in studio3t to export the json like u mentioned, tried that now. Will check and get back.

Benjamin_Flast · 2022-12-19T16:01:55.384Z

Hey @P_Vivek , I’m not sure that’s exactly what’s happening here, can you share the full error with the correlationID value?

Atlas Data Federation can read both Extended JSON and JSON, I am expecting that Studio3t is doing something unusual and maybe exporting incorrect extended json somehow.

P_Vivek · 2022-12-23T04:23:18.021Z

Hi Chris, Studio3T’s has options to export in the format you have mentioned. It is working now. Thanks for the great support.

P_Vivek · 2022-12-23T04:35:00.301Z

Hi @Benjamin_Flast Atlas Data Federation is not reading Extended Json. Studio 3T has another option to export usual Json format it is working fine in Data Federation.
Pls let me know how to make extended Json to work in Data Federation.
Below is the extended Json studio3T is exporting…

{ 
    "_id" : ObjectId("xxxxxxxxxxxxxxxxxx"), 
    "sequenceId" : NumberInt(xxxx), 
    "type" : "ONE_WAY", 
    "area" : {
        "name" : "xxxxxxxxxx"
    }, 
    "cardQuantity" : 9000.0, 
    "startTime" : ISODate("2022-10-01T00:12:10.000+0000"), 
    "endTime" : ISODate("2022-10-01T00:28:12.000+0000"), 
    "status" : "COMPLETED", 
    "billingStatus" : "UNAPPROVED", 
    "unitId" : NumberInt(xxx), 
    "uId" : "xxxxxx", 
    "timezone" : "Asia/Kolkata", 
    "minimumQuantityPercentage" : 15.0, 
    "isFetched" : false, 
    "resendCommandCount" : NumberInt(x), 
    "createdAt" : ISODate("2022-09-30T23:58:07.484+0000"), 
    "createdBy" : "fsauser", 
    "active" : true, 
    "_class" : "com.lantrasoft.iot.api.fillingstation.model.Trip", 
    "balanceVolume" : 4064.465, 
    "dispensedQuantity" : 5409.5689999999995, 
}

chris · 2022-12-23T13:59:16.791Z

That’s not extended json, its not even json. This is the format for mongosh/studio3t. If you’re using the tool as documented and not getting the correct results use their support/raise a bug, its what you paid for.

Once your tools is exporting correct json or you switch to another tool that does then the Data Federation will work with its output.

Tip: If you cannot parse your export with something like jq its not json.

Yilmaz_Durmaz · 2022-12-23T15:33:32.515Z

TL:DR; good thing you got your problem fixed

Hi again @P_Vivek , Let me extend (pun intended ) my last statement about the misunderstanding of JSON/eJSON with an example.

JSON format has only 5 value types: numbers, true/false, objects enclosed in {}, arrays enclosed in [] and everything else is string enclosed in quotes"". and then all keys are strings again. oh, and its root is also an object.

{ "key": { "just_a_sub_key": "value" } }

eJSON is a perfect JSON file with a trick: sub-keys in sub-objects can have a meaning.

{ "key": { "ejson_sub_key": "value" } }

$oid and $date are two of those meaningful sub keys. if you read with a JSON reader they are just some subkeys, but if you read with an eJSON parser, the “values” they refer to will be converted to ObjectId and Date objects further down your program.

a quick warning here is this: if you see any non-quoted variable name (other than true/false and numbers) then that file is not a valid JSON/eJSON file. you may say they are actually partial JavaScript files and are meant to be read without any parsing, mostly by the programs they are written for.

system · 2022-12-28T15:33:44.545Z

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.