Support Questions
Find answers, ask questions, and share your expertise

Jolt Transformation on nested arrays, new objects, and incremental counters as id

Jolt Transformation on nested arrays, new objects, and incremental counters as id

New Contributor

All,

I am newbie in nifi, and would appreciate some help. I want to:

1. Create objects based on RecordType

2. Each record type will have an 4 arrays (Names, Address, Identifiers, and Sanctions)

2. Each Array for example Names array will contain all names object which will have (firstname, lastname and other attributes). This means if the input has 4 records and they are all of Recordtype "person" then I expect 4 objects in Names array. Same for address, identifiers, and sanctions array.

I have been able to advance the input from xml up to this point but need some help to get to the expected output. Any help at all will be very much appreciated.

 

[INPUT]

{
"ConsolidatedList": [
{
"AliasType": "Primary",
"AliasTypeName": "Primary",
"BusinessRegNumber": {
"nil": null
},
"nil": null,
"Country": null,
"CountryOfBirth": "UK",
"CurrentOwners": {
"nil": null
},
"DateListed": "2021-03-15T00:00:00",
"DateListedDay": 15,
"DateListedMonth": 3,
"DateListedYear": 2021,
"DateOfBirth": "01/09/1975",
"DateOfBirthId": 4751,
"DayOfBirth": 1,
"EmailAddress": {
"nil": null
},
"FCOId": "UK0377",
"FlagOfVessel": {
"nil": null
},
"FullAddress": null,
"FullName": " Daniel Law",
"FurtherIdentifiyingInformation": {
"nil": null
},
"Gender": "Male",
"GroupID": 14070,
"GroupStatus": "Asset Freeze Targets",
"GroupTypeDescription": "Person",
"GrpStatus": "A",
"HIN": {
"nil": null
},
"ID": 33233,
"IMONumber": {
"nil": null
},
"LastUpdated": "2021-03-15T00:00:00",
"LastUpdatedDay": 15,
"LastUpdatedMonth": 3,
"LastUpdatedYear": 2021,
"LengthOfVessel": {
"nil": null
},
"ListingType": "UK autonomous",
"MonthOfBirth": 9,
"Name6": "Daniel",
"NameTitle": null,
"NationalIdNumber": {
"nil": null
},
"Nationality": "Syria",
"OrgType": {
"nil": null
},
"OtherInformation": "",
"ParentCompany": {
"nil": null
},
"PassportDetails": "",
"PhoneNumber": {
"nil": null
},
"Position": "Media Adviser",
"PostCode": null,
"PreviousFlags": {
"nil": null
},
"PreviousOwners": {
"nil": null
},
"RegimeName": "Cane",
"Subsidiaries": {
"nil": null
},
"TonnageOfVessel": {
"nil": null
},
"TownOfBirth": "North T",
"TypeOfVessel": {
"nil": null
},
"UKStatementOfReasons": "Something good is happening",
"Website": {
"nil": null
},
"YearBuilt": {
"nil": null
},
"YearOfBirth": 1975,
"address1": null,
"address2": null,
"address3": null,
"address4": null,
"address5": null,
"address6": null,
"name1": "Luna",
"name2": null,
"name3": null,
"name4": null,
"name5": null
},
{
"AliasType": "Primary",
"AliasTypeName": "AKA",
"BusinessRegNumber": {
"nil": null
},
"nil": null,
"Country": null,
"CountryOfBirth": "UK",
"CurrentOwners": {
"nil": null
},
"DateListed": "2021-03-15T00:00:00",
"DateListedDay": 15,
"DateListedMonth": 3,
"DateListedYear": 2021,
"DateOfBirth": "01/09/1975",
"DateOfBirthId": 4751,
"DayOfBirth": 1,
"EmailAddress": {
"nil": null
},
"FCOId": "UK0377",
"FlagOfVessel": {
"nil": null
},
"FullAddress": null,
"FullName": " Law",
"FurtherIdentifiyingInformation": {
"nil": null
},
"Gender": "Male",
"GroupID": 14070,
"GroupStatus": "Asset",
"GroupTypeDescription": "Entity",
"GrpStatus": "A",
"HIN": {
"nil": null
},
"ID": 33233,
"IMONumber": {
"nil": null
},
"LastUpdated": "2021-03-15T00:00:00",
"LastUpdatedDay": 15,
"LastUpdatedMonth": 3,
"LastUpdatedYear": 2021,
"LengthOfVessel": {
"nil": null
},
"ListingType": "UK autonomous",
"MonthOfBirth": 9,
"Name6": "Daniel",
"NameTitle": null,
"NationalIdNumber": {
"nil": null
},
"Nationality": "Syria",
"OrgType": {
"nil": null
},
"OtherInformation": "",
"ParentCompany": {
"nil": null
},
"PassportDetails": "",
"PhoneNumber": {
"nil": null
},
"Position": "Media Adviser",
"PostCode": null,
"PreviousFlags": {
"nil": null
},
"PreviousOwners": {
"nil": null
},
"RegimeName": "Cane",
"Subsidiaries": {
"nil": null
},
"TonnageOfVessel": {
"nil": null
},
"TownOfBirth": "North T",
"TypeOfVessel": {
"nil": null
},
"UKStatementOfReasons": "Sundays are special",
"Website": {
"nil": null
},
"YearBuilt": {
"nil": null
},
"YearOfBirth": 1975,
"address1": null,
"address2": null,
"address3": null,
"address4": null,
"address5": null,
"address6": null,
"name1": "Luna",
"name2": null,
"name3": null,
"name4": null,
"name5": null
}

]
}

 

[SPEC] So far

[
{
"operation": "shift",
"spec": {
"ConsolidatedList": {
"*": {
"LastUpdated": "LastUpdated",
"$": "Records.[&1]._id",
"UKStatementOfReasons": "Records.[&1].AdditionalInformation",
"GroupTypeDescription": "Records.[&1].RecordType",
"# ": "Records.[&1].Names.[0].Other Name",
"# ": "Records.[&1].Names.[0].UnparsedName",
"name1": "Records.[&1].Names.[0].FirstName",
"name2": "Records.[&1].Names.[0].FirstName",
"name3": "Records.[&1].Names.[0].FirstName",
"name4": "Records.[&1].Names.[0].FirstName",
"name5": "Records.[&1].Names.[0].FirstName",
"Name6": "Records.[&1].Names.[0].LastName",
"AliasType": "Records.[&1].Names.[0].NameType",

"#1": "Records.[&1].Identifiers.[0]._id",
"YearOfBirth": "Records.[&1].Identifiers.[0].IDValue",
"#Date Of Birth": "Records.[&1].Identifiers.[0].IDType",
"#2": "Records.[&1].Identifiers.[1]._id",
"TownOfBirth": "Records.[&1].Identifiers.[1].IDValue",
"#Place Of Birth": "Records.[&1].Identifiers.[1].IDType",
"#3": "Records.[&1].Identifiers.[2]._id",
"PassportDetails": "Records.[&1].Identifiers.[2].IDValue",
"#Passport": "Records.[&1].Identifiers.[2].IDType",

"PostCode": "Records.[&1].Addresses.[0].PostalCode",
"address2": "Records.[&1].Addresses.[0].Street",
"address6": "Records.[&1].Addresses.[0].City",
"Country": "Records.[&1].Addresses.[0].StateProvince",
"CountryOfBirth": "Records.[&1].Addresses.[0].Country.Name",
"#Address": "Records.[&1].Addresses.[0].Country.RelationType",

"# ": "Records.[&1].Sanctions.[0]._id",
"# ": "Records.[&1].Sanctions.[0].Sanction",
"# ": "Records.[&1].Sanctions.[0].ExpirationDate",
"# ": "Records.[&1].Sanctions.[0].SanctionDate"
}
}
}
},
{
"operation": "modify-overwrite-beta",
"spec": {
"Count": "=size(@(1,Records))",
"Pages": "1",
"LastUpdated": "=firstElement(@(1,LastUpdated))",
"Records": {
"*": {
"Names": {
"*": {
"FirstName": "=join(' ',@(1,FirstName))"
}
}
}
}
}},
{
"operation": "default",
"spec": {
"RecordType": [
"Individual",
"Entity",
"Vessel"
]
}
},
{
"operation": "sort"
}
]

 

[EXPECTED OUTPUT]

{
"Count": "2", // Count of records
"LastUpdated": "08/03/2020 12:00:00",
"RecordType": ["Entity","Individual","Vessel"],
"Pages": "1",
"Records": [
{
"_id": "1", // Counter for number number of records
"AdditionalInformation": "Something good is happening"
"RecordType": "Person",
"Names": [
{
"_id": "1", //counter for names object in the array
"OtherName": "",
"UnparsedName": "",
"FirstName": "",
"LastName": "",
"NameType": "Primary"
}
],
"Identifiers": [
{
"_id": "1",
"IDValue": "1971",
"IDType": "Date Of Birth"
}
],
"Addresses": [
{
"_id": "1",
"PostalCode": "",
"Street": "",
"City": "",
"StateProvince": "",
"Country": {
"Name": "",
"RelationType": "Address"
}
}
],
"Sanctions": [
{
"_id": "1",
"Sanction": "HM",
"ExpirationDate": "",
"SanctionDate": ""
}
]
},
{
"_id": "2",
"AdditionalInformation": "Sundays are special"
"RecordType": "Entity",
"Names": [
{
"_id": "1",
"OtherName": "",
"UnparsedName": "",
"FirstName": "",
"LastName": "",
"NameType": "Primary"
}
],
"Identifiers": [
{
"_id": "1",
"IDValue": "1971",
"IDType": "Date Of Birth"
}
],
"Addresses": [
{
"_id": "1",
"PostalCode": "",
"Street": "",
"City": "",
"StateProvince": "",
"Country": {
"Name": "",
"RelationType": "Address"
}
}
],
"Sanctions": [
{
"_id": "1",
"Sanction": "HM",
"ExpirationDate": "",
"SanctionDate": ""
}
]
}
]
}