[SERVER-24748] Aggregation does not provide a way to access a specific element of an array Created: 23/Jun/16  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Benjamin Murphy Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 2
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-29395 Cannot sort with aggregation by an ar... Closed
is duplicated by SERVER-58521 Dot notation updates with arrays can ... Closed
is duplicated by SERVER-59309 update with aggregation pipeline and ... Closed
Related
related to SERVER-47935 findOneAndUpdate and update insert ob... Closed
is related to SERVER-58521 Dot notation updates with arrays can ... Closed
Assigned Teams:
Query Optimization
Participants:

 Description   

In query, specifying a sort on 'time.0', where time is an array, will sort the documents according to the first element of that array. Aggregation does not allow users to access an element of an array via field path, i.e., $time.0 is undefined on an array. For feature parity, aggregation should support syntax that gives users the ability to access an element of an array with dot notation.



 Comments   
Comment by Charlie Swanson [ 29/Jan/20 ]

david.percy you aren't generally allowed to use expressions in the sort key pattern, with the exception of $meta. You can do a $set/$addFields before the sort and order by that field though.

Comment by David Percy [ 28/Jan/20 ]

I thought {$sort: {$arrayElemAt: ... }} might work, but it's not supported today:

> db.foo.insert({ numbers: [1, 2, 3] })
WriteResult({ "nInserted" : 1 })
> db.foo.aggregate([ {$sort: {$arrayElemAt: ["$numbers", 0]}} ])
2020-01-28T16:25:29.978+0000 E  QUERY    [js] uncaught exception: Error: command failed: {
	"ok" : 0,
	"errmsg" : "Illegal key in $sort specification: $arrayElemAt: [ \"$numbers\", 0.0 ]",
	"code" : 15974,
	"codeName" : "Location15974"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:612:17
assert.commandWorked@src/mongo/shell/assert.js:702:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1012:12
@(shell):1:1

> db.articles.insert({ comments: [ { author: 'Alice', body: 'hello' } ] })
WriteResult({ "nInserted" : 1 })
> db.articles.aggregate([ {$sort: {$let: {vars: {firstComment: {$arrayElemAt: ["$comments", 0]}}, in: "$firstComment.author"}}} ])
2020-01-28T16:28:01.942+0000 E  QUERY    [js] uncaught exception: Error: command failed: {
	"ok" : 0,
	"errmsg" : "$meta is the only expression supported by $sort right now",
	"code" : 17312,
	"codeName" : "Location17312"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:612:17
assert.commandWorked@src/mongo/shell/assert.js:702:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1012:12
@(shell):1:1

Comment by Charlie Swanson [ 29/Mar/17 ]

Yes, this is pointing out the difference in how agg treats 'paths':

test> db.foo.drop()
true
test> db.foo.insert({x: [1, 2, 3]})
WriteResult({
  "nInserted": 1
})
test> db.foo.insert({x: {0: 1}})
WriteResult({
  "nInserted": 1
})
test> db.foo.find({'x.0': 1})  // Finds both documents, considers 'x.0' to be the zeroth index of 'x' in the first example, and the field named '0' within 'x' in the second.
{
  "_id": ObjectId("58dbc5fb9b8a5e6fdc19c24e"),
  "x": [
    1,
    2,
    3
  ]
}
{
  "_id": ObjectId("58dbc6349b8a5e6fdc19c24f"),
  "x": {
    "0": 1
  }
}
test> db.foo.aggregate([{$addFields: {cmp: {$eq: ['$x.0', 1]}}}])  // Matches only the second, since 'x.0' is always interpreted as the field named '0' within 'x'.
      {
        "_id": ObjectId("58dbc5fb9b8a5e6fdc19c24e"),
        "x": [
          1,
          2,
          3
        ],
        "cmp": false
      },
      {
        "_id": ObjectId("58dbc6349b8a5e6fdc19c24f"),
        "x": {
          "0": 1
        },
        "cmp": true
      }
    ]

Interestingly, the projection stuff is the same...

test> db.foo.drop()
true
test> db.foo.insert({x: [1, 2, 3]})
WriteResult({
  "nInserted": 1
})
test> db.foo.insert({x: {0: 4}})
WriteResult({
  "nInserted": 1
})
test> db.foo.find({}, {'x.0': 1})
{
  "_id": ObjectId("58dbc5fb9b8a5e6fdc19c24e"),
  "x": [ ]
}
{
  "_id": ObjectId("58dbc6349b8a5e6fdc19c24f"),
  "x": {
    "0": 4
  }
}
test> db.foo.aggregate([{$project: {'x.0': 1}}])
      {
        "_id": ObjectId("58dbc5fb9b8a5e6fdc19c24e"),
        "x": [ ]
      },
      {
        "_id": ObjectId("58dbc6349b8a5e6fdc19c24f"),
        "x": {
          "0": 4
        }
      }

Comment by Asya Kamsky [ 29/Mar/17 ]

Is this different from $arrayElemAt expression?

Generated at Thu Feb 08 04:07:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.