How to get particular object from array with some conditions in MongoDB

Question

I have a following structure of data in my MongoDB :

[
 {
  "_id" : "**************",
  "primaryKey" : 1,
  "currentState" : [ 
    {
        "value" : 5,
        "status"  : "Y"
    },
    {
        "value" : 5,
        "status"  : "N"
    }
  ],
  "futureState" : {
        "value" : 5,
        "status"  : "F"
    }
 },
 {
  "_id" : "**************",
  "primaryKey" : 2,
  "currentState" : [ 
    {
        "value" : 5,
        "status"  : "N"
    }
  ],
  "futureState" : {}
 }
]

I want to get only objects having status Y as currentState field and get the respective futureState field in another document.

Expected Output :

  /** Single doc into two docs **/
  [{
     "_id": "**************",
     "primaryKey": 1,
     "currentState":{
        "value" : 5,
        "status"  : "Y"
     }         
    },
    {
     "_id": "**************",
     "primaryKey": 1,
     "futureState ":{
        "value" : 5,
        "status"  : "F"
     }         
    }
  ]

I don't know how to get this data in MongoDB, Please help me. Thanks

Answer 1

You can do that using below query :

db.collection.aggregate([
    /** First match is optional for small dataset but highly preferred to filter required docs from huge dataset */
    { $match: { 'currentState.status': 'Y' } },
    /** Retain only objects in currentState which has status == 'Y' & add a fake empty object to array  */
    { $addFields: { 'currentState': { $concatArrays: [{ $filter: { input: '$currentState', cond: { $eq: ['$$this.status', 'Y'] } } }, [{}]] } } },
    /** unwind currentState field across all docs */
    { $unwind: '$currentState' },
    /** if currentState is an object for a doc then keep that field & remove futureState else vice-versa  */
    {
        $addFields: {
            futureState: { $cond: [{ $ne: ['$currentState', {}] }, '$$REMOVE', '$futureState'] },
            currentState: { $cond: [{ $ne: ['$currentState', {}] }, '$currentState', '$$REMOVE'] }
        }
    }
])

Test : MongoDB-Playground

Answer 2

You can do it in 2 ways:

Aggregation

db.collection.aggregate([
  {
    $project: {
      doc1: {
        $filter: {
          input: {
            $map: {
              input: {
                $objectToArray: "$$ROOT"
              },
              as: "root",
              in: {
                k: "$$root.k",
                v: {
                  $cond: [
                    {
                      $eq: [
                        "$$root.k",
                        "currentState"
                      ]
                    },
                    {
                      $ifNull: [
                        {
                          $arrayElemAt: [
                            {
                              $filter: {
                                input: "$$root.v",
                                cond: {
                                  $eq: [
                                    "$$this.status",
                                    "Y"
                                  ]
                                }
                              }
                            },
                            0
                          ]
                        },
                        {}
                      ]
                    },
                    "$$root.v"
                  ]
                }
              }
            }
          },
          cond: {
            $ne: [
              "$$this.k",
              "futureState"
            ]
          }
        }
      },
      doc2: {
        $filter: {
          input: {
            $objectToArray: "$$ROOT"
          },
          cond: {
            $ne: [
              "$$this.k",
              "currentState"
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      tmp: [
        {
          $arrayToObject: "$doc1"
        },
        {
          $arrayToObject: "$doc2"
        }
      ]
    }
  },
  {
    $unwind: "$tmp"
  },
  {
    $replaceWith: "$tmp"
  }
])

MongoPlayground

JS

db.collection.find({}).forEach(function(doc){

    var aux          = Object.assign({}, doc);
    delete aux.currentState;

    doc.currentState = doc.currentState.filter(x => x.status == "Y");
    doc.currentState = doc.currentState.length > 0 ? doc.currentState[0] : {};
    delete doc.futureState;

    print(doc);
    print(aux);
})