I am stuck in a query, my case is

if user id exists then user id
else if user name exists then user name
else if user email exists then user email
else ''

Here, is my code.

$project: {
    ..... other fields,
    'userDetail': {
        $cond: {
            if: {
                $exists: ['$user.id']
            },
            then: '$user.id',
            else: {
                $cond: {
                    if: {
                        $exists: ['$user.name']
                    },
                    then: '$user.name',
                    else: {
                        $cond: {
                            if: {
                                $exists: ['$user.email']
                            },
                            then: '$user.email',
                            else: '',
                        }
                    },
                }
            }
        }
    }
}

Since, $exists don't work on this. Can anybody, help me finding the solution to this problem? Also, if user.id is '' (empty string) & user.name is "Marcus". I want the result to return user.name.

CodePudding user response：

If I've understood correctly (and based on your example) you can use a query like this:

The trick here is to use ifNull instead of $exists.

db.collection.aggregate([
  {
    "$project": {
      "userDetail": {
        "$cond": [
          {
            "$ifNull": [
              "$user.id",
              false
            ]
          },
          // user id exists
          "$user.id",
          // user id no exists
          {
            "$cond": [
              {
                "$ifNull": [
                  "$user.name",
                  false
                ]
              },
              // user name exists
              "$user.name",
              // user name no exists
              {
                "$cond": [
                  {
                    "$ifNull": [
                      "$user.email",
                      false
                    ]
                  },
                  // user email exists
                  "$user.email",
                  // user email no exists
                  ""
                ]
              }
            ]
          }
        ]
      }
    }
  }
])

Example here

CodePudding user response：

Query

• if id is false, try the name, else if name false, try the email, else ""
• false are all not-exists/null/false
• the types of those fields are not booleans, so you are safe to use this way

*in case you really needed the $exist for example you wanted to keep the null or the false values, you could use this {"$eq":[{"$type":"$user.id"}, "missing"]}

Test code here

aggregate(
[{"$set":
  {"userInfo":
   {"$switch":
    {"branches":
     [{"case":"$user.id", "then":"$user.id"},
      {"case":"$user.name", "then":"$user.name"},
      {"case":"$user.email", "then":"$user.email"}],
     "default":""}}}}])

CodePudding user response：

You can use the nested $ifNull operator, by checking nested condition,

  {
    $project: {
      userDetail: {
        $ifNull: [
          "$user.id",
          {
            $ifNull: [
              "$user.name",
              { $ifNull: ["$user.email", ""] }
            ]
          }
        ]
      }
    }
  }

Playground