CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
jens_h
jens_h

Reputation: 377

filter array and return specific property

I have following projection:

db.tickets.aggregate([
{
  $match: {
    "satisfaction_rating.id": {"$exists": true}
  }
},
{
  $project: {
    "ticketId": "$id",
    "employee": "$assignee.name",
    "subject": "$subject",
    "memberId": {
      "$filter": {
         "input": "$custom_fields",
         "as": "field",
         "cond": {"$eq": ["$$field.id", 24685851]}
       }
    },
    "requester": "$requester.name",
    "created": "$created_at",
    "solved": "$metric_set.solved_at",
    "resolutionTimeInHours": {"$trunc": {"$divide": ["$metric_set.full_resolution_time_in_minutes.business", 60]}},
    "score": "$satisfaction_rating.score",
    "comment": "$satisfaction_rating.comment"
  }
},
{
  $out: "satisfaction"
}]);

$filter returns an array. What I want to do is to pick up the first element and get a property which should then be bound to memberId instead of an array with one element.

I was looking around for a while but didn´t find a suitable solution. Can anyone give me a hint?

Upvotes: 4

Views: 2328

Answers (2)

jens_h
jens_h

Reputation: 377

@user3100115 led me into the right direction.

Here my final solution:

"memberId": {
  "$let": {
      "vars": {
             "memberId": {
                "$arrayElemAt": [
                {"$filter": {
                        "input": "$custom_fields",
                        "as": "field",
                        "cond": {"$eq": ["$$field.id", 24685851]}
                }}
                ,0]
                }   
        },
        "in": "$$memberId.value"
    }
}

Upvotes: 11

Sede
Sede

Reputation: 61225

You can use the $arrayElemAt operator to return the element from $filter's result and use the $let operator with the dot notation to access the sub-document field.

"memberId": {
    "$let": {
        "vars": { 
            "field": { 
                "$arrayElemAt": [
                    "$filter": {
                        "input": "$custom_fields",
                        "as": "field",
                        "cond": { "$eq": [ "$$field.id", 24685851 ]}
                    },
                    0
                ]
            }
        },
        "in": "$$field.id"
    }
}

Upvotes: 4

Related Questions