How to compare two object elements in a mongodb array

Question

{
  "customerSchemes": [
    {
      "name": "A",
      "startDate": some date in valid date format
    },
    {
      "name": "B",
      "startDate": some date in valid date format.
    }
  ]
}

I am trying to figure out all documents where scheme A started before scheme B. Please note that the scheme Array is not in ascending order of startDate. Plan B can have an earlier date as compared to plan A. I believe unwind operator could be of some use here but not sure how to progress with next steps.

Answer 1

aggregate():

• $filter to filter name: "A" from customerSchemes
• $arrayElemAt to get first element from filtered result from above step
• same steps like above for name: "B"
• $let to declare variables for "A" in a and "B" in b
• in to check condition from above variables if a's startDate is greater than b's startDate then return true otherwise false
• $expr expression match with $eq to match above process, if its true then return document

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $eq: [
          {
            $let: {
              vars: {
                a: {
                  $arrayElemAt: [
                    {
                      $filter: {
                        input: "$customerSchemes",
                        cond: { $eq: ["$$this.name", "A"] }
                      }
                    },
                    0
                  ]
                },
                b: {
                  $arrayElemAt: [
                    {
                      $filter: {
                        input: "$customerSchemes",
                        cond: { $eq: ["$$this.name", "B" ] }
                      }
                    },
                    0
                  ]
                }
              },
              in: { $gt: ["$$a.startDate", "$$b.startDate"] }
            }
          },
          true
        ]
      }
    }
  }
])

Playground

---

find():

You can use above match stage expression condition in find() query as well without any aggregation pipeline,

Playground

latest support hint: if you are using latest(4.4) MongoDB version then you can use $first instead of $arrayElemAt, see Playground

Answer 2

You could use $unwind array and format the elements for comparison effectively transforming into key value pair. This assumes you only have two array values so I didn't know apply any filtering.

Something like

db.colname.aggregate(
[
  {"$unwind":"$customerSchemes"},
  {"$group":{
    "_id":"$_id",
    "data":{"$push":"$$ROOT"},
    "fields":{
      "$mergeObjects":{
        "$arrayToObject":[[["$customerSchemes.name","$customerSchemes.startDate"]]]
      }
    }
  }},
  {"$match":{"$expr":{"$lt":["$fields.A","$fields.B"]}}},
  {"$project":{"_id":0,"data":1}}
])

Working example here - https://mongoplayground.net/p/mSmAXHm0-o-

Using $reduce

db.colname.aggregate(
[
  {"$addFields":{
    "fields":{
      "$reduce":{
        "input":"$customerSchemes",
        "initialValue":{},
        "in":{
          "$mergeObjects":[
            {"$arrayToObject":[[["$$this.name","$$this.startDate"]]]},
            "$$value"]
        }
      }
    }
  }},
  {"$match":{"$expr":{"$lt":["$fields.A","$fields.B"]}}},
  {"$project":{"fields":0}}
])

Working example here - https://mongoplayground.net/p/WNxbScI9N9b

Answer 3

So the idea is

1. Sort the customerSchemes array by startDate.
2. Pick the first item from the sorted list.
3. Include it only if the customerSchemes.name is A.

Try this query:

db.collection.aggregate([
    { $unwind: "$customerSchemes" },
    {
        $sort: { "customerSchemes.startDate": 1 }
    },
    {
        $group: {
            _id: "$_id",
            customerSchemes: { $push: "$customerSchemes" }
        }
    },
    {
        $match: {
            $expr: {
                $eq: [{ $first: "$customerSchemes.name" }, "A"]
            }
        }
    }
]);

Output:

/* 1 createdAt:3/12/2021, 6:40:42 PM*/
{
    "_id" : ObjectId("604b685232a8d433d8ede6c4"),
    "customerSchemes" : [
        {
            "name" : "A",
            "startDate" : ISODate("2021-03-01T00:00:00.000+05:30")
        },
        {
            "name" : "B",
            "startDate" : ISODate("2021-03-02T00:00:00.000+05:30")
        }
    ]
},

/* 2 createdAt:3/12/2021, 6:40:42 PM*/
{
    "_id" : ObjectId("604b685232a8d433d8ede6c6"),
    "customerSchemes" : [
        {
            "name" : "A",
            "startDate" : ISODate("2021-03-01T00:00:00.000+05:30")
        },
        {
            "name" : "B",
            "startDate" : ISODate("2021-03-05T00:00:00.000+05:30")
        }
    ]
}

Test data:

/* 1 createdAt:3/12/2021, 6:40:42 PM*/
{
    "_id" : ObjectId("604b685232a8d433d8ede6c4"),
    "customerSchemes" : [
        {
            "name" : "A",
            "startDate" : ISODate("2021-03-01T00:00:00.000+05:30")
        },
        {
            "name" : "B",
            "startDate" : ISODate("2021-03-02T00:00:00.000+05:30")
        }
    ]
},

/* 2 createdAt:3/12/2021, 6:40:42 PM*/
{
    "_id" : ObjectId("604b685232a8d433d8ede6c5"),
    "customerSchemes" : [
        {
            "name" : "A",
            "startDate" : ISODate("2021-03-03T00:00:00.000+05:30")
        },
        {
            "name" : "B",
            "startDate" : ISODate("2021-03-02T00:00:00.000+05:30")
        }
    ]
},

/* 3 createdAt:3/12/2021, 6:40:42 PM*/
{
    "_id" : ObjectId("604b685232a8d433d8ede6c6"),
    "customerSchemes" : [
        {
            "name" : "B",
            "startDate" : ISODate("2021-03-05T00:00:00.000+05:30")
        },
        {
            "name" : "A",
            "startDate" : ISODate("2021-03-01T00:00:00.000+05:30")
        }
    ]
}