distinct values from multiple fields in array

Posted on

Question :

I have a similar mongodb collection:

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

I want distinct values of resource for both import and export.
Expected result is:

{
    "countries" : ["Spain","France"],
    "export" : ["PORK", "MILK"],
    "import" : ["WHEAT","BEEF"]
}

Here’s what I’m doing

db.countries.aggregate([
{$group: {
    _id: null,
    countries: {$addToSet: '$country'},
    export: {$addToSet: '$exchange.export.resource'},
    import: {$addToSet: '$exchange.import.resource'}
    }}
])

which returns:

{
    "countries" : ["Spain","France"],
    "export" : [["PORK"], ["MILK"]],
    "import" : [["WHEAT"],["BEEF"]]
}

these extra arrays (["PORK"]) surrounding values are annoying: how can I avoid them ?

Answer :

Nevermind, just found the answer, just need to add 2 unwind stages

[{
    $unwind: {
        path: '$exchange.export'
    }
}, {
    $unwind: {
        path: '$exchange.import'
    }
}, {
    $group: {
        _id: null,
        countries: {$addToSet: '$country'},
        export: {$addToSet: '$exchange.export.resource'},
        import: {$addToSet: '$exchange.import.resource'}
    }
}]

Leave a Reply

Your email address will not be published. Required fields are marked *