mongodb交叉连接查询提供一个字段的总和

yjghlzjz 于 2023-03-22 发布在 Go

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

这是演示数据。考虑我在mongodb集合中有以下文档，称为transaction

{User:'user1',transaction:10,shop:'shop1'},
{User:'user1',transaction:20,shop:'shop2'},
{User:'user2',transaction:60,shop:'shop3'},
{User:'user3',transaction:80,shop:'shop4'},
{User:'user1',transaction:50,shop:'shop2'},
{User:'user2',transaction:5,shop:'shop1'},
{User:'user3',transaction:11,shop:'shop4'},
{User:'user2',transaction:32,shop:'shop2'},
{User:'user3',transaction:56,shop:'shop1'},
{User:'user1',transaction:89,shop:'shop3'},
{User:'user2',transaction:12,shop:'shop4'}

我需要与所有可能的组合和交易的结果应该为他们总结一样

User1 Shop1 10
User1 Shop2 70
User1 Shop3 89
User1 Shop4 0
User2 Shop1 5
User2 Shop2 32
User2 Shop3 60
User2 Shop4 12
User3 Shop1 56
User3 Shop2 0
User3 Shop3 0
User3 Shop4 91

你能帮我查询最佳的过程，以实现结果。提前感谢
我得到用户和商店组合列表使用following查询，但不知道我将如何获得交易的总和。

test> db.transaction.aggregate(
[{$lookup: 
{
  from: 'transaction',
  pipeline: [{$project: {_id:0, User:1}}],
  as: 'User'
}}, 
{$unwind: 
{
  path: "$User"
}}, 
{$project: {
User: 1,
shop: 1
}}
])
test>

mongodb

来源：https://stackoverflow.com/questions/75767423/mongodb-crossjoin-query-to-provide-sum-of-one-field

1条答案

按热度按时间

r55awzrz1#

您只需要group您的数据。

**注意：**您不会得到User* Shop* 0

db.transaction.aggregate([
  {
    $group: {
      _id: {
        User: "$User",
        shop: "$shop"
      },
      transaction: {
        $sum: "$transaction"
      }
    }
  },
  {
    $project: {
      _id: 0,
      User: "$_id.User",
      shop: "$_id.shop",
      transaction: 1
    }
  },
  {
    $sort: {
      User: 1,
      shop: 1
    }
  }
])

MongoPlayground

赞(0）回复(0）举报 2023-03-22

我来回答

mongodb交叉连接查询提供一个字段的总和

1条答案

相关问题

热门标签

最新问答