MongoDB aggregation with unwind on empty array

Question

I have a collection of documents in MongoDB, where each document has a subdocument containing an array.

{
  _id: <id>
  key1: "value1",
  key2: "value2",
  ...
  versions: [
    { version: 2, key1: <othervalue2>, key2: <othervalue2>}
    { version: 1, key1: <othervalue2>, key2: <othervalue2>}
  ]
}

I want to query the collection and return the document with ALL its fields, along with the elements of the array that match certain parameters (or an empty array when none match). With my current code, I'm only able to get a result when there's AT LEAST one element of the array that matches.

I'm using these parameters for aggregation:

{$match: {_id: <someID>}},
{$unwind: '$versions'},
{$match: {'versions.version': {$gte: <version>}}},
{$group: {_id: '$_id', key1: {$first: '$key1'}, ..., 'versions': {$push: '$versions'}}

For example, querying where arrayelement.version >= 2 should return (this works with the current code):

{
  _id: <id>
  key1: "value1",
  key2: "value2",
  ...
  versions: [
    { version: 2, key1: <othervalue2>, key2: <othervalue2>}
  ]
}

And querying where arrayelement.version >= 4 should return:

{
  _id: <id>
  key1: "value1",
  key2: "value2",
  ...
  versions: []
}

None of the solutions I have seen address the issue of returning an object even when the array is empty. Is this even possible?

your $match aggregation is not satisfying the criteria. So, it is not returning any result in case of empty array — PaulShovan
– PaulShovan, Commented Jun 25, 2015 at 19:10
@gypsyCoder Actually $unwind ignores empty arrays so these documents will never get to the second $match. — zero323
– zero323, Commented Jun 25, 2015 at 19:48

zero323 · Accepted Answer · 2015-06-25 19:41:46Z

5

You can check if versions array is empty and if so add some placeholder, for example an empty document. Omitting rest of the pipeline it could be something like this:

[
    {
        "$project" : {
            "versions" : {
                "$cond" : {
                    "if" : {"$eq" : [{"$size" : "$versions" }, 0]},
                    "then" : [{ }],
                    "else" : "$versions"
                }
            },
            "_id" : 1
        }
    },
    {
        "$unwind" : "$versions"
    }
]

Keep in mind that {$match: {'versions.version'}} in your pipeline will filter it out so you have to adjust this part.

edited Jun 25, 2015 at 19:41

answered Jun 25, 2015 at 19:27

zero323

331k108 gold badges981 silver badges958 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Over a year ago

Thanks! That did it. Full answer below.

rom Over a year ago

How would you do this if you wanted to pass over (not include) the empty arrays?

John · Accepted Answer · 2015-06-26 01:49:01Z

Thanks to zero323 for setting me on the right track. Full filter is below

{$match: {_id: <someid>}},
{
    "$project" : {
        "versions" : {
            "$cond" : {
                "if" : {$or: [{$eq : [{$size : "$versions" }, 0]},{$eq : ["$version", <version>]}]},
                "then" : [null],
                "else" : "$versions"
            }
        },
        "_id" : 1,
        "key1": 1,
        "key2": 1,
        ...
        "version": 1

    }
},
{$unwind: '$versions'},
{$match: {$or: [{'versions.version':{ $exists: false}},{'versions.version': {$gte: <version>}}]}},
{$group: {_id:'$_id',key1:{$first: '$key1'}, key2:{$first: '$key2'},...,version:{$first:'$version'},versions:{$push: '$versions'}}},
{
    "$project" : {
        "versions" : {
            "$cond" : {
                "if" : {$eq : ["$versions", [null]]},
                "then" : [],
                "else" : "$versions"
            }
        },
        "_id" : 1,
        "key1": 1,
        "key2": 1,
        ...
        "version": 1
    }
}

Ujjwal Kumar Gupta · Accepted Answer · 2020-07-22 03:23:42Z

0

Starting from MongoDb 3.2, $unwind operator supports preserveNullAndEmptyArrays:<boolean>.

So when preserveNullAndEmptyArrays:true, it will also include value which doesn't have any data or empty data.

Code change -

{$unwind: {path: '$versions', preserveNullAndEmptyArrays: true} },

for more info, visit - https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/#document-operand-with-options

answered Jul 22, 2020 at 3:23

Ujjwal Kumar Gupta

2,3961 gold badge25 silver badges34 bronze badges

Collectives™ on Stack Overflow

MongoDB aggregation with unwind on empty array

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related