Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution
Highlighted

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

New Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

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.

1 REPLY 1

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

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.