MongoDB Query for documents in array .NET MVC

Question

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);

Possible duplicate of Projecting only matched sub-documents along with original document fields mongodb C# — s7vr
– s7vr, Commented Mar 27, 2017 at 12:19
@Veeram saw your answer in that post, but i not understand why using so many BsonDocument — FeelRightz
– FeelRightz, Commented Mar 27, 2017 at 14:05
BsonDocument is equivalent for key value pair. Example {"key":"value"} in C# written as new BsonDocument { { "key", "value" } } — s7vr
– s7vr, Commented Mar 27, 2017 at 14:09
@Veeram thanks, but what u show to me , is not im looking for — FeelRightz
– FeelRightz, Commented Mar 27, 2017 at 14:12
Np. What are you looking for ? What driver version do you use ? — s7vr
– s7vr, Commented Mar 27, 2017 at 14:14

Vince Bowdren · Accepted Answer · 2017-03-28 08:17:16Z

2

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);

edited Mar 28, 2017 at 8:17

answered Mar 27, 2017 at 15:58

Vince Bowdren

9,2853 gold badges37 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Clement Amarnath · Accepted Answer · 2017-03-28 08:24:11Z

-1

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

edited Mar 28, 2017 at 8:24

answered Mar 27, 2017 at 10:52

Clement Amarnath

5,4761 gold badge23 silver badges34 bronze badges

8 Comments

FeelRightz Over a year ago

how can the aggregrate , $unwind and $match do in asp.net using ImongoQuery ?

FeelRightz Over a year ago

because i using Imongoquery for whole web application, i cant follow the code that u suggested, sorry but thanks

Clement Amarnath Over a year ago

I have given the Mongo Shell query, i'm not familiar with asp.net, please convert the shell query into your technology. This post has some good examples mikaelkoskinen.net/post/…

FeelRightz Over a year ago

I also new in mongodb i have no idea how to convert

Clement Amarnath Over a year ago

I have added few references which can give you some idea in using the aggregation in C#. I hope by referring these posts you should be able to convert the shell query into your required format.

|

Collectives™ on Stack Overflow

MongoDB Query for documents in array .NET MVC

2 Answers 2

Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related