Using aggregation to sorting in complex conditional in Mongodb

Question

I create a topic about using $match which was solved, but now I'm stuck in a more complex example. For example, consider the struct of my Json:

{
    user: 'bruno',
    answers: [
        {id: 0, value: 3.5},
        {id: 1, value: "hello"}
    ]
}

I would like to add or order, apply some transformations on the values contained in 'value' only the identifier 'id' equal to 0, for example a sort method.

Consider the following data:

db.my_collection.insert({ user: 'bruno', answers: [ {id: 0, value: 3.5}, {id: 1, value: "hello"}]})

db.my_collection.insert({ user: 'bruno2', answers: [ {id: 0, value: 0.5}, {id: 1, value: "world"}]})

I tried using:

db.my_collection.aggregate ({$sort: {"answers": {"$elemMatch": {id: 0, value: -1}}}})

But, it didn't work. The expected result was: {user: 'bruno2' answers: [{id: 0, value: 0.5}, {id: 1, value: "world"}]}, {user: 'bruno' answers: [{id: 0, value: 3.5}, {id: 1, value: "hello"}]})

Thank you.

david.storch · Accepted Answer · 2013-09-16 16:13:17Z

First, note that your sort example is malformed: the aggregate method takes an array as input, where each element in the array specifies a stage in an aggregation pipeline. Also, note that the $elemMatch operator cannot be used as part of a $sort stage.

One way to achieve what you're trying to do with your sort example is use the aggregation framework's $unwind pipeline operator. Unwinding an array will peel the array elements off one-by-one into separate documents. For example, the following query

db.my_collection.aggregate([ {$unwind: "$answers"} ]);

returns something like the following:

[
    {
        "_id" : ObjectId("5237157f3fac8e36fdb0b96e"),
        "user" : "bruno",
        "answers" : {
            "id" : 0,
            "value" : 3.5
        }
    },
    {
        "_id" : ObjectId("5237157f3fac8e36fdb0b96e"),
        "user" : "bruno",
        "answers" : {
            "id" : 1,
            "value" : "hello"
        }
    },
    {
        "_id" : ObjectId("523715813fac8e36fdb0b96f"),
        "user" : "bruno2",
        "answers" : {
            "id" : 0,
            "value" : 0.5
        }
    },
    {
        "_id" : ObjectId("523715813fac8e36fdb0b96f"),
        "user" : "bruno2",
        "answers" : {
            "id" : 1,
            "value" : "world"
        }
    }
]

Adding a $match phase will allow you to grab only the documents where answers.id is zero. Finally, a $sort phase allows you to sort by answers.value. All together the aggregation query is:

db.my_collection.aggregate([
    {$unwind: "$answers"},
    {$match: {"answers.id": 0}},
    {$sort: {"answers.value": -1}}
]);

And the output:

[
    {
        "_id" : ObjectId("5237157f3fac8e36fdb0b96e"),
        "user" : "bruno",
        "answers" : {
            "id" : 0,
            "value" : 3.5
        }
    },
    {
        "_id" : ObjectId("523715813fac8e36fdb0b96f"),
        "user" : "bruno2",
        "answers" : {
            "id" : 0,
            "value" : 0.5
        }
    }
]

Based on what your asking, it doesn't sound like you'll always need $unwind or even the aggregation framework. If instead you wanted to find the document with answers.id equal to 0 and answers.value equal to 3.5, and then change answers.value to 4 you could use find with $elemMatch followed by db.collection.save():

doc = db.my_collection.findOne({"answers": {$elemMatch: {"id": 0, "value": 3.5}}});
for (i=0; i<doc.answers.length; i++) {
  if (doc.answers[i].id === 0) {
    doc.answers[i].value = 4;
    db.my_collection.save(doc);
    break;
  }
}

I've been researching and found that the model of my JSON was complicated then proposed for applications developers a simpler way that I see it facilitated the development. Thank you for your solution, I learn a lot and understand your purpose.

Collectives™ on Stack Overflow

Using aggregation to sorting in complex conditional in Mongodb

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related