Does Teiid MongoDb has Pushdown Query capability

[fullysane]

Hi I am trying Teiid to query MongoDb, the data base has 1.5M document but with indexes. My query took a long time to return data even with accessing by indexed key fields. Teiid seems to retrieve all data from DB and then filter them in the Teiid server . I tried pushdown query syntax /*+MAKEDEP*/ but it still not helping. Does Teiid MongoDb connector support  Pushdown Query capability?

[rareddy]

It does have query pushdown functionality but depends on the query and the translator capabilities. You can start looking at the query plan and also in debug log it will show the query it is sending to MongoDB. That will give you a good picture of the scenario. Based on it one can see why the query not being pushed down.

Ramesh..

[fullysane]

Hi Ramesh:

Thank you for your help!

We are using Direct Query Procedure (Native query) for Mongo like below to get all the data from a Mongo Collection. Then using JsontoXML and finally as a View.

Our query:

Select * from View where  DummyNbr = 123

We want to only pull those documents for our condition but Our query is hanging because it is trying to get all the data. How can we use the Pushdown approach in the Query. Can you give a sample query example for this scenario.

select x.* from TABLE(call native

('test_col;{"$project" :

    {

"_id": 1,

"DummyNbr" : 1,

"DummyName" : 1,

"DummyDate" : 1,

}}

')) t

Note :

Our query condition has to be dynamic as different Users will use with different where condition.

[rareddy]

Was there a reason why you opted to use Direct Query procedure? If you are using the Direct Query procedure, Teiid DOES NOT do any optimizations to the query, it will execute AS IS you define, that is is the reason it is called "Direct Query or Native Query". You can parameterize the queries some time by using $1 in a query like

native('View;{$match : { \"DummyNbr\" : $1}};{$project : {"DummyNbr", "DummyName", "DummyDate"}}', 123)

If not do not use the Direct Procedure, and depend on the metadata from Teiid, and issue call directly as "select * from View where DummyNbr=123" and Teiid will generate correct pushdown query to select a single record.

[fullysane]

In our case the collection is already created and we just need to read it. It is already nested and level is sometimes 4-5. As the structure of json is already decided, we can only go with MERGE because everything is in one collection. But we found below limitation in the documentation hence went for native query.

It will be really helpful if you can suggest Teiid VDB sample for existing collection with below document.

Note:

Collection Name is “MyTransport”

Currently nested embedding of documents has limited support due to capabilities of handling nested arrays is limited in the MongoDB. Nesting of "EMBEDDALBLE" property with multiple levels is OK, however more than two levels with MERGE is not recommended

{

"_id" : ObjectId("6da4003b8b11dc123e54da5c"),

"RecordId" : "6da4003b8b11dc123e54da5c ",

"detailPak" : {

"PctAmt" : "1",

"ReceiptsAmt" : "3000",

"LimitAmt" : "100",

"Logistics" : {

"myTypeCd" : {

"lit" : "Non-conclusive",

"value" : "N"

},

"OurDetail" : [

                {

"PercentageOfTotalAmt" : "80",

"comment" : "b",

"subDetail" : [

                        {

"Name" : "Car",

"Code" : "3"

},

                        {

"Name" : "Bus",

"Code" : "1"

},

                        {

"Name" : "Bike",

"Code" : "1"

},

                        {

"Name" : "Train",

"Code" : "1"

},

                        {

"Name" : "Airplane",

"Code" : "3"

},

                        {

"Name" : "ship",

"Code" : "2"

}

],

"myCd" : "XYZ",

"myAmt" : "50",

"myAmt1" : "0"

}

]

     }

    }

}

[rareddy]

Are also trying to write data to MongoDB using Teiid? The warning is more for write scenarios. As there was/is no good way inject/remove/update an array item in the MongoDB. I am not sure if MongoDB made any progress in this area. If it is read-only, try to read using the Teiid's importer and see what you get. I do not have a setup ready to go at my end, so it would take some time for me to set up MongoDB and Teiid and example to test it out.

Let me know results from this, then I will consider setting up locally.

Ramesh..