Aggregation matching a string in an array

Dear,

I’ve to sum all requiredQuantity from a collection where articles.article matching a given name.

The field of the collection that I’ve to aggregate has the following format:


"articles": [
    {
      "article": "Azoto 5.0 in bombole  da 8 m3",
      "code": "-",
      "category": "Gas tecnici e criogenici",
      "subcategory": "Gas tecnici in bombola",
      "requiredQuantity": 15,
      "unitPrice": 9.520000457763672,
      "totalPrice": 142.8000030517578,
      "unitOfMeasure": "M^3"
},
{
 another article
},
{
etc.
}]

This field is inside an order collection that contains a lot of record with other articles.
I tried something like that:

[
  
  {
    $match:
      /**
       * query: The query in MQL.
       */
      {
        "articles.article":
          "Azoto 5.0 in bombole  da 8 m3",
      },
  },
  {
    $project:
      /**
       * specifications: The fields to
       *   include or exclude.
       */
      {
        "articles.article": 1,
        "articles.requiredQuantity": 1,
      },
  },
  {
    $group:
      {
        _id: "$articles.requiredQuantity",
        total: {
          $sum: "$requiredQuantity",
        },
      },
  },
]

But doesn’t work. How can obtain what I want?
Thanks

Hello @morellik, Welcome back to the MongoDB community forum,

Follow something like this,

  • $match your condition, looks good,
  • $project to get the total of requiredQuantity,
  • $reduce to get the total of requiredQuantity, it will iterate the array and check if the match article then $add it to the total
  • $group by null and get the final count of the requiredQuantity
[
  { $match: { "articles.article": "Azoto 5.0 in bombole  da 8 m3" } },
  {
    $project: {
      requiredQuantity: {
        $reduce: {
          input: "$articles",
          initialValue: 0,
          in: {
            $cond: [
              { $eq: ["$$this.article", "Azoto 5.0 in bombole  da 8 m3"] },
              { $add: ["$$this.requiredQuantity", "$$value"] },
              "$$value"
            ]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      requiredQuantity: { $sum: "$requiredQuantity" }
    }
  }
]
1 Like

Wow! Excellent! Thank you very much.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.