Details
-
Type:
New Feature
-
Status: Closed
-
Priority:
Major - P3
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 3.1.5
-
Component/s: Aggregation Framework
-
Labels:None
-
Backwards Compatibility:Minor Change
-
Sprint:Quint Iteration 4, Quint Iteration 5
Description
At present, there's no way to extract a specific element from an array-valued field. An operator should be added that could be used in a projection, e.g.:
db.runCommand({aggregate:"c", pipeline:[
{ $project : {
foo :
}}
]});
The effect of this would be put add the computed field (virtual field) foo to the projection, and give it the value of arrayField[5].
Issue Links
- is depended on by
-
JAVA-2001
Projection builder support for array indexing operator
-
- Closed
-
- is duplicated by
-
SERVER-6075
Accessing array elements by position fails in $project
-
- Closed
-
-
SERVER-16462
Can't access specific array elements in $group
-
- Closed
-
- is related to
-
SERVER-6074
Allow $slice operator in $project
-
- Closed
-
-
DRIVERS-234
Aggregation Builder Support for 3.2
-
- Closed
-
- related to
-
SERVER-5299
array element selection syntax returns wrong result
-
- Closed
-
-
CSHARP-1400
Support the new array indexing operator
-
- Closed
-
Activity
I'd like to add that I do not see any reason for introducing another operator or to be forced to use $slice to do this. It would be nice if $slice was supported, but this is another matter - this has simply to do with the dot notation not working as advertised. Besides, $slice still returns an array - The problem here is that a dot notation expression should simply extract the value from the array (having whichever type that value has).
Consider the following query:
db.zips.aggregate([{$match: {"loc.0": -72.188455}}, {$project: {_id:1, loc: 1, lon: {$let: { vars:
{lon: "$loc.0"}, in: "$$lon"}} }}]);
this returns:
{ "_id" : "01010", "loc" : [ -72.188455, 42.116543 ], "lon" : [ ] }This is wrong. Expected was:
{ "_id" : "01010", "loc" : [ -72.188455, 42.116543 ], "lon" : -72.188455 }Notice that
1) the expression "loc.0" is valid and can be successfully filtered to -72.188455 in the $match stage. This indicates the expression itself is valid
2) lon is returned as an empty array. This is wrong for two reasons: a) we're expecting the scalar (-72.188455), b) the array is empty.
The simpler query without $let, should, as far as I can see also work:
db.zips.aggregate([{$match: {"loc.0": -72.188455}}, {$project: {_id:1, loc: 1, lon: "$loc.0" }}]);
...but yields the same result - empty array. This already indicates that mongodb knows it is accessing an array so at least it partially understands that "loc" is being accessed (it is the only array in the document)
This would return a scalar (or value of type of array element, rather than array of a single element which is what slice would return.
Author:
{u'username': u'cswanson310', u'name': u'Charlie Swanson', u'email': u'charlie.swanson@mongodb.com'}Message: SERVER-4589: Add $arrayElemAt aggregation expression
Branch: master
https://github.com/mongodb/mongo/commit/c317c2b9416ffeb9a79ac8bec1eb79ae10448a4a
The new expression is called $arrayElemAt. It takes two arguments, an array and an index. As is hopefully self explanatory, it returns the element at the given index in the array. Negative indices are accepted as indexes from the back of the array. If the index is out of bounds, it returns the missing value, meaning the field will not exist in the output.
If either argument is null, it returns null, otherwise it errors if the first argument is not an array, or the second argument cannot be represented as an integer value (2, NuberLong(2), and 2.0 work, 2.5 and NumberLong(2^40) do not).
For all the edge case details, see the javascript test which was part of the commit.
For anyone interested, backports of this useful feature:
3.0: https://github.com/evergage/mongo/commit/cde793ed9c85b9356e3c20a6bc09f536cc35f245
2.6: https://github.com/evergage/mongo/commit/49dc3c9fc5c723034dfd88d5a2a97010a9172984
Should this support something like $slice as well?