MongoDB: Assign document objects to field in '$project' stage

user_8161918 0
Viewed 95

I have a user collection:

[
  {"_id": 1,"name": "John", "age": 25, "valid_user": true}
  {"_id": 2, "name": "Bob", "age": 40, "valid_user": false}
  {"_id": 3, "name": "Jacob","age": 27,"valid_user": null}
  {"_id": 4, "name": "Amelia","age": 29,"valid_user": true}
]

I run a '$facet' stage on this collection. Checkout this MongoPlayground. I want to talk about the first output from the facet stage. The following is the response currently:

{
  "user_by_valid_status": [
      {
        "_id": false,
        "count": 1
      },
      {
        "_id": true,
        "count": 2
      },
      {
        "_id": null,
        "count": 1
      }
    ]
}

However, I want to restructure the output in this way:

"analytics": {
    "invalid_user": {
        "_id": false
        "count": 1
    },
    "valid_user": {
        "_id": true
        "count": 2
    },
    "user_with_unknown_status": {
        "_id": null
        "count": 1
    }
}

The problem with using a '$project' stage along with 'arrayElemAt' is that the order may not be definite for me to associate an index with an attribute like 'valid_users' or others. Also, it gets further complicated because unlike the sample documents that I have shared, my collection may not always contain all the three categories of users.

Is there some way I can do this?

aggregation-framework mongodb
1 Answers

You can use $switch conditional operator,

  • $project to show value part in v with _id and count field as object, k to put $switch condition
db.collection.aggregate([
  {
    "$facet": {
      "user_by_valid_status": [
        {
          "$group": {
            "_id": "$valid_user",
            "count": { "$sum": 1 }
          }
        },
        {
          $project: {
            _id: 0,
            v: { _id: "$_id", count: "$count" },
            k: {
              $switch: {
                branches: [
                  { case: { $eq: ["$_id", null] }, then: "user_with_unknown_status" },
                  { case: { $eq: ["$_id", false] }, then: "invalid_user" },
                  { case: { $eq: ["$_id", true] }, then: "valid_user" }
                ]
              }
            }
          }
        }
      ],
      "users_above_30": [{ "$match": { "age": { "$gt": 30 } } }]
    }
  },
  • $project stage in root, convert user_by_valid_status array to object using $arrayToObject
  {
    $project: {
      analytics: { $arrayToObject: "$user_by_valid_status" },
      users_above_30: 1
    }
  }
])

Playground

user_89871280
Related