Details
-
Improvement
-
Status: Closed
-
Major - P3
-
Resolution: Done
-
3.1.7
-
Major Change
-
Quint 9 09/18/15, QuInt A (10/12/15)
Description
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
Attachments
Issue Links
- 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
-