I want to have the mongodb query given these details below, and to have best in performance.

Let me start by an example:

Let me have a collection,

/// the collection

services = 
[
  {
    _id: obj(id1),
    provider_id: "provider1",
    service_code: "code1",
    price: 20
  },
  {
    _id: obj(id2),
    provider_id: "provider1",
    service_code: "code2",
    price: 20
  },
  {
    _id: obj(id3),
    provider_id: "provider1",
    service_code: "code3",
    price: 20
  },
  {
    _id: obj(id4),
    provider_id: "provider2",
    service_code: "code1",
    price: 10
  },
  {
    _id: obj(id5),
    provider_id: "provider2",
    service_code: "code2",
    price: 20
  },
  {
    _id: obj(id6),
    provider_id: "provider3",
    service_code: "code1",
    price: 20
  }
]

I want to get all service providers that (only) support these both service codes : code1, code2.

And the total price sorted by total price.

The output is like this:

/// required output based the posted collection and required requirements.

output = 
          
    [ 
      { 
        provider_id: "provider1",
        total_price: 40,
      },

      { 
        provider_id: "provider2",
        total_price: 30,
      }
]

I don't want this output:

[
  {
    "_id": "provider1",
    "total_price": 40
  },
  {
    "_id": "provider2",
    "total_price": 30
  },
  {
    "_id": "provider3",
    "total_price": 20
  }
]

How to accomplish this? Thanks

CodePudding user response：

This is probably the most basic use case of $group.

db.collection.aggregate([
  {
    $match: {
      service_code: {
        $in: [
          "code1",
          "code2"
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$provider_id",
      "service_codes": {
        "$addToSet": "$service_code"
      },
      "total_price": {
        "$sum": "$price"
      }
    }
  },
  {
    $match: {
      $expr: {
        $eq: [
          {
            "$setIntersection": [
              "$service_codes",
              [
                "code1",
                "code2"
              ]
            ]
          },
          [
            "code1",
            "code2"
          ]
        ]
      }
    }
  },
  {
    $project: {
      service_codes: false
    }
  },
  {
    $sort: {
      total_price: -1
    }
  }
])

Here is the Mongo playground for your reference.

CodePudding user response：

Mongo Playground

This is not like your desired output but it only returns the data which matches with provider_Id.

$unwind documentation $match documentation

CodePudding user response：

I was able to get the output you needed using $project:

db.collection.aggregate([
  {
    $match: {
      service_code: {
        $in: [
          "code1",
          "code2"
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$provider_id",
      "total_price": {
        "$sum": "$price"
      }
    }
  },
  {
    "$project": {
      "provider_id": "$_id",
      "total_price": 1,
      "_id": 0
    },
  },
  {
    $sort: {
      total_price: -1
    }
  }
])

I modified rays answer a little bit and just added the project aggregate :) My playground is here Mongo Playground