I am having a problem with my query using MongoDB aggregation.

I have a collection as follows:

[
  {
    id: 1,
    name: 'cash', 
    amount: 10,
  }
  {
    id: 2
    name: 'IPT',
    amount: 10,
    terminal_type: 'inside',
    card: {
      id: 1,
      name: 'visa',
    },
  },
  {
    id: 2,
    name: 'IPT', 
    amount: 10,
    terminal_type: 'outside',
    card: {
      id: 1,
      name: 'visa',
    },
  },
]

Expected result:

[
  {
    id: 1,
    name: 'cash',
    amount: 10,
  },
  {
    id: 2,
    name: 'IPT',
    amount: 20,
    cards: [
      {
        id: 1,
        name: 'visa',
        amount: 20,
      },
    ],
    terminals: [
      {
        name: 'inside',
        amount: 10,
      },
      {
        name: 'outside',
        amount: 10,
      },
    ],
  },
]

What I have tried:

{
  $group: {
    _id: {
      id: '$id',
      card_id: '$card.id',
      terminal_type: '$terminal_type',
    },
    name: {$first: '$name'},
    amount: {$sum: '$amount'},
    card_name: {$sum: '$card.name'},
  }
},
{
  $group: {
    _id: {
      id: '$id',
      card_id: '$_id.card_id',
    },
    name: {$first: '$name'},
    amount: {$sum: '$amount'},
    card_name: {$first: '$card_name'},
    terminals: {
      $push: {
        { $cond: [
            {$ifnull: ['$terminal_type', false]},
            {
              type: '$terminal_type',
              amount: '$amount',
            },
            '$$REMOVE',
          ]
        }
      }
    }
  }
},
{
  $group: {
    _id: '$_id.id',
    name: {$first: '$name'},
    amount: {$sum: '$amount'},
    cards: {
      $push: {
        { $cond: [
            {$ifnull: ['$id.card_id', false]},
            {
              id: '$_id.card_id',
              name: '$card_name',
              amount: '$amount',
            },
            '$$REMOVE',
          ],
        },
    },
    terminals: // This is the problem where I can't figure out how get this value
  }
}

I considered to unwind terminals before the last group pipeline but I ended up getting duplicate documents which make the sum for the amount incorrect. Can anyone help me to solve this or point me to where I can read and understand more about this? Thank you very much.

CodePudding user response：

One option to go is to $group and then $reduce:

db.collection.aggregate([
  {$group: {
      _id: "$id",
      name: {$first: "$name"},
      amount: {$sum: "$amount"},
      terminals: {$push: {name: "$terminal_type", amount: "$amount"}},
      cards: {$push: {id: "$card.id", name: "$card.name", amount: "$amount"}},
      cardIds: {$addToSet: "$card.id"},
      terminalNames: {$addToSet: "$terminal_type"}
  }},
  { $project: {
      _id: 0, id: "$_id", name: 1, amount: 1,
      cards: {
        $map: {
          input: "$cardIds",
          as: "card",
          in: {
            id: "$$card",
            amount: {$reduce: {
                input: "$cards",
                initialValue: 0,
                in: {$add: [
                    "$$value",
                    {$cond: [{$eq: ["$$card", "$$this.id"]},"$$this.amount", 0]}
                ]}
            }},
            name: {$reduce: {
                input: "$cards",
                initialValue: "",
                in: {$cond: [{$eq: ["$$this.id", "$$card"]}, "$$this.name", "$$value"]}
            }}
          }
      }},
      terminals: {
        $map: {
          input: "$terminalNames",
          as: "terminal",
          in: {
            name: "$$terminal",
            amount: {$reduce: {
                input: "$terminals",
                initialValue: 0,
                in: {$add: [
                    "$$value",
                    {$cond: [{$eq: ["$$terminal", "$$this.name"]}, "$$this.amount", 0]}
                ]}
            }}
          }
        }
      }
  }}
])

See how it works on the playground example

Another option may be to use $unwind as you mentioned:

db.collection.aggregate([
  {$group: {
      _id: "$id",
      name: {$first: "$name"},
      amount: {$sum: "$amount"},
      terminals: {$push: {
          type: "terminal",
          name: "$terminal_type",
          key: "$terminal_type",
          amount: "$amount"
      }},
      cards: {$push: {
          type: "card",
          id: "$card.id",
          key: "$card.id",
          name: "$card.name",
          amount: "$amount"
      }}
  }},
  {$project: {amount: 1, name: 1, docs: {$concatArrays: ["$cards", "$terminals"]}}},
  {$unwind: "$docs"},
  {$group: {
      _id: {_id: "$_id", type: "$docs.type", key: "$docs.key"},
      amount: {$sum: "$docs.amount"},
      name: {$first: "$docs.name"},
      dataAmount: {$first: "$amount"},
      dataName: {$first: "$name"}
  }},
  {$group: {
      _id: "$_id._id",
      amount: {$first: "$dataAmount"},
      name: {$first: "$dataName"},
      terminals: {$push: {
          $cond: [
            {$and: [{$eq: ["$_id.type", "terminal"]}, {$gt: ["$name", null]}]},
            {amount: "$amount", name: "$name"},
            "$$REMOVE"
          ]
      }},
      cards: {$push: {
          $cond: [
            {$and: [{$eq: ["$_id.type", "card"]}, {$gt: ["$name", null]}]},
            {amount: "$amount", name: "$name", id: "$_id.key"},
            "$$REMOVE"
          ]
      }}
  }}
])

See how it works on the playground example