Transform a nested JSON into SQL and insert it in a DB

[#1]

Hey guys,

I am currently facing an issue related to the nested JSON-Date I am trying to process. My current NiFi flow is: GetFile => SplitJson => ConvertJSONtoSQL => PutSQL. Everything seems to be quite fine, but it seems that there are Problems with handling nested JSONs.

My quick Research brought up two possibilities: using JoltTransformJSON or an ExecuteScript. I'd rather prefer the first one. But I do not know, actually, how to achieve the Right and desired Transformation of the nested JSON into a flat one.

Hope you can give me some hints.

Regards,

Julez

[#2 · Accepted solution]

Another possibility is FlattenJson, but your destination table would have to have the same naming convention (based on your choice of the Separator property in FlattenJson) in order for the INSERT to be successful.

JoltTransformJSON won't automatically flatten things, you'd need to know your schema in order to write the spec, but then you'd have more control over what the output field names would be. ExecuteScript could be a last resort; Groovy has very good JSON-handling capabilities.