Docs Home → MongoDB Manual
$unwind
Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.
You can pass a field path operand or a document operand to unwind an array field.
You can pass the array field path to
$unwind
. When using this syntax, $unwind
does not output a document if the field value is null, missing, or an empty array.
{ $unwind: }
When you specify the field path, prefix
the field name with a dollar sign $
and enclose in quotes.
You can pass a document to $unwind
to specify various behavior options.
{ $unwind: { path: , includeArrayIndex: , preserveNullAndEmptyArrays: } }
path | string | Field path to an array field. To specify a field path, prefix the field name with a dollar sign |
includeArrayIndex | string | Optional. The name of a new field to hold the array index of the element. The name cannot start with a dollar sign |
preserveNullAndEmptyArrays | boolean | Optional.
The default value is |
Changed in version 3.2: $unwind
stage no longer errors on non-array operands. If the operand does not resolve to an array but is not missing, null, or an empty array,
$unwind
treats the operand as a single element array. If the operand is null, missing, or an empty array, the behavior of $unwind
depends on the value of the
preserveNullAndEmptyArrays option.
Previously, if a value in the field specified by the field path is not an array, db.collection.aggregate[]
generates an error.
If you specify a path
for a field that does not exist in an input document or the field is an empty array, $unwind
, by default, ignores the input document and will not output documents for that input document.
To output documents where the array field is missing, null or an empty array, use the preserveNullAndEmptyArrays option.
In mongosh
, create a sample collection named inventory
with the following document:
db.inventory.insertOne[{ "_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] }]
The
following aggregation uses the $unwind
stage to output a document for each element in the sizes
array:
db.inventory.aggregate[ [ { $unwind : "$sizes" } ] ]
The operation returns the following results:
{ "_id" : 1, "item" : "ABC1", "sizes" : "S" } { "_id" : 1, "item" : "ABC1", "sizes" : "M" } { "_id" : 1, "item" : "ABC1", "sizes" : "L" }
Each document is identical to the input document except for the value of the sizes
field which now holds a value from the original sizes
array.
Consider the clothing
collection:
db.clothing.insertMany[[ { "_id" : 1, "item" : "Shirt", "sizes": [ "S", "M", "L"] }, { "_id" : 2, "item" : "Shorts", "sizes" : [ ] }, { "_id" : 3, "item" : "Hat", "sizes": "M" }, { "_id" : 4, "item" : "Gloves" }, { "_id" : 5, "item" : "Scarf", "sizes" : null } ]]
$unwind
treats the sizes
field as a single element array if:
the field is present,
the value is not null, and
the value is not an empty array.
Expand the sizes
arrays with
$unwind
:
db.clothing.aggregate[ [ { $unwind: { path: "$sizes" } } ] ]
The $unwind
operation returns:
{ _id: 1, item: 'Shirt', sizes: 'S' }, { _id: 1, item: 'Shirt', sizes: 'M' }, { _id: 1, item: 'Shirt', sizes: 'L' }, { _id: 3, item: 'Hat', sizes: 'M' }
In document
"_id": 1
,sizes
is a populated array.$unwind
returns a document for each element in thesizes
field.In document
"_id": 3
,sizes
resolves to a single element array.Documents
"_id": 2, "_id": 4
, and"_id": 5
do not return anything because thesizes
field cannot be reduced to a single element array.
Note
The { path: }
syntax is optional. The following $unwind
operations are equivalent.
db.clothing.aggregate[ [ { $unwind: "$sizes" } ] ] db.clothing.aggregate[ [ { $unwind: { path: "$sizes" } } ] ]
The preserveNullAndEmptyArrays
and
includeArrayIndex
examples use the following collection:
db.inventory2.insertMany[[ { "_id" : 1, "item" : "ABC", price: NumberDecimal["80"], "sizes": [ "S", "M", "L"] }, { "_id" : 2, "item" : "EFG", price: NumberDecimal["120"], "sizes" : [ ] }, { "_id" : 3, "item" : "IJK", price: NumberDecimal["160"], "sizes": "M" }, { "_id" : 4, "item" : "LMN" , price: NumberDecimal["10"] }, { "_id" : 5, "item" : "XYZ", price: NumberDecimal["5.75"], "sizes" : null } ]]
The following $unwind
operation uses the
preserveNullAndEmptyArrays option to include documents whose sizes
field is null, missing, or an empty array.
db.inventory2.aggregate[ [ { $unwind: { path: "$sizes", preserveNullAndEmptyArrays: true } } ] ]
The output includes those documents where the sizes
field is null, missing, or an empty array:
{ "_id" : 1, "item" : "ABC", "price" : NumberDecimal["80"], "sizes" : "S" } { "_id" : 1, "item" : "ABC", "price" : NumberDecimal["80"], "sizes" : "M" } { "_id" : 1, "item" : "ABC", "price" : NumberDecimal["80"], "sizes" : "L" } { "_id" : 2, "item" : "EFG", "price" : NumberDecimal["120"] } { "_id" : 3, "item" : "IJK", "price" : NumberDecimal["160"], "sizes" : "M" } { "_id" : 4, "item" : "LMN", "price" : NumberDecimal["10"] } { "_id" : 5, "item" : "XYZ", "price" : NumberDecimal["5.75"], "sizes" : null }
The following
$unwind
operation uses the includeArrayIndex option to include the array index in the output.
db.inventory2.aggregate[ [ { $unwind: { path: "$sizes", includeArrayIndex: "arrayIndex" } }]]
The operation unwinds the sizes
array and includes the array index in the new
arrayIndex
field. If the sizes
field does not resolve to a populated array but is not missing, null, or an empty array, the arrayIndex
field is null
.
{ "_id" : 1, "item" : "ABC", "price" : NumberDecimal["80"], "sizes" : "S", "arrayIndex" : NumberLong[0] } { "_id" : 1, "item" : "ABC", "price" : NumberDecimal["80"], "sizes" : "M", "arrayIndex" : NumberLong[1] } { "_id" : 1, "item" : "ABC", "price" : NumberDecimal["80"], "sizes" : "L", "arrayIndex" : NumberLong[2] } { "_id" : 3, "item" : "IJK", "price" : NumberDecimal["160"], "sizes" : "M", "arrayIndex" : null }
In mongosh
, create a sample collection named inventory2
with the following documents:
db.inventory2.insertMany[[ { "_id" : 1, "item" : "ABC", price: NumberDecimal["80"], "sizes": [ "S", "M", "L"] }, { "_id" : 2, "item" : "EFG", price: NumberDecimal["120"], "sizes" : [ ] }, { "_id" : 3, "item" : "IJK", price: NumberDecimal["160"], "sizes": "M" }, { "_id" : 4, "item" : "LMN" , price: NumberDecimal["10"] }, { "_id" : 5, "item" : "XYZ", price: NumberDecimal["5.75"], "sizes" : null } ]]
The following pipeline unwinds the sizes
array and groups the resulting documents by the unwound size values:
db.inventory2.aggregate[ [ // First Stage { $unwind: { path: "$sizes", preserveNullAndEmptyArrays: true } }, // Second Stage { $group: { _id: "$sizes", averagePrice: { $avg: "$price" } } }, // Third Stage { $sort: { "averagePrice": -1 } } ] ]
First Stage:The $unwind
stage outputs a new document for each element in the sizes
array. The stage uses the preserveNullAndEmptyArrays option to include in the output those documents where sizes
field is missing, null or an empty array. This stage passes the following documents to the next stage:
{ "_id" : 1, "item" : "ABC", "price" : NumberDecimal["80"], "sizes" : "S" } { "_id" : 1, "item" : "ABC", "price" : NumberDecimal["80"], "sizes" : "M" } { "_id" : 1, "item" : "ABC", "price" : NumberDecimal["80"], "sizes" : "L" } { "_id" : 2, "item" : "EFG", "price" : NumberDecimal["120"] } { "_id" : 3, "item" : "IJK", "price" : NumberDecimal["160"], "sizes" : "M" } { "_id" : 4, "item" : "LMN", "price" : NumberDecimal["10"] } { "_id" : 5, "item" : "XYZ", "price" : NumberDecimal["5.75"], "sizes" : null }
Second Stage:The $group
stage groups the documents by sizes
and calculates the average price
of each size. This stage passes the following documents to the next stage:
{ "_id" : "S", "averagePrice" : NumberDecimal["80"] } { "_id" : "L", "averagePrice" : NumberDecimal["80"] } { "_id" : "M", "averagePrice" : NumberDecimal["120"] } { "_id" : null, "averagePrice" : NumberDecimal["45.25"] }
Third Stage:The $sort
stage sorts the documents by averagePrice
in descending order. The operation returns the following result:
{ "_id" : "M", "averagePrice" : NumberDecimal["120"] } { "_id" : "L", "averagePrice" : NumberDecimal["80"] } { "_id" : "S", "averagePrice" : NumberDecimal["80"] } { "_id" : null, "averagePrice" : NumberDecimal["45.25"] }
Tip
In
mongosh
, create a sample collection named sales
with the following documents:
db.sales.insertMany[[ { _id: "1", "items" : [ { "name" : "pens", "tags" : [ "writing", "office", "school", "stationary" ], "price" : NumberDecimal["12.00"], "quantity" : NumberInt["5"] }, { "name" : "envelopes", "tags" : [ "stationary", "office" ], "price" : NumberDecimal["19.95"], "quantity" : NumberInt["8"] } ] }, { _id: "2", "items" : [ { "name" : "laptop", "tags" : [ "office", "electronics" ], "price" : NumberDecimal["800.00"], "quantity" : NumberInt["1"] }, { "name" : "notepad", "tags" : [ "stationary", "school" ], "price" : NumberDecimal["14.95"], "quantity" : NumberInt["3"] } ] } ]]
The following operation groups the items sold by their tags and calculates the total sales amount per each tag.
db.sales.aggregate[[ // First Stage { $unwind: "$items" }, // Second Stage { $unwind: "$items.tags" }, // Third Stage { $group: { _id: "$items.tags", totalSalesAmount: { $sum: { $multiply: [ "$items.price", "$items.quantity" ] } } } } ]]
First StageThe first
$unwind
stage outputs a new document for each element in the items
array:
{ "_id" : "1", "items" : { "name" : "pens", "tags" : [ "writing", "office", "school", "stationary" ], "price" : NumberDecimal["12.00"], "quantity" : 5 } } { "_id" : "1", "items" : { "name" : "envelopes", "tags" : [ "stationary", "office" ], "price" : NumberDecimal["19.95"], "quantity" : 8 } } { "_id" : "2", "items" : { "name" : "laptop", "tags" : [ "office", "electronics" ], "price" : NumberDecimal["800.00"], "quantity" : 1 } } { "_id" : "2", "items" : { "name" : "notepad", "tags" : [ "stationary", "school" ], "price" : NumberDecimal["14.95"], "quantity" : 3 } }
Second StageThe second $unwind
stage outputs a new document for each element in the
items.tags
arrays:
{ "_id" : "1", "items" : { "name" : "pens", "tags" : "writing", "price" : NumberDecimal["12.00"], "quantity" : 5 } } { "_id" : "1", "items" : { "name" : "pens", "tags" : "office", "price" : NumberDecimal["12.00"], "quantity" : 5 } } { "_id" : "1", "items" : { "name" : "pens", "tags" : "school", "price" : NumberDecimal["12.00"], "quantity" : 5 } } { "_id" : "1", "items" : { "name" : "pens", "tags" : "stationary", "price" : NumberDecimal["12.00"], "quantity" : 5 } } { "_id" : "1", "items" : { "name" : "envelopes", "tags" : "stationary", "price" : NumberDecimal["19.95"], "quantity" : 8 } } { "_id" : "1", "items" : { "name" : "envelopes", "tags" : "office", "price" : NumberDecimal["19.95"], "quantity" : 8 } } { "_id" : "2", "items" : { "name" : "laptop", "tags" : "office", "price" : NumberDecimal["800.00"], "quantity" : 1 } } { "_id" : "2", "items" : { "name" : "laptop", "tags" : "electronics", "price" : NumberDecimal["800.00"], "quantity" : 1 } } { "_id" : "2", "items" : { "name" : "notepad", "tags" : "stationary", "price" : NumberDecimal["14.95"], "quantity" : 3 } } { "_id" : "2", "items" : { "name" : "notepad", "tags" : "school", "price" : NumberDecimal["14.95"], "quantity" : 3 } }
Third StageThe $group
stage groups the documents by the tag and calculates the total sales amount of items with each tag:
{ "_id" : "writing", "totalSalesAmount" : NumberDecimal["60.00"] } { "_id" : "stationary", "totalSalesAmount" : NumberDecimal["264.45"] } { "_id" : "electronics", "totalSalesAmount" : NumberDecimal["800.00"] } { "_id" : "school", "totalSalesAmount" : NumberDecimal["104.85"] } { "_id" : "office", "totalSalesAmount" : NumberDecimal["1019.60"] }
Tip
Aggregation with the Zip Code Data Set,
Aggregation with User Preference Data