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.

you should store dates not as string but as Date object. There is really no reason for storing dates as string — Tal Rofe
– Tal Rofe, Commented Mar 8, 2021 at 21:54
You can try using the $reduce operator to return a boolean based upon the comparison. — prasad_
– prasad_, Commented Mar 9, 2021 at 3:47
How many elements (or schemes) are likely to be there in the customerSchemes array? What are Plan A and Plan B - are they same as the scheme A and B? Are there any other schemes other than A and B? — prasad_
– prasad_, Commented Mar 11, 2021 at 3:39

turivishal · Accepted Answer · 2021-03-11 17:30:29Z

4

+300

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

answered Mar 11, 2021 at 17:30

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

turivishal Over a year ago

the answer is flexible you can use $lt instead of $gt in in of $let, there are two meanings of term earlier, its up to OP's requirement.

Haniel Baez · Accepted Answer · 2021-03-12 06:38:04Z

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

Dheemanth Bhat · Accepted Answer · 2021-03-14 22:35:06Z

So the idea is

Sort the customerSchemes array by startDate.
Pick the first item from the sorted list.
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")
        }
    ]
}

Collectives™ on Stack Overflow

How to compare two object elements in a mongodb array

3 Answers 3

aggregate():

find():

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

aggregate():

find():

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related