MongoDB查询：仅在满足特定条件的情况下才获得文档的加入

Question

我希望 mongodb 查询给出下面的这些详细信息，并获得最佳性能。

让我从一个例子开始：

让我有一个集合，

/// 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
  }
]

我想要获取（仅）支持这两个服务代码的所有服务提供商：code1、code2。

以及总价按总价排序。

输出是这样的：

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

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

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

我不想要这个输出：

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

如何完成这个？谢谢

Answer 1

这可能是 $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
    }
  }
])

这是 Mongo Playground 供您参考。

Answer 2

我能够使用$项目获得您需要的输出：

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
    }
  }
])

我修改了射线，回答一点，刚刚添加了汇总项目:)我的游乐场在这里

Answer 3

Mongo Playground

这与您想要的输出不同，但它只返回与provider_Id匹配的数据。

    db.collection.aggregate({
  "$unwind": "$service"
},
{
  "$match": {
    "service.provider_id": "provider1"
  }
})

$unwind 文档 $match 文档