comparing two fields in a nested array

Question

{
    "_id" : ObjectId("62622dd73905f04f59db2971"),
    "array1" : [ 
        {
            "_id" : "12",
            "array2" : [ 
                {
                    "_id" : "123",
                    "answeredBy" : []
                }, 
                {
                    "_id" : "124",
                    "answeredBy" : []
                }
            ]
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("626230e03905f04f59db29f5"),
    "array1" : [ 
        {
            "_id" : "22",
            "array2" : [ 
                {
                    "_id" : "223", //compare this
                    "answeredBy" : []
                }, 
                {
                    "_id" : "220", // and this
                    "answeredBy" : []
                }
            ]
        }
    ]
}

I am trying to compare array2 zero index _id with one index _id

My query is

db.getCollection('nestedArray').find({$expr:{$gt:['$array1.array2.0._id','$array1.array2.1._id']}})

showing zero records

YuTing · Accepted Answer · 2022-04-22 05:21:09Z

1

Use $arrayElemAt

db.collection.find({
  $expr: {
    $gt: [
      {
        $arrayElemAt: [ { $arrayElemAt: [ "$array1.array2._id", 0 ] }, 0 ]
      },
      {
        $arrayElemAt: [ { $arrayElemAt: [ "$array1.array2._id", 0 ] }, 1 ]
      }
    ]
  }
})

mongoplayground

If you use index, it is a different situation. Check mongoplayground below.

db.collection.find({
  $expr: {
    $gt: [
      "$array1.0.array2.0._id",
      "$array1.0.array2.1._id"
    ]
  }
})

mongoplayground

edited Apr 22, 2022 at 5:21

answered Apr 22, 2022 at 5:15

YuTing

6,6692 gold badges8 silver badges19 bronze badges

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

1 Comment

Sugumar Venkatesan Over a year ago

Explanation would be helpful, not understandable how this works..

Collectives™ on Stack Overflow

comparing two fields in a nested array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related