tMongoDBoutput JSON Tree - write ObjectId (a reference to a different Object)

[bglaplante]

I need to store a field as an ObjectId when writing my MongoDB.

Situation:

Two collections: Customer and Contract

First I load Customer records, and an ID is generated by MongoDB for each of those.

Then, I load the Contract collection using tMongoDBoutput (JSON Tree option), and I do a lookup on Customer, to read the _id (which is returned in my tMongoDBinput as a string), and then I wish to write that value into the Contract, field customer_id.  Fine but it is written as "5a106cdb72b2da36f074f2a4".  I need it to be written as ObjectId("5a106cdb72b2da36f074f2a4").  I've tried @class of objectId, @class id, and @type objectId and id, and it just keeps coming out as a string type.  What is the correct way to write the field as ObjectId?

I can't seem to find any list of valid @class and @type for the JSON Tree configuration.

[Anonymous]

Hello,

So far, we doesn't have any datatype as ObjectID.

Have you tried to use tWriteJSONField component and for an object element, you need to add an attribute named class and set its static value to object?

Here is the related component reference:https://help.talend.com/reader/LNO9Tqm8svZQklRyXL8OYw/e_8p6JjSNgXjcaSyWdvlwg 

Best regards

Sabrina

[bglaplante]

My latest iteration, at your suggestion (although not exactly) is:

tDBInput ->      tMap -> tSortRow -> tMongoDBOutput

tMongoDBInput  ^

In the tMongoDBInput, I read the referenced data, selecting the legacyID (int) and the _id (string) - this is the contract_id

In the tMap I link my data on legacyID.  The output (2 fields) is (1) the customer to be modified, and (2) the related contract forumulated as:

new org.bson.types.ObjectId( contract._id )  ... which is set as type Object

I'm then sorting on the customer #.

The tMongoDBOutput is using the "Generate JSON Document"

The array "contracts" has a child, contractId (related to the object input field contractId) and has @class object.

Once again, the output is simply a string, not an ObjectId.

On another angle - can this be done using tMongoDBRow as a post load update?  I have a mongoDB command line solution (to update the type), but I'm unsure how to have Talend run that for me.   txxxsqlrow is clear, but tMongoDBRow is confusing.

[DBonill1656091925]

 Hello, 

In this case you have to use BSON objects by adding __DOLLAR__ before the object. 

Documentation: https://help.talend.com/en-US/components/8.0/mongodb/tmongodboutput-standard-properties?ver=39#ancho... 

Example:

This is equivalent to: 

 {
     "organization": {
         "$oid": "63df25a1343a3278747d6398"
     }
 }

Best regards

David