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.
Query
id is false or empty string, try the name, else if name false or empty string, try the email, else ""*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"]}
aggregate(
[{"$set":
{"userInfo":
{"$switch":
{"branches":
[{"case":{"$and":["$user.id", {"$ne":["$user.id", ""]}]},
"then":"$user.id"},
{"case":{"$and":["$user.name", {"$ne":["$user.name", ""]}]},
"then":"$user.name"},
{"case":{"$and":["$user.email", {"$ne":["$user.email", ""]}]},
"then":"$user.email"}],
"default":""}}}}])
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
You can use the nested $ifNull operator, by checking nested condition,
{
$project: {
userDetail: {
$ifNull: [
"$user.id",
{
$ifNull: [
"$user.name",
{ $ifNull: ["$user.email", ""] }
]
}
]
}
}
}