-
Type: Improvement
-
Resolution: Done
-
Priority: Major - P3
-
Affects Version/s: 3.1.7
-
Component/s: Aggregation Framework
-
Major Change
-
Quint 9 09/18/15, QuInt A (10/12/15)
The stage {$unwind: '$field'} currently produces no output documents for an input with any of the following criteria:
- field is not present.
- field is present, but has a value of null.
- field is an empty array
SERVER-12685 is requesting that condition 1 can optionally produce output. This ticket is requesting that condition 3 can optionally produce output. For example, with a syntax like
{ $unwind: { path: '$field', includeEmpty: <bool> } }
The old syntax of {unwind: '$field'} should still be accepted as well.
This would enable a $lookUp + $unwind to produce a stream of documents that is equivalent to a left outer join. Currently this is not possible as the $lookUp stage produces an empty array for documents which have no matches in the foreign collection. If one were to unwind the as field afterwards, such a non-matching document would not produce any results.
e.g. If you have these collections:
products |
---|
{_id: 'tshirt', p: 12} |
{_id: 'shorts', p: 30} |
sales |
---|
{_id: 0, product_id: 'tshirt', q: 2} |
{_id: 1, product_id: 'tshirt', q: 1} |
And you use the following pipeline:
db.products.aggregate([ { $lookUp: { from: 'sales', localField: '$_id', foreignField: '$product_id', as: 'sale' }, // Produces two documents: // {_id: 'tshirt', p: 12, sale: [{_id: 0, ...}, {_id: 1, ...} ]} // {_id: 'shorts', p: 30, sale: []} { $unwind: '$sale' } ])
You'll get the following output:
{_id: 'tshirt', p: 30, sale: {_id: 0, product_id: 0, q: 2}} {_id: 'tshirt', p: 30, sale: {_id: 1, product_id: 0, q: 1}}
Which does not include any output for the second document, and hence is not an outer join.
If you replaced the $unwind stage with the proposed
{$unwind: {field: '$sale', includeEmpty: true}}
You would instead get the following results:
{_id: 'tshirt', p: 30, sale: {_id: 0, product_id: 0, q: 2}} {_id: 'tshirt', p: 30, sale: {_id: 1, product_id: 0, q: 1}} {_id: 'shorts', p: 12, sale: null}
It may also be desirable to specify a default value in this case rather than always using null
- is related to
-
DRIVERS-234 Aggregation Builder Support for 3.2
- Closed
-
SERVER-12685 Expand $unwind behavior to include empty documents optionally
- Closed
- related to
-
CSHARP-1401 Add an option to $unwind to include documents with empty arrays
- Closed
-
JAVA-1957 Add an option to $unwind to include documents with empty arrays
- Closed