CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Darryl Quinn
Darryl Quinn

Reputation: 53

Combining multiple sub-documents into a new doc in mongo

I am trying to query multiple sub-documents in MongoDB and return as a single doc. I think the aggregation framework is the way to go, but, can't see to get it exactly right.

Take the following docs:

{
    "board_id": "1",
    "hosts": 
     [{
        "name": "bob",
        "ip": "10.1.2.3"
     },
    {
         "name": "tom",
         "ip": "10.1.2.4"
    }]
 }

{
    "board_id": "2",
    "hosts": 
    [{
        "name": "mickey",
        "ip": "10.2.2.3"
    },
    {
        "name": "mouse",
        "ip": "10.2.2.4"
    }]
}

{
     "board_id": "3",
     "hosts": 
    [{
        "name": "pavel",
        "ip": "10.3.2.3"
    },
    {
        "name": "kenrick",
        "ip": "10.3.2.4"
     }]
}

Trying to get a query result like this:

{
    "hosts": 
    [{
        "name": "bob",
        "ip": "10.1.2.3"
    },
    {
         "name": "tom",
         "ip": "10.1.2.4"
    },
    {
         "name": "mickey",
         "ip": "10.2.2.3"
    },
    {
        "name": "mouse",
        "ip": "10.2.2.4"
    }, 
    {
         "name": "pavel",
         "ip": "10.3.2.3"
     },
     {
        "name": "kenrick",
        "ip": "10.3.2.4"
     }]
}

I've tried this:

db.collection.aggregate([ { $unwind: '$hosts' }, { $project : { name: 1, hosts: 1, _id: 0 }} ])

But it's not quite what I want.

Upvotes: 3

Views: 1261

Answers (2)

Saleem
Saleem

Reputation: 9008

You can definitely do this with aggregate. Let's assume your data is in collection named board, so please replace it with whatever your collection name is.

db.board.aggregate([
{$unwind:"$hosts"},
{$group:{_id:null, hosts:{$addToSet:"$hosts"}}},
{$project:{_id:0, hosts:1}}
]).pretty()

it will return

    {
        "hosts" : [
            {
                "name" : "kenrick",
                "ip" : "10.3.2.4"
            },
            {
                "name" : "pavel",
                "ip" : "10.3.2.3"
            },
            {
                "name" : "mouse",
                "ip" : "10.2.2.4"
            },
            {
                "name" : "mickey",
                "ip" : "10.2.2.3"
            },
            {
                "name" : "tom",
                "ip" : "10.1.2.4"
            },
            {
                "name" : "bob",
                "ip" : "10.1.2.3"
            }
        ]
    }

Upvotes: 2

Blakes Seven
Blakes Seven

Reputation: 50426

So your basic problem here is that the arrays are contained in separate documents. So while you are correct to $unwind the array for processing, in order to bring the content into a single array you would need to $group the result across documents, and $push the content to the result array:

db.collection.aggregate([
    { "$unwind": "$hosts" },
    { "$group": {
        "_id": null,
        "hosts": { "$push": "$hosts" }
    }}
])

So just as $unwind will "deconstruct" the array elements, the $push accumulator in $group brings "reconstructs" the array. And since there is no other key to "group" on, this brings all the elements into a single array.

Note that a null grouping key is only really practical when the resulting document would not exceed the BSON limit. Otherwise you are better off leaving the individual elements as documents in themselves.

Optionally remove the _id with an additional $project if required.

Upvotes: 1

Related Questions