Mongodb - criteria on ObjectId

[mtawk]

I'm using Teiid 9.0.0 with mongodb translator

I'm adding a criteria over ObjectId value:

select * from  "testModel_gq"."Customer2" "Customer2"  where "Customer2"."_id" = 'a59e3722dcfd3f5879563cf8'

I'm getting the following error:

TEIID30492 Expressions of type OBJECT, CLOB, BLOB, or XML cannot be used in comparison: Customer2."_id" = 'a59e3722dcfd3f5879563cf8'.

Is there a specific syntax to write the criteria in this case?

[rareddy]

_id construct of the MongoDB. You need to use the column on your table. Typically _id maps your Table's PK.

[mtawk]

Since _id maps the table PK, and teiid recognize it in select statement, why I can not create a criteria over its value.

It is about the interpretation of the mongodb command: test.Customer2.find({_id:"a59e3722dcfd3f5879563cf8"})

Also such a criteria may be needed if we want to update the values of a certain collection document

[rareddy]

Mark,

What I am saying is if you defined the PK on your table and supplied a value for that PK then use that for criteria, as the value of the PK *you* defined will be replaced as _id in MongoDB collection. If you used "native" importer to import the existing collections, or you named your PK column name as "_id" then "_id" is mapped as PK of your table. 

Looks like you already have this mapping. What is data type showing on your PK column?

One thing I have not implemented/tested "AUTO_INCREMENT" feature with primary key, where if you do not define the "_id" value it is automatically generated. MongoDB already does this, but Teiid needs to allow it go through when "_id" value not there in the INSERT statement. You can log a feature request for that.

Ramesh..

[mtawk]

Feature added : [TEIID-4618] Mongodb auto-increment primary key - JBoss Issue Tracker

[rareddy]

Added and resolved [TEIID-4620] MongoDB: Model "_id" during metadata import as string data type - JBoss Issue Tracker