Conditional grouping with $exists inside $cond

Question

I have two keys A and B and their existence in the document is mutually exclusive. I have to group by A when A exists and group by B when B exists. So I am $projecting the required value into a computed key called MyKey on which I'll perform a $group. But it looks like I'm making a mistake with the syntax. I tried writing $project in two ways:

{
  $project: {
    MyKey: {
      $cond: [{ $exists: ["$A", true] }, "$A", "$B"] }
    }
  }

and

{
  $project: {
    MyKey: {
      $cond: [{ "A": { $exists: true } }, "$A", "$B"] }
    }
  }

But I keep getting the error:

{ 
  "errmsg": "exception: invalid operator '$exists'", 
  "code" : 15999, 
  "ok" : 0 
}

What's going wrong?

Hazlett

Answer 1

Use $ifNull instead of $cond in your $project:

{ $project: {MyKey: {$ifNull: ['$A', '$B'] }}}

If A exists and is not null its value will be used; otherwise the value of B is used.

Shantay

can i use both $isNull and cond? – Kish

Note: If you don't want to set a field with the value null you can do something like: $ifNull: ["$field", newValue || "$field"], And if the newValue variable does not exist, it will not create the field. Setting undefined still creates the object, that's why the solution works nicely :) – That

Perfect! I was trying to use $cond or $exisits an the $ifNull saved me. – Dollarfish

Answer 2

if one wants to check $exists with in $cond an alternative approach is to use $not with $cond

{$project: {MyKey: {$cond: [{$not: ["$A"]}, "$B", "$A"]}}} 

and truth table for $not is as

Hopes that Helps

Fredkin

Please be cautious before you use $not it basically converts falsy values into true & visa-versa, in case if you use it to check a field exists ({$not: ["$A"]}) then in that case, if your fields actual value is 0 then it would return true, where you'll be expecting trues for documents in which A field doesn't exist. Which is why this might not work in some cases !! – Stew

this was the only thing that worked for me. I had to also check for null in case the field existed but was null: { "$and" : [{ "$not" : ["$MyKey"] }, { "MyKey" : null }] } – Cooperative

Answer 3

You can simulate exists with

$ne : [$var_to_check, undefined]

This returns true if the var is defined

Cinchonize

In Java there is a BsonUndefined sub type for BsonValue – Honewort

Hmm not working for me – Bulky

Solution above doesn't work for me, I found this one https://mcmap.net/q/242878/-how-to-project-whether-field-exists, which works. – Pathognomy

"This returns true if the var is undefined"; that means it will return true when the property is missing altogether (i.e. nonexistent), just like you said. As opposed to comparing to null, which doesn't check existence, it checks for null value (i.e. $ne: ['$var_to_check', null] is wrong) Thanks! – Kaiak

Answer 4

Some sharing that i found in $cond and check is existing:

$cond: {
  if: "$A1",
  then: "$A1",
  else: "$B1"
}

Euphonious

Answer 5

TL;DR:

A strict equivalent of what could be expected of { k: { $exists: true } } in an aggregation or $expr statement can be achieved with: { $or: ['$k', { $in: ['$k', [null, 0, false]] }] }

Explanation:

Comparison of a field value with null using $eq or $in is strict and would return false if field is not set.

The truth table for these is the following

                        | null  | unset | truthy | falsy |
------------------------+-------+-------+--------+-------+
{ $eq: ['$k', null] }   | TRUE  | FALSE | FALSE  | FALSE |
{ $in: ['$k', [null]] } | TRUE  | FALSE | FALSE  | FALSE |

So, to know when a field is set, we have to toggle truthy and falsy columns of the table to true.

Truthy values are… well, truthy, so we can use them as-is: { $or: ['$k', { $eq: ['$k', null] }] }

In MongoDB, falsy values are 0, false and null so we can add the missing ones to our $in clause: { $in: ['$k', [null, 0, false]] }

By merging these, we obtain { $or: ['$k', { $in: ['$k', [null, 0, false]] }] } which will now return true if the field is either truthy, 0, false or null and false if unset.

Spectra

Answer 6

I found your questions while looking for a similar problem, but insted of a key, I was looking for my parameters. I finally solved the issue.

This is what I used for my $_id.status parameter, to check that if it exists inside the cond.

$cond: [{
     $or: [{
          $ne: ["$_id.status", null]
     }]
}, 1, null]

$or is not needed. I keep it there... just for fun. I don't think it affects the query that much for the moment. I will test the speed later.

Zilla