DataWeave数据转换需求：按PAYLOAD_NUMBER分组处理数组数据

输入数据 #

[
{
    "ID_TYPE": "4",
    "DATE": "20230529",
    "ADM_TIME": "17:00",
    "PAYLOAD_NUMBER": "597418",
    "PAYLOAD_COMMENT": "HELLO"
},
{
    "ID_TYPE": "4",
    "DATE": "20230531",
    "ADM_TIME": "17:00",
    "PAYLOAD_NUMBER": "597418",
    "PAYLOAD_COMMENT": "HELLO"
},
{
    "ID_TYPE": "4",
    "DATE": "20230602",
    "ADM_TIME": "17:00",
    "PAYLOAD_NUMBER": "597418",
    "PAYLOAD_COMMENT": "HELLO"
},
{
    "ID_TYPE": "4",
    "DATE": "20230628",
    "ADM_TIME": "8:00",
    "PAYLOAD_NUMBER": "597500",
    "PAYLOAD_COMMENT": "Comments"
},
{
    "ID_TYPE": "4",
    "DATE": "20230628",
    "ADM_TIME": "17:00",
    "PAYLOAD_NUMBER": "597500",
    "PAYLOAD_COMMENT": "Comments"
},
{
    "ID_TYPE": "4",
    "DATE": "20230629",
    "ADM_TIME": "12:00",
    "PAYLOAD_NUMBER": "597500",
    "PAYLOAD_COMMENT": "Comments"
},
{
    "ID_TYPE": "4",
    "DATE": "20230630",
    "ADM_TIME": "17:00",
    "PAYLOAD_NUMBER": "597500",
    "PAYLOAD_COMMENT": "Comments"
},
{
    "ID_TYPE": "4",
    "DATE": "20230702",
    "ADM_TIME": "12:00",
    "PAYLOAD_NUMBER": "597500",
    "PAYLOAD_COMMENT": "Comments"
}
]

期望输出 #

[
    {
            "TIMES": "17:00",
            "MORNING": "0",
            "NOON": "0",
            "EVENING": "3",
            "TOTAL_QUANTITY": "3",
            "DATES": "2023/05/29, 2023/06/02, 2023/05/31",
            "PAYLOAD_NUMBER": "597418",
            "PAYLOAD_COMMENT": "HELLO"
        },
        {
            "TIMES": "8:00, 12:00, 17:00",
            "MORNING": "1",
            "NOON": "2",
            "EVENING": "2",
            "TOTAL_QUANTITY": "5",
            "DATES": "2023/06/28, 2023/06/29, 2023/06/30, 2023/07/02",
            "PAYLAOD_NUMBER": "597500",
            "PAYLOAD_COMMENT": "Comments"
        }
    ]

转换规则 #

按PAYLOAD_NUMBER分组后，需实现以下字段处理：

• TIMES：汇总分组内所有ADM_TIME的去重列表，用逗号分隔
• MORNING：统计ADM_TIME在00:01-09:59区间的记录数量，转为字符串
• NOON：统计ADM_TIME在10:00-14:59区间的记录数量，转为字符串
• EVENING：统计ADM_TIME在15:00-23:59区间的记录数量，转为字符串
• TOTAL_QUANTITY：计算上述三个时段数量的总和，转为字符串
• DATES：汇总分组内所有DATE的去重列表，将YYYYMMDD格式转换为YYYY/MM/DD后用逗号分隔
• PAYLOAD_NUMBER：分组对应的编号
• PAYLOAD_COMMENT：分组对应的注释内容

DataWeave 解决方案 #

%dw 2.0
output application/json
---
payload groupBy $.PAYLOAD_NUMBER mapObject ((groupedItems, key) -> {
    TIMES: (groupedItems.ADM_TIME distinctBy $) joinBy ", ",
    MORNING: (groupedItems filter ((item) -> 
        (item.ADM_TIME as Time {format: "HH:mm"}) >= |00:01| 
        and (item.ADM_TIME as Time {format: "HH:mm"}) <= |09:59|
    ) sizeOf) as String,
    NOON: (groupedItems filter ((item) -> 
        (item.ADM_TIME as Time {format: "HH:mm"}) >= |10:00| 
        and (item.ADM_TIME as Time {format: "HH:mm"}) <= |14:59|
    ) sizeOf) as String,
    EVENING: (groupedItems filter ((item) -> 
        (item.ADM_TIME as Time {format: "HH:mm"}) >= |15:00| 
        and (item.ADM_TIME as Time {format: "HH:mm"}) <= |23:59|
    ) sizeOf) as String,
    TOTAL_QUANTITY: (
        (groupedItems filter ((item) -> 
            (item.ADM_TIME as Time {format: "HH:mm"}) >= |00:01| 
            and (item.ADM_TIME as Time {format: "HH:mm"}) <= |09:59|
        ) sizeOf) +
        (groupedItems filter ((item) -> 
            (item.ADM_TIME as Time {format: "HH:mm"}) >= |10:00| 
            and (item.ADM_TIME as Time {format: "HH:mm"}) <= |14:59|
        ) sizeOf) +
        (groupedItems filter ((item) -> 
            (item.ADM_TIME as Time {format: "HH:mm"}) >= |15:00| 
            and (item.ADM_TIME as Time {format: "HH:mm"}) <= |23:59|
        ) sizeOf)
    ) as String,
    DATES: (groupedItems.DATE distinctBy $ map ((dateStr) -> 
        dateStr as Date {format: "yyyyMMdd"} as String {format: "yyyy/MM/dd"}
    )) joinBy ", ",
    PAYLOAD_NUMBER: key,
    PAYLOAD_COMMENT: groupedItems[0].PAYLOAD_COMMENT
}) pluck $

代码说明 #

1. 分组处理：使用groupBy $.PAYLOAD_NUMBER将输入数据按编号分组，再通过mapObject遍历每个分组
2. TIMES字段：提取分组内所有ADM_TIME，用distinctBy去重后通过joinBy拼接成字符串
3. 时段统计：将ADM_TIME转换为Time类型，通过时间区间过滤后用sizeOf获取数量，再转为字符串
4. TOTAL_QUANTITY字段：直接累加三个时段的统计数量，转为字符串
5. DATES字段：提取分组内所有DATE去重后，先转换为Date类型再格式化为YYYY/MM/DD字符串，最后拼接
6. 基础字段：直接取分组的PAYLOAD_NUMBER作为key，取分组第一条数据的PAYLOAD_COMMENT作为注释（假设同分组注释一致）
7. 最终转换：用pluck $将mapObject生成的对象转为数组格式

内容的提问来源于stack exchange，提问作者user22395789