Can't map Object in collection

[serversage]

I am using mongo_fdw to gain access to a client's MongoDB instance. One of their Collections has an Object that has a bunch of additional columns. Mapping it to JSONB returns nothing, mapping it to JSON returns the error:

ERROR:  invalid input syntax for type json
DETAIL:  Token "nan" is invalid.
CONTEXT:  JSON data, line 1: ...ll, "risk" : null, "previous" : {  }, 
"age" : nan...

The collection looks like - keeping in mind I don't have access to any of the client's code or actual definitions:

Users
   _id String,
   createdAt Date,
   profile Object
     {
       firstName String,
       lastName String,
       age int32,
       etc...
     }

I have tried both json and jsonb for profile:

CREATE FOREIGN TABLE users (
   _id text,
   "createdAt" timestamptz,
   "profile" json
)
SERVER client_mongo_srv
   OPTIONS (database 'client_mongo_db', collection 'users');

As an aside, the mongo_fdw AT enterprisedb.com does not seem to work, I received a bounce back.

UPDATE: Forgot to put what versions of everything we are running:

• Ubuntu 16.04 LTS
• PostgreSQL 11.5
• v11 of the MongoDB FDW (Tag REL-5_2_6)
• Everything else is whatever version is downloaded by the MongoDB FDW build process (json-c v0.13.1 for example).

[lucaswxp]

Did you manage to get this working?

[vaibhavdalvi93]

Thank you for reporting. This issue is not reproducible at my end.

I am able to fetch the JSON object as shown below.

Created and inserted data into 'user' collection in MongoDB server:

MongoDB Enterprise > db.user.insert({"_id" : ObjectId("58a1ebbaf543ec0b90545859"), "createdAt": ISODate("2014-12-12T07:12:10Z"),  "profile" : {  "firstName": "vaibhav", "lastName" : "dalvi", "age"      : NumberInt(27)     } });
WriteResult({ "nInserted" : 1 })

MongoDB Enterprise > db.user.find()
{ "_id" : ObjectId("58a1ebbaf543ec0b90545859"), "createdAt" : ISODate("2014-12-12T07:12:10Z"), "profile" : { "firstName" : "vaibhav", "lastName" : "dalvi", "age" : 27 } }

Create foreign table:

CREATE FOREIGN TABLE users (
   _id NAME,
   "createdAt" timestamptz,
   "profile" json
)
SERVER mongo_server
         OPTIONS (database 'data', collection 'user');

Fetch data:

select * from users ;
           _id            |         createdAt         |                            profile                            
--------------------------+---------------------------+---------------------------------------------------------------
 58a1ebbaf543ec0b90545859 | 2014-12-12 12:42:10+05:30 | { "firstName" : "vaibhav", "lastName" : "dalvi", "age" : 27 }
(1 row)

To get each individual filed of the 'profile' object, create foreign table as follows:

CREATE FOREIGN TABLE users (
   _id NAME,
   "createdAt" timestamptz,
   "profile.firstName" text,
   "profile.lastName" text,
   "profile.age" int
)
SERVER mongo_server
         OPTIONS (database 'data', collection 'user');

Fetch data:

select * from users ;
           _id            |         createdAt         | profile.firstName | profile.lastName | profile.age 
--------------------------+---------------------------+-------------------+------------------+-------------
 58a1ebbaf543ec0b90545859 | 2014-12-12 12:42:10+05:30 | vaibhav           | dalvi            |          27
(1 row)

The collection looks like - keeping in mind I don't have access to any of the client's code or actual definitions:

If you don't have access to actual definitions then fetching exact values of field's (i.e. columns) are difficult because if the field is available in the foreign table and has compatible definition then only there is guarantee of result correctness.

However, if you are totally unaware of foreign collection fields then you can make use of "full document retrieval" function available in mongo_fdw. This fetches the entire collection in the JSON format. This can be done using following syntax:

Create foriegn table with column name '__doc':

CREATE FOREIGN TABLE users (__doc json
)
SERVER mongo_server
         OPTIONS (database 'data', collection 'user');

Fetch data:

select * from users ;
                                                                                   __doc                                                  
 ------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------
 { "_id" : { "$oid" : "58a1ebbaf543ec0b90545859" }, "createdAt" : { "$date" : 1418368330000 }, "profile" : { "firstName" : "vaibhav", "las
tName" : "dalvi", "age" : 27 } }

This will give an idea of fields available in collection on mongodb side.

ERROR: invalid input syntax for type json DETAIL: Token "nan" is invalid. CONTEXT: JSON data, line 1: ...ll, "risk" : null, "previous" : { }, "age" : nan...

This error is coming when parsing JSON object and it's been identified by Postgres not mongo_fdw. So, there is chance of invalid field in 'profile' object of 'user' collection in client's code.

[vaibhavdalvi93]

@serversage , Are you still facing the same issue? if you are able to resolve the issue then can you please close the ticket? If not, we're happy to assist you further on this issue.

[serversage]

I honestly have no idea if the issue still exists. After a few months of waiting for a response we gave up, found a workaround, and moved on. By the time there was a response to this ticket (almost 18 months later) we had finished the project. Since I have no way of reproducing the issue at this point I'll close the ticket.