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

How to select docs whose id isn’t in array prop of other docs in collection?

I have collection of documents which has the following shape:

const categorySchema = new Schema<ICategory>({
  title: { type: String, required: true },  
  children: [{ type: Types.ObjectId, ref: categoryModelName }],  
});

I want to get all documents id of which is not contained in children array property of all other documents in the collection. How to write such query, if possible?

Tags:

2

Answers


  1. 0

    you can write an aggregation pipeline

    1. self lookup and create a field which has documents which the current document is a child of
    2. If a child of array is empty it means there are no documents where the current document is a child. So filter out those using match
    db.categoryCollection.aggregate([
  { $lookup: { from: "categoryCollection", localField: "_id", foreignField: "children", as: "childOf" } },
  { $match: { childOf: { $size: 0 } } },
  { $project: { _id: 1 } }
])

    playground

    Login or Signup to reply.
  2. 0

    Here’s another way to do it, although if the collection is very large the "$group" stage may exceed the max allowable memory. Comments are in the aggregation pipeline.

    db.collection.aggregate([
  {
    "$group": {
      // go through each document
      "_id": null,
      // push all _id's (already unique)
      "ids": {"$push": "$_id"},
      // this will be an array of arrays unfortunately
      "children": {"$addToSet": "$children"}
    }
  },
  {
    "$project": {
      // don't need _id
      "_id": 0,
      // assign theIds as the output
      "theIds": {
        // set difference = what's in ids but not in children
        "$setDifference": [
          "$ids",
          { // collapse children to a single flat array
            "$reduce": {
              "input": "$children",
              "initialValue": [],
              "in": {"$concatArrays": ["$$value", "$$this"]}
            }
          }
        ]
      }
    }
  }
])

    Try it on mongoplayground.net.

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

Back To Top
Search