MongoDB Aggregation: How to return only the values that don't exist in all documents

user_10501694 0
Viewed 95

Lets say I have an array ['123', '456', '789']

I want to Aggregate and look through every document with the field books and only return the values that are NOT in any documents. For example if '123' is in a document, and '456' is, but '789' is not, it would return an array with ['789'] as it's not included in any books fields in any document.

.aggregate( [
{
  $match: {
    books: {
        $in: ['123', '456', '789']
    }
  }
},

I don't want the documents returned, but just the actual values that are not in any documents.

aggregation-framework mongodb-query mongodb aggregate database
2 Answers

Here's one way to scan the entire collection to look for missing book values.

db.collection.aggregate([
  {  // "explode" books array to docs with individual book values
    "$unwind": "$books"
  },
  {  // scan entire collection creating set of book values
    "$group": {
      "_id": null,
      "allBooksSet": {
        "$addToSet": "$books"  // <-- generate set of book values
      }
    }
  },
  {
    "$project": {
      "_id": 0,  // don't need this anymore
      "missing": {  // use $setDifference to find missing values
        "$setDifference": [
          [ "123", "456", "789" ],  // <-- your values go here
          "$allBooksSet"  // <-- the entire collection's set of book values
        ]
      }
    }
  }
])

Example output:

[
  {
    "missing": [ "789" ]
  }
]

Try it on mongoplayground.net.

user_14093740

Based on @rickhg12hs's answer, there is another variation replacing $unwind with $reduce, which considered less costly. Two out of Three steps are the same:

db.collection.aggregate([
  {
    $group: {
      _id: null,
      allBooks: {$push: "$books"}
    }
  },
  {
    $project: {
      _id: 0,
      allBooksSet: {
        $reduce: {
          input: "$allBooks",
          initialValue: [],
          in: {$setUnion: ["$$value", "$$this"]}
        }
      }
    }
  },
  {
    $project: {
      missing: {
        $setDifference: [["123","456", "789"], "$allBooksSet"]
      }
    }
  }
])

Try it on mongoplayground.net.

user_184823100
Related