CODEWITHSUNDEEP
mongodbmongodb-query
HVA
HVA

Reputation: 43

Exclude Nested Fields and Project only a few Fields

In this JSON snippet, I am looking to retrieve only 3 fields _id.RULES.1.0._Rules.stringValue , _id.MYOPERATION and _id.CURRENTSTATE

{
  "_id" : {
  "RULES" : [
    { 
      "@class" : "a.b.c.Rules",
      "_Rules" : {
        "@class" : "a.b.c.RulesType",
        "type" : 2,
        "stringValue" : "Y_RULES"
      }
    }
  ],
  "MYOPERATION" : 1,
  "CURRENTSTATE" : "PROPOSED"
  }
}

I have tried the following per https://docs.mongodb.com/manual/tutorial/project-fields-from-query-results/

db.results.find(
  {},
  {
    "_id.CURRENTSTATE":1, 
    "_id.RULES.1.0._Rules.stringValue":1,
    "_id.CURRENTSTATE":1
  }
)

The results I am getting are similar to db.results.find();

Any thoughts or ideas appreciated.

Upvotes: 1

Views: 1061

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151220

You cannot actually select fields from embedded content and particularly from an array like that. If you need that level of manipulation then you need to use aggregate() and $project:

db.results.aggregate([
  { "$project": {
     "_id": {
       "MYOPERATION": 1,
       "CURRENTSTATE": 1,
       "RULES": {
         "$arrayElemAt": [
           { "$map": {
             "input": "$_id.RULES",
             "in": {
               "_Rules": { 
                 "stringValue": "$$this._Rules.stringValue"
               } 
             }
           }},
           0
         ]
       }
     }
  }}
])

That would return the full path for the first array index:

{
    "_id" : {
        "MYOPERATION": 1,
        "CURRENTSTATE" : "PROPOSED",
        "RULES" : {
            "_Rules" : {
                    "stringValue" : "Y_RULES"
            }
        }
    }
}

Alternately if you just want the "value" of the specified key then you could do:

db.results.aggregate([
  { "$project": {
    "_id": {
      "MYOPERATION": 1,
      "CURRENTSTATE": 1,
      "stringValue": {
        "$arrayElemAt": [
          "$_id.RULES._Rules.stringValue",
          0
        ]
      }
    }
  }}
])

Which just returns that value:

{
  "_id" : { 
    "MYOPERATION": 1,
    "CURRENTSTATE" : "PROPOSED", 
    "stringValue" : "Y_RULES"
  }
}

In either case it's the $arrayElemAt you want to return a value from a specific array index rather than the "index notation" for this type of data. You want the $map wherever you need to transform content within an array

There is also:

db.results.aggregate([
  { "$project": {
    "_id": 0,
    "MYOPERATION": "$_id.MYOPERATION",
    "CURRENTSTATE": "$_id.CURRENTSTATE",
    "RULES._Rules.stringValue": "$_id.RULES._Rules.stringValue"
  }}
])

But that probably does not return what you actually want, since the array entry becomes "inverted" on the actual end values:

{
    "MYOPERATION": 1,
    "CURRENTSTATE" : "PROPOSED",
    "RULES" : {
        "_Rules" : {
                "stringValue" : [
                        "Y_RULES"
                ]
        }
    }
}

Whatever end result you are likely after, it's more likely to involve some combination of the $arrayElemAt and possibly $map methods to get to the results you actually want.

Also note that _id does not really look like a good place to store all of that information and looks more like a mistake than intentional. The structure has a very XML "feel" as if it was converted from that as an original source. Where that is the case, you would be better off looking at that conversion process and actually "reshaping" that output before it even gets inserted into MongoDB.

Upvotes: 3

Related Questions