具有多个条件的聚合和基于计数键的总和匹配 [英] Aggregation with multiple criteria's and sum match based on the count key

问题描述

我试图获取另一个集合中对象数组中存在的元素计数.示例:

I was trying get to get the count of element that is present in the array of objects in another collection. Example:

集合 A:

{
   _id:1,
   name:"Sample1"
}
{
  _id:2,
  name:"Sample 2"
}
{
  _id:3,
  "name":"Sample 3"
}
{
  _id:4,
  "name":"Sample 4"
}

集合 B:

{
   _id:11,
   items:[ {_id:1, name:"sample1",size:1},{_id:3, name:"sample 3",size:5}]
}
{
   _id:12,
   items:[ {_id:1, name:"sample1",size:2},{_id:3, name:"sample 3",size:6}]
}
{
   _id:13,
   items:[ {_id:2, name:"sample2", size:5},{_id:1, name:"sample 1",size:8}],
is_delete:true
}
{
   _id:14,
   items:[ {_id:1, name:"sample1",size:3},{_id:3, name:"sample 3",size:1}]
}

注意:items 中的 _id 是字符串.

Note: The _id in items is string.

预期输出:

{
   _id:1,
   name:"Sample1",
  count:6
}
{
  _id:2,
  name:"Sample 2",
  count:0
}
{
  _id:3,
  "name":"Sample 3",
  "count":12
}
{
  _id:4,
  "name":"Sample 4",
  "count":0
}

请帮我写一个 mongo 查询以获得预期的输出.

Please help me to write a mongo query to get the expected out put.

推荐答案

由于有两个集合，所以需要使用

Since there are two collections, we need to use

• $lookup 加入两个集合.这里我使用了 不相关的子查询
• 聚合在 colA 集合中执行，但在 lookup 的 pipeline 中，我们在 colB 上执行聚合>.$unwind 有助于解构 items.$match 有助于消除不需要的数据(匹配阶段需要 $expr).
• 一旦我们的加入成功，我们只需要使用 $size
计算数组
• $reduce 有助于对 size
的数组值求和

• $lookup to join tow collections. Here I used uncorelated subqueries
• The aggregation is performed in colA collections, but inside the lookup's pipeline we perform aggregation on colB. $unwind helps to de-structure the items. $match helps to eliminate unwanted data (match stage requires $expr).
• Once our join is succeeded, we need to just count the array using $size
• $reduce helps to sum the array value of size

mongo 脚本如下.

The mongo script is given below.

db.colA.aggregate([
  {
    $lookup: {
      from: "colB",
      let: {
        bid: "$_id"
      },
      pipeline: [
        {
          $match: {
            $or: [
              {
                is_delete: false
              },
              {
                is_delete: {
                  "$exists": false
                }
              }
            ]
          }
        },
        {
          $unwind: "$items"
        },
        {
          $match: {
            $expr: {
              $eq: [
                "$items._id",
                "$$bid"
              ]
            }
          }
        },
        
      ],
      as: "data"
    }
  },
  {
    $project: {
      count: {
        $reduce: {
          input: "$data",
          initialValue: 0,
          in: {
            $add: [
              "$$value",
              "$$this.items.size"
            ]
          }
        }
      }
    }
  }
])

工作 Mongo 游乐场

这篇关于具有多个条件的聚合和基于计数键的总和匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！