如何调整Jolt转换规则，将时间字段嵌套至transformedPayload下

输入JSON数据 #

{
  "trackingNumber": "1ZEA83550362028861",
  "localActivityDate": "20210324",
  "localActivityTime": "183500",
  "scheduledDeliveryDate": "20220525",
  "actualDeliveryDate": "20220729",
  "actualdeliveryTime": "183500",
  "gmtActivityDate": "20210324",
  "gmtActivityTime": "223500",
  "activityStatus": {
    "type": "G",
    "code": "OR",
    "description": "Origin Scan"
  },
  "activityLocation": {
    "city": "RANDALLSTOWN,",
    "stateProvince": "MD",
    "postalCode": "21133",
    "country": "US"
  }
}

原Jolt转换规则 #

[
  {
    "operation": "shift",
    "spec": {
      "trackingNumber": "transformedPayload.trackingInfo",
      "localActivityDate": "tmp_Date",
      "localActivityTime": "tmp_Time",
      "scheduledDeliveryDate": "tmp_App",
      "actualDeliveryDate": "tmp_Del_Date",
      "actualdeliveryTime": "tmp_Del_Time",
      "activityStatus": {
        "type": "transformedPayload.events.type",
        "code": "transformedPayload.events.statusCode",
        "description": "transformedPayload.events.statusDescription"
      },
      "activityLocation": {
        "city": "transformedPayload.address.city",
        "stateProvince": "transformedPayload.address.state",
        "postalCode": "transformedPayload.address.postalCode",
        "country": "transformedPayload.address.country"
      }
    }
  },
  {
    "operation": "modify-default-beta",
    "spec": {
      "tmp_Year": "=substring(@(1,tmp_Date),0,4)",
      "tmp_Month": "=substring(@(1,tmp_Date),4,6)",
      "tmp_Day": "=substring(@(1,tmp_Date),6,8)",
      "tmp_Hours": "=substring(@(1,tmp_Time),0,2)",
      "tmp_Minutes": "=substring(@(1,tmp_Time),2,4)",
      "tmp_Seconds": "=substring(@(1,tmp_Time),4,6)",
      "timeStamp": "=concat(@(1,tmp_Year),'-',@(1,tmp_Month),'-',@(1,tmp_Day),'T',@(1,tmp_Hours),':',@(1,tmp_Minutes),':',@(1,tmp_Seconds),'Z')",
      "tmp_App_Year": "=substring(@(1,tmp_App),0,4)",
      "tmp_App_Month": "=substring(@(1,tmp_App),4,6)",
      "tmp_App_Day": "=substring(@(1,tmp_App),6,8)",
      "appointmentTime": "=concat(@(1,tmp_App_Year),'-',@(1,tmp_App_Month),'-',@(1,tmp_App_Day))",
      "tmp__Del_Year": "=substring(@(1,tmp_Del_Date),0,4)",
      "tmp_Del_Month": "=substring(@(1,tmp_Del_Date),4,6)",
      "tmp_Del_Day": "=substring(@(1,tmp_Del_Date),6,8)",
      "tmp_Del_Hours": "=substring(@(1,tmp_Del_Time),0,2)",
      "tmp_Del_Minutes": "=substring(@(1,tmp_Del_Time),2,4)",
      "tmp_Del_Seconds": "=substring(@(1,tmp_Del_Time),4,6)",
      "deliveryTime": "=concat(@(1,tmp__Del_Year),'-',@(1,tmp_Del_Month),'-',@(1,tmp_Del_Day),'T',@(1,tmp_Del_Hours),':',@(1,tmp_Del_Minutes),':',@(1,tmp_Del_Seconds),'Z')"
    }
  },
  {
    "operation": "remove",
    "spec": {
      "tmp_*": ""
    }
  }
]

当前转换输出 #

{
  "transformedPayload" : {
    "trackingInfo" : "1ZEA83550362028861",
    "events" : {
      "type" : "G",
      "statusCode" : "OR",
      "statusDescription" : "Origin Scan"
    },
    "address" : {
      "city" : "RANDALLSTOWN,",
      "state" : "MD",
      "postalCode" : "21133",
      "country" : "US"
    }
  },
  "timeStamp" : "2021-03-24T18:35:00Z",
  "appointmentTime" : "2022-05-25",
  "deliveryTime" : "2022-07-29T18:35:00Z"
}

目标输出结构 #

{
  "transformedPayload" : {
    "trackingInfo" : "1ZEA83550362028861",
    "events" : {
      "type" : "G",
      "statusCode" : "OR",
      "statusDescription" : "Origin Scan"
    },
    "address" : {
      "city" : "RANDALLSTOWN,",
      "state" : "MD",
      "postalCode" : "21133",
      "country" : "US"
    },
    "timeStamp" : "2021-03-24T18:35:00Z",
    "appointmentTime" : "2022-05-25",
    "deliveryTime" : "2022-07-29T18:35:00Z"
  }
}

修改后的Jolt转换规则 #

[
  {
    "operation": "shift",
    "spec": {
      "trackingNumber": "transformedPayload.trackingInfo",
      "localActivityDate": "tmp_Date",
      "localActivityTime": "tmp_Time",
      "scheduledDeliveryDate": "tmp_App",
      "actualDeliveryDate": "tmp_Del_Date",
      "actualdeliveryTime": "tmp_Del_Time",
      "activityStatus": {
        "type": "transformedPayload.events.type",
        "code": "transformedPayload.events.statusCode",
        "description": "transformedPayload.events.statusDescription"
      },
      "activityLocation": {
        "city": "transformedPayload.address.city",
        "stateProvince": "transformedPayload.address.state",
        "postalCode": "transformedPayload.address.postalCode",
        "country": "transformedPayload.address.country"
      }
    }
  },
  {
    "operation": "modify-default-beta",
    "spec": {
      "tmp_Year": "=substring(@(1,tmp_Date),0,4)",
      "tmp_Month": "=substring(@(1,tmp_Date),4,6)",
      "tmp_Day": "=substring(@(1,tmp_Date),6,8)",
      "tmp_Hours": "=substring(@(1,tmp_Time),0,2)",
      "tmp_Minutes": "=substring(@(1,tmp_Time),2,4)",
      "tmp_Seconds": "=substring(@(1,tmp_Time),4,6)",
      "transformedPayload.timeStamp": "=concat(@(1,tmp_Year),'-',@(1,tmp_Month),'-',@(1,tmp_Day),'T',@(1,tmp_Hours),':',@(1,tmp_Minutes),':',@(1,tmp_Seconds),'Z')",
      "tmp_App_Year": "=substring(@(1,tmp_App),0,4)",
      "tmp_App_Month": "=substring(@(1,tmp_App),4,6)",
      "tmp_App_Day": "=substring(@(1,tmp_App),6,8)",
      "transformedPayload.appointmentTime": "=concat(@(1,tmp_App_Year),'-',@(1,tmp_App_Month),'-',@(1,tmp_App_Day))",
      "tmp_Del_Year": "=substring(@(1,tmp_Del_Date),0,4)",
      "tmp_Del_Month": "=substring(@(1,tmp_Del_Date),4,6)",
      "tmp_Del_Day": "=substring(@(1,tmp_Del_Date),6,8)",
      "tmp_Del_Hours": "=substring(@(1,tmp_Del_Time),0,2)",
      "tmp_Del_Minutes": "=substring(@(1,tmp_Del_Time),2,4)",
      "tmp_Del_Seconds": "=substring(@(1,tmp_Del_Time),4,6)",
      "transformedPayload.deliveryTime": "=concat(@(1,tmp_Del_Year),'-',@(1,tmp_Del_Month),'-',@(1,tmp_Del_Day),'T',@(1,tmp_Del_Hours),':',@(1,tmp_Del_Minutes),':',@(1,tmp_Del_Seconds),'Z')"
    }
  },
  {
    "operation": "remove",
    "spec": {
      "tmp_*": ""
    }
  }
]

修改说明 #

1. 在modify-default-beta阶段，将原本生成在根节点的timeStamp、appointmentTime、deliveryTime字段，直接指定路径为transformedPayload.xxx，生成后字段会自动嵌套到目标节点内。
2. 修正了原规则中tmp__Del_Year的多余下划线问题，改为tmp_Del_Year，避免deliveryTime生成出错。

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