Unmatched JSON keyname and table columnname

[#1]

I am new to NiFi, and I am trying to put some data into a postgresql database. I am trying to do it with the ConvertJSONtoSQL processor, unfortunately the JSON keyname and the columename of the table having not the same names (for example keyname = ServiceGroup and columnname = service_group).

How can I map the key- and the columnname, so I can store the data in the tablename.

[#2 · Accepted solution]

To change the key names of your JSON you can transform it with JOLT.

The Processor name calls JoltTransformJSON

In the property you can "Jolt Specification" you can insert following code, that would be change your key from ServiceGroup to service_group and afterwards you can send the flowfile to the sql processor..

[
  {
    "operation": "shift",
    "spec": {
      "ServiceGroup": "service_group",
      "*": "&"
    }
  }
]

[#3]

@Faerballert Thank you very much. This is what I am asking for 😄

But I am having still the problem that the output queue of my ConvertJSONtoSQL, is fillled with the question mark "?".

Am I still missing something?

I thought it would automatically map the json field to the postgresql column and fill the VALUE statement with the values from the json.

[#4]

You can use the "PutSQL" Processor to read your content of the sql output and it will be stored in your database.

Or maybe instead of ConvertJsonToSQL, you can try it directly with "PutDatabaseRecord" Processor

[#5]

@FaerballertThanks again. It turned out that I just should ignore the question marks in the SQL statement. Everything works for now.