NIfi ConvertJsonToSQL fails when Json has a array value and the column that needs to be mapped is varchar

Anderosn — Tue, 01 Aug 2023 19:38:18 GMT

I have a json
{
"REQUESTID" : "379",
"REQUESTTYPE" : "V",
"REQUESTCONTEXT" : "B2B",
"CARDNUMBER" : [ "5537290000000511", "5537290000000522" ],
"EXPIRYDATE" : [ "08/20", "09/21" ],
"EMAIL" : [ "John.Jones123@abcmail.com", "Jason.456@gmail.com" ],
"PHONENUMBER" : "11234565555"
}
CARDNUMBER, EXPIRYDATE and EMAIL are varchar in sql table
when Json is passed to covertToSQL processor, I am seeing empty string in the attribute for these columns in prepared statement (Insert)

Re: NIfi ConvertJsonToSQL fails when Json has a array value and the column that needs to be mapped is varchar

SAMSAL — Wed, 02 Aug 2023 16:35:28 GMT

Hi @Anderosn ,

The ConvertJsonToSQL according to the documentation will map fields only with simple type:

https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.6.0/org.apache.nifi.processors.standard.ConvertJSONToSQL/index.html

"...The incoming FlowFile is expected to be "flat" JSON message, meaning that it consists of a single JSON element and each field maps to a simple type..."

If you dont care about storing array fields as is, then you can use JoltTransformJson to convert array fields to concatenated string. The jolt spec to do this can be as the following:

[ { "operation": "modify-overwrite-beta", "spec": { "CARDNUMBER": "=join(',',@(1,CARDNUMBER))", "EXPIRYDATE": "=join(',',@(1,EXPIRYDATE))", "EMAIL": "=join(',',@(1,EMAIL))" } } ]

This will produce the following output:

{ "REQUESTID" : "379", "REQUESTTYPE" : "V", "REQUESTCONTEXT" : "B2B", "CARDNUMBER" : "5537290000000511,5537290000000522", "EXPIRYDATE" : "08/20,09/21", "EMAIL" : "John.Jones123@abcmail.com,Jason.456@gmail.com", "PHONENUMBER" : "11234565555" }

Which can be converted to SQL using the ConvertJsonToSQL and all fields will be populated.

Hope that works.

If that helps please accept solution.

Thanks

question Re: NIfi ConvertJsonToSQL fails when Json has a array value and the column that needs to be mapped is varchar in Support Questions

NIfi ConvertJsonToSQL fails when Json has a array value and the column that needs to be mapped is varchar

Re: NIfi ConvertJsonToSQL fails when Json has a array value and the column that needs to be mapped is varchar