MongoDB: Find and then modify the result depending on if certain value in field array

Question

Is there an easy solution in MongoDB to find some objects that match a query and then to modify the result without modifying the persistent data depending on if a certain value is contained in an array?

Let explain me using an example:

students = [
  { 
    name: "Alice", 
    age: 25, 
    courses: [ { name: "Databases", credits: 6 },{ name: "Java", credits: 4 }] 
  }, 
  { 
    name: "Bob",  
    age: 22, 
    courses: [ { name: "Java", credits: 4 } ] 
  }, 
  { 
    name: "Carol", 
    age: 19, 
    courses: [ { name: "Databases", credits: 6 } ] 
  }, 
  { 
    name: "Dave", age: 18
  }
]

Now, I want to query all students. The result should return all their data except 'courses'. Instead, I want to output a flag 'participant' indicating whether that person participates in the Databases course:

result = [
  { name: "Alice", age: 25, participant: 1 }, 
  { name: "Bob", age: 22, participant: 0 },
  { name: "Carol", age: 19, participant: 1 }, 
  { name: "Dave", age: 18, participant: 0}
]

without changing anything in the database.

I've already found a solution using aggregate. But it's very complicated and unhandy and so, I would like to know if there is a more handy solution for this problem.

My current solution looks like the following:

db.students.aggregate([
  {$project: {"courses": {$ifNull: ["$courses", [{name: 0}]]}, name: 1, _id: 1, age: 1}}, 
  {$unwind: "$courses"}, 
  {$project: {name: 1, age: 1, participant: {$cond: [{$eq: ["$courses.name", "DB"]}, 1, 0]}}}, 
  {$group: {_id: {_id: "$_id", age: 1, name: "$name"}, participant: {$sum: "$participant"}}}, 
  {$project: {_id: 0, _id: "$_id._id", age: "$_id.age", name: "$_id.name", participant: 1}}
]);

One point I don't like in this solution is that I have to specify the output fields exactly three times. Also, this pipe is quite long.

I guess, it will be quite small. I plan to implement a paged query later (like Facebook when you scroll down), so some dozen objects will be retrieved each time. But this is executed very often. — Green 绿色
– Green 绿色, Commented Nov 20, 2015 at 12:59

chridam · Accepted Answer · 2015-11-20 14:31:30Z

Run the following aggregation pipeline to get the desired result:

db.students.aggregate([
    {
        "$project": {
            "name": 1,
            "age": 1,
            "participant": {
                "$size": {
                    "$ifNull" : [ 
                        {
                            "$setIntersection" : [
                                {
                                    "$map": {
                                        "input": "$courses",
                                        "as": "el",
                                        "in": {
                                            "$eq": [ "$$el.name", "Databases" ]
                                        }
                                    }
                                },
                                [true]
                            ]
                        },
                        []
                    ]
                }                
            }
        }
    }
])

Output:

{
    "result" : [ 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd216"),
            "name" : "Alice",
            "age" : 25,
            "participant" : 1
        }, 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd217"),
            "name" : "Bob",
            "age" : 22,
            "participant" : 0
        }, 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd218"),
            "name" : "Carol",
            "age" : 19,
            "participant" : 1
        }, 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd219"),
            "name" : "Dave",
            "age" : 18,
            "participant" : 0
        }
    ],
    "ok" : 1
}

The above pipeline uses only one step, $project in which the new field participant is created via a series of nested operators.

Crucial to the operations is the deeply nested $map operator which in essence creates a new array field that holds values as a result of the evaluated logic in a subexpression to each element of an array. Let's demonstrate this operation only by executing the pipeline with just the $map part:

db.students.aggregate([
    {
        "$project": {
            "name": 1,
            "age": 1,
            "participant": {
                "$map": {
                    "input": "$courses",
                    "as": "el",
                    "in": {
                        "$eq": [ "$$el.name", "Databases" ]
                    }
                }               
            }
        }
    }
])

Output

{
    "result" : [ 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd216"),
            "name" : "Alice",
            "age" : 25,
            "participant" : [ 
                true, 
                false
            ]
        }, 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd217"),
            "name" : "Bob",
            "age" : 22,
            "participant" : [ 
                false
            ]
        }, 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd218"),
            "name" : "Carol",
            "age" : 19,
            "participant" : [ 
                true
            ]
        }, 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd219"),
            "name" : "Dave",
            "age" : 18,
            "participant" : null
        }
    ],
    "ok" : 1
}

Probe the array further by introducing the $setIntersection operator which returns a set with elements that appear in all of the input sets. Thus in the above you would need to get a resulting array that has true to denote that document user participated in a Database course, else it will return an empty or null array. Let's see how adding that operator affects the previous result:

db.students.aggregate([
    {
        "$project": {
            "name": 1,
            "age": 1,
            "participant": {
                "$setIntersection" : [
                    {
                        "$map": {
                            "input": "$courses",
                            "as": "el",
                            "in": {
                                "$eq": [ "$$el.name", "Databases" ]
                            }
                        }
                    },
                    [true]
                ]                
            }
        }
    }
])

Output:

{
    "result" : [ 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd216"),
            "name" : "Alice",
            "age" : 25,
            "participant" : [ 
                true
            ]
        }, 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd217"),
            "name" : "Bob",
            "age" : 22,
            "participant" : []
        }, 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd218"),
            "name" : "Carol",
            "age" : 19,
            "participant" : [ 
                true
            ]
        }, 
        {
            "_id" : ObjectId("564f1bb67d3c273d063cd219"),
            "name" : "Dave",
            "age" : 18,
            "participant" : null
        }
    ],
    "ok" : 1
}

To handle nulls, apply the $ifNull operator, equivalent to the coalesce command in SQL to substitute null values with an empty array:

db.students.aggregate([
    {
        "$project": {
            "name": 1,
            "age": 1,
            "participant": {
                "$ifNull" : [ 
                    {
                        "$setIntersection" : [
                            {
                                "$map": {
                                    "input": "$courses",
                                    "as": "el",
                                    "in": {
                                        "$eq": [ "$$el.name", "Databases" ]
                                    }
                                }
                            },
                            [true]
                        ]
                    },
                    []
                ]                
            }
        }
    }
])

After this you can then wrap the $ifNull operator with the $size operator to return the number of elements in the participants array, and that yields the final output as above.

Marcin Bilski · Accepted Answer · 2015-11-20 13:43:30Z

0

Based on what you said about the small number of objects, how about simply pulling out the database name and using JavaScript map to transform it? You're not saving much in terms of transfer and the code will be way more readable than the pipeline.

answered Nov 20, 2015 at 13:43

Marcin Bilski

6218 silver badges13 bronze badges

1 Comment

Green 绿色 Over a year ago

You mean, just call db.students.find() with "pulling out the database name"?

Collectives™ on Stack Overflow

MongoDB: Find and then modify the result depending on if certain value in field array

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related