JOLT转换：将嵌套数组指定字段重命名为timestamp且保留其他内容

初始输入JSON #

{"trackingNumber": "1ZEA83550362028861","localActivityDate": "20210324","localActivityTime": "183500","scheduledDeliveryDate": "20210324","actualDeliveryDate": "20210324","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": "modify-overwrite-beta","spec": {"tsY": "=substring(@(1,localActivityDate),0,4)","tsM": "=substring(@(1,localActivityDate),4,6)","tsD": "=substring(@(1,localActivityDate),6,8)","tsH": "=substring(@(1,localActivityTime),0,2)","tsMi": "=substring(@(1,localActivityTime),2,4)","tsS": "=substring(@(1,localActivityTime),4,6)","timeStamp": "=concat(@(1,tsY),'-',@(1,tsM),'-',@(1,tsD),'T',@(1,tsH),':',@(1,tsMi),':',@(1,tsS),'Z')","aTY": "=substring(@(1,scheduledDeliveryDate),0,4)","aTM": "=substring(@(1,scheduledDeliveryDate),4,6)","aTD": "=substring(@(1,scheduledDeliveryDate),6,8)","appointmentTime": "=concat(@(1,aTY),'-',@(1,aTM),'-',@(1,aTD))","dTY": "=substring(@(1,actualDeliveryDate),0,4)","dTM": "=substring(@(1,actualDeliveryDate),4,6)","dTD": "=substring(@(1,actualDeliveryDate),6,8)","dTH": "=substring(@(1,actualdeliveryTime),0,2)","dTMi": "=substring(@(1,actualdeliveryTime),2,4)","dTS": "=substring(@(1,actualdeliveryTime),4,6)","deliveryTime": "=concat(@(1,dTY),'-',@(1,dTM),'-',@(1,dTD),'T',@(1,dTH),':',@(1,dTMi),':',@(1,dTS),'Z')}},{"operation": "shift","spec": {"*Number": "transformedPayload.&(0,1)Info","activityStatus": {"*": "transformedPayload.events.&"},"activityLocation": {"*": "transformedPayload.address.&"},"timeStamp": "transformedPayload.events[0].&","appointmentTime": "transformedPayload.events[1].&","deliveryTime": "transformedPayload.events[2].&","activityStatus": {"type": "transformedPayload.events[0].type","code": "transformedPayload.events[0].statusCode","description": "transformedPayload.events[0].statusDescription"},"activityLocation": {"city": "transformedPayload.address.city","stateProvince": "transformedPayload.address.state","postalCode": "transformedPayload.address.postalCode","country": "transformedPayload.address.country"}}},{"operation": "modify-default-beta","spec": {"metaData": {"domain": "LTL","eventType": "statusUpdate","version": "v1"},"transformedPayload": {"events": {"[1]": {"statusCode": "AB","statusDescription": "Delivery Scheduled"},"[2]": {"statusCode": "D1","statusDescription": "Delivered"}}}}]

当前转换结果 #

{"transformedPayload": {"events": [{"type": "G","statusCode": "OR","statusDescription": "Origin Scan","timeStamp": "2021-03-24T18:35:00Z"},{"appointmentTime": "2021-03-24","statusCode": "AB","statusDescription": "Delivery Scheduled"},{"deliveryTime": "2021-03-24T18:35:00Z","statusCode": "D1","statusDescription": "Delivered"}],"address": {"city": "RANDALLSTOWN,","state": "MD","postalCode": "21133","country": "US"},"trackingInfo": "1ZEA83550362028861"},"metaData": {"domain": "LTL","eventType": "statusUpdate","version": "v1"}}

需求 #

将events数组第1、2索引中的appointmentTime和deliveryTime字段重命名为timestamp，得到如下目标结果：

{"transformedPayload": {"events": [{"type": "G","statusCode": "OR","statusDescription": "Origin Scan","timeStamp": "2021-03-24T18:35:00Z"},{"timestamp": "2021-03-24","statusCode": "AB","statusDescription": "Delivery Scheduled"},{"timestamp": "2021-03-24T18:35:00Z","statusCode": "D1","statusDescription": "Delivered"}],"address": {"city": "RANDALLSTOWN,","state": "MD","postalCode": "21133","country": "US"},"trackingInfo": "1ZEA83550362028861"},"metaData": {"domain": "LTL","eventType": "statusUpdate","version": "v1"}}

解决方案 #

问题出在shift操作中使用&保留原字段名，只需直接指定目标字段名即可完成重命名。调整后的JOLT规则如下：

[{"operation": "modify-overwrite-beta","spec": {"tsY": "=substring(@(1,localActivityDate),0,4)","tsM": "=substring(@(1,localActivityDate),4,6)","tsD": "=substring(@(1,localActivityDate),6,8)","tsH": "=substring(@(1,localActivityTime),0,2)","tsMi": "=substring(@(1,localActivityTime),2,4)","tsS": "=substring(@(1,localActivityTime),4,6)","timeStamp": "=concat(@(1,tsY),'-',@(1,tsM),'-',@(1,tsD),'T',@(1,tsH),':',@(1,tsMi),':',@(1,tsS),'Z')","aTY": "=substring(@(1,scheduledDeliveryDate),0,4)","aTM": "=substring(@(1,scheduledDeliveryDate),4,6)","aTD": "=substring(@(1,scheduledDeliveryDate),6,8)","appointmentTime": "=concat(@(1,aTY),'-',@(1,aTM),'-',@(1,aTD))","dTY": "=substring(@(1,actualDeliveryDate),0,4)","dTM": "=substring(@(1,actualDeliveryDate),4,6)","dTD": "=substring(@(1,actualDeliveryDate),6,8)","dTH": "=substring(@(1,actualdeliveryTime),0,2)","dTMi": "=substring(@(1,actualdeliveryTime),2,4)","dTS": "=substring(@(1,actualdeliveryTime),4,6)","deliveryTime": "=concat(@(1,dTY),'-',@(1,dTM),'-',@(1,dTD),'T',@(1,dTH),':',@(1,dTMi),':',@(1,dTS),'Z')}},{"operation": "shift","spec": {"*Number": "transformedPayload.&(0,1)Info","timeStamp": "transformedPayload.events[0].&","appointmentTime": "transformedPayload.events[1].timestamp","deliveryTime": "transformedPayload.events[2].timestamp","activityStatus": {"type": "transformedPayload.events[0].type","code": "transformedPayload.events[0].statusCode","description": "transformedPayload.events[0].statusDescription"},"activityLocation": {"city": "transformedPayload.address.city","stateProvince": "transformedPayload.address.state","postalCode": "transformedPayload.address.postalCode","country": "transformedPayload.address.country"}}},{"operation": "modify-default-beta","spec": {"metaData": {"domain": "LTL","eventType": "statusUpdate","version": "v1"},"transformedPayload": {"events": {"[1]": {"statusCode": "AB","statusDescription": "Delivery Scheduled"},"[2]": {"statusCode": "D1","statusDescription": "Delivered"}}}}]

关键调整点 #

在shift操作的spec中，将：

• "appointmentTime": "transformedPayload.events[1].&" 修改为 "appointmentTime": "transformedPayload.events[1].timestamp"
• "deliveryTime": "transformedPayload.events[2].&" 修改为 "deliveryTime": "transformedPayload.events[2].timestamp"

同时移除了shift操作中重复的activityStatus和activityLocation映射（原规则中重复定义会导致冗余，保留明确的字段映射即可）。

调整后运行转换，即可得到目标结果。

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