Mongo Aggregation and Filter

Question

I am trying to filter documents using the following query:

db.projects.aggregate([
    {
        $project: { 
            deployments: { 
                $filter: { 
                    input: "$releases.deployments", 
                    as: "deployment", 
                    cond: { $eq: ["$$deployment.environment", "Live"] }
                }
            }
        }
    }
])

The output for deployments is always an empty array although if I change the condition to $ne then it returns all of the results.

How do I get the filter condition to return only records where deployment.environment equals the string Live?

Here is a sample piece of json:

{
      "project_id": "1",
      "project_group": "A",
      "releases": [
        {
          "version": "1",
          "deployments": [
            {
              "environment": "Integration",
              "created": "2019-10-01T06:40:01.000Z",
              "state": "Success",
              "name": "Deploy to Integration"
            },
            {
              "environment": "Test",
              "created": "2019-10-01T08:23:58.000Z",
              "state": "Success",
              "name": "Deploy to Test"
            },
            {
              "environment": "Live",
              "created": "2019-10-01T09:02:17.000Z",
              "state": "Success",
              "name": "Deploy to Live"
            }
          ]
        }
      ]
    }

unwind releases before project and then group after project stage — sushant mehta, Oct 01 '19 at 12:12
@Neil How it's duplicate of https://stackoverflow.com/questions/29071748/find-in-double-nested-array-mongodb? — Himanshu Sharma, Oct 01 '19 at 12:59
@Mr.S.Sharma It's a very clear duplicate. "filter an embedded array" is exactly what the question asks. The OP should read it in order to see how it is actually done and taking in to consideration ALL conditions. You might even learn something there yourself. — Neil Lunn, Oct 02 '19 at 09:35

Himanshu Sharma · Accepted Answer · 2019-10-01T12:32:52.337

Your query would work perfectly if releases would be an embedded document but its an array of embedded documents.

The following query will iterate over each element of releases and filter deployments which have environment as Live.

db.collection.aggregate([
    {
        $project:{
            "releases":{
                $map:{
                    "input":"$releases",
                    "as":"release",
                    "in":{
                        $mergeObjects:[
                            "$$release",
                            {
                                "deployments":{
                                    $filter:{
                                        "input":"$$release.deployments",
                                        "as":"deployment",
                                        "cond":{
                                            $eq:["$$deployment.environment","Live"]
                                        }
                                    }
                                }
                            }
                        ]
                    }
                }
            }
        }
    },
    {
        $project:{
            "releases":{
                $filter:{
                    "input":"$releases",
                    "as":"release",
                    "cond":{
                        $ne:["$$release.deployments.0",null]
                    }
                }
            }
        }
    }
]).pretty()

Output:

{
    "_id" : ObjectId("5d93401ef2e6411a68a145ee"),
    "releases" : [
        {
            "version" : "1",
            "deployments" : [
                {
                    "environment" : "Live",
                    "created" : "2019-10-01T09:02:17.000Z",
                    "state" : "Success",
                    "name" : "Deploy to Live"
                }
            ]
        }
    ]
}

This works but it also includes a lot of empty records for example: { "deployments" : [ ], "version" : "1.0.66.63" } how do I keep those out of the resultset? — berimbolo, Oct 01 '19 at 12:27
We just need to add a filter on empty deployments. Let me update the query. — Himanshu Sharma, Oct 01 '19 at 12:28
Thanks, I still have the empty records but I am 100 miles ahead of where I was 10 minutes ago, which was going over the same piece of documentation and trying to work out why i got no results! — berimbolo, Oct 01 '19 at 12:36

score 0 · Answer 2 · answered Oct 01 '19 at 12:18

Try aggregate below:

db.projects.aggregate([
  {
    $unwind: "$releases"
  },
  {
    $project: {
      version: "$releases.version",
      deployments: {
        $filter: {
          input: "$releases.deployments",
          as: "deployment",
          cond: {
            $eq: [
              "$$deployment.environment",
              "Live"
            ]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      releases: {
        $push: {
          deployments: "$deployments",
          version: "$version"
        }
      }
    }
  }
])

Mongo Aggregation and Filter

2 Answers2