Skip to main content
Anderosn
Anderosn
Silver ⭐️⭐️⭐️
August 1, 2023
Solved

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

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)

SAMSAL
SAMSALAnswer
Platinum ⭐️⭐️⭐️
August 2, 2023

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

 

 

 

View in thread

1 reply

SAMSAL
SAMSALAnswer
Platinum ⭐️⭐️⭐️
August 2, 2023

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

 

 

 

Translate
    Terms & ConditionsAccessibility statement