I have the below 2 documents from a post collection. How to get only key value pairs from "post" object. The match condition will be using "post_id".

{
   "_id":"1001",
   "post":{
      "country_name":"India",
      "state_name":"Maharashtra",
      "city_name":"Mumbai",
      "duration":"10",

      "country":[
         {
            "name":"india"   
         }
      ],
      "site":[
         {
            "site_no":"101",
            "code":"Taj",
            "name":"santacruz"
         }
      ]
   },
   "post_id":"abcd123"
}

{
   "_id":"1002",
   "post":{
      "country_name":"India",
      "state_name":"Karnataka",
      "city_name":"Bangalore",
      "duration":"20",

      "country":[
         {
            "name":"india"   
         }
      ],
      "site":[
         {
            "site_no":"201",
            "code":"COLES",
            "name":"Coles Park"
         }
      ]
   },
   "post_id":"abcd234"
}

The expected result is:

"abcd123":{
  "country_name":"India",
  "state_name":"Maharashtra",
  "city_name":"Mumbai",
  "duration":"10"
 }
"abcd234" : {
"country_name":"India",
      "state_name":"Karnataka",
      "city_name":"Bangalore",
      "duration":"20"
}

I'm able to filter for one object, but for the bulk and with good performance, can you help me to solve this.

CodePudding user response：

You can try this aggregate query:

The trick here is to create an object with keys k and v to define the key and value for the next stage.

The key will be post_id and the value the object with desired values.

In this case values has to be dinamically created so you can:

• Parse $post object to an array, which allows you to filter values inside.
• $filter that values to not get arrays or objects using $type.
• Then convert again the array to object using $arrayToObject.

And the last step is to use $replaceRoot so you can get your desired output.

db.collection.aggregate([
  {
    "$project": {
      "k": "$post_id",
      "v": {
        "$arrayToObject": {
          "$filter": {
            "input": { "$objectToArray": "$post" },
            "cond": {
              "$not": [
                {
                  "$in": [
                    { "$type": "$$this.v" },
                    [ "object", "array" ]
                  ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": { "$arrayToObject": [ [ {  k: "$k", v: "$v" } ] ] }
    }
  }
])

Example here