如何在MongoDB中组合小组和项目

Question

在这里，我有一个SQL查询，需要转换为Mongo查询。 SQL查询：

select p.producer_id,p.name from producer p join
(select distinct ps.service_id,ps.value from service ps where ps.service_id=p.service_id) join
(select distinct pp.property_id,pp.property from property pp where  pp.service_id=p.service_id) 

我的Mongo查询：

db.producer.aggregate([
        {
          "$lookup": {
            "from": "service",
            "localField": "producer_id",
            "foreignField": "service_id",
            "as": "ps"
          }
        },
        {
          "$unwind": "$ps"
        },
        {
          "$lookup": {
            "from": "property",
            "localField": "producer_id,
            "foreignField": "property_id",
            "as": "pp"
          }
        },
        {
          "$unwind": "$pp"
        },
        {
          "$group": {
            "_id": {
              "property_id": "$pp.property_id",
              "service_id": "$ps.service_id"
            }
          }
        },
        {
          "$project": {
            "producer_id": "$p.producer_id",
            "name": "$p.name",
            "service_id":"$ps.service_id",
            "value":"$ps.value",
            "property_id":"$pp.property_id",
            "property":"$pp.property",
            "_id":0
          }
        }

      ]);

示例输入记录： 生产者：

[{producer_id:1,name:'test'},{producer_id:2,name:'test2'}]

服务：

[{service_id:1,value:12},{service_id:1,value:13},{service_id:2,value:14}]

属性：

[{property_id:1,property:12},{property_id:1,property:56},{property_id:2,property:34}]

但是在输出中，我可以看到组结果。当我尝试尝试从各个集合中投射剩余的列（“名称”，“值”，“属性”字段）时，这些值未显示在输出中。在这里，我必须选择不同的记录，并显示不同的记录以及其他记录。当我尝试在组中添加所有字段时（'name'，'value'，'属性'字段），我可以看到所有记录，但性能很慢。有人可以帮我吗？

Answer 1

在查询中，在$ group阶段中，您需要使用累加器操作员进行额外字段

{
    "$group": {
        "_id": {
            "property_id": "$pp.property_id",
            "service_id": "$ps.service_id"
        },
        "field": {
          "accumulator": "$value"
        },
        ...
    }
}

累加器操作员

尝试此方法：

db.producer.aggregate([
  //make sure service.service_id is indexed for better performance
  {
    "$lookup": {
      "from": "service",
      "localField": "producer_id",
      "foreignField": "service_id",
      "as": "ps"
    }
  },
  //make sure property.property_id is indexed for better performance
  {
    "$lookup": {
      "from": "property",
      "localField": "producer_id",
      "foreignField": "property_id",
      "as": "pp"
    }
  },
  {
    "$unwind": "$pp"
  },
  {
    "$unwind": "$ps"
  },
  {
    "$group": {
      // SQL "distinct"
      "_id": {
        "service_id": "$ps.service_id",
        "property_id": "$pp.property_id",
        "value": "$ps.value",
        "property": "$pp.property"
      },
      "producer_id": {
        "$first": "$producer_id"
      },
      "name": {
        "$first": "$name"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "producer_id": "$producer_id",
      "name": "$name",
      "service_id": "$_id.service_id",
      "property_id": "$_id.property_id",
      "value": "$_id.value",
      "property": "$_id.property"
    }
  }
])

mongoplayground