mongodb return just the subdocument by querying the field

Question

I have a structure like this:

{
"_id" : ObjectId("5b155aa3e7179a6034c6dd5b"),
"pinnedKpi" : {
    "Ver01" : [
        "130",
        "138",
        "122",
        "134"
    ],
    "Ver02" : [
        "265",
        "263",
        "142",
        "264"
    ],
    "Ver03" : [ ],
    "Ver04" : [
        "126",
        "134",
        "122",
        "138"
    ]
},
"email" : "john@doe.ca",

Is it possible to do a query like return just the array where email = john@doe.ca and pinnedKpi.Ver01 ---> ["130","138","122","134"]

I want to be able to say return the array where "email" : "john@doe.ca", and pinnkedKpi is ver01 — JohnSnow, Jul 05 '18 at 17:58

score 1 · Accepted Answer · answered Jul 05 '18 at 18:00

1

Just use this:

db.collection.find({ // find all documents
    "email": "john@doe.ca" // where the "email" field equals some value
}, {
    "_id": 0, // do not return the "_id" field (included by default)
    "pinnedKpi.Ver01": 1 // only return the "pinnedKpi.Ver01" field
})

answered Jul 05 '18 at 18:00

dnickless

10,733
1
19
34

What is the significance and meaning of the 1 here -->"pinnedKpi.Ver01": 1 – JohnSnow Jul 05 '18 at 18:03
second part of the query ("_id": 0,"pinnedKpi.Ver01": 1) stand for projection instruction. 0 can be false, 1 can be true. Here "_id", included by default, is excluded, and pinnedKpi.Ver01 is manually included in projection. – matthPen Jul 05 '18 at 18:24
In this same example if I pass in a variable to it like `pinnedKpi.Ver0${version}` it doesn't work eventhough it console.logs the same string as if I hardcode it – JohnSnow Jul 05 '18 at 18:53
Can you just your attempt, please, so we can see what's wrong. Also, please let us know which language/client you use. – dnickless Jul 05 '18 at 19:29

score 0 · Answer 2 · answered Jul 05 '18 at 18:21

if array doesn't need to be the root element of response, you can use the following query to return pinnedKpi.Ver01 array with email criteria and presence of Ver01 element in pinnedKpi array :

    db.test1.find(
      {"email" : "john@doe.ca", "pinnedKpi.Ver01" : {"$exists" : true}},
    {"pinnedKpi.Ver01" : 1}
    );

Which output :

{ 
    "_id" : ObjectId("5b155aa3e7179a6034c6dd5b"), 
    "pinnedKpi" : {
        "Ver01" : [
            "130", 
            "138", 
            "122", 
            "134"
        ]
    }
}

If array result need to be the root element, you can use aggregation framework to achieve this :

db.test1.aggregate(
    [
        {
            $match: {email:"john@doe.ca","pinnedKpi.Ver01":{$exists:true}}
        },
        {
            $replaceRoot: {
            newRoot: "$pinnedKpi"
            }
        },
        {
            $project: {
               Ver01:1
            }
        },
    ]
);

Output :

{ 
    "Ver01" : [
        "130", 
        "138", 
        "122", 
        "134"
    ]
}

In this same example above yours if I pass in a variable to it like pinnedKpi.Ver0${version} it doesn't work eventhough it console.logs the same string as if I hardcode it .. HOw can I pass variables — JohnSnow, Jul 05 '18 at 19:07

mongodb return just the subdocument by querying the field

2 Answers2