How to check if 2 elements inside an array of objects have the same value, in mongoDB?

Issue

My users collection data looks like this inside mongoDB:

_id: ObjectId,
sports: [
  {
    name: 'cricket',
    history: [
      {
        from: 10,
        to: 30
      },
      {
        from: 30,
        to: 30
      }
    ]
  },
  // ... other sports as well
]

What I’m trying to query for are all the users that have inside sports.history an matching element that satisfies this condition: from === to. (Users can have multiple sports each with it’s own history).
I’m trying to achieve this inside the query, not bring users inside my express app and filter them afterwards.

Any help is much appreciated. Thanks!

Solution

Using $expr operator, you can go about this using various array operators to query the collection. Consider first flattening the 2D array ‘$sports.history’ with $reduce:

 {
    $reduce: {
        input: "$sports.history",
        initialValue: [],
        in: { $concatArrays: [ "$$value", "$$this" ] }
    }
}

and filtering the reduced array on the given condition with $filter

{ $filter: {
    input: {
        $reduce: {
            input: "$sports.history",
            initialValue: [],
            in: { $concatArrays: [ "$$value", "$$this" ]
            }
        }
    },
    cond: {
        $eq: ['$$this.from', '$$this.to']
    }
} }

Check the length of the array resulting from the above expression using $size:

{ $size: { 
    { $filter: {
        input: {
            $reduce: {
                input: '$sports.history',
                initialValue: [],
                in: { $concatArrays: [ '$$value', '$$this' ] }
            }
        },
        cond: {
            $eq: ['$$this.from', '$$this.to']
        }
    } }
} }

If length of filtered array is greater than zero, then the user exists:

{ $gt: [
    { $size: { 
        $filter: {
            input: {
                $reduce: {
                    input: "$sports.history",
                    initialValue: [],
                    in: { $concatArrays: [ "$$value", "$$this" ] }
                }
            },
            cond: {
                $eq: ['$$this.from', '$$this.to']
            }
        }
    } },
    0
] }

Overall your final query should look like this:

db.users.find({
    $expr: {
        $gt: [
            { $size: {
                $filter: {
                    input: {
                        $reduce: {
                            input: "$sports.history",
                            initialValue: [],
                            in: { $concatArrays: [ "$$value", "$$this" ] }
                        }
                    },
                    cond: {
                        $eq: ['$$this.from', '$$this.to']
                    }
                }
            } },
            0
        ]
    }
})

Mongo Playground

Answered By – chridam

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0