CODEWITHSUNDEEP
mongodbaggregation-framework
Syffys
Syffys

Reputation: 610

MongoDB Aggregation to $group and conditionnally $addToSet

Given the following dummy collection, I want to extract exporting and importing countries for a given resource:

[{
    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"]
        }]
    }
}]

Expected result:

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

I've been playing with $group but I can't find a way to conditionnally $addToSet countries.

Upvotes: 2

Views: 52

Answers (1)

mickl
mickl

Reputation: 49945

You can use $concatArrays to combine exchange.export and exchange.import arrays. This allows you to $group by country and then you need to get back import and export using $filter and $map operators, try:

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" 
                } 
            }
        }
    }
])

Output:

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

Upvotes: 1

Related Questions