[SERVER-9028] Using positional operator with db.collection.find() to fetch multiple embedded documents does not work as expected Created: 20/Mar/13  Updated: 10/Dec/14  Resolved: 07/Apr/13

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.2.3
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Alexey Kamensky Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Platform: Ubuntu 12.10 x64
Platform2: Debian Squeeze


Issue Links:
Duplicate
duplicates SERVER-6612 Support projecting multiple array val... Closed
Related
related to SERVER-1155 Update with positional operator with ... Closed
Participants:

 Description   

I am not very sure whether this is bug, or that was intention to limit functionality this way. However here is the description:

PRECONDITIONS:

  • There is a DB with collection.
  • Collection documents have structure as follows:

Root document:
{
_id:ObjectId(),
name: ...,
description: ...,
subdocuments:
[

{ _id:ObjectId(), name: 'test1', description: ..., }

,

{ _id:ObjectId(), name: 'test2', description: ..., }

,
...
]
}

STEPS TO REPRODUCE:

  • Open MongoDB Shell, switch to db "DB"
  • Execute query:
    db.collection.find( {'subdocuments.test': /test/i}

    ,

    {'subdocuments.$': 1, _id:0}

    )

EXPECTED OUTCOME:

  • The returned result should be the list of all subdocuments across the entire collection. Perhaps grouped into arrays by root documents, so if there are two satisfying elements in one root document and three in another then results will be list with two arrays where one has two elements and another has three.

ACTUAL OUTCOME:

  • Error message with code 16352 appears. Which is illogical, because same conditions query but findOne instead of find, works as a charm and returns the array with one element in it, which is searched embedded document.

As it is stated in ACTUAL OUTCOME block, the expected outcome is based on behavior with similar query but for finding single document.
Means "db.collection.findOne(

{'subdocuments.test': /test/i}

,

{'subdocuments.$': 1, _id:0}

)" works, whereas "db.collection.find(

{'subdocuments.test': /test/i}

,

{'subdocuments.$': 1, _id:0}

)" doesn't.

Why is this important?
We chose this schema because 99% of queries fetch root document together with its subdocuments, but according to new functionality we should be able to get only subdocuments satisfying to specific conditions (think of it as regex search).
Of course its possible to first get entire root document (just remove field part from query and it will work), but then we would need to iterate through whole subdocuments array searching for the right ones. Seems like double work here, because DB already did scan for it ones (or used indexes) and we need to do it ourselves second time. Waste of time and resources.



 Comments   
Comment by Scott Hernandez (Inactive) [ 07/Apr/13 ]

This feature request (returning multiple matches, instead of just one) in here: SERVER-6612.

The regex positional operator issue is here: SERVER-1155

Comment by Ronald Stalder [ 07/Apr/13 ]

From: http://stackoverflow.com/questions/15496071/finding-embedded-documents-in-mongoose-odm

Issue can be reproduced in mongo shell

> db.xx.find().pretty()
{
"_id" : ObjectId("51610020672afd480ccfb9c4"),
"name" : "any",
"subsets" : [

{ "number" : 3, "name" : "aba" }

,

{ "number" : 4, "name" : "aka" }

]
}
> // works as expected:
> db.xx.find(

{"subsets.number": 3}

,{_id:0, "subsets.$":1})
{ "subsets" : [

{ "number" : 3, "name" : "aba" }

] }
> // does not work
> db.xx.find(

{"subsets.name": /k/}

,{_id:0, "subsets.$":1})
error: {
"$err" : "positional operator (subsets.$) requires corresponding field in query specifier",
"code" : 16352
}
> // but this works!
> db.xx.find({"subsets": {$elemMatch:

{name:/k/}

}},{_id:0, "subsets.$":1})
{ "subsets" : [

{ "number" : 4, "name" : "aka" }

] }

Generated at Thu Feb 08 03:19:08 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.