如何在mongodb中組合組和專案

這里我有一個需要轉換為 mongo 查詢的 sql 查詢。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) 

我的蒙戈查詢：

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'、'property' fields），我可以看到所有記錄，但性能很慢。誰能幫我解決這個問題？

uj5u.com熱心網友回復：

在您的查詢中，在$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"
    }
  }
])

Mongo游樂場

標籤：mongodb 聚合框架

上一篇：NoSuchBeanDefinitionException與回應式mongo存盤庫：需要找不到型別的bean

下一篇：從MongoDB中的值陣列創建多個檔案