Question 191101 in Mongodb Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    This doesn’t have to be done in aggregation. Particularly if the matchingLookups field is the only field you are interested in (or there are few others), then you can do this with find() instead if you prefer:

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

    Note that I’ve retained and simplified the query portion of the command. This will result in only documents that have matches being returned (as opposed to some documents coming back with empty lists). An index on that field would also allow the command to execute efficiently.

    If you did want more fields then it would be better to use aggregation, swapping the $project stage for an $addFields. Still probably best to prepend it with a $match to filter the documents (prior to filtering the arrays within them) if needed.

    Playground demonstration here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search