consider all fields when grouping in mongodb

Question

consider all fields when grouping in mongodb

Navigation

#1 by (1 votes)

0

I have the following array:

[
  {
    “user”: “USER1”,
    “last”: “USER1”
  },
  {
    “user”: “USER1”,
    “last”: “USER2”
  },
  {
    “user”: “USER2”,
    “last”: “USER1”
  },
  {
    “user”: “USER2”,
    “last”: “USER2”
  },
  {
    “user”: “USER1”,
    “last”: “USER2”
  },
  {
    “user”: “USER1”,
    “last”: null
  }
]

I'm running the following query on mongodb:

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

I expect this result:

[
  {
    _id: “USER1”
    total: 4,
    last: 2
  },
{
    _id: “USER2”
    total: 2,
    last: 3
  }
]

But I'm getting this:

[
  {
    _id: “USER1”
    total: 4,
    last: 1
  },
  {
    _id: “USER2”
    total: 2,
    last: 1
  }
]

I know the problem is in grouping, it only finds last if the current user is the same.

How can I group, but at the time of counting consider all values?

mongodb

asked by anonymous 10.09.2018 / 16:44

1 answer

Pyqt5 how can I make a button select a stackedwidget from qt Callback with Redux-thunk

score 1 · Accepted Answer

I'll take you step-by-step to get you everything!

//Primeiro vamos extrair somente as informações que queremos para nossa busca, no caso os users diferentes
db.teste.aggregate([
  {$group:{
    _id: "$user"
  }}
])

//Aqui vamos pegar os indices da busca reinserir no nosso filtro
db.teste.aggregate([
  {$group:{
    _id: "$user"
  }},
  {$lookup:{
    from: "teste",
    let: { indice: "$_id" },
    pipeline: [],
    as: "res"
  }}
])

//Vamos contrar o total de elementos reinseridos
db.teste.aggregate([
  {$group:{
    _id: "$user"
  }},
  {$lookup:{
    from: "teste",
    let: { indice: "$_id" },
    pipeline: [
      {$group:{
        _id: null,
        total:{$sum:1}
      }}
    ],
    as: "res"
  }}
])

//Agora vamos testar uma cond verdadeira
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 testada vamos extrair o que queremos
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"
  }}
])

//Vamos tirar o _id da sub-colection pois não precisamos dele
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"
  }}
])