获取特定字段mongodb的计数 [英] Get Count of specific field mongodb

问题描述

我正在使用以下查询从用户和项目集合中获取组合数据:

I am using below query to get combined data from users and project collections:

db.collection.aggregate([
 { 
   "$group": {
     "_id": "$userId",
     "projectId": { "$push": "$projectId" }
    }
 },
 { 
   "$lookup": {
     "from": "users",
     "let": { "userId": "$_id" },
     "pipeline": [
        { "$match": { "$expr": { "$eq": [ "$_id", "$$userId" ] }}},
        { "$project": { "firstName": 1 }}
      ],
     "as": "user"
    }
 },
 { "$unwind": "$user" },
 {
   "$lookup": {
     "from": "projects",
     "let": { "projectId": "$projectId" },
     "pipeline": [
        { "$match": { "$expr": { "$in": [ "$_id", "$$projectId" ] }}},
        { "$project": { "projectName": 1 }}
      ],
     "as": "projects"
    }
 }
])

其结果如下:

[
  {
    "_id": "5c0a29e597e71a0d28b910aa",
    "projectId": [
        "5c0a2a8897e71a0d28b910ac",
        "5c0a4083753a321c6c4ee024"
    ],
    "user": {
        "_id": "5c0a29e597e71a0d28b910aa",
        "firstName": "Amit"
    },
    "projects": [
        {
            "_id": "5c0a2a8897e71a0d28b910ac",
            "projectName": "LN-PM"
        },
        {
            "_id": "5c0a4083753a321c6c4ee024",
            "projectName": "fallbrook winery"
        }
    ]
  },
  {
    "_id": "5c0a29c697e71a0d28b910a9",
    "projectId": [
        "5c0a4083753a321c6c4ee024"
    ],
    "user": {
        "_id": "5c0a29c697e71a0d28b910a9",
        "firstName": "Rajat"
    },
    "projects": [
        {
            "_id": "5c0a4083753a321c6c4ee024",
            "projectName": "fallbrook winery"
        }
    ]
  }
]

现在我有另一个表"Worksheets"，并且想在项目数组中包含 hours 字段，这将通过在projects数组中指定projectId来确定，该字段是通过worksheets表来计算的.它将在工作表中找到，小时数将增加_id在工作表中的次数.下面是我的工作表集合:

Now i have another table "Worksheets" and want to include hours field in projects Array, which will be calculated from the worksheets table by specifying the projectId which is _id in the projects array. It will be find in worksheet table and hours will be incremented how many times this _id has in worksheets table. Below is my worksheet collection:

{
  "_id" : ObjectId("5c0a4efa91b5021228681f7a"),
  "projectId" : ObjectId("5c0a4083753a321c6c4ee024"),
  "hours" : 8,
  "userId" : ObjectId("5c0a29c697e71a0d28b910a9"),
  "__v" : 0 
}

{
 "_id" : ObjectId("5c0a4f4191b5021228681f7c"),
  "projectId" : ObjectId("5c0a2a8897e71a0d28b910ac"),
  "hours" : 6,
  "userId" : ObjectId("5c0a29e597e71a0d28b910aa"),
 "__v" : 0
}

结果将如下所示:

{
    "_id": "5c0a29c697e71a0d28b910a9",
    "projectId": [
        "5c0a4083753a321c6c4ee024"
    ],
    "user": {
        "_id": "5c0a29c697e71a0d28b910a9",
        "firstName": "Rajat"
    },
    "projects": [
        {
            "_id": "5c0a4083753a321c6c4ee024",
            "projectName": "fallbrook winery",
             "hours":8
        }
    ]
}

推荐答案

您可以在下面的聚合中使用

You can use below aggregation

$lookup 3.6 嵌套语法允许您在 $lookup 管道.您可以在嵌套的 $lookup 点线

$lookup 3.6 nested syntax allows you to join nested collection inside the $lookup pipeline. You can perform all the aggregation inside the nested $lookup pipline

db.collection.aggregate([
  { "$group": {
    "_id": "$userId",
    "projectId": { "$push": "$projectId" }
  }},
  { "$lookup": {
    "from": "users",
    "let": { "userId": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$_id", "$$userId" ] }}},
      { "$project": { "firstName": 1 }}
    ],
    "as": "user"
  }},
  { "$unwind": "$user" },
  { "$lookup": {
    "from": "projects",
    "let": { "projectId": "$projectId" },
    "pipeline": [
      { "$match": { "$expr": { "$in": [ "$_id", "$$projectId" ] }}},
      { "$lookup": {
        "from": "worksheets",
        "let": { "projectId": "$_id" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": [ "$projectId", "$$projectId" ] }}},
          { "$group": {
            "_id": "$projectId",
            "totalHours": { "$sum": "$hours" }
          }}
        ],
        "as": "workHours"
      }}
      { "$project": {
        "projectName": 1,
        "hours": { "$arrayElemAt": ["$workHours.totalHours", 0] }
      }}
    ],
    "as": "projects"
  }}
])

这篇关于获取特定字段mongodb的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！