基于JOLT规范转换日期为YYYY-MM-DD-hh:mm:ss格式的技术问询

Alright, let's figure out how to use JOLT to replace those invalid transactionDate values with the required error message. Here's a complete, step-by-step solution:

Input JSON #

[ 
  { 
    "correlationId": "12345", 
    "payloadFormat": "Money", 
    "payload": { 
      "transactionDate": "2018010118060", 
      "name": "Dallas" 
    } 
  }, 
  { 
    "correlationId": "67895", 
    "payloadFormat": "cash", 
    "payload": { 
      "transactionDate": "2018010128040", 
      "name": "Texas" 
    } 
  } 
]

Expected Output JSON #

[ 
  { 
    "correlationId": "12345", 
    "payloadFormat": "Money", 
    "payload": { 
      "transactionDate": "date should be in yyyy-MM-dd HH:mm:ss", 
      "name": "Dallas" 
    } 
  }, 
  { 
    "correlationId": "67895", 
    "payloadFormat": "cash", 
    "payload": { 
      "transactionDate": "date should be in yyyy-MM-dd HH:mm:ss", 
      "name": "Texas" 
    } 
  } 
]

JOLT Specification #

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "correlationId": "[&1].correlationId",
        "payloadFormat": "[&1].payloadFormat",
        "payload": {
          "name": "[&2].payload.name",
          "transactionDate": "[&2].payload.transactionDate"
        }
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "payload": {
          "transactionDate": "=ifElse(or(not(equals(length(@(1,transactionDate)),14)), greaterThan(substring(@(1,transactionDate),8,2),23)), 'date should be in yyyy-MM-dd HH:mm:ss', @(1,transactionDate))"
        }
      }
    }
  }
]

How This Works #

Let's break down the spec step by step:

• Shift Operation: This first step copies over all existing fields from the input to the output exactly as they are. We preserve correlationId, payloadFormat, payload.name, and keep the original transactionDate intact so we can validate it next.
• Modify-Overwrite-Beta Operation: This is where we handle invalid date detection and replacement:
  • We use JOLT's ifElse() function paired with or() to check two common invalid date conditions:
    1. Length Check: A valid yyyyMMddHHmmss date string must be 14 characters long. If the input transactionDate isn't, it's marked invalid.
    2. Hour Range Check: We extract the hour portion (characters 8-9 in the string) and check if it's greater than 23 (since valid hours range from 00 to 23).
  • If either condition is true, we overwrite transactionDate with the specified error message. If both conditions are false, we leave the original date as-is (you can extend this with additional checks like valid month/day ranges if you need to cover more edge cases).

内容的提问来源于stack exchange，提问作者Rajesh Kumar singh