mongoose Mongo聚合多个集合

628mspwn 于 2023-03-12 发布在 Go

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

我在mongodb上有四个不同的收藏。
采集1（用户）：

{
   id:'123',
   name:'user_name',
   cities:['1','2'],
   services:['1','2'],
   email:'mail@domain.com'
}

集合2（服务）：

{
   service_id:'1',
   service_name:'Service Name 1'
},
{
   service_id:'2',
   service_name:'Service Name 2'
},
{
   service_id:'3',
   service_name:'Service Name 3'
},
{
   service_id:'4',
   service_name:'Service Name 4'
}

收集3（价格）：

{
   price_id:'1',
   service_id:'1',
   price:'£15'
   user_id:'123'
},
{
   price_id:'2',
   service_id:'2',
   price:'£25'
   user_id:'123'
},

集合4（城市）：

{
   city_id:'1',
   city_name:'City Name 1'
},
{
   city_id:'2',
   city_name:'City Name 2'
},
{
   city_id:'3',
   city_name:'City Name 3'
},
{
   city_id:'4',
   city_name:'City Name 4'
},

我试过得到下面这样的结果，但我做不到。

{
  user_id:1,
  name:'user_name',
  cities:['City Name 1','City Name 2'],
  prices:[
    {
      service_name:'Service Name 1,
      price:'£15'
    },
    {
     service_name:'Service Name 2',
     price:'£25'
    }
  ]
  email:'mail@domain.com'

在使用Collection.aggregate之前，我使用Collection.findOne或Collection.find将记录与其id进行匹配以获得预期结果，我得到了我所期望的结果。我不知道这是因为MongoDB还是node.js，但有时它会返回空结果，这是一个问题。使用collection.aggregate获得这样结果的最佳方法是什么？
谢谢

mongoose

来源：https://stackoverflow.com/questions/75690716/mongo-aggregate-multiple-collections

1条答案

按热度按时间

wmtdaxz31#

您可以参考以下汇总：https://mongoplayground.net/p/z--wtO__TrK

db.user.aggregate([
  {
    "$match": {
      id: "123",
      
    }
  },
  {
    "$lookup": {
      "from": "cities",
      "let": {
        "city_id": "$cities"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$city_id",
                "$$city_id"
              ]
            }
          }
        },
        {
          "$project": {
            "_id": 0,
            "city_id": 0
          }
        }
      ],
      "as": "cities"
    }
  },
  {
    "$lookup": {
      "from": "services",
      "let": {
        "service_id": "$services"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$service_id",
                "$$service_id"
              ]
            }
          }
        },
        {
          "$project": {
            "_id": 0,
            //"service_id": 0
            
          }
        }
      ],
      "as": "services"
    }
  },
  {
    "$unwind": {
      "path": "$services",
      
    }
  },
  {
    "$lookup": {
      "from": "prices",
      "let": {
        "service_id": [
          "$services.service_id"
        ]
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$service_id",
                "$$service_id"
              ]
            }
          }
        },
        {
          "$project": {
            "_id": 0,
            //"service_id": 0
            
          }
        }
      ],
      "as": "prices"
    }
  },
  {
    "$addFields": {
      "services.price": {
        "$arrayElemAt": [
          "$prices.price",
          0
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$id",
      "name": {
        "$first": "$name"
      },
      "email": {
        "$first": "$email"
      },
      "cities": {
        "$first": "$cities"
      },
      "services": {
        "$addToSet": "$services"
      }
    }
  }
])

流水线阶段：

match: to filter based on userid
    lookup: to get citi name
    lookup: to get service name
    unwind: as wehave multiple services we need to get exact price for given service so unwinding
    lookup: to get price based on matched service id
    addfields: project price under service 
    group: as we have unwinded services we need to rewind/group by user.

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

我来回答

mongoose Mongo聚合多个集合

1条答案

相关问题

热门标签

最新问答