Trying to select a single record from MongoDB using findOne searching for a value in a nested array

Question

I've got a collection in MongoDB (6.0.2 community edition) called VS_Logs. In there is an array of objects looking a bit like this:

Lookups: [
{ REG: "ABC", .... // other stuff},
{ REG: "123", .... // other stuff} etc...
]

I'm trying to select the object from Lookups where REG = ABC.

I've tried this:

db.VS_Logs.findOne({"Lookups" : {$elemMatch: {"REG": "ABC" }}})
db.VS_Logs.find({"Lookups" : {$elemMatch: {"REG": "ABC" }}})

Both of those return all records.

I also tried:

db.VS_Logs.findOne({"Lookups.REG": "ABC"})
db.VS_Logs.find({"Lookups.REG": "ABC"})

Same result. What am I doing wrong?

Answer 1

db.VS_Logs.find({"Lookups.REG": "ABC"})
This query will retrieve all documents in the database where there is at least one object in the Lookups array whose REG property is set to AB. It doesn't matter if there are other objects in the array that don't meet this condition.
Instead of the entire document, if you are trying to select only those objects from Lookups array where REG = ABC. Try this,

db.collection.aggregate([
  {
    $project: {
      matchingLookups: {
        $filter: {
          input: "$Lookups",
          as: "lookup",
          cond: {
            $eq: [
              "$$lookup.REG",
              "ABC"
            ]
          }
        }
      }
    }
  }
])

This aggregation pipeline will return the documents with an array called matchingLookups that only contains the objects from the Lookups array where the REG property is set to ABC. The result would look somewhat like this,

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "matchingLookups": [
      {
        "REG": "ABC"
      }
    ]
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "matchingLookups": []
  }
]

For Reference: https://mongoplayground.net/p/8C9Rz1rT0Zh