question Re: NiFi - JSON seperated date and time - make a timestamp and convert in Support Questions

NiFi - JSON seperated date and time - make a timestamp and convert

justenji — Thu, 13 Feb 2020 08:53:36 GMT

Hi all!

What I get:
JSON with an arbitrary number of entries beneath "table".

{ "head": { "dnr": "122" }, "table": [ { "zn": 1, "datum": "20200211", <=== yyyyMMdd CET "uhrzvon": "10:40" <=== HH:mm CET }, { "zn": 2, "datum": "20200211", "uhrzvon": "10:47" }, { "zn": 3, "datum": "20200212", "uhrzvon": "08:11" } ] }

What I need:
Each entry contains "datum" and "uhrzvon" (CET) which I need to join and convert to timestamp (UTC). Because of a later JOLT-Transformation, I thought I can change the original entry of "datum" to use this in JOLT.

{ "head": { "dnr": "122" }, "table": [ { "zn": 1, "datum": "2020-02-11 09:40:00.000", <=== yyyy-MM-dd HH:mm:ss.SSS UTC "uhrzvon": "10:40" }, { "zn": 2, "datum": "2020-02-11 09:47:00.000", "uhrzvon": "10:47" }, { "zn": 3, "datum": "2020-02-12 07:11:00.000", "uhrzvon": "08:11" } ] }

What I tried:

RecordProcessing without schema
I can format the date, but not the time (either it stays empty or the value ist wron). And I can't finde a way to bring this two values together.
UpdateRecord

brings this result

{ "head": { "dnr": "122" }, "table": [ { "zn": 1, "datum" : "2020-02-11 00:00:00.000", "uhrzvon" : "04:51:40.000", complete wrong },

ExtractText for whole JSON-Content to an attribute and tried to work with new possibilities for JSON of expression language
With jsonPathPut I can't add key/value to the needed path ($.table). It only works on top level.
UpdateAttribute
JOLT
I didn't find a way to join and convert these two information here.

Groovy-Script
I have no clue of it. I saw some discussion around this but can't find a fitting example.

Any help is very wellcome.
NiFi 1.11.1

Re: NiFi - JSON seperated date and time - make a timestamp and convert

PVVK — Sat, 19 Sep 2020 16:32:04 GMT

Hi @justenji !

Please find the attached groovy code. Use it in ExecuteGroovyScript processor.

import java.nio.charset.StandardCharsets
import groovy.json.JsonSlurper
import groovy.json.JsonOutput

flowFile = session.get()
if (!flowFile) return

try {
 def jsonSlurper = new JsonSlurper();
 def jsonOutput = new JsonOutput();

 def input = flowFile.read().withStream {
  data -> jsonSlurper.parse(data)
 }

 def tables = input.table;

 for(int i=0;i<tables.size();i++){
  def pattern = 'yyyyMMdd';
  def datum = tables[i].datum;

  if(tables[i].containsKey('uhrzvon')){
   pattern = pattern + 'HH:mm';
   datum = datum + tables[i].uhrzvon;
  }
  tables[i].datum = new Date().parse(pattern,datum,TimeZone.getTimeZone('GMT+0200')).format('yyyy-MM-dd HH:mm:ss.SSSZ',TimeZone.getTimeZone('GMT'));
 }

 input.table = tables

 flowFile = session.write(flowFile, {
  outputStream -> outputStream.write(jsonOutput.toJson(input).toString().getBytes(StandardCharsets.UTF_8))
 }as OutputStreamCallback)

 session.transfer(flowFile, REL_SUCCESS);
} catch (e) {
 log.error('Error Occured,{}', e)
 session.transfer(flowFile, REL_FAILURE)
}

Re: NiFi - JSON seperated date and time - make a timestamp and convert

justenji — Mon, 21 Sep 2020 07:15:39 GMT

@PVVK

In the meantime the problem was solved on an other way.
But thank you for the script I will test it and I can learn a lot of it!

Re: NiFi - JSON seperated date and time - make a timestamp and convert

justenji — Mon, 21 Sep 2020 07:39:05 GMT

@PVVK

It works perfect! Thank you very much.