0

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 got 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#, 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?

ICHV
  • 1
  • 1

2 Answers2

0

because there is no collection template posted, i'm attaching a snippet something similar to what you would be looking for. does this help?

       var builder = Builders<BsonDocument>.Filter;
       //and operator can be used similar to below by using operator "&" or builder.And. 

        var filter = builder.Eq("state", "nj") | builder.Eq("state", "CO"); 
        var filter2 = builder.Eq("pop", 6033) | builder.Eq("city", "nyc");
        filter = builder.And(filter, filter2);
        var pipeline = grades.Aggregate()
            .Unwind(x => x["Items"]) 
          .Match(filter);


        var list = pipeline.ToList();

        foreach (var item in list)
        {
            //do something
        }
KaSh
  • 175
  • 1
  • 11
  • I hope so. Will try this approach, thank you. The question is about MongoCursor syntax. MongoCursor toReturn = collection.?; – ICHV May 11 '16 at 16:55
  • well if you want a cursor on aggregation, you need aggregateargs or pass aggregateoptions into aggregate consturctor. or edited answer above. You cant get MongoCursor out of it. you can get IAsyncCursor instead like this var aggCursor = grades.Aggregate() .Unwind(x => x["Items"]) .Match(filter).ToCursor(); – KaSh May 12 '16 at 08:59
  • take a look at this link for detailed info on asynccursor http://stackoverflow.com/questions/29682371/how-is-an-iasynccursor-used-for-iteration-with-the-mongodb-c-sharp-driver?rq=1 – KaSh May 12 '16 at 09:08
0

I got help and sharing the solution:

 //Create matching criteria used in the aggregation pipeline to bring back only the specified documents based on date range

    var match = new BsonDocument("$match",
            new BsonDocument("$and",
                new BsonArray()
                .Add(new BsonDocument("$or", new BsonArray().Add(new BsonDocument("CreatedOn", new BsonDocument("$gt", maxUpdatedOnBSON))).Add(new BsonDocument("UpdatedOn", new BsonDocument("$gt", maxUpdatedOnBSON)))))
                .Add(new BsonDocument("$or", new BsonArray().Add(new BsonDocument("CreatedOn", new BsonDocument("$lt", cutoffDate))).Add(new BsonDocument("UpdatedOn", new BsonDocument("$lt", cutoffDate)))))));

    //create the arguments to pass to the $unwind method of the aggregation
    var unwindargs = new BsonDocument("path", "$LineItems");
    unwindargs.Add("includeArrayIndex", "arrayIndex");

    //create the unwind stage and add the arguments
    var unwind = new BsonDocument("$unwind", unwindargs);

    //create a new pipeline and gather the results 
    var pipeline = new[] { match, unwind };
    var mongoArgs = new AggregateArgs { Pipeline = pipeline };

    var toReturn = collection.Aggregate(mongoArgs).ToList();
ICHV
  • 1
  • 1