how can I find an array of values in a $match aggregation and group the result?

Question

Note:I think it is not possible to use javascript code in this live editor

this is my live code: https://mongoplayground.net/p/IM4rY2K7Alt

I am new to Mongodb I have a lot of faith to understand this exercise to understand more about the topic ofaggregations

My problem is this. I have a collection with a structure like this:

[
      {
        "toystore": 22,
        "toystore_name": "Toystore A",
        "toys": [
          {
            "toy": "buzz",
            "code": 17001,
            "price": 500
          },
          {
            "toy": "woddy",
            "code": 17002,
            "price": 1000
          },
          {
            "toy": "pope",
            "code": 17003,
            "price": 300
          }
        ]
      },
      {
        "toystore": 11,
        "toystore_name": "Toystore B",
        "toys": [
          {
            "toy": "jessie",
            "code": 17005,
            "price": 500
          },
          {
            "toy": "rex",
            "code": 17006,
            "price": 2000
          }
        ]
      }
    ]

I have n toy stores, and within each toy store I have the toys that this store has available within thetoys field (is an array).

I receive a list of the toys that I want to look for in all the stores and the quantify of toys that I need to quote.

 var originalData=[       
        {
          "toys.code": 17001,
          "quantify":2
        },
        {
          "toys.code": 17003,
          "quantify":4
        },
        {
          "toys.code": 17005,
          "quantify":5
        },
        {
          "toys.code": 17005,
          "quantify":6
        }
 ]

I'm using javascript, so I manage to create an array like this:

 let filters = [];
  originalData.forEach((data) => {
    filters.push({ "toys.code": data.code });
  });
//output
filters= [
    {
      "toys.code": 17001
    },
    {
      "toys.code": 17003
    },
    {
      "toys.code": 17005
    },
    {
      "toys.code": 17005
    }
  ]

//in the live code I do this simulation
/*$match: {
  $or: filters
}*/

/*$match: {
      $or: [
        {
          "toys.code": 17001
        }, {
          "toys.code": 17003
        }, {
          "toys.code": 17005
        }, {
          "toys.code": 17005
        }
      ],*/

to search for toys (I search for toys by their code toys.code), here I have my first problem, I don't know how to pass thequantity and then get the total price of the toys

(price_total: {$ multiply: [" $toys.price", quantify]})

I would like to group the result by store, and show how many toys were quoted by store with an output like this:

[
  {
    "_id": "Toystore A",
    "total_coincidences":2
    "toy_array": [
      {
        "total":1,//  "total":1, //1 time is founded this toy in array filter
        "price_original": 500,
        "toy": "buzz",
        "price_total":1000 
        // 2 * 500=1000  (price total)
      },
      {
        "total":1,//  "total":1, //1 time is founded this toy in array filter
        "price_original": 300,
        "toy": "pope",
        "price_total":1200 
        // 4 * 300= 1200(price total)
      }
    ]
  },
  {
    "_id": "Toystore B",
     "total":1, //1 element distinct is founded

    "toy_array": [
      {
        "total":2, //two times is founded this toy in array filter
        "price_original": 500,
        "toy": "jessie"
        "price_total":5500 
        //this toy is two times in the filter array
        // 5 * 500 = 2500
        // 6 * 500  = 3000
        //3000+ 2500=5500 (price total)
      }
    ]
  }
]

this is my code:

db.collection.aggregate([
      {
        $unwind: "$toys"
      }, {
        $match: {
          $or: [
            {
              "toys.code": 17001
            }, {
              "toys.code": 17003
            }, {
              "toys.code": 17005
            }, {
              "toys.code": 17005
            }
          ], 
        }
      }, {
        $group: {
          _id: "$toystore_name", toy_array: {
            $push: {
              price_original: "$toys.price", 
              /* price_total: { $multiply: ["$toys.price", qunantify] }*/
              toy: "$toys.toy"
            }, 
          }, 
        }, 
      }, 
    ])

and this is my live code:

user_3710490 · Answer

You need to change your filters as shown below.

Explanation

We include filters in the document structure with the $addFields operator. This will help us calculate all the necessary values.
We filter toys with the recent included filters attribute with the $expr operator.
Applying the $filter operator, we get all toys with the same code from filters. Now, we can calculate total and price_total values.
We could calculate total_coincidences inside the $group stage like this:

total_coincidences : {$sum:1}

But, you've mentioned "distinct element". So, we create set of unique toy codes and count items in the set.

db.collection.aggregate([
  {
    $unwind: "$toys"
  },
  {
    $addFields: {
      "filters": [
        {
          "code": 17001,
          "quantify": 2
        },
        {
          "code": 17003,
          "quantify": 4
        },
        {
          "code": 17005,
          "quantify": 5
        },
        {
          "code": 17005,
          "quantify": 6
        }
      ]
    }
  },
  {
    $match: {
      $expr: {
        $in: [ "$toys.code", "$filters.code"]
      }
    }
  },
  {
    $group: {
      _id: "$toystore_name",
      total_coincidences: {
        $addToSet: "$toys.code"
      },
      toy_array: {
        $push: {
          "price_original": "$toys.price",
          "toy": "$toys.toy",
          "total": {
            $size: {
              $filter: {
                input: "$filters",
                cond: {
                  $eq: [ "$$this.code", "$toys.code"]
                }
              }
            }
          },
          price_total: {
            $sum: {
              $map: {
                input: {
                  $filter: {
                    input: "$filters",
                    cond: {
                      $eq: [ "$$this.code", "$toys.code" ]
                    }
                  }
                },
                in: {
                  $multiply: [ "$toys.price", "$$this.quantify" ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $addFields: {
      total_coincidences: {
        $size: "$total_coincidences"
      }
    }
  },
  {
    $sort: { _id: 1 }
  }
])

MongoPlayground

how can I find an array of values in a $match aggregation and group the result?

1 Answers

Explanation