Suppose I have an aggregate pipeline which returns the following set of documents:

[
  { "_id": 0, "refs": [1, 2] }, 
  { "_id": 1, "refs": [2, 3] }
]

How do I extend the aggregate pipeline to

1. collect the set of distinct values in "refs" (i.e. [1, 2, 3]) and
2. return the documents with _id matching the values in that set (i.e. 3 documents with _id 1, 2, and 3).

CodePudding user response：

One option is to add 4 steps to your aggregation:

1. $group in order to collect all refs
2. $reduce with $setUnion in order to get the distinct values.
3. $lookup in order to get the docs from the refs
4. Format

  {$group: {_id: 0, refs: {$push: "$refs"}}},
  {$project: {
      refs: {$reduce: {
          input: "$refs",
          initialValue: [],
          in: {$setUnion: ["$$value", "$$this"]}
        }
      }
    }
  },
  {$lookup: {
      from: "collection",
      localField: "refs",
      foreignField: "_id",
      as: "docs"
    }
  },
  {$unwind: "$docs"},
  {$replaceRoot: {"newRoot": "$docs"}}

See how it works on the playground example

CodePudding user response：

There are lots of ways to do this. Here's another way.

db.collection.aggregate([
  {"$unwind": "$refs"},
  {
    "$group": {
      "_id": null,
      "refs": {"$addToSet": "$refs"}
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "localField": "refs",
      "foreignField": "_id",
      "as": "docs"
    }
  },
  {"$unwind": "$docs"},
  {"$replaceWith": "$docs"}
])

Try it on mongoplayground.net.

CodePudding user response：

Query

• try to join the _id with some ref
• if join happens >1 time (=> results is not empty array)
• we keep the document else filter out

*not sure if you want this, if its not what you need, if you can add the expected output

Playmongo

coll1.aggregate(
[{"$lookup": 
   {"from": "coll1",
    "localField": "_id",
    "foreignField": "refs",
    "as": "results"}},
 {"$match": {"$expr": {"$ne": ["$results", []]}}},
 {"$unset": ["results"]}])