How do I match an array of sub-documents in MongoDB?

Question

Match documents if a value in an array of sub-documents is greater than some value only if the same document contains a field that is equal to some value

I have a collection that contains documents with an array of sub-documents. This array of sub-documents contains a field that dictates whether or not I can filter the documents in the collection based on another field in the sub-document. This'll make more sense when you see an example of the document.

    {  
    "_id":"ObjectId('XXX')",
    "Data":{  
        "A":"",
        "B":"-25.78562 ; 28.35629",
        "C":"165"
    },
    "SubDocuments":[  
        {  
            "_id":"ObjectId('XXX')",
            "Data":{  
                "Value":"XXX",
                "DataFieldId":"B"
            }
        },
        {  
            "_id":"ObjectId('XXX')",
            "Data":{  
                "Value":"",
                "DataFieldId":"A"
            }
        },
        {  
            "_id":"ObjectId('XXX')",
            "Data":{  
                "Value":"105",
                "DataFieldId":"Z"
            }
        }
    ]
}

I only want to match documents that contain sub-documents with a DataFieldId that is equal to Z but also filter for Values that are greater than 105 only if Data Field Id is equal to Z.

@Jitendra I'm filtering a list of the documents above based on Data.DataFieldId and Data.Value. It's part of an aggregation pipeline... — Faiyaaz Khan, Apr 12 '19 at 14:08
Please confirm that you want "DataFieldId" is equal to "Z" AND "Value" greater than 105. — Jitendra, Apr 12 '19 at 14:52

score 1 · Answer 1 · answered Apr 12 '19 at 14:48

Try as below:

db.collection.aggregate([
{
    $project: {
        _id:1,
        Data:1,
        filteredSubDocuments: {
            $filter: {
                input: "$SubDocuments",
                as: "subDoc",
                cond: { 
                    $and: [
                        { $eq: ["$$subDoc.Data.DataFieldId", "Z"] },
                        { $gte: ["$$subDoc.Data.Value", 105] }
                    ]
                }
            }
        }
    }
}
])

Resulted response will be:

{
    "_id" : ObjectId("5cb09659952e3a179190d998"),
    "Data" : {
        "A" : "",
        "B" : "-25.78562 ; 28.35629",
        "C" : "165"
    },
    "filteredSubDocuments" : [
        {
            "_id" : "ObjectId('XXX')",
            "Data" : {
                "Value" : 105,
                "DataFieldId" : "Z"
            }
        }
    ]
}

Thanks @Jitendra. This isn't what I'm trying to do though. I'd like to filter many of the parent documents (the documents with _id and Data.A, Data.B, Data.C) based on the subdocuments that contain Data.DataFieldId = "Z" and have a Data.Value greater than 105. I'll try and read up a bit more on the filter command and see if I can make it work. Thanks for the help and sorry for the late reply. — Faiyaaz Khan, Apr 15 '19 at 06:35
You're welcome @FaiyaazKhan. So you can get the idea from here about MongoDB $filter pipeline stage now as per your requirement you can apply various $and and $or combinations in it to get your desired results. — Jitendra, Apr 15 '19 at 06:41

score 0 · Answer 2 · answered Apr 14 '19 at 09:15

0

This can be done by using the $elemMatch operator on sub-documents, for details you can click on provided link. For your problem you can try below query by using $elemMatch which is match simpler than aggregation:

    db.collectionName.find({
    "SubDocuments": { 
        $elemMatch: {
            "Data.DataFieldId": "Z" , 
            "Data.Value" : {$gte: 105}  
        } 
    } })

Its working fine, I have verified it locally, one modification you required is that you have to put the value of SubDocuments.Data.Value as Number or Long as per your requirements.

answered Apr 14 '19 at 09:15

krishna Prasad

3,541
1
34
44

Thanks @krishna, this isn't what I'm trying to do though. I'd like to filter many of the parent documents (the documents with _id and Data.A, Data.B, Data.C) based on the subdocuments that contain Data.DataFieldId = "Z" and have a Data.Value greater than 105. I'll have a look at the $elemMatch command and see if it's capable of doing what I require. Thanks for the help. – Faiyaaz Khan Apr 15 '19 at 06:37
You're welcome, even you can try the `$elemMatch` in aggregation pipeline, for reference check the ans of question: https://stackoverflow.com/questions/47825790/aggregate-documents-where-objects-in-array-matches-multiple-conditions/ – krishna Prasad Apr 15 '19 at 08:11

How do I match an array of sub-documents in MongoDB?

2 Answers2