[SERVER-20551] sort on multikey index after $elemMatch projection Created: 22/Sep/15  Updated: 07/Apr/23  Resolved: 23/Sep/15

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

Type: Question Priority: Major - P3
Reporter: Evan Altman Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-13426 weird $sort behavior Closed
duplicates SERVER-19497 Sort is not done on embedded field Closed
is duplicated by SERVER-36338 Multikey sorting of nested array elem... Closed
Related
related to SERVER-19402 Change semantics of sorting by array ... Closed
Participants:

 Description   

I'm attempting a descending sort on a subdocument field which is indexed. Without any projection on my query, it appears that my documents use the multikey index's highest value as the sort key. This makes sense to me. However, when I employ $elemMatch to return just one subdocument, per document, the sort key still appears to be the highest value of the multikey index, regardless of whether its subdocument has been projected out. Is there a way to use a multikey indexed field, but respect the $elemMatch?

Example:

{ "_id": "a", "values":[

{"test": 4}

,

{"test": 1, "keep": true}

] }
{ "_id": "b", "values":[

{"test": 3}

,

{"test": 2, "keep": true}

] }



 Comments   
Comment by Evan Altman [ 25/Sep/15 ]

I realized that because the question contains $elemMatch, there is no answer. That said, using $unwind in the aggregation pipeline should technically allow me to do what I want (albeit not using find().sort() ).

Comment by J Rassi [ 23/Sep/15 ]

One more thing: I forgot to directly address your original question "Is there a way to use a multikey indexed field, but respect the $elemMatch?", so I thought to follow up. No, unfortunately the query subsystem always constructs query plans such that the sort logically happens "before" the projection. This provides clean semantics for such use cases as sorting on a field that is projected out of the document (e.g. db.collection.find({}, {a: 0}).sort({a: 1}); if the projection happened "before" the sort, then each document would generate a null key and the result would be unsorted.

Comment by J Rassi [ 23/Sep/15 ]

Hi,

The results that you've posted are consistent with the server's sort semantics. For sorting on an array in the case of an empty query predicate, the server picks the minimum element of the array as the document's sort key for ascending sorts, and the maximum value of the array as the document's sort key for descending sorts.

Please see my comment at SERVER-19497 for a similar example/explanation, and my comment at SERVER-13426 for a more complex example/explanation.

As a result, I'm closing this ticket with resolution "Works as Designed". If you're curious, see also the related ticket SERVER-19402 and the tickets linked therein.

~ Jason Rassi

Comment by Evan Altman [ 22/Sep/15 ]

Hey Rassi,

> db.temp_collection.find().sort({"values.test":-1})
{ "_id" : "a", "values" : [ { "test" : 4 }, { "test" : 1, "keep" : true } ] }
{ "_id" : "b", "values" : [ { "test" : 3 }, { "test" : 2, "keep" : true } ] }
 
> db.temp_collection.find().sort({"values.test":-1}).explain(true)
 
{
	"cursor" : "BasicCursor",
	"isMultiKey" : false,
	"n" : 2,
	"nscannedObjects" : 2,
	"nscanned" : 2,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 2,
	"scanAndOrder" : true,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"allPlans" : [
		{
			"cursor" : "BasicCursor",
			"isMultiKey" : false,
			"n" : 2,
			"nscannedObjects" : 2,
			"nscanned" : 2,
			"scanAndOrder" : true,
			"indexOnly" : false,
			"nChunkSkips" : 0
		}
	],
	"server" : "cs-8:27000",
	"filterSet" : false,
	"stats" : {
		"type" : "SORT",
		"works" : 8,
		"yields" : 0,
		"unyields" : 0,
		"invalidates" : 0,
		"advanced" : 2,
		"needTime" : 4,
		"needFetch" : 0,
		"isEOF" : 1,
		"forcedFetches" : 0,
		"memUsage" : 132,
		"memLimit" : 33554432,
		"children" : [
			{
				"type" : "COLLSCAN",
				"works" : 4,
				"yields" : 0,
				"unyields" : 0,
				"invalidates" : 0,
				"advanced" : 2,
				"needTime" : 1,
				"needFetch" : 0,
				"isEOF" : 1,
				"docsTested" : 2,
				"children" : [ ]
			}
		]
	}
}
 
> db.temp_collection.find({},{"values":{"$elemMatch":{ "keep": true}}}).sort({"values.test":-1});
{ "_id" : "a", "values" : [ { "test" : 1, "keep" : true } ] }
{ "_id" : "b", "values" : [ { "test" : 2, "keep" : true } ] }
 
> db.temp_collection.find({},{"values":{"$elemMatch":{ "keep": true}}}).sort({"values.test":-1}).explain(true)
 
{
	"cursor" : "BasicCursor",
	"isMultiKey" : false,
	"n" : 2,
	"nscannedObjects" : 2,
	"nscanned" : 2,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 2,
	"scanAndOrder" : true,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"allPlans" : [
		{
			"cursor" : "BasicCursor",
			"isMultiKey" : false,
			"n" : 2,
			"nscannedObjects" : 2,
			"nscanned" : 2,
			"scanAndOrder" : true,
			"indexOnly" : false,
			"nChunkSkips" : 0
		}
	],
	"server" : "cs-8:27000",
	"filterSet" : false,
	"stats" : {
		"type" : "PROJECTION",
		"works" : 8,
		"yields" : 0,
		"unyields" : 0,
		"invalidates" : 0,
		"advanced" : 2,
		"needTime" : 0,
		"needFetch" : 0,
		"isEOF" : 1,
		"children" : [
			{
				"type" : "SORT",
				"works" : 8,
				"yields" : 0,
				"unyields" : 0,
				"invalidates" : 0,
				"advanced" : 2,
				"needTime" : 4,
				"needFetch" : 0,
				"isEOF" : 1,
				"forcedFetches" : 0,
				"memUsage" : 132,
				"memLimit" : 33554432,
				"children" : [
					{
						"type" : "COLLSCAN",
						"works" : 4,
						"yields" : 0,
						"unyields" : 0,
						"invalidates" : 0,
						"advanced" : 2,
						"needTime" : 1,
						"needFetch" : 0,
						"isEOF" : 1,
						"docsTested" : 2,
						"children" : [ ]
					}
				]
			}
		]
	}
}

Comment by J Rassi [ 22/Sep/15 ]

Hi,

Could you please provide the output of running explain(true) against both queries?

Thanks.

~ Jason Rassi

Comment by Evan Altman [ 22/Sep/15 ]

find().sort({"values.test":-1});

should return documents in order "a", "b"

I would expect

find({},{"values":{"$elemMatch":{ "keep": true}}).sort({"values.test":-1});

to return documents in order "b", "a".

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