question Re: Unmatched JSON keyname and table columnname in Support Questions

Unmatched JSON keyname and table columnname

kaluman — Tue, 12 Jan 2021 23:58:20 GMT

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.

Re: Unmatched JSON keyname and table columnname

Faerballert — Wed, 13 Jan 2021 12:15:24 GMT

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", "*": "&" } } ]

Re: Unmatched JSON keyname and table columnname

kaluman — Wed, 13 Jan 2021 12:57:40 GMT

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

Re: Unmatched JSON keyname and table columnname

Faerballert — Wed, 13 Jan 2021 14:09:25 GMT

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

Re: Unmatched JSON keyname and table columnname

kaluman — Wed, 13 Jan 2021 15:51:45 GMT

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