Question

0

498

Views

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.

over 4 years ago · Santiago Trujillo

3 answers

Answer question

0

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

over 4 years ago · Santiago Trujillo Report

0

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

over 4 years ago · Santiago Trujillo Report

Answer question

Find remote jobs

Accepted Answer · 2022-05-13T14:30:24.409Z

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,