Need Date Field Transformation in JSON Using JOLT (Convert Date "1118083350" into Date : 18/11/2020 and Time: 053350)

user_14498580 0
Viewed 1526

Below is my Input

[
  {
    "corrId": "ed1e30",
    "payloadFormat": "CASH",
    "payload": {
      "DateTime": "1118083350"         
    }
  },
  {
    "correlationId": "ed1e30c",
    "payloadFormat": "CREDIT",
    "payload": {
     "DateTime": "1119092545"
    }
  }
]

Expected output should be

[
  {
    "correlationId": "ed1e30",
    "payloadFormat": "CASH",
    "Date": "18/11/2020",
    "Time": "083350"
          
    
  },
  {
    "correlationId": "ed1e30c",
    "payloadFormat": "CREDIT",
    "Date": "19/11/2020",
    "Time": "092545"
    
      
    }
  }
]
jolt json
2 Answers

Jolt doesn't have Date utilities, but it can be done by modify-default-beta operations,

[
  {
    "operation": "modify-default-beta",
    "spec": {
      "*": {
        "Time": "=substring(@(1,payload.DateTime),4,10)",
        "month": "=substring(@(1,payload.DateTime),0,2)",
        "day": "=substring(@(1,payload.DateTime),2,4)"
      }
    }
  },
  {
    "operation": "modify-default-beta",
    "spec": {
      "*": {
        "Date": "=concat(@(1,day),'/',@(1,month),'/2020')"
      }
    }
  }, {
    "operation": "remove",
    "spec": {
      "*": {
        "payload": "",
        "month": "",
        "day": "",
        "DateTime": ""
      }
    }
  }
]
user_61129070

I have used below JOLT spec to convert the date.

[{
    "operation": "shift",
        "spec": {
          "*": {
            "@": "&",
            "payload": {
              "DateTime": "&2.payload.TMPDE"
            }
          }
        }
      }, {
        "operation": "modify-default-beta",
        "spec": {
          "*": {
            "payload": {
              "DateM": "=substring(@(1,TMPDE), 0, 2)",
              "DateD": "=substring(@(1,TMPDE), 2, 4)",
              "Time": "=substring(@(1,TMPDE), 4, 10)",
              "TrnDate": "=join('/',@(1,DateD),@(1,DateM),2020)"
            }
          }
        }
      },
    
      {
        "operation": "shift",
        "spec": {
          "*": {
            "correlationId": "[&1].COR_REL_ID",
            "payloadFormat": "[&1].payloadFormat",
            "payload": {
              "@TrnDate": "[#3].Date",
              "@Time": "[#3].Time"
            }
          }
        }
    }
    ]
user_144985800
Related