Skip to main content
justenji
justenji
Gold ⭐️⭐️
February 13, 2020
Solved

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

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
    image.png

         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
    image.png
  • 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

PVVK
PVVKAnswer
Gold ⭐️
September 19, 2020

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)
}

View in thread

3 replies

PVVK
PVVKAnswer
Gold ⭐️
September 19, 2020

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)
}

Translate
justenji
justenjiAuthor
Gold ⭐️⭐️
September 21, 2020

@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!

Translate
justenji
justenjiAuthor
Gold ⭐️⭐️
September 21, 2020

@PVVK 

It works perfect! Thank you very much.

Translate
Terms & ConditionsAccessibility statement