一、$unwind介绍
解析输入文档中的数组字段,为每个元素输出一个文档。每个输出文档都是输入文档,数组字段的值被元素替换。如果字段值为null、缺失或数组为空,则$unwind不会输出文档。
1.1 语法
{ $unwind: <field path> }
可以将文档传递给$unwind以指定各种行为选项
{
$unwind:
{
path: <field path>,
includeArrayIndex: <string>,
preserveNullAndEmptyArrays: <boolean>
}
}
参数解析
-
path:string类型,数组字段的字段路径。若要指定字段路径,请在字段名称前加上美元符号$并将其括在引号中。
-
includeArrayIndex:string类型,可选的。用于保存元素的数组索引的新字段的名称。新字段名称不能以美元符号$开头。
-
preserveNullAndEmptyArrays:boolean类型,可选的。如果为真,如果路径为空、丢失或数组为空,则
$unwind
输出文档。如果为false,如果路径为空、丢失或数组为空,则$unwind
不输出文档。默认值为false。
二、简单示例
2.1 简单示例
初始化数据
db.inventory.insertOne({ "_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] })
按照sizes字段拆分数据
db.inventory.aggregate( [ { $unwind : "$sizes" } ] )
结果
{ "_id" : 1, "item" : "ABC1", "sizes" : "S" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "M" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "L" }
2.2 拆分数组字段是null或者[]数据
初始化数据
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 }
])
2.2.1 示例一
下面的$unwind
操作是等效的,并为size字段中的每个元素返回一个文档。如果size字段没有解析为数组,但没有丢失、null或空数组,则$unwind将非数组操作数视为单个元素数组。
db.inventory2.aggregate( [ { $unwind: "$sizes" } ] )
db.inventory2.aggregate( [ { $unwind: { path: "$sizes" } } ] )
结果
{ "_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" : 3, "item" : "IJK", "price" : NumberDecimal("160"), "sizes" : "M" }
2.2.2 示例二
下面的$unwind操作使用includeArrayIndex选项在输出中包含数组索引。
db.inventory2.aggregate([
{
$unwind:
{
path: "$sizes",
includeArrayIndex: "arrayIndex"
}
}])
结果
{ "_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 }
2.2.3 示例三
下面的$unwind操作使用preserveNullAndEmptyArrays选项来包含size字段为null、缺失或空数组的文档。
db.inventory2.aggregate( [
{
$unwind: { path: "$sizes", preserveNullAndEmptyArrays: true }
}
] )
结果
{ "_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 }
2.3 拆分数组字段并分组
初始化数据
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 }
])
2.3.1 示例一
下面的管道将展开大小数组,并将产生的文档按展开后的大小值进行分组
db.inventory2.aggregate( [
// First Stage
{
$unwind: { path: "$sizes", preserveNullAndEmptyArrays: true }
},
// Second Stage
{
$group:
{
_id: "$sizes",
averagePrice: { $avg: "$price" }
}
},
// Third Stage
{
$sort: { "averagePrice": -1 }
}
] )
结果
{ "_id" : "M", "averagePrice" : NumberDecimal("120") }
{ "_id" : "L", "averagePrice" : NumberDecimal("80") }
{ "_id" : "S", "averagePrice" : NumberDecimal("80") }
{ "_id" : null, "averagePrice" : NumberDecimal("45.25") }
2.4 拆分嵌入式数组文档
初始化数据
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("1.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")
}
]
}
])
2.4.1 示例一
下面的操作将按标签销售的商品分组,并计算每个标签的总销售额。
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" ] }
}
}
}
])
结果
{ "_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") }
三、项目实战
3.1 功能说明
按日期统计动态数据
3.2 相关代码
查询参数
@Data
public class QueryCalendarSummaryVO {
@ApiModelProperty("日期从")
private LocalDate from;
@ApiModelProperty("日期到")
private LocalDate to;
}
实现方法
public List<DynamicCalendarSummaryDTO> getCalendarSummaryList(QueryCalendarSummaryVO vo) {
List<AggregationOperation> operations = new ArrayList<>();
operations.add(Aggregation.match(Criteria.where("calendar.deleted").is(false)));
operations.add(Aggregation.match(Criteria.where("calendar.calendar_event_times").exists(true)
.andOperator(Criteria.where("calendar.calendar_event_times").gt(vo.getFrom()),
Criteria.where("calendar.calendar_event_times").lt(vo.getTo()))));
TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai"));
operations.add(Aggregation.unwind("calendar.calendar_event_times"));
operations.add(Aggregation.project("calendar.calendar_event_times").and(DateOperators.DateToString.dateOf("calendar.calendar_event_times").toString("%Y-%m-%d").withTimezone(DateOperators.Timezone.valueOf("Asia/Shanghai"))).as("date"));
operations.add(Aggregation.group("date").count().as("count"));
operations.add(Aggregation.project("date", "count").and("date").previousOperation());
operations.add(Aggregation.sort(Sort.Direction.ASC, "date"));
Aggregation aggregation = Aggregation.newAggregation(operations);
AggregationResults<DynamicCalendarSummaryDTO> results = mongoTemplate.aggregate(aggregation, "dynamic", DynamicCalendarSummaryDTO.class);
List<DynamicCalendarSummaryDTO> list = results.getMappedResults();
return list;
}
返回结果
@Data
public class DynamicCalendarSummaryDTO {
@ApiModelProperty(value = "日期")
private String date;
@ApiModelProperty(value = "数量")
private Long count;
}
动态实体
@Data
@Document(collection = "dynamic")
public class DynamicDetailEntity implements Serializable {
@JsonDeserialize(using = ObjectIdJsonDeserializer.class)
@JsonSerialize(using = ObjectIdJsonSerializer.class)
@JSONField(serializeUsing = ObjectIDSerializer.class, deserializeUsing = ObjectIDSerializer.class)
@MongoId
private ObjectId id;
//是否删除(false:未删除;true:已删除)
private Boolean deleted = false;
//创建日期
@CreatedDate
@Field("createTime")
private LocalDateTime createTime;
//修改时间
@LastModifiedDate
@Field("modifyTime")
private LocalDateTime modifyTime;
@Version
private Integer version;
//用户ID
@Indexed
private Long memberId;
//动态内容
private String content;
//动态日历
@Field(value = "calendar")
private CalendarDetail calendar;
// 其他字段省略
}
动态日历实体
@Data
public class CalendarDetail {
@ApiModelProperty("主题(话题)")
private String title;
@ApiModelProperty("发生的时间")
@Field("calendar_event_times")
private List<LocalDateTime> calendarEventTimes;
@Field("event_timestamp")
private List<Long> calendarEventTimestamps;
// 其他字段省略
}
四、$unwind排序+分页问题
需要在unwind
前排序才有效,否则分页时会出现重复数据。
@Override
public IPage<DynamicDetailDTO> getCityTodayList(QueryCityCalendarVO vo, Long memberId) {
PageModel sort = MongoUtils.sort(vo);
List<AggregationOperation> operations = new ArrayList<>();
operations.add(MongoUtils.match("city_code", vo.getCityCode()));
operations.add(MongoUtils.match("calendar.calendar_type", 2));
//需要在unwind前排序才有效
operations.add(Aggregation.sort(Sort.Direction.ASC, "calendar.sort"));
operations.add(Aggregation.unwind("calendar.activity_dates"));
Long timestamp = LocalDate.now().atStartOfDay().toInstant(ZoneOffset.of("+8")).toEpochMilli();
Long endTimestamp = LocalDate.now().atTime(23, 59, 59).toInstant(ZoneOffset.of("+8")).toEpochMilli();
operations.add(Aggregation.match(Criteria.where("calendar.activity_dates").exists(true)
.andOperator(Criteria.where("calendar.activity_dates.localDateTimestamp").gte(timestamp),
Criteria.where("calendar.activity_dates.localDateTimestamp").lte(endTimestamp))));
Long totalCount = repositoryOps.getTotalCount(operations, "dynamic", DynamicCalendarDetailModel.class);
Aggregation aggregation = Aggregation.newAggregation(operations);
AggregationResults<DynamicCalendarDetailModel> results = mongoTemplate.aggregate(aggregation, "dynamic", DynamicCalendarDetailModel.class);
IPage<DynamicDetailDTO> page = MongoUtils.getPage(sort.getPageRequest().getPageNumber(), sort.getLimit(), totalCount, detailDTOS);
return page;
}