MongoDB聚合：分组并推送多个属性

3htmauhk 于 2022-11-22 发布在 Go

关注(0)|答案(1)|浏览(158)

我使用MongoDB聚合进行查询时遇到问题。
我有一个集合如下：

[
  {
    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',
    },
  },
]

预期结果：

[
  {
    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,
      },
    ],
  },
]

我尝试过的方法：

{
  $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
  }
}

我曾考虑在最后一个组管道之前展开terminals，但最后我得到了重复的文档，这使得金额的总和不正确。有人能帮助我解决这个问题吗？或者告诉我在哪里可以阅读和了解更多关于这个问题的信息？非常感谢。

mongodb

来源：https://stackoverflow.com/questions/74515763/mongodb-aggregation-group-and-push-multiple-attributes

1条答案

按热度按时间

xtfmy6hx1#

一种选择是先访问$group，然后访问$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]}
                ]}
            }}
          }
        }
      }
  }}
])

了解它在playground example上的工作原理
另一个选项可能是使用您提到的$unwind：

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"
          ]
      }}
  }}
])

了解它在playground example上的工作原理

赞(0）回复(0）举报 2022-11-22

我来回答

MongoDB聚合：分组并推送多个属性

1条答案

相关问题

热门标签

最新问答