How to persist a very big JSON to a CLOB in Oracle with Apahe Nifi

[#1]

I have a process which creates a big JSON (the content is between 5k to 400k), (and has embedded objects, arrays, arrays of objects....) my flow-file has an attribute, named ETL_ID, I want to persist the JSON to a CLOB column of an Oracle table.
Well using standard PutSQL won't work as JSON content is more than 4000 chars.

With some JOLT transformers I converted the content of flow file to something like:

{
  "ID": 1234, // ETL_ID
  "REQUEST": { the very big json }
}

then I used PutDatabaseRecord processor to write it down, but then instead of JSON something like

MapRecord[{ContractId=null, PrintDocumentNo=1402/101/53034/53034/6, CompanyLifeProposalNo=qsn-190, InsurerId=116118216, FieldId=501, BranchId=null, AgencyId=130946, IntroducerBrokerId=null, IntroducerBranchId=null, IntroducerAgencyId=130946, BeginDate=2023-09-05, EndDate=2033-09-05, IssueDate=2023-09-05, PaymentType=233, Installment=10, InstallmentPeriod=242, Salary=null, SalaryPremiumPercentage=null, GuaranteedProfitCeiling=229, LicenceNo=null, LicenceRegisterDate=null, LicenceExpireDate=null, PostalCode=null, Insureds=[Ljava.lang.Object;@2ac5e7fb, Beneficiaries=[Ljava.lang.Object;@6cf36437, InsuredCoverages=[Ljava.lang.Object;@211a9f0a, AnnualCalculations=[Ljava.lang.Object;@49a84289}] 

as you can see for nested Objects, and Arrays the toString() of them is written!

is written to the CLOB field, the problem is everything was okay with Nifi 2.0.0, but it does not work with Apache Nifi 2.7.1.

[#2]

By changing `Suppress Null Values` to `Always Suppress` the problem get solved, now by removing nulls, Nifi writes correct JSON to database, but now the question is why?

Why did solve the issue? was that intentional or a accidental side effect? Will it behave the same in future releases?

[#3]

@Pashazadeh 

Apache NiFI 2.0.x was a technical milestone/preview releases that underwent many changes before the first GA release with NiFi 2.1.x.  I would not expect a change in behavior going forward, unless some bug is introduced or the community agrees on a change in functionality/behavior.  

While I don't have a specific answer to what bug resulted in the difference in behavior you encountered, here are some changes that affected the JsonRecordSetWriter.

1. NIFI-14331
2. NIFI-13963 / NIFI-13843
3. NIFI-12670

If you still have your NiFi 2.0.0 running, you could run your flow using a convertRecord with same record readers and writers and then compare the output content with what you see with 2.7.1 output.  Maybe that can help figure out what is happening and if either of those bugs affecting earlier NiFi 2.x versions is related.

Thanks,
Matt

[#4]

@Pashazadeh, Welcome to our community! To help you get the best possible answer, I have tagged in our NiFi experts  @MattWho @vafs, who may be able to assist you further.

Please feel free to provide any additional information or details about your query. We hope that you will find a satisfactory solution to your question.