SSIS MongoDB datasource with nested documents

Question

SSIS MongoDB datasource with nested documents

ichv

SSC-Addicted

Points: 449
More actions
May 9, 2016 at 10:50 am

#310470

I recently started using MongoDB as a source in SSIS (using C# driver). I am very new with MongoDB and C#. When I did not have nested documents, statements like below worked for me:
var query = Query.And(Query.Or(Query.GT("CreatedOn",maxUpdatedOnBSON), Query.GT("UpdatedOn", maxUpdatedOnBSON)),
Query.Or(Query.LT("CreatedOn", cutoffDate), Query.LT("UpdatedOn", cutoffDate)),Query.In("TestType", testTypes) );
MongoCursor<BsonDocument> toReturn = collection.Find(query);
Now, I am working on collection having nested documents. I was able to create java script, and it works with MongoDB itself
db.Test.aggregate( [
{ $unwind : { path: "$Items",includeArrayIndex: "arrayIndex"} } ,
{ $match: { $and: [
{$or: [ { CreatedOn: { $gt: ISODate("2015-11-22T00:00:00Z")} }, {UpdatedOn: { $gt: ISODate("2015-11-22T00:00:00Z") } } ] },
{$or: [ { CreatedOn: { $lt: ISODate("2016-05-09T00:00:00Z")} }, {UpdatedOn: { $lt: ISODate("2016-05-09T00:00:00Z") } } ] }
] }
}] )
In C# script, as I understand, I have to use aggregate instead of find but I cannot translate this code to C#. I still have selection criteria and unwind.
Can you please help?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

When you say the javascript works, what does this mean? You get data? do you get a single record? or at least a flattened recordset?

Steve.

ichv SSC-Addicted Points: 449 More actions · Answer 2

Thank you for reply.

I got data as I need: all flattened; "header" data added for each nested row.

I just need to get the same result using C# data source with MongoDB Bson driver.

In other words, I need to get something like

MongoCursor<BsonDocument> toReturn = collection.aggregate(???);

so that performed two tasks: filter out mot matching documents and flat out nested documents at the same way as js works:

db.Test.aggregate( [

{ $unwind : { path: "$Items",includeArrayIndex: "arrayIndex"} } ,

{ $match: { $and: [

{$or: [ { CreatedOn: { $gt: ISODate("2015-11-22T00:00:00Z")} }, {UpdatedOn: { $gt: ISODate("2015-11-22T00:00:00Z") } } ] },

{$or: [ { CreatedOn: { $lt: ISODate("2016-05-09T00:00:00Z")} }, {UpdatedOn: { $lt: ISODate("2016-05-09T00:00:00Z") } } ] }

] }

}] )

I was not able to find good example online so any help is very appreciated.

Once again, I am very new in using MongoDB C# driver.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

So what have you tried and what messages do you get back?

it looks like you might need to use the original type and the new type in the unwind call , like .Unwind<OriginalType, NewType>(.....

Steve.

ichv SSC-Addicted Points: 449 More actions · Answer 4

stevefromOZ (5/9/2016)
So what have you tried and what messages do you get back?
it looks like you might need to use the original type and the new type in the unwind call , like .Unwind<OriginalType, NewType>(.....

Steve, Thank you for trying to help.

The problem is I initially don't know what to do and need road map. I am very new in this.

My previous projects did not have nested documents, and I created variable query to find only data satisfying some conditions.

example:

var query = Query.And(Query.Or(Query.GT("CreatedOn",maxUpdatedOnBSON), Query.GT("UpdatedOn", maxUpdatedOnBSON)), Query.Or(Query.LT("CreatedOn", cutoffDate), Query.LT("UpdatedOn", cutoffDate)),Query.In("TestType", testTypes) );

Then I used mongo cursor with "find":

MongoCursor<BsonDocument> toReturn = collection.Find(query);

Now, I have nested documents, and I need to use something like this:

MongoCursor<BsonDocument> toReturn = collection.aggregate(???);

The question is what I need to replace "???"

It should be something that would provide me with the same result as the java script I used to get result from MongoDB: limit data by fields values and flatten nested item subdocuments adding header fields for each item.

In the other words, I need to translate js below to C# so that I could use MongoCursor: