CODEWITHSUNDEEP
mongodb
Kingfranz
Kingfranz

Reputation: 314

Is this query possible in MongoDB?

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?

Upvotes: 0

Views: 64

Answers (2)

R2D2
R2D2

Reputation: 10737

I suppose you need something like this:

db.collection.aggregate([
 {
   $unwind: "$category"
 },
 {
  $sort: {
  _id:-1,
  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

Upvotes: 2

YuTing
YuTing

Reputation: 6629

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.

Upvotes: 2

Related Questions