组计数2项对象数组mongodb [英] Group count 2 item object array mongodb

问题描述

我有以下数组：

[
  {
    "counter": 123456,
    "user": "USER1",
    "last": "USER1"
  },
  {
    "counter": 123,
    "user": "USER1",
    "last": "USER2"
  },
  {
    "counter": 111,
    "user": "USER2",
    "last": "USER2"
  },
  {
    "counter": 1122,
    "user": "USER2",
    "last": "USER2"
  },
  {
    "counter": 112233,
    "user": "USER1",
    "last": "USER2"
  },
]

我正在对mongodb进行以下查询：

I'm doing the following query on mongodb:

{$group: {
  _id: "$user",
  total: {$sum: 1},
  last: {$sum: {$cond: [
    {$eq: ['$last’, '$user']}, 1, 0
  ]}}
}}

我想得到以下结果：

[
  {
    _id: "USER1"
    total: 3,
    last: 1
  },
{
    _id: "USER2"
    total: 2,
    last: 4
  }
]

但是我明白了：

[
  {
    _id: "USER1"
    total: 3,
    last: 1
  },
{
    _id: "USER2"
    total: 2,
    last: 2
  }
]

当我创建组时，我无法计数最后一个项目令人满意

When I make the group I can not count the last item satisfactorily

如何获得预期的结果？谢谢您的帮助。

How can I get the expected result? Thank you for your help.

推荐答案

您只有2个 USER2，那么在该_id上不能$ sum 4 $ last 。
如果需要按用户索引来尝试使用compose _id。

You have only 2 "USER2" then can't $sum 4 $last on that _id. try using compose _id if you need by index in user.

db.teste.aggregate([
  {$group:{
    _id: {user:"$user",last:"$last"},
    total: {$sum:1},
    last: {
      $sum: {
        $cond:[ {$eq: ["$last", user]}, 1, 0]
      }
    }
  }}
])

如果需要在不同字段中添加字符串或其他出现方式。查询是：

If you need to add string or other occurrences in different fields. Query is:

db.teste.aggregate([
  {$group:{
    _id: "$user"
  }},
  {$lookup:{
    from: "teste",
    let: { indice: "$_id" },
    pipeline: [
      {$group:{
        _id: null,
        user:{$sum:{$cond:[
          {$eq:["$user", "$$indice"]}, 1, 0
        ]}},
        last:{$sum:{$cond:[
          {$eq:["$last", "$$indice"]}, 1, 0
        ]}}
      }},
      {$project:{
        _id: 0
      }}
    ],
    as: "res"
  }}
])

从零开始对英雄：

//First we'll extract only what we need find, on this case distinct users
db.teste.aggregate([
  {$group:{
    _id: "$user"
  }}
])

//Here we will get the indexes of the search and reinsert in our filter using $let (nodejs var)
db.teste.aggregate([
  {$group:{
    _id: "$user"
  }},
  {$lookup:{
    from: "teste",
    let: { indice: "$_id" },
    pipeline: [],
    as: "res"
  }}
])

//Let's counter the total of reinserted elements
db.teste.aggregate([
  {$group:{
    _id: "$user"
  }},
  {$lookup:{
    from: "teste",
    let: { indice: "$_id" },
    pipeline: [
      {$group:{
        _id: null,
        total:{$sum:1}
      }}
    ],
    as: "res"
  }}
])

//Now let's test a true condition
db.teste.aggregate([
  {$group:{
    _id: "$user"
  }},
  {$lookup:{
    from: "teste",
    let: { indice: "$_id" },
    pipeline: [
      {$group:{
        _id: null,
        user:{$sum:{$cond:[
          {$eq:[true, true]}, 1, 0
        ]}}
      }}
    ],
    as: "res"
  }}
])

//cond tested let's extract what we want
db.teste.aggregate([
  {$group:{
    _id: "$user"
  }},
  {$lookup:{
    from: "teste",
    let: { indice: "$_id" },
    pipeline: [
      {$group:{
        _id: null,
        user:{$sum:{$cond:[
          {$eq:["$user", "$$indice"]}, 1, 0
        ]}},
        last:{$sum:{$cond:[
          {$eq:["$last", "$$indice"]}, 1, 0
        ]}}
      }}
    ],
    as: "res"
  }}
])

//Let's take the _id of the sub-colection because we do not need it
db.teste.aggregate([
  {$group:{
    _id: "$user"
  }},
  {$lookup:{
    from: "teste",
    let: { indice: "$_id" },
    pipeline: [
      {$group:{
        _id: null,
        user:{$sum:{$cond:[
          {$eq:["$user", "$$indice"]}, 1, 0
        ]}},
        last:{$sum:{$cond:[
          {$eq:["$last", "$$indice"]}, 1, 0
        ]}}
      }},
      {$project:{
        _id: 0
      }}
    ],
    as: "res"
  }}
])

这篇关于组计数2项对象数组mongodb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！