CODEWITHSUNDEEP
mongodbaggregation-framework
Michael Kitchell
Michael Kitchell

Reputation: 119

Nested $addFields in MongoDB

I have the following document:

[
  {
    "callId": "17dac51e-125e-499e-9064-f20bd3b1a9d8",
    "caller": {
      "firstName": "Test",
      "lastName": "Testing",
      "phoneNumber": "1231231234"
    },
    "inquiries": [
      {
        "inquiryId": "b0d14381-ce75-49aa-a66a-c36ae20b72a8",
        "routeHistory": [
          {
            "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
            "routeDate": "2020-01-01T06:00:00.000Z",
            "status": "routed"
          },
          {
            "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
            "routeDate": "2020-01-03T06:00:00.000Z",
            "status": "routed"
          }
        ]
      },
      {
        "inquiryId": "9d743be9-7613-46d7-8f9b-a04b4b899b56",
        "routeHistory": [
          {
            "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
            "routeDate": "2020-01-01T06:00:00.000Z",
            "status": "ended"
          },
          {
            "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
            "routeDate": "2020-01-03T06:00:00.000Z",
            "status": "ended"
          }
        ]
      }
    ]
  }
]

And I'm using the following aggregate:

{
  $unwind: '$inquiries',
},
{
  $addFields: {
    'inquiries.routeHistory': {
      $filter: {
        input: '$inquiries.routeHistory',
        cond: {
          $eq: [{ $max: '$inquiries.routeHistory.routeDate' }, '$$this.routeDate'],
        },
      },
    },
  },
},
{
  $group: {
    _id: '$_id',
    callId: { $first: '$callId' },
    caller: { $first: '$caller' },
    inquiries: { $push: '$inquiries' },
  },
}

I would like to expand this query to be able to further filter at the inquiry grain, so that I am returning only the inquiry that contains my specified criteria. E.g. if I wanted to find where inquiry.routeHistory.status = ended, I would expect the following results:

[
  {
    "callId": "17dac51e-125e-499e-9064-f20bd3b1a9d8",
    "caller": {
      "firstName": "Test",
      "lastName": "Testing",
      "phoneNumber": "1231231234"
    },
    "inquiries": [
      {
        "inquiryId": "9d743be9-7613-46d7-8f9b-a04b4b899b56",
        "routeHistory": [
          {
            "assignedUserId": "cfa0ffe9-c77d-4eec-87d7-4430f7772e81",
            "routeDate": "2020-01-03T06:00:00.000Z",
            "status": "ended"
          }
        ]
      }
    ]
  }
]

Is there a way to do nested $addField or is there another route I could take?

Upvotes: 2

Views: 3421

Answers (1)

mickl
mickl

Reputation: 49945

Since you're using $unwind you can do that easily by adding $match since expression: "inquiries.routeHistory.status": "ended" will return true if there's any document in routeHistory having such status:

db.collection.aggregate([
    {
        $unwind: "$inquiries"
    },
    {
        $match: {
            "inquiries.routeHistory.status": "ended"
        }
    },
    {
        $addFields: {
            "inquiries.routeHistory": {
                $filter: {
                    input: "$inquiries.routeHistory",
                    cond: {
                        $eq: [ { $max: "$inquiries.routeHistory.routeDate" }, "$$this.routeDate" ]
                    }
                }
            }
        }
    },
    {
        $group: {
            _id: "$_id",
            callId: { $first: "$callId" },
            caller: { $first: "$caller" },
            inquiries: { $push: "$inquiries" }
        }
    }
])

Mongo Playground

Upvotes: 2

Related Questions