The following query yields a different result in MongoDB and its Oracle emulation (tested on 23.9.0.25.08).
I have the following documents:
ora> db.dccomics.find()
[
{
_id: ObjectId('689709653f69ce62e0c59f37'),
contact: [
{ email: [ 'bruce@wayneenterprises.com', 'batman@batcave.com' ] }
]
},
{
_id: ObjectId('689709653f69ce62e0c59f38'),
contact: [ { email: 'alfred@wayneenterprises.com' } ]
},
{
_id: ObjectId('689709653f69ce62e0c59f39'),
contact: [ { email: [ 'catwoman@gotham.com' ] } ]
}
]
This query returns all documents, including the one that has no array for “email”:
ora> db.dccomics.find({
"contact.email": { $elemMatch: { $gte: "a" } }
})
[
{
_id: ObjectId('689709653f69ce62e0c59f37'),
contact: [
{ email: [ 'bruce@wayneenterprises.com', 'batman@batcave.com' ] }
]
},
{
_id: ObjectId('689709653f69ce62e0c59f38'),
contact: [ { email: 'alfred@wayneenterprises.com' } ]
},
{
_id: ObjectId('689709653f69ce62e0c59f39'),
contact: [ { email: [ 'catwoman@gotham.com' ] } ]
}
]
The same in MongoDB returns only the two documents with an array:
https://mongoplayground.net/p/prTdRkXLYJm
The documentation says:
$elemMatch only matches array elements