CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
smdufb
smdufb

Reputation: 565

$group by alternative when undefined

I have a collection of documents that I want to group by a field unless it does not exist, then I want to group by a second field instead.

As an example consider these 4 documents:

{
    prop1: 'anything',
    prop2: 1
},
{
    prop1: 'anything',
    prop2: 2
},
{
    prop1: 'something'
},
{
    prop1: 'something else'
}

When I group by 'prop2' I will get 3 grouped results. One for:

prop2 == 1 and

prop2 == 2 and

prop2 == undefined

But I want to get 4 results. One for:

prop2 == 1 and

prop2 == 2 and

prop1 == 'something' and

prop1 == 'something else'

Is this even possible? Is it a very complex query? Am I better off filling the missing field so it can be grouped?

Upvotes: 2

Views: 1405

Answers (2)

Sede
Sede

Reputation: 61225

You need to use the $ifNull conditional aggregation operator in the $group stage. 

db.coll.aggregate([ 
    { "$group": { "_id": { "$ifNull": [ "$prop1", "$prop2" ] } } }
])

The $ifNull operator return the value of the second expression if first expression evaluates to a null value, or if the field is missing. And that is what you want here.

You may want to add a $project stage to your pipeline but that is will be redundant and cause a drop of performance in your application.

Upvotes: 3

Mr Tarsa
Mr Tarsa

Reputation: 6652

You can use $ifNull operator in $project stage before grouping as follows

db.cullection.aggregate(
  { $project: { "groupVal": { $ifNull: [ '$prop2', '$prop1' ] } } }, 
  { $group: { "_id": "$groupVal" } }
);

For each document it will set the value groupVal according to which it should be grouped in the next $group stage. So, for documents where prop2 field is defined, the value from prop2 will be selected, otherwise the value from prop1.

Upvotes: 2

Related Questions