It's a tradeoff between flexibility and structure. You can define the nested structure as a string in the schema and it can contain any json structured data but you NiFi won't be able to parse it. If your structure is the same but the data types can be different you might check the UNION type in Avro https://stackoverflow.com/a/50177318 ________________________________ From: Aaron Rich <aaron.r...@gmail.com> Sent: Thursday, November 23, 2023 7:15 To: users@nifi.apache.org <users@nifi.apache.org> Subject: Re: RecordReader/Writer with nested json resulting in MapRecord[] statement
Thanks Lehel! Follow up question - since I'm focusing on the could event wrapper, is there a way to be able to define the data nested record generically so that it can work for any structure in the data field? I was hoping by calling it a string that would let me do it but obviously not. I could end up with 100+ different types in the data record and was hoping I could just drop any of them into the database as long as they are JSON to match the column type. I really don't want to have to make each record schema if I can get a generic "JSON Record" definition that might work (hopefully this makes sense). Thank you so much for your help! -Aaron On Wed, Nov 22, 2023, 2:51 AM Lehel Boér <lehe...@hotmail.com<mailto:lehe...@hotmail.com>> wrote: Hi Aaron, You need to define the schema for the "data" field as a nested record. { "name": "data", "type": { "type": "record", "name": "nestedData", "fields": [ {"name": "message_type", "type": "string"}, {"name": "message_time", "type": "string"}, {"name": "file_ingest_time", "type": "string"} ] } } Best Regards, Lehel ________________________________ From: Aaron Rich <aaron.r...@gmail.com<mailto:aaron.r...@gmail.com>> Sent: Wednesday, November 22, 2023 7:30 To: d...@nifi.apache.org<mailto:d...@nifi.apache.org> <d...@nifi.apache.org<mailto:d...@nifi.apache.org>>; users@nifi.apache.org<mailto:users@nifi.apache.org> <users@nifi.apache.org<mailto:users@nifi.apache.org>> Subject: RecordReader/Writer with nested json resulting in MapRecord[] statement Hello, I'm trying to use the record writer/writers to take a json defined event and write it to postgres database. The event follows the CloudEvent definition and has a data element that is JSON structured: { "data" : { "message_type" : "V5", "message_time" : "1614597071000", "file_ingest_time": "1682018983320" }, "spec_version" : "1.0.2", "subject" : "1969", "data_schema" : "URI://something", "id" : "80e12364-831e-446a-a260-86bccc469f25", "source" : "service", "time" : "2023-11-22T00:03:03Z", "type" : "V5", "data_content_type" : "application/json" } I'm trying to write into a postgres db table that has each field as a column and data as a JSON type column. When I try to write the the database with PutDatabaseRecord, I'm getting MapRecord sting that is trying to be written into the data column: MapRecord[{message_type=V5, message_time=1614597071000,file_ingest_time=1682018983320}] I _think_ the issue is with the schema I'm defining: { "type": "record", "name": "eventSchema0", "fields" : [ {"name":"data", "type": "string"}, {"name":"spec_version", "type": "string"}, {"name":"subject", "type": "string"}, {"name":"data_schema", "type": "string"}, {"name":"id", "type": "string"}, {"name":"source", "type": "string"}, {"name":"time", "type": "string"}, {"name":"type", "type": "string"}, {"name":"data_content_type", "type": "string"} ] } What do I need to change so the JSON structure in the "data" field can be properly written using a record based processor? Thanks for the help. -Aaron
