MongoDB Aggregation filter on documents within documents

Question

Can you help me with a situation... I Have this json but I would like to return only the nodes.

{
   "_id":{
      "userArea":NumberInt(4927)
   },
   "pages":{
      "12":{
         "page":NumberInt(2635), 
         "progress":"COMPLETED",
         "progressType":"USER_PROGRESS",
         "end":11
      },
      "13":{
         "page":NumberInt(2627),
         "progress":"COMPLETED",
         "progressType":"USER_PROGRESS",
         "end":ISODate("2018-04-19T15:04:29.000+0000")
      }
      "14":{
         "page":NumberInt(2627),
         "progress":"CANCELLED",
         "progressType":"USER_PROGRESS",
         "end":ISODate("2018-04-19T15:04:29.000+0000")
      }
   }
}

This way.... without header

      "12":{
         "page":NumberInt(2635), 
         "progress":"COMPLETED",
         "progressType":"USER_PROGRESS",
         "end":11
      },
      "13":{
         "page":NumberInt(2627),
         "progress":"COMPLETED",
         "progressType":"USER_PROGRESS",
         "end":ISODate("2018-04-19T15:04:29.000+0000")
      }

Can you help me? I need to filter only the completed!!!

Answer 1

You can use this aggregation query:

• First use $objectToArray to generate an array and can filter the values using the value v. This is done into the input.
• Then with that input values you can filter the elements whose progress value is not CANCELLED. And convert again to an object with $arrayToObject

db.collection.aggregate([
  {
    "$project": {
      "pages": {
        "$arrayToObject": {
          "$filter": {
            "input": {
              "$objectToArray": "$pages"
            },
            "cond": {
              "$ne": [
                "$$this.v.progress",
                "CANCELLED"
              ]
            }
          }
        }
      }
    }
  }
])

Example here

Sorry, on my first attemp I read "without header" and I thought you wanted using $replaceRoot but I think this new query is what you want (also avoiding $unwind).

But I think you say "without hedaer" to show the output simplified in the question. Also if you can't output the header simply add a new stage using $replaceRoot. Example here

Answer 2

You need to do many pipelines as in here

db.collection.aggregate([
  {
    "$project": {
      "p": {
        "$objectToArray": "$pages"
      }
    }
  },
  {
    "$unwind": "$p"
  },
  {
    "$match": {
      "p.v.progress": "COMPLETED"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "p": {
        "$addToSet": "$p"
      }
    }
  },
  {
    $project: {
      "a": {
        "$arrayToObject": "$p"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$a"
    }
  }
])

1. Reshape so that you can apply filter.
2. Group again to get back to your original schema. Else, you can stop at $match pipeline.

If possible, change your schema, Do not use dynamic keys.