如何使用JOLT v0.1.1转换JSON中的日期为YYYY-MM-DD格式

问题背景 #

原始JSON输入：

{"entityType": "person","id": 24285,"properties": {"firstName": "Zia","lastName": "Rus","email": "ziarus@zia.com","phoneNumber": null,"requestingUserGuid": "Cugxxasod Daspohs, Uyowiye","generalReportReference": 244,"employment": [{"employerName": "Avature","startDate": "01-Jan-2003","endDate": "04-Nov-2008","jobTitle": ""},{"employerName": "Avature","startDate": "06-Jul-2012","endDate": "","jobTitle": ""}],"supervisor": "Supervisor","education": [{"institutionName": "Universitate","major": "gead","degree": "gased","startDate": "01-Feb-2022","endDate": "01-Oct-2024"},{"institutionName": "College","major": "brup","degree": "brup","startDate": "05-Apr-2016","endDate": ""}],"externalIdentifier": 24285,"clientGuid": "6253d1c2-b02f-4513-8348-89db9b8ba449","productGuid": "e2f507b4-025c-439b-9ddb-833f9e537e60","applicantGuid": ""}}

当前已完成大部分数据转换，需将startDate和endDate字段格式化为YYYY-MM-DD格式，场景特点：

• employment和education数组可包含1个或多个对象
• endDate可能为空字符串，不能用默认值覆盖方案处理

当前使用的JOLT转换配置：

[{"operation": "shift","spec": {"properties": {"firstName": "firstName","lastName": "lastName","email": "email","phoneNumber": "phoneNumber","requestingUserGuid": "requestingUserGuid","generalReportReference": "generalReportReference","clientGuid": "clientGuid","productId": "productId","applicantGuid": "applicantGuid","externalIdentifier": "externalIdentifier","employment": {"*": {"employerName|endDate": {"": null,"*": {"@1": "&4[&3].&2"}},"*": {"*": {"@1": "&4[&3].&2"}}}},"education": {"*": {"institutionName|registrarPhone|endDate": {"": null,"*": {"@1": "&4[&3].&2"}},"*": {"*": {"@1": "&4[&3].&2"}}}}},"education": {"*": {"~institutionName": "N/A","~registrarPhone": "(111) 111-1111"}},"employment": {"*": {"~employerName": "N/A"}}}},{"operation": "modify-default-beta","spec": {"employment": {"*": {"employerName": "N/A","firstNameUsed": "@(4,firstName)","lastNameUsed": "@(4,lastName)"},"education": {"*": {"institutionName": "N/A","registrarPhone": "(111) 111-1111"}}}},{"operation": "modify-overwrite-beta","spec": {"employment": {"*": {"supervisor": "Supervisor"}}}}]

疑问：如何实现日期字段的格式转换？能否通过JOLT完成，还是需要改用NiFi中的其他处理器？

解决方案 #

1. 纯JOLT实现方式 #

JOLT无内置日期格式化函数，但可通过字符串拆分+重组实现，仅适用于固定输入格式（如你的DD-MMM-YYYY）。在现有配置中新增modify-overwrite-beta操作：

[
  // 原有shift操作
  {"operation": "shift","spec": {"properties": {"firstName": "firstName","lastName": "lastName","email": "email","phoneNumber": "phoneNumber","requestingUserGuid": "requestingUserGuid","generalReportReference": "generalReportReference","clientGuid": "clientGuid","productId": "productId","applicantGuid": "applicantGuid","externalIdentifier": "externalIdentifier","employment": {"*": {"employerName|endDate": {"": null,"*": {"@1": "&4[&3].&2"}},"*": {"*": {"@1": "&4[&3].&2"}}}},"education": {"*": {"institutionName|registrarPhone|endDate": {"": null,"*": {"@1": "&4[&3].&2"}},"*": {"*": {"@1": "&4[&3].&2"}}}}},"education": {"*": {"~institutionName": "N/A","~registrarPhone": "(111) 111-1111"}},"employment": {"*": {"~employerName": "N/A"}}}},
  // 新增日期格式化操作
  {"operation": "modify-overwrite-beta", "spec": {
    "employment": {
      "*": {
        "startDate": "=concat(substring(@(1,startDate),7,4), '-',=switch(substring(@(1,startDate),3,3), 'Jan', '01', 'Feb', '02', 'Mar', '03', 'Apr', '04', 'May', '05', 'Jun', '06', 'Jul', '07', 'Aug', '08', 'Sep', '09', 'Oct', '10', 'Nov', '11', 'Dec', '12"), '-', substring(@(1,startDate),0,2))",
        "endDate": ["=isEmpty(@(1,endDate))", null, "=concat(substring(@(1,endDate),7,4), '-',=switch(substring(@(1,endDate),3,3), 'Jan', '01', 'Feb', '02', 'Mar', '03', 'Apr', '04', 'May', '05', 'Jun', '06', 'Jul', '07', 'Aug', '08', 'Sep', '09', 'Oct', '10', 'Nov', '11', 'Dec', '12"), '-', substring(@(1,endDate),0,2))"]
      }
    },
    "education": {
      "*": {
        "startDate": "=concat(substring(@(1,startDate),7,4), '-',=switch(substring(@(1,startDate),3,3), 'Jan', '01', 'Feb', '02', 'Mar', '03', 'Apr', '04', 'May', '05', 'Jun', '06', 'Jul', '07', 'Aug', '08', 'Sep', '09', 'Oct', '10', 'Nov', '11', 'Dec', '12"), '-', substring(@(1,startDate),0,2))",
        "endDate": ["=isEmpty(@(1,endDate))", null, "=concat(substring(@(1,endDate),7,4), '-',=switch(substring(@(1,endDate),3,3), 'Jan', '01', 'Feb', '02', 'Mar', '03', 'Apr', '04', 'May', '05', 'Jun', '06', 'Jul', '07', 'Aug', '08', 'Sep', '09', 'Oct', '10', 'Nov', '11', 'Dec', '12'), '-', substring(@(1,endDate),0,2))"]
      }
    }
  }},
  // 原有modify-default-beta操作
  {"operation": "modify-default-beta","spec": {"employment": {"*": {"employerName": "N/A","firstNameUsed": "@(4,firstName)","lastNameUsed": "@(4,lastName)"},"education": {"*": {"institutionName": "N/A","registrarPhone": "(111) 111-1111"}}}},
  // 原有modify-overwrite-beta操作
  {"operation": "modify-overwrite-beta","spec": {"employment": {"*": {"supervisor": "Supervisor"}}}}
]

核心逻辑：

• 用substring拆分原始日期的日、月、年部分
• 用switch将英文月份缩写转为两位数字
• 用concat重组为YYYY-MM-DD格式
• 对endDate做三元判断，空字符串时保留为null，非空则格式化

2. NiFi处理器替代方案（更灵活） #

若日期格式可能变化或需处理复杂场景，推荐用NiFi处理器组合：

1. JoltTransformJSON：保留原有字段结构转换逻辑
2. UpdateRecord：用NiFi表达式语言做日期格式化，配置示例：
   • 记录读取器：JsonTreeReader
   • 记录写入器：JsonRecordSetWriter
   • 更新策略：RECORD_PATH
   • 添加更新规则：
     • 路径：/employment/*/startDate，值：${field.value:toDate('dd-MMM-yyyy'):format('yyyy-MM-dd')}
     • 路径：/employment/*/endDate，值：${field.value:isEmpty():ifElse(null, ${field.value:toDate('dd-MMM-yyyy'):format('yyyy-MM-dd')})}
     • 路径：/education/*/startDate，值：${field.value:toDate('dd-MMM-yyyy'):format('yyyy-MM-dd')}
     • 路径：/education/*/endDate，值：${field.value:isEmpty():ifElse(null, ${field.value:toDate('dd-MMM-yyyy'):format('yyyy-MM-dd')})}

该方式无需硬编码月份映射，支持更多日期格式，容错性更强。

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