Imagine a data set like this:

db.test.insertMany([
  { '_id':1, 'name':'aa1', 'price':10, 'quantity': 2, 'category': ['coffe'] },
  { '_id':2, 'name':'aa2', 'price':20, 'quantity': 1, 'category': ['coffe', 'snack'] },
  { '_id':3, 'name':'aa3', 'price':5,  'quantity':10, 'category': ['snack', 'coffe'] },
  { '_id':4, 'name':'aa4', 'price':5,  'quantity':20, 'category': ['coffe', 'cake'] },
  { '_id':5, 'name':'aa5', 'price':10, 'quantity':10, 'category': ['animal', 'dog'] },
  { '_id':6, 'name':'aa6', 'price':5,  'quantity': 5, 'category': ['dog', 'animal'] },
  { '_id':7, 'name':'aa7', 'price':5,  'quantity':10, 'category': ['animal', 'cat'] },
  { '_id':8, 'name':'aa8', 'price':10, 'quantity': 5, 'category': ['cat', 'animal'] },
]);

I'm trying to make a query with this result (or something like it):

[
  { ['animal', 'dog'],  125 },
  { ['animal', 'cat'],  100 },
  { ['coffe', 'cake'],  100 },
  { ['coffe', 'snack'],  70 },
  { ['coffe'],           20 }
]

Meaning that it is:

1. Grouped by category.
2. The category is treated as a set (i.e. order is not important).
3. The result is sorted by price*quantity per unique category 'set'.

I've tried everything I know (which is very limited) and googled for days without getting anywhere. Is this even possible in an aggregate query or do I have find a different way?

CodePudding user response：

I suppose you need something like this:

db.collection.aggregate([
 {
   $unwind: "$category"
 },
 {
  $sort: {
  category: -1
 }
 },
 {
  $group: {
    _id: "$_id",
    category: {
    $push: "$category"
  },
  price: {
    $first: "$price"
  },
   quantity: {
    $first: "$quantity"
  }
 }
 },
 {
  $group: {
    _id: "$category",
    sum: {
     $sum: {
      $multiply: [
        "$price",
        "$quantity"
      ]
     }
    }
  }
  },
  {
    $project: {
      mySet: "$_id",
      total: "$sum"
   }
  },
  {
   $sort: {
     total: -1
   }
  }
  ])

Explained:

1. $unwind the $category array so you can sort the categories in same order.
2. $sort by category & _id so you can have same order per category & _id
3. $group by _id so you can push the categories back to array but sorted
4. $group by category set so you can sum the price*quantity
5. $project the needed fields
6. $sort by descending order as requested.

Please, note output has name for the set and total for the sum to be valid JSON since it is not possible to have the output as {[X,Y],Z} and need to be {m:[X,Y],z:Z}

playground

CodePudding user response：

db.collection.aggregate([
  {
    "$match": {}
  },
  {
    "$group": {
      "_id": {
        $function: {
          body: "function(arr) { return arr.sort((a,b) => a.localeCompare(b))}",
          args: [ "$category" ],
          lang: "js"
        }
      },
      "sum": {
        "$sum": { "$multiply": [ "$price", "$quantity" ] }
      }
    }
  },
  {
    "$sort": { sum: -1 }
  }
])

mongoplayground

In mongodb 5.2 version you can use $sortArray instead of function sort that I used.