JavaDeveloper
JavaDeveloper

Reputation: 5660

How to compare two object elements in a mongodb array

{
  "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.

Upvotes: 5

Views: 3365

Answers (3)

Dheemanth Bhat
Dheemanth Bhat

Reputation: 4452

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")
        }
    ]
}

Upvotes: 2

s7vr
s7vr

Reputation: 75984

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

Upvotes: 4

turivishal
turivishal

Reputation: 36144

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

Upvotes: 4

Related Questions