0

I wrote the following code in MongoDB:

db.departments.insert({name:"Accounting",teams:[
    {name:"Alpha team",employees:[
        {name:"john"},
        {name:"david"}
    ]},
    {name:"True team",employees:[
        {name:"oliver"},
        {name:"sam"}
    ]}
]});

db.departments.insert({name:"Engineering",teams:[
    {name:"Blue team",employees:[
        {name:"singh"},
        {name:"jane"}
    ]},
    {name:"Lazy team",employees:[
        {name:"marleen"},
        {name:"florence"}
    ]}
]});

I want to do the equivalent of SELECT name FROM employee WHERE name LIKE '%o%' ORDER BY name DESC and get the results [{"name":"oliver"},{"name":"john"},{"name":"florence"}].

I just became aware of projections, so I tried this:

db.departments.find({"teams.employees.name":/.*o.*/}, {_id:0, "teams.employees.name": 1}).pretty();

But the result was this instead:

{
        "teams" : [
                {
                        "employees" : [
                                {
                                        "name" : "john"
                                },
                                {
                                        "name" : "david"
                                }
                        ]
                },
                {
                        "employees" : [
                                {
                                        "name" : "oliver"
                                },
                                {
                                        "name" : "sam"
                                }
                        ]
                }
        ]
}
{
        "teams" : [
                {
                        "employees" : [
                                {
                                        "name" : "singh"
                                },
                                {
                                        "name" : "jane"
                                }
                        ]
                },
                {
                        "employees" : [
                                {
                                        "name" : "marleen"
                                },
                                {
                                        "name" : "florence"
                                }
                        ]
                }
        ]
}

What is wrong with my syntax? I am open to other suggestions to achieve my objective

John
  • 32,403
  • 80
  • 251
  • 422
  • `{ $unwind: "$teams" }, { $unwind: "$teams.employees" }, { $replaceRoot: { newRoot: "$teams.employees" }}` https://mongoplayground.net/p/lmIfXIW3vTd – Ashh Nov 28 '19 at 05:49
  • You can only achieve complex filtering using aggregation. You need to unwind all your array fields first, then project the necessary fields and then match using the regex filter. db.departments.aggregate([ {$unwind: "$teams"}, {$unwind: "$teams.employees"}, {$project: {"_id":0, "name": "$teams.employees.name" }}, {$match: {"name": {$regex: /.*o.*/ }}} ]) Try this and let me know the outcome – Dexter Nov 28 '19 at 06:47
  • Thanks @Anban, your suggestion solved one issue, but lead to this issue: https://stackoverflow.com/questions/59090237/mongodb-aggregate-queries-vs-mysql-select-field1-from-table – John Nov 28 '19 at 13:58
  • @Ashh thanks your suggestion has solved one issue, but now lead to this issue: https://stackoverflow.com/questions/59090237/mongodb-aggregate-queries-vs-mysql-select-field1-from-table – John Nov 28 '19 at 13:58

1 Answers1

0

Use

db.departments.aggregate([
    { $unwind: "$teams" },
    { $unwind: "$teams.employees" },
    { $project: { _id: 0, name: "$teams.employees.name" } },
    { $match: { "name": { $regex: /.*o.*/ }} }
]).toArray()

Result

[
    {
        "name" : "john"
    },
    {
        "name" : "oliver"
    },
    {
        "name" : "florence"
    }
]
Miguel Medellín
  • 11
  • 4
  • Thanks, but this solution is leading to this problem: https://stackoverflow.com/questions/59090237/mongodb-aggregate-queries-vs-mysql-select-field1-from-table – John Nov 28 '19 at 13:57