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.

Merge multiple JSON files on the same Table

Merge multiple JSON files on the same Table

New Contributor

Hi guys,

  I am having a problem trying to use Nifi to read 5 different types of Jsons and store them in the same table in Sql Server.

Json's files come with several records each and each of your records may have different schemas depending on the messagetype attribute.

Exemplifying:

I can receive files like this:

 


{"CategoryName": "Sport", "SupplierName": "XXXX", "StoreName": "XXXX", "Customer Account": "XXXX", "appName": "XXXXX", "messageType": "R1"}
{"CategoryName": null, "VendorName": null, "StoreName": null, "CustomerAccount": "XXXX", "appName": "XXXX", "messageType": "R2"}
{"CategoryName": "Others", "SupplierName": "XXXXX", "StoreName": "XXXX", "Customer Account": "XXXX", "appName": "XXXXX", "messageType": "R3"}
{"CustomerAccount": "XXXXXXX", "SalesStatus": "XXXXXXX", "PurchaseDate": XXXXXX, "SupplierName ":" XXXXX "," StoreName ":" XXXXXX "," RetailServerID ": null," SalesID ":" XXXXXX "," CommissionValue ":" 0 ", "PurchaseValue": "0", "appName": "XXXX", "messageType": "R4"}
{"paymentID": 0, "id": "XXXX", "CustomerAccount": "XXXX", "Sales Status ":" XXXX "," trackingDate ":" XXXXX "," Program ": null, "SalesValue": 0 , "commission": 0, "CustomerReturn": 0, "createdAt": "XXXX", "updatedAt": 0, "PartnerReturn": 0, "CustomerReturnXX": 0, "CustomerPayment": 0, "supplier": { "id": 0, "name": "XXXX", "description": "XXXXX"}, "store": {"id": 0, "supplier": {"id": 0, "name": " XXXXX "," description ":" XXXXX "}," name ":" XXXXX "," description ":" XXXXX "," linkSite ":" XXXXXX "," location ": true," flHighlight ": true," idLojaX ": 0," CustomerService ": false}," Currency ":" BRL "," InPaymentDate ": null, "PartnerData": null, "CustomerID": null, "appName": "XXXXX", "messageType": " R5 "}

 

I thought of using SplitContent to separate items, but since there are items with "{}" inside them, I can't use them as item delimiters.

Can anyone give me a hand?

Thanks!

Don't have an account?
Coming from Hortonworks? Activate your account here