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

MongoDB aggregation: Counting results of the lookup without joining

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: {...},
 ...
]
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 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

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

Back To Top
Search