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?

score 100 · Accepted Answer · edited Feb 13 '14 at 12:26

100

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.

edited Feb 13 '14 at 12:26

joao

4,067
3
33
37

answered Jan 08 '13 at 14:09

JohnnyHK

305,182
66
621
471

2

can i use both $isNull and cond? – Aryeh Armon Jun 08 '16 at 14:54
1

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 :) – callmemath Jul 21 '20 at 07:06
Perfect! I was trying to use $cond or $exisits an the $ifNull saved me. – Jean Gonçalves Mar 30 '23 at 21:28

score 68 · Answer 2 · answered Mar 09 '17 at 07:19

68

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

answered Mar 09 '17 at 07:19

Imran

2,906
1
19
20

2

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 !! – whoami - fakeFaceTrueSoul Dec 10 '19 at 19:37
1

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 }] } – Daniel Cumings Jan 24 '22 at 17:49

score 24 · Answer 3 · answered Sep 07 '17 at 05:20

24

You can simulate exists with

$ne : [$var_to_check, undefined]

This returns true if the var is defined

answered Sep 07 '17 at 05:20

Delcon

2,355
1
20
23

In Java there is a `BsonUndefined` sub type for `BsonValue` – Robert Mar 29 '22 at 15:44
2

Hmm not working for me – Glen Thompson Jun 14 '22 at 23:13
3

Solution above doesn't work for me, I found this one https://stackoverflow.com/a/38720905/6529932, which works. – MichalPr Jul 07 '22 at 08:59

score 6 · Answer 4 · answered Jun 29 '22 at 05:37

6

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

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

answered Jun 29 '22 at 05:37

Yuu

159
1
10

score 1 · Answer 5 · edited Nov 07 '16 at 18:50

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.

score 1 · Answer 6 · answered Feb 07 '23 at 15:45

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.

Conditional grouping with $exists inside $cond

6 Answers6

Linked

Related