[SERVER-30417] add expression to get value by keyname from object Created: 28/Jul/17  Updated: 30/Oct/23  Resolved: 22/Apr/21

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: 5.0.0-rc0

Type: Improvement Priority: Major - P3
Reporter: Asya Kamsky Assignee: Alya Berciu
Resolution: Fixed Votes: 14
Labels: asya, expression, needs-syntax, pm1457-nominee, qopt-team
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
is depended on by SERVER-21889 Oplog format for $set operations on s... Backlog
is depended on by COMPASS-5126 Add expression to set value in object... Closed
is depended on by MONGOSH-1010 Add expression to set value in object... Closed
is depended on by VSCODE-300 Add expression to set value in object... Closed
Documented
Duplicate
is duplicated by SERVER-38124 executing the expression in a field Closed
is duplicated by SERVER-38463 Add $objectValueAt Expression Closed
is duplicated by SERVER-37341 get value from document using expression Closed
Problem/Incident
causes COMPASS-5173 Add expression to get value by keynam... Closed
causes MONGOSH-1003 Add expression to get value by keynam... Closed
causes VSCODE-299 Add expression to get value by keynam... Closed
Related
related to SERVER-14466 Support projecting the $id or $ref fo... Closed
related to PHPLIB-24 Implement DBRef model class Closed
related to SERVER-30365 How to exclude (sub)fields starting w... Backlog
related to SERVER-41611 add expression to set value in object... Closed
related to SERVER-57914 Make $getField return missing if "inp... Closed
is related to SERVER-56546 Update $getField expression syntax Closed
is related to SERVER-56224 Support $getField in SBE Backlog
is related to SERVER-30575 Please add escaping convention for do... Backlog
Backwards Compatibility: Fully Compatible
Sprint: Query 2020-08-10, Query 2020-08-24, Query Optimization 2021-04-05, Query Optimization 2021-04-19, Query Optimization 2021-05-03
Participants:

 Description   

While it's possible via combined use of $objectToArray and $filter with $map, it would be nice to have a simple expression to get value of particular field of an object:

db.foo.find()
{ _id: 1, a: 5, foo: { b: "a", c: 99.9 } }
db.foo.aggregate({$project:{ foo_c_is: {$getField:[ "c", "$foo" ] } } })
{ _id: 1, foo_c_is: 99.9 }

This will allow easily plucking out a value when the field name is stored in another part of the same document, or when it has some funny character that precludes it being used as subfield name (like leading "$") as in SERVER-30365 and SERVER-14466 or embedded '.' when querying oplog or system.profile collections.

Second argument could be optional and default to "$$ROOT".



 Comments   
Comment by Githook User [ 21/Apr/21 ]

Author:

{'name': 'Alya Berciu', 'email': 'alyacarina@gmail.com', 'username': 'alyacb'}

Message: SERVER-30417: Implement $getField expression

Co-authored-by: Katherine Wu <katherine.wu@mongodb.com>
Branch: master
https://github.com/mongodb/mongo/commit/8736544c4a2f4fbd38792f656408f7b807b786d7

Comment by Githook User [ 21/Apr/21 ]

Author:

{'name': 'Alya Berciu', 'email': 'alyacarina@gmail.com', 'username': 'alyacb'}

Message: SERVER-30417: Implement $getField expression
Branch: master
https://github.com/10gen/mongo-enterprise-modules/commit/1285bbcd7ccb06c4d3d647b5774b9926b265ff07

Comment by Charlie Swanson [ 02/Sep/20 ]

Pulling out of sprint since we are awaiting some decisions about whether this is within the scope of a broader project.

Comment by George Mihailov [ 16/Mar/20 ]

I'm stumbling to this over and over. Ideally, it should support a nested path

{ $objectValueAt: ['$object', '$key.key2'] }

in a perfect world, even support arrays

{ $objectValueAt: ['$object', '$array.0.key'] }

Comment by Asya Kamsky [ 10/Jun/19 ]

I opened SERVER-41611 to track setting field in sub-object helper expression, since this ticket is specifically for getting the value.

Comment by Jeffrey Mariconda [ 10/Dec/18 ]

I proposed something similar at SERVER-38463, although I think that this syntax should probably be modified to be more consistent with the current $arrayElemAt syntax like the following:

{
    $addFields: {
        valueAtKey: { $objectValueAt: ['$someObjectField', '$someKeyField'] }
    }
}

Comment by naresh buruzula [ 15/Nov/18 ]

I am facing a issue which might be similar to this.
 

"outputs" : {"1234" : "actual value"}
identifiers: {"abc": "1234"}
}

I want to get the identifier (1234) of "abc" from "identifiers".
Use that identifier(1234) and fetch the value from "outputs"

 

i tried something like below. But that did not work.
db.submissions.aggregate([
{ $match : { "requests.Certification" :{"$exists":true}} },
{ $addFields: {
   "ftpath": {
      $let: {
         vars: {

// here "path" will be assigned with $outputs.1234
            "path": {$concat: [

{ $literal: "$outputs." }

, "1234"]}},
            in: "$$path"
            // $$path will give "$outputs.1234". Can i not execute "$outputs.value" and return the value inside "$outputs.1234"
            }
        }
     }
   }
])

Is there anyway to achieve this?

Thank you.

Comment by Cade Embery [ 31/Oct/18 ]

+1 for this, Our exact use case would be mainly for $arrayElemAt aswell.

 

Maybe something like $get, or $pick?

{
 "$addFields":{
 "speakerName":{
    "$cond": { 
       "if":"$podcast.data.speaker", 
        "then": "$podcast.data.speaker", 
        "else": {
          "$get":["$arrayElemAt":["$speakerPresenter",0], "title"],
          "$pick":["$arrayElemAt":["$speakerPresenter",0], ["title", "age", "gender"]]
        }
      }
    }
 }
}

 

Comment by Asya Kamsky [ 23/Oct/18 ]

Here is another example where we have to use $let in aggregation expression just to pull out value of a specific known field because there is no way to add "get field v" to an expression so it has to be assigned to variable first:

db.collections.aggregate({$project:{"hashed":{
     $let:{
        vars:{
           val:{$arrayElemAt:[{$objectToArray:"$key"},0]}
        },
        in:"$$val.v"
     }
}}})

With new expression it could be

db.collections.find({$project:{"hashed", {
     $getField:[
         "v", 
         {$arrayElemAt:[{$objectToArray:"$key"},0]}
     ]
}}})

Comment by Asya Kamsky [ 28/Jul/17 ]

I guess it wouldn't help SERVER-30365 which is looking to exclude the field, just one that's looking to include or access the field value.

Comment by Asya Kamsky [ 28/Jul/17 ]

The aggregation workaround in 3.4.4 or later is:

db.foo.aggregate({$project:{
    foo_c_is:{$arrayElemAt:[
        {$map:{
             input:{$filter:{
                 input:{$objectToArray:"$foo"},
                 cond:{$eq:["$$this.k", {$literal:"c"}]}
             }},
             in:"$$this.v"
         }},
        0
    ]}
}})
{ "_id" : 1, "foo_c_is" : 99.9 }

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