mongodb - MongoDB 聚合到 $group 并有条件地 $addToSet

Question

鉴于以下虚拟集合，我想提取给定资源的出口国和进口国：

[{
    country: "France",
    exchange: {
        export: [{
            resource: "MILK",
            origin: ["Toulouse", "Bordeaux"]
        }],
        import: [{
            resource: "BEEF",
            origin: ["Lyon", "Marseille"]
        }]
    }
}, {
    country: "Spain",
    exchange: {
        export: [{
            resource: "PORK",
            origin: ["Madrid", "Barcelona"]
        }],
        import: [{
            resource: "MILK",
            origin: ["Valencia", "Bilbao"]
        }]
    }
}]

预期结果：

{
    resource: "MILK",
    exportingCountries: ["France"],
    importingCountries: ["Spain"]
}

我一直在玩，$group但我找不到有条件的$addToSet国家的方法。

标签： mongodbaggregation-framework

Answer 1

您可以使用$concatArrays来组合exchange.export和exchange.import数组。这允许您$group bycountry然后您需要返回import并export使用$filter和$map运算符，尝试：

db.col.aggregate([
    {
        $project: {
            country: 1,
            resources: {
                $concatArrays: [
                    { $map: { input: "$exchange.export", in: { resource: "$$this.resource", exchange: "export" } } },
                    { $map: { input: "$exchange.import", in: { resource: "$$this.resource", exchange: "import" } } },
                ]
            }
        }
    },
    {
        $unwind: "$resources"
    },
    {
        $group: {
            _id: "$resources.resource",
            resources: { $addToSet: { country: "$country", exchange: "$resources.exchange" } }
        }
    },
    {
        $project: {
            _id: 0,
            resource: "$_id",
            importingCountries: { 
                $map: { 
                    input: { $filter: { input: "$resources", as: "r", cond: { $eq: [ "$$r.exchange", "import" ] } } },
                    in: "$$this.country" 
                } 
            },
            exportingCountries: { 
                $map: { 
                    input: { $filter: { input: "$resources", as: "r", cond: { $eq: [ "$$r.exchange", "export" ] } } },
                    in: "$$this.country" 
                } 
            }
        }
    }
])

输出：

{ "resource" : "PORK", "importingCountries" : [ ], "exportingCountries" : [ "Spain" ] }
{ "resource" : "BEEF", "importingCountries" : [ "France" ], "exportingCountries" : [ ] }
{ "resource" : "MILK", "importingCountries" : [ "Spain" ], "exportingCountries" : [ "France" ] }