MongoDB Aggregation聚合操作之$unwind

最新推荐文章于 2024-02-11 23:29:33 发布

佩奇你过来

最新推荐文章于 2024-02-11 23:29:33 发布

阅读量1k

点赞数 1

文章标签： oracle

本文链接：https://blog.csdn.net/m0_59573674/article/details/131599794

版权

一、$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;
    }

佩奇你过来

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
1
评论
MongoDB Aggregation聚合操作之$unwind

解析输入文档中的数组字段，为每个元素输出一个文档。每个输出文档都是输入文档，数组字段的值被元素替换。如果字段值为null、缺失或数组为空，则$unwind不会输出文档。
复制链接

扫一扫