CODEWITHSUNDEEP
arraysmongodbmongodb-queryaggregation-framework
Stephanie Coy
Stephanie Coy

Reputation: 3

Mongo: querying array that contains objectId equal to the document id

I have a document where an array, internalProps, contains ObjectIds. The array could contain the document's own _id value. I need to be able to return all documents where the array exists, is not empty, and does NOT contain the _id value, but I haven't been able to form a query that will return this subset of data.

Documents:

{
  _id: ObjectId('669fdc567085fd4c423449b9'),
  name: 'doc1'
  internalProp: [
    ObjectId('669fdc567085fd4c423449b9'),
    ObjectId('6661ebe9a857d3b793b47f3d')
  ]
},
{
  _id: ObjectId('669fdbdb7085fd4c42344912'),
  name: 'doc2'
  internalProp: [
    ObjectId('66745161dcda75cb07172681'),
    ObjectId('6661ece57861b00aed91e14f')
  ]
},
{
  _id: ObjectId('669d121fda13e81f58afc8b6'),
  name: 'doc3',
  internalProp: [
    ObjectId('669fdc567085fd4c423449b9')
  ]
}

I can return documents where the array exists and is not empty with the first 2 parts of the $elemMatch below, but I haven't been able to match the _id property with the third part no matter how I format it.

db.modules.find({ internalProp: { $elemMatch: { $exists: true, $ne: [], $nin: ["$_id", ["$_id"]] } } })

I would expect this to return doc2 and doc3 from the above data because doc1's _id value is contained in doc1's internalProp array.

I've tried using $nin as well as various forms of aggregation pipeline, but I consistently return 0 documents when I add in trying to match the _id. Is there a specific way I should be referencing the document _id other than '$_id'?

Upvotes: 0

Views: 19

Answers (1)

ray
ray

Reputation: 15227

Use $ifNull to handle the first 2 conditions and chain up the conditions in an $and in $expr.

db.collection.find({
  "$expr": {
    "$and": [
      {
        "$ne": [
          {
            "$ifNull": [
              "$internalProp",
              []
            ]
          },
          []
        ]
      },
      {
        "$not": {
          "$in": [
            "$_id",
            "$internalProp"
          ]
        }
      }
    ]
  }
})

Mongo Playground

Upvotes: 0

Related Questions