Nifi ConvertJsonToSQL - Save json object as a db column value

Question

The description of ConvertJsonToSQL says that "If a field maps to a JSON object, that JSON object will be interpreted as Text". So I'm trying to save a json text "{ "dateEndM" : "2021-12-14 00:00:00.0", "dateEndY" : "2022-01-11 00:00:00.0" }" into the db field "InboundData". Input:

{
  "Code" : "27037834",
  "ProductID" : "2",
  "CalculationStatusId" : "1",
  "SystemCode" : "aCRM",
  "InboundData" : {
    "dateEndM" : "2021-12-14 00:00:00.0",
    "dateEndY" : "2022-01-11 00:00:00.0"
  }
}

Output sql "INSERT INTO Calculation (Code, ProductId, CalculationStatusId, SystemCode, InboundData) VALUES (?, ?, ?, ?, ?)" values:

sql.args.1.type
-9
sql.args.1.value
27037834
sql.args.2.type
4
sql.args.2.value
2
sql.args.3.type
4
sql.args.3.value
1
sql.args.4.type
-9
sql.args.4.value
aCRM
sql.args.5.type
-9
sql.args.5.value
Empty string set

Why sql.args.5.value of InboundData is empty? How is it possible to save json object as text in db? Thanks

Answer 1

There is an unresolved issue for processor ConvertJsonToSQL NIFI-4359

Possible workoaround with the EvaluateJsonPath -> ConvertJsonToSQL -> UpdateAttribute:

EvaluateJsonPath

• Destination: flowfile-attribute
• Return Type: json
• InboundData: $.InboundData

UpdateAttribute

• sql.args.5.value: ${InboundData}