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

Find MongoDB documents that are not contained across arrays

MongoDB Collection A contains documents with an array with some document ids of collection B:

Collection A:

{
    some_ids_of_b: ["id1", ...]
}

Collection B:

{
    _id: "id1"
},
{
    _id: "id2"
},
...

How do I query all documents from B whose _ids are NOT in contained in the some_ids_of_b arrays of documents of A?

Tags:

3

Answers


  1. 0

    One option is:

    db.collectionB.aggregate([
  {$lookup: {
      from: "collectionA",
      let: {my_id: "$_id"},
      pipeline: [
        {$match: {$and: [
              {_id: collADocId},
              {$expr: {$in: ["$$my_id", "$some_ids_of_b"]}}
        ]}},
        {$project: {_id: 1}}
      ],
      as: "some_ids_of_b"
  }},
  {$match: {"some_ids_of_b.0": {$exists: false}}},
  {$unset: "some_ids_of_b"}
])

    See how it works on the playground example

    Login or Signup to reply.
  2. 0

    Simple lookup from collection B to A and filter to keep only those documents where you don’t find any matches.

    db.collb.aggregate([
  {
    "$lookup": {
      "from": "colla",
      "localField": "_id",
      "foreignField": "someIdsOfB",
      "as": "a"
    }
  },
  {
    $match: {
      $expr: {
        $eq: [{$size: "$a"}, 0]
      }
    }
  }
])

    Demo

    Login or Signup to reply.
  3. 0

    You can do it with Aggregation Framework:

    • $group and $addToSet – To get all $some_ids_of_b from all the documents in A collection.
    • $set with $reduce – To create an array with all unique values of the IDs from the B collection.
    • $lookup – To fetch the documents from the B collection, where the _id of the document is not present in the $b_ids array.
    • $project – To project data as expected output.
    db.A.aggregate([
  {
    "$group": {
      "_id": null,
      "b_ids": {
        "$addToSet": "$some_ids_of_b"
      }
    }
  },
  {
    "$set": {
      b_ids: {
        $reduce: {
          input: "$b_ids",
          initialValue: [],
          in: {
            $setUnion: [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  },
  {
    "$lookup": {
      from: "B",
      let: {
        b_ids: "$b_ids"
      },
      pipeline: [
        {
          "$match": {
            "$expr": {
              $ne: [
                {
                  "$in": [
                    "$_id",
                    "$$b_ids"
                  ]
                },
                true
              ]
            }
          }
        }
      ],
      as: "data"
    }
  },
  {
    "$project": {
      data: 1,
      _id: 0
    }
  }
])

    Working Example

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

Back To Top
Search