Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Unmatched JSON keyname and table columnname

avatar
New Contributor

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.

1 ACCEPTED SOLUTION

avatar
Expert Contributor

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 solution in original post

4 REPLIES 4

avatar
Expert Contributor

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

 

avatar
New Contributor

@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

 

avatar
Expert Contributor

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

avatar
New Contributor

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