Search code examples
jsonapache-nifijolt

Convert Date format in a JSON using JOLT

I have a working JOLT except my date part is wrong. I need the date to be in proper format as in the output. Need help to edit my existing jolt

Input JSON :

{
  "Timestamp_UTC": "28-Aug-2023 12:01:00",
  "Control area - Redox 1_Red Sensor": 439.1354,
  "Measure area - Redox 2_Blue Sensor": 455.73474,
  "Voltage": 13.48,
  "Measure area - PH_1_S/N: 2213805": -0.096,
  "Control area - PH_2_S/N: 2213826_CONTROL": 6.171
}

Output JSON :

{
  "Date": "28-08-2023 12:01:00",
  "data": [
    {
      "code": "REDOX1",
      "value": 439.1354
    },
    {
      "code": "REDOX2",
      "value": 455.73474
    },
    {
      "code": "PH1",
      "value": -0.096
    },
    {
      "code": "A1-PH2",
      "value": 6.171
    }
  ],
  "stationname": "ftp_device_01"
}

Current JOLT :

[
  {
    "operation": "shift",
    "spec": {
      "Timestamp_UTC": "Date",
      "Control area - Redox 1_Red Sensor": "data.REDOX1",
      "Measure area - Redox 2_Blue Sensor": "data.REDOX2",
      "Measure area - PH_1_S/N: 2213805": "data.PH1",
      "Control area - PH_2_S/N: 2213826_CONTROL": "data.A1-PH2"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "Date": "&",
      "data": {
        "*": {
          "$": "&2[#2].code",
          "@": "&2[#2].value"
        }
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "data": {
        "*": {
          "value": [
            "=notNull",
            0
          ]
        }
      },
      "DtLen": "=size(@(1,Date))",
      "DtDatePart": "=substring(@(1,Date),0,10)",
      "DtDatePartSplit": "=split('/',@(1,DtDatePart))",
      "DtTimePart": "=substring(@(1,Date),10,@(1,DtLen))",
      "DtTimePartTrim": "=trim(@(1,DtTimePart))",
      "Date": "=concat(@(1,DtDatePartSplit[2]),'-',@(1,DtDatePartSplit[1]),'-',@(1,DtDatePartSplit[0]),' ',@(1,DtTimePartTrim))"
    }
  },
  {
    "operation": "remove",
    "spec": {
      "Dt*": ""
    }
  },
  {
    "operation": "default",
    "spec": {
      "stationname": "ftp_device_01"
    }
  }
]
Solution

  • As you've been working on NiFi, you can use expression language along with some more processors rather than manipulating the Date's value within a Jolt specification.

    You can add

    • GetFile processor : Assuming the input JSON value is in a file

    • SplitJSON processor : To individually get each JSON value as attributes by using

      JsonPath Expression is $.*

    • EvaluateJSONPath processor : To create an attribute called Timestamp_UTC by adding a property

      with name Timestamp_UTC vs. with value $.Timestamp_UTC as

      the Destination property's value is flowfile-attribute

    • UpdateAttribute processor : To perform datetime conversion by adding a new

      property Timestamp_UTC with value

      ${Timestamp_UTC:toDate('dd-MMM-yyyy HH:mm:ss'):format('yyyy-MM-dd HH:mm:ss')}

    • JoltTransformJSON processor with the following specification :

      [
  {
    "operation": "shift",
    "spec": {
      "Timestamp_UTC": "Date",
      "*": "data.&" // all elements other than "Timestamp_UTC" 
    }
  },
  {
    "operation": "shift",
    "spec": {
      "Date": "&",
      "data": {
        "*": {
          "$": "&2[#2].code",
          "@": "&2[#2].value"
        }
      },
      "#ftp_device_01": "stationname" // to add a fixed valued attribute
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "Date": "${Timestamp_UTC}",
      "data": {
        "*": {
          "value": [
            "=notNull",
            0
          ]
        }
      }
    }
  }
]

    The following image shows the complete NiFi flow :

    enter image description here

    Yet, you can do all in a JoltTransformJSON processor as an alternative but need to hardcode all the month numbers vs. their abbreviated literals along with the following transformation spec :

    [
  {
    "operation": "shift",
    "spec": {
      "Timestamp_UTC": "Date",
      "*": "data.&" // all elements other than "Timestamp_UTC" 
    }
  },
  {
    "operation": "shift",
    "spec": {
      "Date": "&",
      "data": {
        "*": {
          "$": "&2[#2].code",
          "@": "&2[#2].value"
        }
      },
      "#ftp_device_01": "stationname" // to add a fixed valued attribute
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "Date": "=split('-',@(1,&))",
      "data": {
        "*": {
          "value": [
            "=notNull",
            0
          ]
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "Date": {
        "1": {
          "Jan": { "#01": "&3" },
          "Feb": { "#02": "&3" },
          "Mar": { "#03": "&3" },
          "Apr": { "#04": "&3" },
          "May": { "#05": "&3" },
          "Jun": { "#06": "&3" },
          "Jul": { "#07": "&3" },
          "Aug": { "#08": "&3" },
          "Sep": { "#09": "&3" },
          "Oct": { "#10": "&3" },
          "Nov": { "#11": "&3" },
          "Dec": { "#12": "&3" }
        },
        "*": "&1"
      },
      "*": "&"
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "Date": "=join('-',@(1,&))"
    }
  }
]