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

Mongodb – How to make array of objectIDs of a collection in mongo aggregate query and find IDs in another collection exists in that list

I need a query to find any documents exists in users collection which has groups._id which is not present in _id of groups collection

users collection – docs

    {
      "_id": ObjectId("65c35a039773c8593fc45ddb"),
      "email": "email",
      "name": "name",
      "groups": [
        {
          "_id": ObjectId("65c1b7716f130b7c4190104a"),
          "name": "testgroup"
        }
      ]
    },
    {
      "_id": ObjectId("65c35a039773c8593fc45ddf"),
      "email": "email",
      "name": "name",
      "groups": [
        {
          "_id": ObjectId("65c1b7716f130b7c4190104b"),
          "name": "testgroup1"
        }
      ]
    },
    {
      "_id": ObjectId("65c35a039773c8593fc45ddc"),
      "email": "email",
      "name": "name",
      "groups": []
    }

group collection- docs

{
    "_id" : ObjectId("65c1b7716f130b7c4190104a"),
    "name" : "testgroup",
  
}
{
    "_id" : ObjectId("65c1b7716f130b7c4190104f"),
    "name" : "testgroup2",
  
}

This query returns matching docs , but how to get which are not matching

db.getCollection("users").aggregate([

   { $match: { $expr:{$ne:[0,{ $size: "$groups" }]} } },
   { $lookup: {
      from: "groups",
      localField: "groups._id",
      foreignField: "_id",
      as: "matchingDocuments"   
    }},  
    { $project: {
      existsInCollection2: { $gt: [{ $size: "$matchingDocuments" }, 0] }
    }}
])
Tags:

2

Answers


  1. 0

    One option is to compare the size of both arrays:

    db.users.aggregate([
  {$match: {"groups.0": {$exists: true}}},
  {$lookup: {
      from: "groups",
      localField: "groups._id",
      foreignField: "_id",
      as: "matchingDocuments",
      pipeline: [{$project: {_id: 1}}]
  }},
  {$match: {$expr: {$ne: [{$size: "$groups"}, {$size: "$matchingDocuments"}]}}}
])

    See How it works on the mongoDB playground

    Login or Signup to reply.
  2. 0

    If you are interested in the groups that do not exist in the groups collection , you can use the following pipeline, that filters the array of groups so that only groups without a match are contained in the field invalidGroups:

    [
  {
    $match: {
      $expr: {
        $ne: [
          0,
          {
            $size: "$groups"
          }
        ]
      }
    }
  },
  {
    $lookup: {
      from: "groups",
      localField: "groups._id",
      foreignField: "_id",
      as: "matchingDocuments"
    }
  },
  {
    $set: {
      invalidGroups: {
        $filter: {
          input: "$groups",
          cond: {
            $not: {
              $in: [
                "$$this._id",
                "$matchingDocuments._id"
              ]
            }
          }
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        $ne: [
          0,
          {
            $size: "$invalidGroups"
          }
        ]
      }
    }
  },
  {
    $unset: [
      "matchingDocuments"
    ]
  }
]

    This leads to the following output for the sample data:

    [
  {
    "_id": ObjectId("65c35a039773c8593fc45ddf"),
    "email": "email",
    "groups": [
      {
        "_id": ObjectId("65c1b7716f130b7c4190104b"),
        "name": "testgroup1"
      }
    ],
    "invalidGroups": [
      {
        "_id": ObjectId("65c1b7716f130b7c4190104b"),
        "name": "testgroup1"
      }
    ],
    "name": "name"
  }
]

    See this mongoplayground to test.

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

Back To Top
Search