存在按字段分列的 MongoDB 聚合

我很难相信这个问题还没有在某个地方被问及并得到回答,但是我找不到它的任何痕迹。

我有一个 MongoDB 聚合查询,它需要按布尔值分组: 另一个字段的存在。

例如,让我们从这个集合开始:

> db.test.find()
{ "_id" : ObjectId("53fbede62827b89e4f86c12e"),
"field" : ObjectId("53fbede62827b89e4f86c12d"), "name" : "Erik" }
{ "_id" : ObjectId("53fbee002827b89e4f86c12f"), "name" : "Erik" }
{ "_id" : ObjectId("53fbee092827b89e4f86c131"),
"field" : ObjectId("53fbee092827b89e4f86c130"), "name" : "John" }
{ "_id" : ObjectId("53fbee122827b89e4f86c132"), "name" : "Ben" }

2个文档有“ field”,2个没有。 请注意,“ field”的每个值可能是不同的; 我们只想根据它的存在进行分组(或者非空性对我也适用,我没有存储任何空值)。

我尝试过使用 $project,但是这里不存在 $vis,并且 $cond 和 $ifNull 对我没有帮助。这个领域似乎总是存在,即使它并不存在:

> db.test.aggregate(
{$project:{fieldExists:{$cond:[{$eq:["$field", null]}, false, true]}}},
{$group:{_id:"$fieldExists", count:{$sum:1}}}
)
{ "_id" : true, "count" : 4 }

我希望下面这个更简单的聚合可以工作,但是由于某些原因 $的存在并没有以这种方式得到支持:

> db.test.aggregate({$group:{_id:{$exists:"$field"}, count:{$sum:1}}})
assert: command failed: {
"errmsg" : "exception: invalid operator '$exists'",
"code" : 15999,
"ok" : 0
} : aggregate failed
Error: command failed: {
"errmsg" : "exception: invalid operator '$exists'",
"code" : 15999,
"ok" : 0
} : aggregate failed
at Error (<anonymous>)
at doassert (src/mongo/shell/assert.js:11:14)
at Function.assert.commandWorked (src/mongo/shell/assert.js:244:5)
at DBCollection.aggregate (src/mongo/shell/collection.js:1149:12)
at (shell):1:9
2014-08-25T19:19:42.344-0700 Error: command failed: {
"errmsg" : "exception: invalid operator '$exists'",
"code" : 15999,
"ok" : 0
} : aggregate failed at src/mongo/shell/assert.js:13

有人知道如何从这样的收藏中得到想要的结果吗?

预期结果:

{ "_id" : true, "count" : 2 }
{ "_id" : false, "count" : 2 }
112457 次浏览

The $exists operator is a "query" operator, so it is used basically to "filter" results rather than identify a logical condition.

As a "logical" operator the aggregation framework supports the $ifNull operator. This returns the field value where it exists or the alternate supplied value where it does not or otherwise evaluates to null

db.test.aggregate([
{ "$group": {
"_id": { "$ifNull": [ "$field", false ] },
"count": { "$sum": 1 }
}}
])

But of course, even that is not a "true/false" comparison, so unless you actually want to return the actual value of the field where it is present, then you are probably better off with a $cond statement much like you have:

db.test.aggregate([
{ "$group": {
"_id": { "$cond": [{ "$eq": [ "$field", null ] }, true, false ] },
"count": { "$sum": 1 }
}}
])

Where $ifNull can be very useful is in replacing not existent array fields that would otherwise cause an error using $unwind. You can then do something like return a single element or empty array so this does not cause problems in the rest of your pipeline processing.

I solved the same problem just last night, this way:

> db.test.aggregate({$group:{_id:{$gt:["$field", null]}, count:{$sum:1}}})
{ "_id" : true, "count" : 2 }
{ "_id" : false, "count" : 2 }

See http://docs.mongodb.org/manual/reference/bson-types/#bson-types-comparison-order for a full explanation of how this works.

Added From comment section:

To check if the value doesn't exist or is null use { $lte: ["$field", null] }

I solved it with checking for undefined

$ne : [$var_to_check, undefined]

or

$ne:  [ { $type : "$var_to_check"}, 'missing'] }

This returns true if the var is defined

Dunno how it was but now in 2019 there is clean solution. In aggregation pipeline do this

$match: {"my_field": {$ne: null}}

Nice thing is in my lang 'ne' means not :)

In short

{'$project': {
'field_exists': {'$or': [
{'$eq': ['$field', null]},
{'$gt': ['$field', null]},
]},
}}

Details

$exists means that the field exists, even if it is null or any other empty value. That is why all the answers on this page are incorrect.

Let's test a bit. Check this:

// Let's take any collection that have docs
db.getCollection('collection').aggregate([
// Get arbitrary doc, no matter which, we won't use it
{"$limit": 1},
// Project our own fields (just create them with $literal)
{'$project': {
'_id': 0,
'null_field': {'$literal': null},
'not_null_field': {'$literal': {}},
}},
])

We'll get this:

{
"null_field" : null,
"not_null_field" : {}
}

Then let's clarify which fields exist in this doc:

  1. null_field - exists
  2. not_null_field - exists
  3. non_existent_field - doesn't.

Okay, it's time to test project stage I've mentioned above. Let's add it for every field we're interested in:

{'$project': {
'null_field_exists': {'$or': [
{'$eq': ['$null_field', null]},
{'$gt': ['$null_field', null]},
]},
'not_null_field_exists': {'$or': [
{'$eq': ['$not_null_field', null]},
{'$gt': ['$not_null_field', null]},
]},
'non_existent_field_exists': {'$or': [
{'$eq': ['$non_existent_field', null]},
{'$gt': ['$non_existent_field', null]},
]},
}},

What we get is:

{
"null_field_exists" : true,
"not_null_field_exists" : true,
"non_existent_field_exists" : false
}

Correct!

And a small note: we use null for comparing because it is the smallest value at least valuable (smaller is just the non-existence).

My answer is:

{$match:{
$and:[{
name:{
$exists:true
}
}, {
$expr:{
$eq:["$$id", "$_id"]
}
}]
}}

I use this in lookup, on my pipeline stage. This post 2 rules the first one, name must exist. And the second thing is the relation between these 2 collection. I Am sure u can modify this for your question.

In mongoose only following working

$ne:  [ { $type : "$var_to_check"}, 'missing'] }

I solved it by using $addFields with $ifNull, and then $match the added field by checking if its value is null.

collection.aggregate(
[
{
$addFields:{
fieldName:{
$ifNull:["$fieldToCheckIfExists", null]
}
}
},
{
$match:{
fieldName:{
$ne: null
}
}
]

A semantically transparent solution to check if a field exists and is not null:

{ $ne: [{ $ifNull: ["$field", null] }, null] }

To check if it is missing, replace $ne with $eq.

In Group, if you want to count the existing fields, but not group as the other answers shows, you can use

{
_id: "$groupField",
qtyExists: {
$sum: {
$cond: ["$field", 1, 0]
}
},
qtyNotExists: {
$sum: {
$cond: ["$field", 0, 1]
}
},
}