Skip to main content
kaluman
kaluman
Silver ⭐️⭐️
January 12, 2021
Solved

Unmatched JSON keyname and table columnname

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.

Faerballert
FaerballertAnswer
Gold ⭐️
January 13, 2021

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

 

View in thread

4 replies

Faerballert
FaerballertAnswer
Gold ⭐️
January 13, 2021

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

 

Translate
kaluman
kalumanAuthor
Silver ⭐️⭐️
January 13, 2021

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

 

kaluman_0-1610542427419.png

 

Translate
Faerballert
Faerballert
Gold ⭐️
January 13, 2021

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

Translate
Terms & ConditionsAccessibility statement