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

[#1]

I have a json
 {
  "REQUESTID" : "379",
  "REQUESTTYPE" : "V",
  "REQUESTCONTEXT" : "B2B",
  "CARDNUMBER" : [ "5537290000000511", "5537290000000522" ],
  "EXPIRYDATE" : [ "08/20", "09/21" ],
  "EMAIL" : [ "[email protected]", "[email protected]" ],
  "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)

[#2 · Accepted solution]

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

"...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" : "[email protected],[email protected]",
  "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