CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Aditya Mathur
Aditya Mathur

Reputation: 23

Project embedded document key value, based on condition in mongoDB aggregation

I have a mongo collection called tickets and we are storing ticket details in similar structure documents like this:

    [
      {
        "status": "PAUSED",
        "lifecycle_dates": {
          "OPEN": "d1",
          "CLOSED": "d2",
          "PAUSED": "d3"
        }
      },
      {
        "status": "OPEN",
        "lifecycle_dates": {
          "OPEN": "d1",
          "PAUSED": "d3"
        }
      },
      {
        "status": "CLOSED",
        "lifecycle_dates": {
          "OPEN": "d1",
          "CLOSED": "d2"
        }
      }
    ]

I need to fetch the data which says current status of ticket and status date on.

and I want to project data like :

[
  {
    "status": "PAUSED",
    "lifecycle_date": "d3"
  },
  {
    "status": "OPEN",
    "lifecycle_date": "d1"
  },
  {
    "status": "CLOSED",
    "lifecycle_date": "d2"
  }
]

How can I project single lifecycle date based on current status in mongo aggregation pipeline? something like this:

{
    $project : {
        "status" : 1,
        "lifecycle_date" : $lifecycle_dates[$status]
    }
}

couldn't find any reference or similar problem in mongo reference document here

current mongo version : 3.2

Upvotes: 2

Views: 1406

Answers (3)

Rubin Porwal
Rubin Porwal

Reputation: 3845

db.tickets.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $project: {
                "status": 1,
                _id: 0,
                "lifecycle_dates": {
                    $switch: {
                        branches: [{
                                case: {
                                    $eq: ["$status", "PAUSED"]
                                },
                                then: "$lifecycle_dates.PAUSED"
                            },
                            {
                                case: {
                                    $eq: ["$status", "OPEN"]
                                },
                                then: "$lifecycle_dates.OPEN"
                            },
                            {
                                case: {
                                    $eq: ["$status", "CLOSED"]
                                },
                                then: "$lifecycle_dates.OPEN"
                            }
                        ],

                    }
                }
            }
        },
    ])

Upvotes: 0

Ashh
Ashh

Reputation: 46461

You can try below aggregation

db.collection.aggregate([
  { "$project": {
    "status": 1,
    "lifecycle_date": {
      "$arrayElemAt": [
        { "$filter": {
          "input": { "$objectToArray": "$lifecycle_dates" },
          "as": "life",
          "cond": { "$eq": ["$$life.k", "$status"] }
        }},
        0
      ]
    }
  }},
  { "$project": {
    "status": 1,
    "lifecycle_date": "$lifecycle_date.v"
  }}
])

Upvotes: 1

Subhashree Pradhan
Subhashree Pradhan

Reputation: 812

Updated Answer :

Since you need to fetch the date as per the status, you can use this aggregate query :

db.test.aggregate([ 
{
    $project : {
        _id : 0,
        status : 1,
        lifecycle_date : { $cond: [ {$eq : ["$status","OPEN"]}, "$lifecycle_dates.OPEN", { $cond: [ {$eq : ["$status","CLOSED"]}, "$lifecycle_dates.CLOSED", { $cond: [ {$eq : ["$status","PAUSED"]}, "$lifecycle_dates.PAUSED", "-1" ]} ]} ]}
    }
}])

This is compatible with Mongo 3.2 as well.

Output :

{ "status" : "PAUSED", "lifecycle_date" : "d3" }
{ "status" : "OPEN", "lifecycle_date" : "d1" }
{ "status" : "CLOSED", "lifecycle_date" : "d2" }

=========================================================================

This answer was for the previous question -

Use this aggregate :

db.test.aggregate([
{
    $project : {
        _id : 0,
        status : 1,
        lifecycle_date : "$lifecycle_dates.PAUSED"
    }
}
])

Output : 

{ "status" : "PAUSED", "lifecycle_date" : "d3" }

Upvotes: 2

Related Questions