时间戳

首页 > 解决方案 > mongodb：复杂的嵌套聚合

mongodb - mongodb：复杂的嵌套聚合

问题描述

我有这个收藏：

[
  {
    "_id": {
      "$oid": "60b22e1dbd46fa18a8308318"
    },
    "title": "basketball",
    "price": 12,
    "category": "Furniture",
    "description": "",
    "images": [
      "http://res.cloudinary.com/hadarush100/image/upload/v1622289949/nfg948x3zro6gbiuknrz.jpg"
    ],
    "categoryId": 1,
    "userId": "60ad16493062eb11141d4927",
    "createdAt": 1622289948232,
    "chats": [
      {
        "id": 1,
        "createdAt": 1622289948232,
        "messages": [
          {
            "id": "1",
            "createdAt": 1622289948232,
            "senderId": "60ad16493062eb11141d4927",
            "text": "Hello, Im the seller of this product."
          }
        ]
      },
      {
        "id": "2",
        "createdAt": 1622289948232,
        "messages": [
          {
            "id": 1,
            "createdAt": 1622289948232,
            "senderId": "60ad16493062eb11141d4927",
            "text": "Hello, Im the seller of this product."
          }
        ]
      }
    ]
  }
]

我想找到特定的文档（通过_id），然后深入到这个文档中的特定聊天（通过id），而不是使用$lookup将每条消息中的“senderId”属性替换为包含完整发件人的“发件人”属性存在于另一个集合（用户）中的详细信息（作为用户）。结果需要如下所示：

[
  {
    "_id": {
      "$oid": "60b22e1dbd46fa18a8308318"
    },
    "title": "basketball",
    "price": 12,
    "category": "Furniture",
    "description": "",
    "images": [
      "http://res.cloudinary.com/hadarush100/image/upload/v1622289949/nfg948x3zro6gbiuknrz.jpg"
    ],
    "categoryId": 1,
    "userId": "60ad16493062eb11141d4927",
    "createdAt": 1622289948232,
    "chats": [
      {
        "id": 1,
        "createdAt": 1622289948232,
        "messages": [
          {
            "id": "1",
            "createdAt": 1622289948232,
            "sender": {
                "_id": {
                    "$oid": "60ad16493062eb11141d4927"
                },
                "username": "hadar",
                "email": "hadarushha@gmail.com",
                "profileImgUrl": "https://randomuser.me/api/portraits/men/79.jpg",
                "createdAt": 1621956168518
            },
            "text": "Hello, Im the seller of this product."
          }
        ]
      },
      {
        "id": "2",
        "createdAt": 1622289948232,
        "messages": [
          {
            "id": 1,
            "createdAt": 1622289948232,
            "sender": {
                "_id": {
                    "$oid": "60ad16493062eb11141d4927"
                },
                "username": "hadar",
                "email": "hadarushha@gmail.com",
                "profileImgUrl": "https://randomuser.me/api/portraits/men/79.jpg",
                "createdAt": 1621956168518
            },
            "text": "Hello, Im the seller of this product."
          }
        ]
      }
    ]
  }
]

标签： mongodbmongodb-queryaggregation-framework

解决方案

您可以使用此聚合：

  • $match仅过滤选定的文档（_id）
  • $unwind多次将数组转换为对象
  • $lookup查询外部集合（用户）
  • $group以相反的顺序

我假设您的收藏或多或少是这样的（下一次，发布两个收藏以及一个工作操场上的示例）

db={
  "products": [
    {
      "_id": {
        "$oid": "60b22e1dbd46fa18a8308318"
      },
      "title": "basketball",
      "price": 12,
      "category": "Furniture",
      "description": "",
      "images": [
        "http://res.cloudinary.com/hadarush100/image/upload/v1622289949/nfg948x3zro6gbiuknrz.jpg"
      ],
      "categoryId": 1,
      "userId": "60ad16493062eb11141d4927",
      "createdAt": 1622289948232,
      "chats": [
        {
          "id": 1,
          "createdAt": 1622289948232,
          "messages": [
            {
              "id": "1",
              "createdAt": 1622289948232,
              "senderId": "60ad16493062eb11141d4927",
              "text": "Hello, Im the seller of this product."
            }
          ]
        },
        {
          "id": "2",
          "createdAt": 1622289948232,
          "messages": [
            {
              "id": 1,
              "createdAt": 1622289948232,
              "senderId": "60ad16493062eb11141d4927",
              "text": "Hello, Im the seller of this product."
            }
          ]
        }
      ]
    },
    {
      "_id": {
        "$oid": "60b22e1dbd46fa18a8308319"
      },
      "title": "volleyball",
      "price": 8,
      "category": "Furniture",
      "description": "",
      "images": [
        "http://res.cloudinary.com/hadarush100/image/upload/v1622289949/nfg948x3zro6gbiuknrz.jpg"
      ],
      "categoryId": 1,
      "userId": "60ad16493062eb11141d4927",
      "createdAt": 1622289948232,
      "chats": [
        {
          "id": 1,
          "createdAt": 1622289948232,
          "messages": [
            {
              "id": "1",
              "createdAt": 1622289948232,
              "senderId": "60ad16493062eb11141d4927",
              "text": "Hello, Im the seller of this product."
            }
          ]
        },
        {
          "id": "2",
          "createdAt": 1622289948232,
          "messages": [
            {
              "id": 1,
              "createdAt": 1622289948232,
              "senderId": "60ad16493062eb11141d4928",
              "text": "Hello, Im the seller of this product."
            }
          ]
        }
      ]
    }
  ],
  "users": [
    {
      "_id": {
        "$oid": "60ad16493062eb11141d4927"
      },
      "username": "hadar",
      "email": "hadarushha@gmail.com",
      "profileImgUrl": "https://randomuser.me/api/portraits/men/79.jpg",
      "createdAt": 1621956168518
    },
    {
      "_id": {
        "$oid": "60ad16493062eb11141d4928"
      },
      "username": "test",
      "email": "test@gmail.com",
      "profileImgUrl": "https://randomuser.me/api/portraits/men/49.jpg",
      "createdAt": 1621956168528
    },
    
  ]
}

这是工作聚合：

db.products.aggregate([
  {
    "$match": {
      "_id": {
        "$oid": "60b22e1dbd46fa18a8308319"
      }
    }
  },
  {
    "$unwind": "$chats"
  },
  {
    "$unwind": "$chats.messages"
  },
  {
    "$addFields": {
      "chats.messages.senderIdObjId": {
        "$convert": {
          "input": "$chats.messages.senderId",
          "to": "objectId",
          
        }
      }
    }
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "chats.messages.senderIdObjId",
      "foreignField": "_id",
      "as": "chats.messages.sender"
    }
  },
  {
    "$unwind": "$chats.messages.sender"
  },
  {
    "$group": {
      "_id": "$chats.id",
      "messages": {
        "$push": "$chats.messages"
      },
      "allFields": {
        "$first": "$$ROOT"
      }
    }
  },
  {
    "$addFields": {
      "allFields.chats.messages": "$messages"
    }
  },
  {
    "$replaceWith": "$allFields"
  },
  {
    "$group": {
      "_id": "$_id",
      "chats": {
        "$push": "$chats"
      },
      "allFields": {
        "$first": "$$ROOT"
      }
    }
  },
  {
    "$addFields": {
      "allFields.chats": "$chats"
    }
  },
  {
    "$replaceWith": "$allFields"
  },
  
])

在这里工作的游乐场

推荐阅读