MongoDB aggregation: Counting results of the lookup without joining

Question

I'm working with this query:

    customers.aggregate: [
  {$lookup: {
    from: "users",
    localField: "_id",
    foreignField: "customerId",
    as: "users"
  }}, 
  {$lookup: {
    from: "templates",
        let: {localField: "$_id"},
    pipeline: [{
    $match: { $and: [{
      $expr: { $eq: ["$customerId", "$$localField"]}},
        {module: false}]
    }}],
    as: "templates"
  }},
  {$lookup: {
    from: "publications",
    localField: "_id",
    foreignField: "customerId",
    as: "publications"
  }},
 {$lookup: {
    from: "documents",
    let: {localField: "$_id"},
    pipeline: [{
    $match: { $and: [{
      $expr: { $eq: ["$customerId", "$$localField"]}},
        {createdAt: {$gte: {$date: "<someDate>"}}}]
    }}],
    as: "recentDocuments"
  }}
]

In the last lookup stage I'm filtering documents with the customerId field according to the _id field and newer than <someDate> and then joining those documents to respective "customer" object. And after this step or if possible even in this same step I would also like to add a new field to each resulting "customer" document with the counted number of all the documents (not only those that pass the time filter) from the "documents" collection with the customerId field value corresponding to the customer document's _id. And I also wish not to join those documents to the customer object as I only need a total number of documents with respective customerId. I can only use extended JSON v1 strict mode syntax. The result would look like:

customers: [
 0: {
  users: [...],
  templates: [...],
  publications: [...],
  recentDocuments: [...],
  totalDocuments: <theCountedNumber>
 },
 1: {...},
 2: {...},
 ...
]

score 0 · Answer 1 · answered Apr 14 '22 at 03:09

0

Use $set and $size

db.customers.aggregate([
  {
    $lookup: {
      from: "documents",
      let: { localField: "$_id" },
      pipeline: [
        {
          $match: {
            $and: [
              { $expr: { $eq: [ "$customerId", "$$localField" ] } }
            ]
          }
        }
      ],
      as: "recentDocuments"
    }
  },
  {
    $set: {
      totalDocuments: { $size: "$recentDocuments" }
    }
  }
])

mongoplayground

answered Apr 14 '22 at 03:09

YuTing

6,555
2
6
16

Thank you for your answer and help. But this query would return a number of documents that were joined and I instead have to count the number of the documents that have a corresponding `customerId` field for each customer and only have a field with that number in a customer object, without adding the document objects themselves. My question was not detailed enough. I have edited it now. – Lamer217 Apr 14 '22 at 07:41
@Lamer217 It would be better if you can provide testing data and expected result in a valid JSON by editing your own question. Ask a question like [this](https://stackoverflow.com/questions/71835902/excract-only-the-lookups-return-mongoose) would be more clarify for us to solve your problem. Make sure that your testing data can come up with your expected result in certain logic. – YuTing Apr 14 '22 at 08:01

Lamer217 · Accepted Answer · 2022-04-18T22:12:25.683

So on Thursday I've found a proper syntax to solve my problem. It goes like:

db.customers.aggregate([
    {
    $lookup: {
    from: "users",
    localField: "_id",
    foreignField: "customerId",
    as: "users"
  }}, 
  {$lookup: {
    from: "templates",
        let: {localField: "$_id"},
    pipeline: [{
    $match: { $and: [{
      $expr: { $eq: ["$customerId", "$$localField"]}},
        {module: false}]
    }}],
    as: "templates"
  }},
  {$lookup: {
    from: "publications",
    localField: "_id",
    foreignField: "customerId",
    as: "publications"
  }},
  {$lookup: {
    from: "documents",
    let: {localField: "$_id"},
    pipeline: [{
    $match: { $and: [{
      $expr: { $eq: ["$customerId", "$$localField"]}},
        {createdAt: {$gte: {$date: "<someDate>"}}}]
    }}],
    as: "recentDocuments"
  },
  {$lookup: {
    from: "documents",
    let: {localField: "$_id"},
    pipeline: [{
    $match: {$and: [{
      $expr: {$eq: ["$customerId", "$$localField"]}},
      { $count: "count" }],
    as: "documentsNumber"
  }}
])

This command would, in the last stage of the aggregate pipeline, go over the documents collection again, but this time would return all the documents instead of filtering by the time period, and then would swap the resulting object for every "customer" object with the array with one item being the number of all the documents. The array could be later "unwinded" with the $unwind action, but it proved to decrease the performance drastically, thus - omitted. I really hope this will help someone to solve a similar problem.

MongoDB aggregation: Counting results of the lookup without joining

2 Answers2