MongoDB使用两个不同的键聚合结果 [英] MongoDB aggregate result with two different keys

问题描述

我最近提出了这个问题，该问题帮助我在聚合mongoDB查询中获得用于获取结果的数组(

I have recently put this question that helped me to obtain an array for obtaining a result in an aggregation mongoDB query ( MongoDB Aggregate Array with Two Fields )

我现在的问题是要获得两个不同的键的组合.

My problem right now is to obtain combine two different keys.

我有一个带有普通产品的产品系列:

I have a products collection with general products on it:

{
  "_id" : ObjectId("554b9f223d77c810e8915539"),
  "brand" : "Airtex",
  "product" : "E7113M",
  "type" : "Fuel Pump",
  "warehouse_sku" : [
    "1_5551536f3d77c870fc388a04",
    "2_55515e163d77c870fc38b00a"
   ]
}

我有以下子产品(上面有多个常规产品)

And I have the following child product (it has multiple general products on it)

{
    "_id" : ObjectId("55524d0c3d77c8ba9cb2d9fd"),
    "brand" : "Performance",
    "product" : "P41K",
    "type" : "Fuel Pump Component",
    "general_products" : [
        ObjectId("554b9f123d77c810e891552f"),
        ObjectId("554b9f143d77c810e8915530"),
        ObjectId("554b9f173d77c810e8915533"),
        ObjectId("554b99b83d77c810e8915436"),
        ObjectId("554b9f2e3d77c810e8915549")
    ],
    "warehouse_sku" : [
        "1_555411043d77c8066b3b6720",
        "1_555411073d77c8066b3b6728"
    ]
}

我的问题是我想要具有_id的常规产品以及与特定Warehouse_sku模式匹配的内部子产品.期望的结果是这样的:

My problem here is I want the general products with _id and inside child products that matches a specific warehouse_sku pattern. The desired result is like this:

{ "_id" : ObjectId("554b9f2e3d77c810e8915549") }
{ "_id" : ObjectId("554b99b83d77c810e8915436") }
{ "_id" : ObjectId("554b9f173d77c810e8915533") }
{ "_id" : ObjectId("554b9f143d77c810e8915530") }
{ "_id" : ObjectId("554b9f123d77c810e891552f") }
{ "_id" : ObjectId("554b9f223d77c810e8915539") }

我进行了多个查询，例如:

I have made multiple queries like:

1)

db.products.aggregate([
... {$match:{warehouse_sku: /^1/ }},
... {$unwind:"$general_products"},
... {$group:{_id: "$general_products"}}
... ])

但普通产品上的_id不在上面:

but the _id from general product is not on it:

{ "_id" : ObjectId("554b9f2e3d77c810e8915549") }
{ "_id" : ObjectId("554b99b83d77c810e8915436") }
{ "_id" : ObjectId("554b9f173d77c810e8915533") }
{ "_id" : ObjectId("554b9f143d77c810e8915530") }
{ "_id" : ObjectId("554b9f123d77c810e891552f") }

2)

db.products.aggregate([
... {$match:{warehouse_sku: /^1/ }},
... {$group:{_id: "$_id"}}
... ])

但是请给我通用产品和子产品中的_id [不要]，而不是子产品中常规产品中的_id:

but give me the _id from the general and child products [don't want it] but not the _id's from general products inside the child product:

{ "_id" : ObjectId("55524d0c3d77c8ba9cb2d9fd") }
{ "_id" : ObjectId("554b9f223d77c810e8915539") }

推荐答案

使用以下聚合管道来获取所需的ObjectId列表.这使用 $ifNull 聚合运算符，如果数组general_products字段不存在，则添加_id字段:

Use the following aggregation pipeline to get the desired list of ObjectIds. This uses the $ifNull aggregation operator to add the _id field if the array general_products field does not exist:

db.products.aggregate([
    {
        "$match": {"warehouse_sku": /^1/ }
    },
    {
        "$group": {
            "_id": {
                "_id": "$_id",
                "general_products": "$general_products"
            },
            "data": {
                "$addToSet": "$_id"
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "general_products": {
                "$ifNull": ["$_id.general_products", "$data"]
            }
        }
    },
    {
        "$unwind": "$general_products"
    },
    {
        "$group": {
            "_id": null,
            "list_products": {
                "$addToSet": "$general_products"
            }
        }
    }
]);

这将为您提供一个带有对象ID的数组list_products的文档:

This will give you a document with an array list_products with ObjectIds:

/* 1 */
{
    "result" : [ 
        {
            "_id" : null,
            "list_products" : [ 
                ObjectId("554b9f223d77c810e8915539"), 
                ObjectId("554b9f2e3d77c810e8915549"), 
                ObjectId("554b99b83d77c810e8915436"), 
                ObjectId("554b9f173d77c810e8915533"), 
                ObjectId("554b9f143d77c810e8915530"), 
                ObjectId("554b9f123d77c810e891552f")
            ]
        }
    ],
    "ok" : 1
}

这篇关于MongoDB使用两个不同的键聚合结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！