Aggregate pipeline involving $in

Question

We have a shop collection:

    {
        "_id" : ObjectId("xxxxxx"),
        "shop" : "Q4",
        "batch" : "5647",
    }
    {
        "_id" : ObjectId("xxxxxx"),
        "shop" : "Q4",
        "batch" : "2314",
    }

First step of aggregation as below:

Aggregates.group("$shop", Accumulators.addToSet("batch", "$batch"))

Output:

{ "_id" : "Q4", "batch" : ["5647", "2314"]}

Now - given another collection inventory as below,we need to find those in "batch" above using $in - and add in output

 {
        "_id" : ObjectId("xxxxxx"),
        "bolt" : "5647",
    }
    {
        "_id" : ObjectId("xxxxxx"),
        "bolt" : "0001",
    }
  {
        "_id" : ObjectId("xxxxxx"),
        "bolt" : "0004",
    }

Expected output:

{ "_id" : "Q4", "batch" : ["5647", "2314"],"bolt":["5647"]}

How do we achieve this?

Do you need a MongoDB query or java converted one ? Also are you looking to achieve these two in one single DB call ? — whoami - fakeFaceTrueSoul, Jan 27 '20 at 18:57
Java would be preferable..and they need to be done in a single call.Since the second collection will have ~1M records,tryin to push that entirely into memory for subset is failing — IUnknown, Jan 28 '20 at 04:27

score 0 · Accepted Answer · answered Jan 28 '20 at 00:33

You need to use conditional $lookup. With $reduce we convert bolt:[{...}, {...}] to bolt:["...", "..."]

db.shop.aggregate([
  {
    $group: {
      _id: "$shop",
      batch: {
        $push: "$batch"
      }
    }
  },
  {
    $lookup: {
      from: "inventory",
      let: {
        batch: "$batch"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$bolt",
                "$$batch"
              ]
            }
          }
        }
      ],
      as: "bolt"
    }
  },
  {
    $project: {
      _id: 1,
      batch: 1,
      bolt: {
        $reduce: {
          input: "$bolt",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              [
                "$$this.bolt"
              ]
            ]
          }
        }
      }
    }
  }
])

MongoPlayground

The lookup with $in seems to be taking ages - are index ignored in lookup during aggregation? — IUnknown, Jan 31 '20 at 04:36

Aggregate pipeline involving $in

1 Answers1