question Re: how to replace empty string with null in nested json? in Support Questions

how to replace empty string with null in nested json?

MWM — Thu, 28 Dec 2023 14:13:13 GMT

Hi guys! I have json like that:

{
"id" : "20",
"user" : {
"name" : "John",
"date" : ""
}
}

I transform this date to Long using nifi langugage ${date:isEmpty():ifElse('',${date:toDate('yyyy-MM-dd HH:mm:ss'):toNumber()})} and after this i do in jolttrasformation something like

[
{
"operation": "shift",
"spec": {
"idTransakcji" : "id",
"date": "user.date",
}
},
{
"operation": "modify-overwrite-beta",
"spec": {
"user": {
"date": "=toLong(@(1,date))"
}
}
}

]

and it works. But I have problem beacause when I have emptyString in this date i need to transfer it in null(not null in String) and i dont know how to do it using JoltTransformation or something else so as not to lose what I already have and have null only when there is an empty string.

Re: how to replace empty string with null in nested json?

SAMSAL — Thu, 28 Dec 2023 20:16:37 GMT

Hi @MWM ,

replacing empty string with null is kind of tricky thing with jolt. Unfortunately there is no direct way that I know of but hopefully my comments will make it easy for you to understand.

Based on your spec I assume you have the following input:

{ "idTransakcji": "123", "date": "", "name": "sam" }

The jolt spec would be like this ( comments start with //):

[ { "operation": "shift", "spec": { "idTransakcji": "id", "date": { // Parse date value // if its empty string "" "": { //remove the date from the output "": "user.date" }, //For any values other than "" "*": { //assign the actual value ($) to user.date "$": "user.date" } }, // Assign other fields like name under user "*": "user.&" } }, { "operation": "modify-overwrite-beta", "spec": { "user": { // if the date is found it means that we // should have a value, therefore convert to long "date": "=toLong(@(1,date))" } } }, { "operation": "default", "spec": { "user": { // if the date is not found then set to null // default transformation only works when // the key doesnt exist. "date": null } } } ]

You can try the spec here : https://jolt-demo.appspot.com/#inception

If that helps please accept solution.

Thanks

Re: how to replace empty string with null in nested json?

joseomjr — Thu, 28 Dec 2023 21:02:13 GMT

ExecuteGroovyScript alternative with this input

{ "idTransakcji": "123", "date": "", "name": "sam" }

import groovy.json.JsonOutput import groovy.json.JsonSlurper import java.nio.charset.StandardCharsets JsonSlurper jsonSlurper = new JsonSlurper() JsonOutput jsonOutput = new JsonOutput() FlowFile flowFile = session.get() if(!flowFile) return flowFile = session.write(flowFile, { inputStream, outputStream -> Map data = jsonSlurper.parse(inputStream) data = [ "id": data.idTransakcji, "user": [ "date": data.date?.isNumber() ? Long.parseLong(data.date) : null, "name": data.name ] ] outputStream.write(jsonOutput.toJson(data).getBytes(StandardCharsets.UTF_8)) } as StreamCallback) session.transfer(flowFile, REL_SUCCESS)

Re: how to replace empty string with null in nested json?

MWM — Fri, 29 Dec 2023 13:25:07 GMT

Hi @SAMSAL your solution is workink, thank you so much!