May 9, 2016 at 10:50 am
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?
May 9, 2016 at 11:12 am
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.
May 9, 2016 at 12:46 pm
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.
May 9, 2016 at 8:19 pm
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.
May 10, 2016 at 8:07 am
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:
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") } } ] }
] }
}] )
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy