CODEWITHSUNDEEP
c#.netasp.net-mvcmongodb
FeelRightz
FeelRightz

Reputation: 2999

MongoDB Query for documents in array .NET MVC

With a MongoDB collection structure as in this example:

{
    "_id" : "123",
    "memberinfo" : [{
        "height": 170,
        "weight": 55,
        "status": "approved",
        "fruits" : [ {"name" : "apple","reason" : null },
                     {"name" : "orange","reason" : null},
                     {"name" : "berry","reason" : null}
        ],
    }]
},
{
    "_id" : "456",
    "memberinfo" : [{
        "height" : 160,
        "weight": 90,
        "status": "approved",
        "fruits" : [ {"name" : "berry","reason" : null},
                     {"name" : "orange","reason" : null}
        ],
    },{
        "height" : 160,
        "weight": 90,
        "status": "rejected",
        "fruits" : [ {"name" : "banana","reason" : null}
        ],
    }]
}

How is it possible to query the nested array to get the memberinfo which has the status:approved.

The result should be like this:

{
    "_id" : "123",
    "memberinfo" : [{
        "height": 170,
        "weight": 55,
        "status": "approved",
        "fruits" : [ {"name" : "apple","reason" : null },
                     {"name" : "orange","reason" : null},
                     {"name" : "berry","reason" : null}
        ],
    }]
},
{
    "_id" : "456",
    "memberinfo" : [{
        "height" : 160,
        "weight": 90,
        "status": "approved",
        "fruits" : [ {"name" : "berry","reason" : null},
                     {"name" : "orange","reason" : null}
        ],
    }]
}

I tried this but the result is not right:

IMongoQuery query = Query.And(Query.EQ("memberinfo.status", "approved"));
MongoCursor mongocursor = nsdb.GetCollection(DBPrefix, "Member").Find(query);

Upvotes: 1

Views: 723

Answers (2)

Clement Amarnath
Clement Amarnath

Reputation: 5466

A solution using projection and positional operator is provided by Vince.

An alternate solution using aggregration pipeline together with $unwind and $match is given below

$unwind - this is used to break down the array elements into separate documents

Let us have the collection with the documents 

db.collection.find()

    {
    "_id" : "123",
    "memberinfo" : [{
        "height": 170,
        "weight": 55,
        "status": "approved",
        "fruits" : [ {"name" : "apple","reason" : null },
                     {"name" : "orange","reason" : null},
                     {"name" : "berry","reason" : null}
        ],
    }]
},
{
    "_id" : "456",
    "memberinfo" : [{
        "height" : 160,
        "weight": 90,
        "status": "approved",
        "fruits" : [ {"name" : "berry","reason" : null},
                     {"name" : "orange","reason" : null}
        ],
    },{
        "height" : 160,
        "weight": 90,
        "status": "rejected",
        "fruits" : [ {"name" : "banana","reason" : null}
        ],
    }]
}

After using $unwind

db.collection.aggregate([{$unwind: "$memberinfo"}])

{
        "_id" : "123",
        "memberinfo" : {
                "height" : 170,
                "weight" : 55,
                "status" : "approved",
                "fruits" : [
                        {
                                "name" : "apple",
                                "reason" : null
                        },
                        {
                                "name" : "orange",
                                "reason" : null
                        },
                        {
                                "name" : "berry",
                                "reason" : null
                        }
                ]
        }
}
{
        "_id" : "456",
        "memberinfo" : {
                "height" : 160,
                "weight" : 90,
                "status" : "approved",
                "fruits" : [
                        {
                                "name" : "berry",
                                "reason" : null
                        },
                        {
                                "name" : "orange",
                                "reason" : null
                        }
                ]
        }
}
{
        "_id" : "456",
        "memberinfo" : {
                "height" : 160,
                "weight" : 90,
                "status" : "rejected",
                "fruits" : [
                        {
                                "name" : "banana",
                                "reason" : null
                        }
                ]
        }
}

Now we have successfully unwinded the memberinfo array so that each document will have only one memberinfo.status

Now use $match in the aggregation pipeline to get the desired result

Final Mongo Shell Query: db.collection.aggregate([{$unwind: "$memberinfo"}, {$match:{"memberinfo.status":"approved"}}])

db.collection.aggregate([{$unwind: "$memberinfo"}, {$match:{"memberinfo.status":"approved"}}])

{
        "_id" : "123",
        "memberinfo" : {
                "height" : 170,
                "weight" : 55,
                "status" : "approved",
                "fruits" : [
                        {
                                "name" : "apple",
                                "reason" : null
                        },
                        {
                                "name" : "orange",
                                "reason" : null
                        },
                        {
                                "name" : "berry",
                                "reason" : null
                        }
                ]
        }
}
{
        "_id" : "456",
        "memberinfo" : {
                "height" : 160,
                "weight" : 90,
                "status" : "approved",
                "fruits" : [
                        {
                                "name" : "berry",
                                "reason" : null
                        },
                        {
                                "name" : "orange",
                                "reason" : null
                        }
                ]
        }
}

Hope it Helps!

References:

https://docs.mongodb.com/getting-started/csharp/aggregation/

http://mikaelkoskinen.net/post/mongodb-aggregation-framework-examples-in-c

Upvotes: -1

Vince Bowdren
Vince Bowdren

Reputation: 9208

The query is quite straightforward; you need to specify a projection using the $ positional operator to make sure your query only returns the array items which matched the finding query:

db.Member.find(
  { "memberinfo.status" : "approved" },
  {
    "_id" : 1, 
    "memberinfo.$" : 1
  }
);

Note: this is the native MongoDB version of the query; I'm not familiar with how this should be generated with the C# driver I'm afraid.

C# Update - Elemmatch version ( Works same as positional operator ) [credit to Veerlam].

This solution works when you only have one approved status element in each memberinfo array.

IMongoFields fields = Fields.Include("_id").ElemMatch("memberinfo", Query.EQ("status", "approved"));
MongoCursor mongocursor = nsdb.GetCollection(DBPrefix, "Member").Find((Query.Empty).SetFields(fields);

Upvotes: 2

Related Questions