CODEWITHSUNDEEP
mongodbaggregation-framework
yodalr
yodalr

Reputation: 10902

MongoDB: single find request to return data from different documents with different fields

I have this collection:

{
    "name": "Leonardo",
    "height": "180",
    "weapon": "sword",
    "favorite_pizza": "Hawai"
},
{
    "name": "Donatello",
    "height": "181",
    "weapon": "stick",
    "favorite_pizza": "Pepperoni"
},
{
    "name": "Michelangelo",
    "height": "182",
    "weapon": "nunchucks",
    "favorite_pizza": "Bacon"
},
{
    "name": "Raphael",
    "height": "183",
    "weapon": "sai",
    "favorite_pizza": "Margherita"
}

With using one query I want this result (ordered by height):

{
    "name": "Leonardo",
    "height": "180",
    "weapon": "sword",
    "favorite_pizza": "Hawai"
},
{
    "name": "Donatello",
},
{
    "name": "Michelangelo",
},
{
    "name": "Raphael",
}

So the query needs to first get the document which has smallest height field and then get all contents of that document, then it needs to get all other documents and return only name field of those documents, while ordering those documents by height.

Upvotes: 0

Views: 88

Answers (2)

dnickless
dnickless

Reputation: 10918

Just for completeness reasons...

@Veeram's answer is probably the better choice (I have a feeling it should be faster and easier to understand) but you can achieve the same result using a slightly simpler $group stage followed by slightly more complex $project stage using $reduce:

collection.aggregate([{
    $sort: {
        "height": 1
    }
}, {
    $group: {
        "_id":null,
        "allnames": {
            $push: "$$ROOT"
        }
    }
}, {
    $project: {
        "data": {
            $reduce: {
                input: "$allnames",
                initialValue: null,
                in: {
                    $cond: [{
                        $eq: [ "$$value", null ] // if it's the first time we come here
                    },
                    [ "$$this" ], // we include the entire document
                    {
                        $concatArrays: [ // else we concat
                            "$$value",   // the already concatenated values
                            [ { "name": "$$this.name" } ] // with the "name" of the currently looked at document
                        ]
                    }]
                }
            }
        }
    }
}, {
    $unwind: "$data"
}, {
    $replaceRoot: {
        "newRoot": "$data"
    }
}])

Alternatively - as pointed out by @Veeram in the comment below - , it's possible to write the $reduce in this way:

$project: {
    "data": {
        $reduce: {
            input: { "$slice": [ "$allnames", 1, { $size: "$allnames" } ] }, // process everything in the "allnames" array except for the first item
            initialValue: { "$slice": [ "$allnames", 1 ] }, // start with the first item
            in: { $concatArrays: [ "$$value", [ { "name": "$$this.name" } ] ]} // and keep appending the "name" field of all other items only
        }
    }
}

Upvotes: 0

s7vr
s7vr

Reputation: 75934

Change your height to numeric for correct sorting and you can try below aggregation in 3.4 pipeline.

The query $sorts the document by "height" ascending followed by $group to create two fields, "first" field which has the smallest height record ($$ROOT to access the whole document) and "allnames" to record all names.

$project with $slice + $concatArrays to replace the "allnames" array first element with the smallest height document and get the updated array.

$unwind with $replaceRoot to promote all the docs to top level. 

db.colname.aggregate([
{"$sort":{
  "height":1
}},
{"$group":{
  "_id":null,
  "first":{"$first":"$$ROOT"},
  "allnames":{"$push":{"name":"$name"}}
}},
{"$project":{
  "data":{"$concatArrays":[["$first"],{"$slice":["$allnames",1,{"$size":"$allnames"}] } ]}
}},
{"$unwind":"$data"},
{"$replaceRoot":{"newRoot":"$data"}}
])

Upvotes: 2

Related Questions