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

mongodb return a list of objects from a collection that aren't in any sub documents of another collection

In this mongo playground I have two collections: vulns and tracker.

I would like a query that can return a list of vulns that do not appear in tracker against a specific customerId.

Essentially I’d like to create a list of advisoryIds as an array for the $nin expression – Mongo Playground.

This query is equivalent to what should be returned:

db.vulns.find({
  "advisoryId": {
    $nin: [
      "44444",
      "55555"
    ]
  }
})

And I was able to get those values using a projection:

db.tracker.find({
  "customerId": "12345"
},
{
  "advisory.advisoryId": 1,
  _id: 0
})

I just need to find a way to combine those queries.

Tags:

2

Answers


  1. 0

    Okay, using your example where you find() the advisories for "customerId": "12345" and then lookup the one’s which are missing for that customerId – presumably that’s how you want the combination to occur.

    This pipeline will lookup up each advisoryId from vulns in the trackers collection and additionally match on the customerId. Whenever the lookup presents no results ie an empty array, those are the missing advisories:

    db.vulns.aggregate([
  {
    $lookup: {
      from: "tracker",
      let: {
        advId: "$advisoryId",
        // put the customerId here
        custId: "12345"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$customerId", "$$custId"] },
                { $eq: ["$advisory.advisoryId", "$$advId"] }
              ]
            }
          }
        }
      ],
      as: "found_trackers"
    }
  },
  {
    $match: {
      "found_trackers": []
    }
  },
  { $unset: "found_trackers" }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0

    Here’s another way to do it by first "$match"ing in the tracker collection, collecting the unique advisory.advisoryIds into an array, and then "$lookup" the non-matching docs in vulns.

    Comments are in the pipeline.

    db.tracker.aggregate([
  {
    "$match": {
      // specific customerId
      "customerId": "12345"
    }
  },
  {
    // make array of all unique advisory.advisoryId's
    // for the specific customerId
    "$group": {
      "_id": null,
      "notAdvIds": {
        "$addToSet": "$advisory.advisoryId"
      }
    }
  },
  {
    // go find the vulns not in the array
    "$lookup": {
      "from": "vulns",
      "let": {
        "notAdvIds": "$notAdvIds"
      },
      "as": "vulns",
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$not": {"$in": ["$advisoryId", "$$notAdvIds"]}
            }
          }
        }
      ]
    }
  },
  // just output formatting from here
  {
    // create separate doc for each vuln
    "$unwind": "$vulns"
  },
  {
    // make doc just the vuln
    "$replaceWith": "$vulns"
  }
])

    Try it on mongoplayground.net.

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

Back To Top
Search