Mongodb: How to get a list of subcollection

Question

I have a member collection with following data:

db.member.insert(
{
    userName: "TanNM",
    password: "xxx",
    wantList: [{
        title: "Want 1.1 - HN",
        description: "Want 1.1 description",
        province:{
            name: "Ha Noi",
            districtList:[ { name: "Ha Dong", qty: 25 }, { name: "Ba Dinh", qty: 50 } , { name: "Cau Giay", qty: 25 }, { name: "Hoan Kiem", qty: 50 } ]
        }
    }, {
        title: "Want 1.2 - HN",
        description: "Want 1.2 description",
        province:{
            name: "SG",
            districtList:[ { name: "Ha Dong", qty: 25 }, { name: "Ba Dinh", qty: 50 } , { name: "Cau Giay", qty: 25 }, { name: "Hoan Kiem", qty: 50 } ]
        }
    }],
    stock: [ { size: "S", qty: 25 }, { size: "M", qty: 50 } ],
    category: "clothing"
})

db.member.insert(
{
    userName: "MinhNN",
    password: "xxx",
    wantList: [{
        title: "Want 2.1 - HN",
        description: "Want 2.1 description",
        province:{
            name: "Ha Noi",
            districtList:[ { name: "Ha Dong", qty: 25 }, { name: "Ba Dinh", qty: 50 } , { name: "Cau Giay", qty: 25 }, { name: "Hoan Kiem", qty: 50 } ]
        }
    }, {title: "Want 2.2 - HN",
        description: "Want 2.2 description",
        province:{
            name: "Ha Noi",
            districtList:[ { name: "Ha Dong", qty: 25 }, { name: "Ba Dinh", qty: 50 } , { name: "Cau Giay", qty: 25 }, { name: "Hoan Kiem", qty: 50 } ]
        }
    }],
    stock: [ { size: "S", qty: 25 }, { size: "M", qty: 50 } ],
    category: "clothing"
})

db.member.insert(
{
    userName: "DungNP",
    password: "xxx",
    wantList: {
        title: "Want 3 - SG",
        description: "Want 3 description",
        province:{
            name: "TP Ho Chi Minh",
            districtList:[ { name: "Ha Dong", qty: 25 }, { name: "Ba Dinh", qty: 50 } , { name: "Cau Giay", qty: 25 }, { name: "Hoan Kiem", qty: 50 } ]
        }
    },
    stock: [ { size: "S", qty: 25 }, { size: "M", qty: 50 } ],
    category: "clothing"
})

Member have some Want (wantList), Want in a province/district.

How to get all "want" (Not all document) of all member with province.name is "Ha Noi"

Hi! I've deleted my answer as it doesn't solve your issue. I finally got you mean, which is a bit tricky for me at the moment. When I have time I will try to dig a bit on this, but I hope someone will answer your question. :) Good luck! — Askar
– Askar, Commented Nov 5, 2015 at 0:05

suecarmol · Accepted Answer · 2015-12-01 04:48:02Z

2

I think the answer you are looking for is this

db.member.find({"wantList.province.name": "Ha Noi"});

If you only need to output the "wantList", you should try this query

db.member.find({"wantList.province.name": "Ha Noi"}, {"wantList": 1});

This will only output the "wantList" array where one of the provinces equals "Ha Noi". I hope this was the answer you were looking for.

The second query returns the following:

 { "_id" : ObjectId("56568bbd2688b376a56878c5"), "wantList" : [ { "title" : "Want 1.1 - HN", "description" : "Want 1.1 description", "province" : { "name" : "Ha Noi", "districtList" : [ { "name" : "Ha Dong", "qty" : 25 }, { "name" : "Ba Dinh", "qty" : 50 }, { "name" : "Cau Giay", "qty" : 25 }, { "name" : "Hoan Kiem", "qty" : 50 } ] } }, { "title" : "Want 1.2 - HN", "description" : "Want 1.2 description", "province" : { "name" : "SG", "districtList" : [ { "name" : "Ha Dong", "qty" : 25 }, { "name" : "Ba Dinh", "qty" : 50 }, { "name" : "Cau Giay", "qty" : 25 }, { "name" : "Hoan Kiem", "qty" : 50 } ] } } ] }

{ "_id" : ObjectId("56568bc72688b376a56878c6"), "wantList" : [ { "title" : "Want 2.1 - HN", "description" : "Want 2.1 description", "province" : { "name" : "Ha Noi", "districtList" : [ { "name" : "Ha Dong", "qty" : 25 }, { "name" : "Ba Dinh", "qty" : 50 }, { "name" : "Cau Giay", "qty" : 25 }, { "name" : "Hoan Kiem", "qty" : 50 } ] } }, { "title" : "Want 2.2 - HN", "description" : "Want 2.2 description", "province" : { "name" : "Ha Noi", "districtList" : [ { "name" : "Ha Dong", "qty" : 25 }, { "name" : "Ba Dinh", "qty" : 50 }, { "name" : "Cau Giay", "qty" : 25 }, { "name" : "Hoan Kiem", "qty" : 50 } ] } } ] }

EDIT:

I think you are looking for this answer:

db.member.find(
  {"wantList.province.name": "Ha Noi"}, 
  {"wantList": {"$elemMatch": {"province.name": "Ha Noi"}}}
);

Which returns:

{ "_id" : ObjectId("56568bbd2688b376a56878c5"), "wantList" : [ { "title" : "Want 1.1 - HN", "description" : "Want 1.1 description", "province" : { "name" : "Ha Noi", "districtList" : [ { "name" : "Ha Dong", "qty" : 25 }, { "name" : "Ba Dinh", "qty" : 50 }, { "name" : "Cau Giay", "qty" : 25 }, { "name" : "Hoan Kiem", "qty" : 50 } ] } } ] }
{ "_id" : ObjectId("56568bc72688b376a56878c6"), "wantList" : [ { "title" : "Want 2.1 - HN", "description" : "Want 2.1 description", "province" : { "name" : "Ha Noi", "districtList" : [ { "name" : "Ha Dong", "qty" : 25 }, { "name" : "Ba Dinh", "qty" : 50 }, { "name" : "Cau Giay", "qty" : 25 }, { "name" : "Hoan Kiem", "qty" : 50 } ] } } ] }

If you want to remove the _id you just input the following query:

db.member.find(
  {"wantList.province.name": "Ha Noi"}, 
  {"wantList": {"$elemMatch": {"province.name": "Ha Noi"}}, "_id": 0}
);

EDIT 2:

I think aggregation is needed to solve your problem. Try this query.

db.member.aggregate([
  {"$match": {"wantList.province.name": "Ha Noi"}}, 
  {"$unwind": "$wantList"}, 
  {"$match": {"wantList.province.name": "Ha Noi"}}, 
  {"$project": {"_id": 0, "wantList": 1}}
]);

Which returns:

{ "wantList" : { "title" : "Want 1.1 - HN", "description" : "Want 1.1 description", "province" : { "name" : "Ha Noi", "districtList" : [ { "name" : "Ha Dong", "qty" : 25 }, { "name" : "Ba Dinh", "qty" : 50 }, { "name" : "Cau Giay", "qty" : 25 }, { "name" : "Hoan Kiem", "qty" : 50 } ] } } }
{ "wantList" : { "title" : "Want 2.1 - HN", "description" : "Want 2.1 description", "province" : { "name" : "Ha Noi", "districtList" : [ { "name" : "Ha Dong", "qty" : 25 }, { "name" : "Ba Dinh", "qty" : 50 }, { "name" : "Cau Giay", "qty" : 25 }, { "name" : "Hoan Kiem", "qty" : 50 } ] } } }

{ "wantList" : { "title" : "Want 2.2 - HN", "description" : "Want 2.2 description", "province" : { "name" : "Ha Noi", "districtList" : [ { "name" : "Ha Dong", "qty" : 25 }, { "name" : "Ba Dinh", "qty" : 50 }, { "name" : "Cau Giay", "qty" : 25 }, { "name" : "Hoan Kiem", "qty" : 50 } ] } } }

I think this is the answer you are looking for.

edited Dec 1, 2015 at 4:48

answered Nov 25, 2015 at 4:51

suecarmol

4679 silver badges18 bronze badges

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

9 Comments

Tnadev Over a year ago

No I have checked its not answering question, Have you verified result?

suecarmol Over a year ago

Why is it not the answer. The last query only returns the wantList where the province name is Ha Noi. Could you specify why this isn't the right answer?

Tnadev Over a year ago

I might be wrong sorry, but will you please add result of that query in answer.

suecarmol Over a year ago

Just added the answers. Let me know if that is the answer you are looking for or give an example of what you want your answer to look like.

Parabol Over a year ago

Hi @suecarmol, i tested your answer. But it didn't show wantList with wantList.title : "Want 2.2 - HN". Maybe it was missing a little something. I almost have the answer for my question.

|

Collectives™ on Stack Overflow

Mongodb: How to get a list of subcollection

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related