Member since
08-29-2024
3
Posts
3
Kudos Received
1
Solution
My Accepted Solutions
Title | Views | Posted |
---|---|---|
378 | 08-29-2024 12:25 PM |
10-03-2024
09:27 AM
I have refined my Data Flow to include ISODate, NumberDecimal, and NumberLong using an AVRO Schema Text (shown below), but I'm stilling seeing the unwanted null value fields being inserted into my collection "Testers". Is there a way to ensure that said null valued fields do NOT get inserted into the collection? AVRO Schema Text for PutMongoRecord RecordReader: { "namespace": "nifi", "name": "Appropriation", "type": "record", "fields": [ { "name": "owningOrg", "type":["null","string"] }, { "name": "financialSystemRef", "type":["null","string"] }, { "name": "type", "type":["null","string"] }, { "name": "subType", "type":["null","string"] }, { "name": "migrationKey", "type":["null","string"] }, { "name": "duration", "type":"int" }, { "name": "association", "type":["null","string"] }, { "name": "availBalanceCalc", "type":["null","string"] }, { "name" : "sloa", "type" : { "type": "record", "name": "mysloa", "fields": [ { "name": "activity", "type":["null","string"] }, { "name": "agencyDisbursingId", "type":["null","string"] }, { "name": "availabilityType", "type":["null","string"] }, { "name": "budgetActivity", "type":["null","string"] }, { "name": "budgetLineItemId", "type":["null","string"] }, { "name": "budgetSubActivity", "type":["null","string"] }, { "name": "businessEventType", "type":["null","string"] }, { "name": "costCenter", "type":["null","string"] }, { "name": "costElement", "type":["null","string"] }, { "name": "departmentRegular", "type":["null","string"] }, { "name": "departmentTransfer", "type":["null","string"] }, { "name": "functionalArea", "type":["null","string"] }, { "name": "fundCode", "type":["null","string"] }, { "name": "fundingCenter", "type":["null","string"] }, { "name": "mainAccountCode", "type":["null","string"] }, { "name": "programYear", "type":["null","string"] }, { "name": "projectIdentifier", "type":["null","string"] }, { "name": "reimbursable", "type":["null","string"] }, { "name": "subAccount", "type":["null","string"] }, { "name": "subAllocationHolder", "type":["null","string"] }, { "name": "subClass", "type":["null","string"] }, { "name": "workOrderId", "type":["null","string"] } ] } }, { "name" : "target", "type" : { "type" : "bytes", "logicalType" : "decimal", "precision" : 18, "scale" : 2 } }, { "name" : "_metadata", "type" : { "type": "record", "name": "mymetadata", "fields": [ { "name": "isDeleted", "type":"boolean" }, { "name": "createdMillis", "type":"long" }, { "name": "modifiedMillis", "type":"long" }, { "name": "createdDate", "type": { "type":"long", "logicalType":"timestamp-millis" } }, { "name": "modifiedDate", "type": { "type":"long", "logicalType":"timestamp-millis" } } ] } }, { "name": "isRollover", "type":"boolean" }, { "name": "startFiscalYear", "type":["null","string"] }, { "name": "endFiscalYear", "type":["null","string"] }, { "name" : "financialSystemFields", "type": { "type": "array", "items": { "type": "record", "name": "array_item", "fields": [ { "name": "aaaCode", "type":["null","string"] }, { "name": "accountingClassificationCode", "type":["null","string"] }, { "name": "acrn", "type":["null","string"] }, { "name": "activityCode", "type":["null","string"] }, { "name": "apportionmentCode", "type":["null","string"] }, { "name": "availableBalance", "type":["null","string"] }, { "name": "bureauControlNumber", "type":["null","string"] }, { "name": "businessPartnerNumber", "type":["null","string"] }, { "name": "costCenterDescription", "type":["null","string"] }, { "name": "costCenterName3", "type":["null","string"] }, { "name": "costCenterName4", "type":["null","string"] }, { "name": "costCenterNonOuid", "type":["null","string"] }, { "name": "costCenterPostalCode", "type":["null","string"] }, { "name": "costCode", "type":["null","string"] }, { "name": "expenditureOrg", "type":["null","string"] }, { "name": "expenditureType", "type":["null","string"] }, { "name": "fundDescription", "type":["null","string"] }, { "name": "fundedProgram", "type":["null","string"] }, { "name": "FundId", "type":["null","string"] }, { "name": "fundIdentifier", "type":["null","string"] }, { "name": "limitCode", "type":["null","string"] }, { "name": "logIndicator", "type":["null","string"] }, { "name": "networkId", "type":["null","string"] }, { "name": "objectClassCode", "type":["null","string"] }, { "name": "paaCode", "type":["null","string"] }, { "name": "projectDescription", "type":["null","string"] }, { "name": "requestingCostCenter", "type":["null","string"] }, { "name": "responsibleCostCenter", "type":["null","string"] }, { "name": "subAllotmentNumber", "type":["null","string"] }, { "name": "taskNumber", "type":["null","string"] }, { "name": "transTypeCode", "type":["null","string"] } ] } } }, { "name" : "retiredDate", "type" : { "type":"int", "logicalType":"date" } }, { "name" : "expiredDate", "type" : { "type":"int", "logicalType":"date" } }, { "name" : "closedDate", "type" : { "type":"int", "logicalType":"date" } }, { "name" : "beginDate", "type" : { "type":"int", "logicalType":"date" } }, { "name" : "endDate", "type" : { "type":"int", "logicalType":"date" } } ] } PutMongoRecord Data Provenance output appears to have removed the null fields: [ { "owningOrg" : "OO1234", "financialSystemRef" : "HOWDS", "type" : "APPROPRIATION", "subType" : "WBS", "migrationKey" : "OO1234.1850", "duration" : 1, "availBalanceCalc" : "defaultAvailableBalanceCalculator", "sloa" : { "agencyDisbursingId" : "1234", "businessEventType" : "DISB", "costCenter" : "12345678", "departmentRegular" : "012", "functionalArea" : "1234567890", "fundCode" : "Z8", "fundingCenter" : "HOWD1", "mainAccountCode" : "0130", "projectIdentifier" : "Z.8888888.1.123.2", "reimbursable" : "D", "subAccount" : "000", "subAllocationHolder" : "1800" }, "target" : "3000.00", "_metadata" : { "isDeleted" : false, "createdMillis" : "1727971251189", "modifiedMillis" : "1727971251189", "createdDate" : "20241003 16:00:51.189", "modifiedDate" : "20241003 16:00:51.189" }, "isRollover" : true, "startFiscalYear" : "2024", "endFiscalYear" : "2024", "financialSystemFields" : [ { "costCenterDescription" : "HELPING OTHERS", "costCenterName3" : "69HOII0222", "costCenterName4" : "69", "costCenterNonOuid" : "Z8ABCZZ", "costCenterPostalCode" : "12345", "fundDescription" : "HELPING OTHERS", "fundedProgram" : "Z.8888888.1.123.2", "FundId" : "0123AZ1Z57", "projectDescription" : "HELPING OTHERS", "requestingCostCenter" : "12345678", "responsibleCostCenter" : "12345678" } ], "expiredDate" : 1727654400000, "closedDate" : 1885420800000, "beginDate" : 1696118400000, "endDate" : 1727654400000 } ] But the null fields are somehow inserted into the collection: { "_id" : ObjectId("66febfb82cfc0ddff07b4b52"), "migrationKey" : "OO1234.1850", "_metadata" : { "isDeleted" : false, "createdMillis" : NumberLong(1727971251189), "modifiedMillis" : NumberLong(1727971251189), "createdDate" : ISODate("2024-10-03T16:00:51.189Z"), "modifiedDate" : ISODate("2024-10-03T16:00:51.189Z") }, "association" : null, "availBalanceCalc" : "defaultAvailableBalanceCalculator", "beginDate" : ISODate("2023-10-01T00:00:00.000Z"), "closedDate" : ISODate("2029-09-30T00:00:00.000Z"), "duration" : 1, "endDate" : ISODate("2024-09-30T00:00:00.000Z"), "endFiscalYear" : "2024", "expiredDate" : ISODate("2024-09-30T00:00:00.000Z"), "financialSystemFields" : [ { "aaaCode" : null, "accountingClassificationCode" : null, "acrn" : null, "activityCode" : null, "apportionmentCode" : null, "availableBalance" : null, "bureauControlNumber" : null, "businessPartnerNumber" : null, "costCenterDescription" : "HELPING OTHERS", "costCenterName3" : "69HOII0222", "costCenterName4" : "69", "costCenterNonOuid" : "Z8ABCZZ", "costCenterPostalCode" : "12345", "costCode" : null, "expenditureOrg" : null, "expenditureType" : null, "fundDescription" : "HELPING OTHERS", "fundedProgram" : "Z.8888888.1.123.2", "FundId" : "0123AZ1Z57", "fundIdentifier" : null, "limitCode" : null, "logIndicator" : null, "networkId" : null, "objectClassCode" : null, "paaCode" : null, "projectDescription" : "HELPING OTHERS", "requestingCostCenter" : "12345678", "responsibleCostCenter" : "12345678", "subAllotmentNumber" : null, "taskNumber" : null, "transTypeCode" : null } ], "financialSystemRef" : "HOWDS", "isRollover" : true, "owningOrg" : "OO1234", "retiredDate" : null, "sloa" : { "activity" : null, "agencyDisbursingId" : "1234", "availabilityType" : null, "budgetActivity" : null, "budgetLineItemId" : null, "budgetSubActivity" : null, "businessEventType" : "DISB", "costCenter" : "12345678", "costElement" : null, "departmentRegular" : "012", "departmentTransfer" : null, "functionalArea" : "1234567890", "fundCode" : "Z8", "fundingCenter" : "HOWD1", "mainAccountCode" : "0130", "programYear" : null, "projectIdentifier" : "Z.8888888.1.123.2", "reimbursable" : "D", "subAccount" : "000", "subAllocationHolder" : "1800", "subClass" : null, "workOrderId" : null }, "startFiscalYear" : "2024", "subType" : "WBS", "target" : NumberDecimal("3000.00"), "type" : "APPROPRIATION" } What I want to be inserted into collection would be: { "_id" : ObjectId("66febfb82cfc0ddff07b4b52"), "migrationKey" : "OO1234.1850", "_metadata" : { "isDeleted" : false, "createdMillis" : NumberLong(1727971251189), "modifiedMillis" : NumberLong(1727971251189), "createdDate" : ISODate("2024-10-03T16:00:51.189Z"), "modifiedDate" : ISODate("2024-10-03T16:00:51.189Z") }, "availBalanceCalc" : "defaultAvailableBalanceCalculator", "beginDate" : ISODate("2023-10-01T00:00:00.000Z"), "closedDate" : ISODate("2029-09-30T00:00:00.000Z"), "duration" : 1, "endDate" : ISODate("2024-09-30T00:00:00.000Z"), "endFiscalYear" : "2024", "expiredDate" : ISODate("2024-09-30T00:00:00.000Z"), "financialSystemFields" : [ { "costCenterDescription" : "HELPING OTHERS", "costCenterName3" : "69HOII0222", "costCenterName4" : "69", "costCenterNonOuid" : "Z8ABCZZ", "costCenterPostalCode" : "12345", "fundDescription" : "HELPING OTHERS", "fundedProgram" : "Z.8888888.1.123.2", "FundId" : "0123AZ1Z57", "projectDescription" : "HELPING OTHERS", "requestingCostCenter" : "12345678", "responsibleCostCenter" : "12345678" } ], "financialSystemRef" : "HOWDS", "isRollover" : true, "owningOrg" : "OO1234", "sloa" : { "agencyDisbursingId" : "1234", "businessEventType" : "DISB", "costCenter" : "12345678", "departmentRegular" : "012", "functionalArea" : "1234567890", "fundCode" : "Z8", "fundingCenter" : "HOWD1", "mainAccountCode" : "0130", "projectIdentifier" : "Z.8888888.1.123.2", "reimbursable" : "D", "subAccount" : "000", "subAllocationHolder" : "1800" }, "startFiscalYear" : "2024", "subType" : "WBS", "target" : NumberDecimal("3000.00"), "type" : "APPROPRIATION" }
... View more
Labels:
- Labels:
-
Apache NiFi
08-29-2024
12:25 PM
2 Kudos
I've figured out what needs to be done. The following fixed the issue. [{ "operation": "cardinality", "spec": { "financialSystemFields": "MANY" } }]
... View more
08-29-2024
09:06 AM
1 Kudo
I'm new to NiFi, and I've been struggling with wrapping square brackets around a specific JSON object. Below is my JSON input, my desired JSON output and my attempted JoltTransformJSON shift operation spec. I'm really close, but I'm getting a duplicate of the "financialSystemFields" object. Please advise, and many thanks in advance. JSON Input: [{ "type" : "APPROPRIATION", "subType" : "ADMINISTRATIVE", "duration" : "1", "retiredDate" : null, "expiredDate" : "2023-09-30 00:00:00.0", "closedDate" : "2028-09-30 00:00:00.0", "association" : null, "financialSystemFields" : { "FundId" : "01234NFWJ", "fundedProgram" : null, "projectDescription" : "NO FUN WITH JOLT" } }] Current JSON Output: [{ "type": "APPROPRIATION", "subType": "ADMINISTRATIVE", "duration": "1", "retiredDate": null, "expiredDate": "2023-09-30 00:00:00.0", "closedDate": "2028-09-30 00:00:00.0", "association": null, "financialSystemFields": [{ "FundId": "01234NFWJ", "fundedProgram": null, "projectDescription": "NO FUN WITH JOLT" }, { "FundId": "01234NFWJ", "fundedProgram": null, "projectDescription": "NO FUN WITH JOLT" }] }] Desired JSON Output: [{ "type": "APPROPRIATION", "subType": "ADMINISTRATIVE", "duration": "1", "retiredDate": null, "expiredDate": "2023-09-30 00:00:00.0", "closedDate": "2028-09-30 00:00:00.0", "association": null, "financialSystemFields": [{ "FundId": "01234NFWJ", "fundedProgram": null, "projectDescription": "NO FUN WITH JOLT" }] }] Jolt Operation: [{ "operation": "shift", "spec": { "*": { "@": "[&1]", "@(0,financialSystemFields)": "[&].financialSystemFields" } } }]
... View more
Labels:
- Labels:
-
Apache NiFi