Search code examples
mysqlapache-nifi

ConvertJSONToSQL Json does not have a value for updateKey column

I am new to apache nifi. I have written a sync flow from source database to destination database. Both databases are of mysql as of now.

I have tried by setting Transfer field Namesvalue to true but still got the same error

Flow :

enter image description here

ConvertJSONToSQL Properties: enter image description here

JSON : enter image description here

Source table : enter image description here

Destination table: enter image description here

EvaluateJsonPath properties enter image description here

Solution

  • Instead of using EvaluateJsonPath, use JoltTransformJSON with the following Jolt Specification:

    [
  {
    "operation": "shift",
    "spec": {
      "columns": {
        "*": {
          "@(value)": "[#1].@(1,name)"
        }
      }
    }
  }
]

    which will transform the JSON received from CaptureChangeMySQL to:

    [ {
  "id" : 1,
  "FILENAME" : "27filename",
  "object_name" : "date",
  "handback_path" : "abc12300000pleasepleasepphhhhhhhllgodpleasepleasepleasepp.xlsx",
  "rejected_count" : 100000,
  "success_count" : 100,
  "total_rows" : 30,
  "creation_date" : 123456789,
  "status" : "new_status"
} ]

    This can then be passed on to ConvertJSONToSQL. I have tested this and it works. Whether it is the best way to do it is another matter.

    NiFi Flow