0

I did this in my mongodb:

db.teams.insert({name:"Alpha team",employees:[{name:"john"},{name:"david"}]});
db.teams.insert({name:"True team",employees:[{name:"oliver"},{name:"sam"}]});
db.teams.insert({name:"Blue team",employees:[{name:"jane"},{name:"raji"}]});
db.teams.find({"employees.name":/.*o.*/});

But what I got was:

{ "_id" : ObjectId("5ddf3ca83c182cc5354a15dd"), "name" : "Alpha team", "employees" : [ { "name" : "john" }, { "name" : "david" } ] }
{ "_id" : ObjectId("5ddf3ca93c182cc5354a15de"), "name" : "True team", "employees" : [ { "name" : "oliver" }, { "name" : "sam" } ] }

But what I really want is

[{"name":"john"},{"name":"oliver"}]

I'm having a hard time finding examples of this without using some kind of programmatic iterator/loop. Or examples I find return the parent document, which means I'd have to parse out the embedded array employees and do some kind of UNION statement?

Eg.

How to get embedded document in mongodb? Retrieve only the queried element in an object array in MongoDB collection

Can someone point me in the right direction?

John
  • 32,403
  • 80
  • 251
  • 422

2 Answers2

2

Please add projections to filter out the fields you don't need. Please refer the project link mongodb projections

Your find query should be constructed with the projection parameters like below:

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

This will return you:

[{"name":"john"},{"name":"oliver"}]
John
  • 32,403
  • 80
  • 251
  • 422
Anban
  • 36
  • 2
  • Can you tell me what's wrong with my syntax for this question? https://stackoverflow.com/questions/59081852/mongodb-projections-on-embedded-documents-of-embedded-documents – John Nov 28 '19 at 04:40
  • The query returned: `{ "employees" : [ { "name" : "john" }, { "name" : "david" } ] } { "employees" : [ { "name" : "oliver" }, { "name" : "sam" } ] }`. – prasad_ Nov 28 '19 at 05:39
1

Can be solved with a simple aggregation pipeline. 

db.teams.aggregate([
    {$unwind : "$employees"},
    {$match : {"employees.name":/.*o.*/}},
])

EDIT:

OP Wants to skip the parent fields. Modified query: 

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

Output:

{ "name" : "john" }
{ "name" : "oliver" }
Amit Phaltankar
  • 3,341
  • 2
  • 20
  • 37
  • Your solution returns the parent document and its fields. How do I exclude that? or what if I said `SELECT name FROM employees WHERE name LIKE '%o%' ORDER BY name DESC` – John Nov 28 '19 at 04:56
  • @John you can add another `$project` to alias the result into something like: `{$project:{"name":"$employees.name",_id:0}}` and then add another process of `$sort` as `{$sort:{"name":-1}}` (*here in mongo `-1` represents descending order and 1 represents ascending order.*). – vikscool Nov 28 '19 at 05:04