I am facing a problem, transforming a very complex nested JSON using jolt transformation. Input and output detail is given below.
{
"headers": {
"SAP_MessageProcessingLogID": "AGN8Q1LAvaftfquPZgITeSFFaTOQ",
"SAP_PregeneratedMplId": "AGN8Q1SyLAPV51wXjKeFzGT2geJ0",
"X-dynaTrace": "\u0000\u0000\u0000\u0004���F\u0000\u0000Lv\u0000\u0000\u0000\n\u0000\u0000\u0000\u001ci�����#�\u0000\u0000\u0002=?�\u0004\b\u0000\u0000\u0000\u0001\u0006\b���F\b\b\u0000\u0000Lv\f G[�����\b��y'��X�\u000e\u0010�q)d�;\u0001}",
"breadcrumbId": "ID-8c1f6f36-fe53-4d97-704a-6e6d-1669077660632-34-16",
"SAP_MplCorrelationId": "AGN8Q1K-eWeazc4vLsQJt_lwCV58"
},
"key": {
"eventSource": "DM4003_MDG_D_BusinessPartnerSUITEBulkReplicateRequest",
"eventKey": "100016"
},
"value": {
"metadata": {
"eventVersion": "1.0",
"eventSource": "DM4003_MDG_D_BusinessPartnerSUITEBulkReplicateRequest",
"eventName": "",
"eventTime": "2022-06-10T15:43:10Z",
"eventID": "027F9B28D8161EDCBA9A80F42F397632",
"eventKey": "100016"
},
"payload": {
"BusinessPartnerSUITEBulkReplicateRequest": {
"MessageHeader": {
"ID": "027F9B28D8161EDCBA9A80F42F397632",
"UUID": "027f9b28-d816-1edc-ba9a-80f42f397632",
"CreationDateTime": "2022-06-10T15:43:10Z",
"SenderBusinessSystemID": "DM4003_MDG_D",
"RecipientBusinessSystemID": "RM5100_MDG_R"
},
"BusinessPartnerSUITEReplicateRequestMessage": [
{
"MessageHeader": {
"ID": "027F9B28D8161EDCBA9A80F42F399632",
"UUID": "027f9b28-d816-1edc-ba9a-80f42f399632",
"CreationDateTime": "2022-06-10T15:43:10Z",
"SenderBusinessSystemID": "DM4003_MDG_D",
"RecipientBusinessSystemID": "RM5100_MDG_R"
},
"BusinessPartner": {
"@actionCode": "04",
"@changeOrdinalNumberValue": "1",
"@reconciliationPeriodCounterValue": "1",
"@multipleAssignmentListCompleteTransmissionIndicator": "true",
"@addressInformationListCompleteTransmissionIndicator": "true",
"@addressIndependentInfoCompleteTransmissionIndicator": "true",
"@bankDetailsListCompleteTransmissionIndicator": "true",
"@paymentCardDetailsCompleteTransmissionIndicator": "true",
"@commonListCompleteTransmissionIndicator": "true",
"@roleListCompleteTransmissionIndicator": "true",
"@identificationListCompleteTransmissionIndicator": "true",
"@industrySectorListCompleteTransmissionIndicator": "true",
"@taxNumberListCompleteTransmissionIndicator": "true",
"@financialServicesCompanyCodeInformationCompleteTransmissionIndicator": "true",
"@creditManagementCustomInformationListCompleteTransmissionIndicator": "true",
"@creditManagementExternalCreditInformationListCompleteTransmissionIndicator": "true",
"@ZZSTOFORMCODE": "0000000000",
"@ZZSTOBRANCODE": "0000000000",
"@ZZCOMPSTAT": "0000000000",
"@ZZCOMPSTATGRP": "0000000000",
"UUID": "024f2eb6-b348-1edc-ba9a-393a4d5865e6",
"InternalID": "100016",
"CategoryCode": "2",
"NumberRangeIntervalBusinessPartnerGroupCode": "ZRTS",
"Customer": {
"@accountingInformationListCompleteTransmissionIndicator": "true",
"@salesArrangementListCompleteTransmissionIndicator": "true",
"@unloadingPointsListCompleteTransmissionIndicator": "true",
"@taxClassificationListCompleteTransmissionIndicator": "true",
"@customerTextListCompleteTransmissionIndicator": "true",
"@customerDocumentListCompleteTransmissionIndicator": "true",
"@propertyListCompleteTransmissionIndicator": "true",
"@propertyValuationCompleteTransmissionIndicator": "true",
"InternalID": "IN32",
"MaintenanceProfileCode": "ZRTC",
"AlternativePayeeAllowedIndicator": "false",
"DeletionBlockedIndicator": "false",
"DeletedIndicator": "false",
"PostingBlockedIndicator": "false",
"EqualizationTaxRelevanceIndicator": "false",
"ValueAddedTaxRelevanceIndicator": "false",
"ICMSExemptionIndicator": "false",
"IPIExemptionIndicator": "false",
"SaleSalesAndDistributionBlocks": {
"SalesSupportBlockedIndicator": "false"
},
"MarketingAttributes": {
"AnnualSalesVolumeAmountReportedYear": "0000",
"EmployeeNumberValueReportedYear": "0000"
},
"CivilUseIndicator": "false",
"MilitaryUseIndicator": "false",
"LegalControlIndicators": {
"BiochemicalWarfareIndicator": "false",
"NuclearNonproliferationIndicator": "false",
"NationalSecurityIndicator": "false",
"MissileTechnologyIndicator": "false"
},
"AccountingInformation": {
"@actionCode": "04",
"@dunningInformationListCompleteTransmissionIndicator": "true",
"@withholdingTaxListCompleteTransmissionIndicator": "true",
"@accountingTextListCompleteTransmissionIndicator": "true",
"CompanyID": "IN01",
"EmployeeID": "00000000",
"BlockedIndicator": "false",
"DeletedIndicator": "false",
"GeneralLedgerAccountReference": {
"ID": "1110211001"
},
"PaymentNoticeAccountingIndicator": "false",
"CustomerSupplierClearingIndicator": "false",
"InterestCalculationFrequencyCode": "00",
"InsuredAmount": {
"@currencyCode": "INR",
"$": "0.0"
},
"InsuranceLeadDuration": "P023M",
"LocalProcessingIndicator": "false",
"RecordPaymentHistoryIndicator": "false",
"ProbableCheckPaidDuration": "P023D",
"PayItemsSeparatelyIndicator": "false",
"PaymentAdvicesByEDIIndicator": "false",
"DeletionBlockedIndicator": "false"
},
"SalesArrangement": [
{
"@actionCode": "04",
"@partnerFunctionsListCompleteTransmissionIndicator": "true",
"@salesArrangementTextListCompleteTransmissionIndicator": "true",
"SalesOrganisationID": "IN01",
"DistributionChannelCode": "10",
"DivisionCode": "10",
"DeletedIndicator": "false",
"PricingProcedureDeterminationCodeLong": "01",
"SalesDistrictCode": "000001",
"PriceGroupCode": "01",
"BatchSplitAllowedIndicator": "false",
"ManualInvoiceMaintenanceIndicator": "false",
"CurrencyCode": "INR",
"RebateRelevantIndicator": "false",
"PricingRelevanceIndicator": "false",
"SalesSupportBlockedIndicator": "false",
"RoundingSwitchOffIndicator": "false",
"AgencyBusinessRelevanceIndicator": "false",
"PODRelevanceIndicator": "false",
"PODConfirmationTimeframeDuration": "P023D",
"DocumentIndexAllowedIndicator": "false",
"DeliveryTerms": {
"DeliveryPriorityCode": "00",
"SalesOrderCompleteDeliveryIndicator": "false",
"OrderCombinationAllowedIndicator": "false",
"QuantityTolerance": {
"OverPercentUnlimitedIndicator": "false"
}
}
},
{
"@actionCode": "04",
"@partnerFunctionsListCompleteTransmissionIndicator": "true",
"@salesArrangementTextListCompleteTransmissionIndicator": "true",
"SalesOrganisationID": "IN01",
"DistributionChannelCode": "20",
"DivisionCode": "10",
"DeletedIndicator": "false",
"PricingProcedureDeterminationCodeLong": "01",
"SalesDistrictCode": "000001",
"PriceGroupCode": "01",
"BatchSplitAllowedIndicator": "false",
"ManualInvoiceMaintenanceIndicator": "false",
"RebateRelevantIndicator": "false",
"PricingRelevanceIndicator": "false",
"SalesSupportBlockedIndicator": "false",
"RoundingSwitchOffIndicator": "false",
"AgencyBusinessRelevanceIndicator": "false",
"PODRelevanceIndicator": "false",
"PODConfirmationTimeframeDuration": "P023D",
"DocumentIndexAllowedIndicator": "false",
"DeliveryTerms": {
"DeliveryPriorityCode": "00",
"SalesOrderCompleteDeliveryIndicator": "false",
"OrderCombinationAllowedIndicator": "false",
"QuantityTolerance": {
"OverPercentUnlimitedIndicator": "false"
}
}
},
{
"@actionCode": "04",
"@partnerFunctionsListCompleteTransmissionIndicator": "true",
"@salesArrangementTextListCompleteTransmissionIndicator": "true",
"SalesOrganisationID": "IN01",
"DistributionChannelCode": "30",
"DivisionCode": "10",
"DeletedIndicator": "false",
"PricingProcedureDeterminationCodeLong": "01",
"SalesDistrictCode": "000001",
"PriceGroupCode": "01",
"BatchSplitAllowedIndicator": "false",
"ManualInvoiceMaintenanceIndicator": "false",
"RebateRelevantIndicator": "false",
"PricingRelevanceIndicator": "false",
"SalesSupportBlockedIndicator": "false",
"RoundingSwitchOffIndicator": "false",
"AgencyBusinessRelevanceIndicator": "false",
"PODRelevanceIndicator": "false",
"PODConfirmationTimeframeDuration": "P023D",
"DocumentIndexAllowedIndicator": "false",
"DeliveryTerms": {
"DeliveryPriorityCode": "00",
"SalesOrderCompleteDeliveryIndicator": "false",
"OrderCombinationAllowedIndicator": "false",
"QuantityTolerance": {
"OverPercentUnlimitedIndicator": "false"
}
}
},
{
"@actionCode": "04",
"@partnerFunctionsListCompleteTransmissionIndicator": "true",
"@salesArrangementTextListCompleteTransmissionIndicator": "true",
"SalesOrganisationID": "IN01",
"DistributionChannelCode": "40",
"DivisionCode": "10",
"DeletedIndicator": "false",
"PricingProcedureDeterminationCodeLong": "01",
"SalesDistrictCode": "000001",
"PriceGroupCode": "01",
"BatchSplitAllowedIndicator": "false",
"ManualInvoiceMaintenanceIndicator": "false",
"RebateRelevantIndicator": "false",
"PricingRelevanceIndicator": "false",
"SalesSupportBlockedIndicator": "false",
"RoundingSwitchOffIndicator": "false",
"AgencyBusinessRelevanceIndicator": "false",
"PODRelevanceIndicator": "false",
"PODConfirmationTimeframeDuration": "P023D",
"DocumentIndexAllowedIndicator": "false",
"DeliveryTerms": {
"DeliveryPriorityCode": "00",
"SalesOrderCompleteDeliveryIndicator": "false",
"OrderCombinationAllowedIndicator": "false",
"QuantityTolerance": {
"OverPercentUnlimitedIndicator": "false"
}
}
},
{
"@actionCode": "04",
"@partnerFunctionsListCompleteTransmissionIndicator": "true",
"@salesArrangementTextListCompleteTransmissionIndicator": "true",
"SalesOrganisationID": "IN01",
"DistributionChannelCode": "50",
"DivisionCode": "10",
"DeletedIndicator": "false",
"PricingProcedureDeterminationCodeLong": "01",
"SalesDistrictCode": "000001",
"PriceGroupCode": "01",
"BatchSplitAllowedIndicator": "false",
"ManualInvoiceMaintenanceIndicator": "false",
"RebateRelevantIndicator": "false",
"PricingRelevanceIndicator": "false",
"SalesSupportBlockedIndicator": "false",
"RoundingSwitchOffIndicator": "false",
"AgencyBusinessRelevanceIndicator": "false",
"PODRelevanceIndicator": "false",
"PODConfirmationTimeframeDuration": "P023D",
"DocumentIndexAllowedIndicator": "false",
"DeliveryTerms": {
"DeliveryPriorityCode": "00",
"SalesOrderCompleteDeliveryIndicator": "false",
"OrderCombinationAllowedIndicator": "false",
"QuantityTolerance": {
"OverPercentUnlimitedIndicator": "false"
}
}
},
{
"@actionCode": "04",
"@partnerFunctionsListCompleteTransmissionIndicator": "true",
"@salesArrangementTextListCompleteTransmissionIndicator": "true",
"SalesOrganisationID": "IN01",
"DistributionChannelCode": "60",
"DivisionCode": "10",
"DeletedIndicator": "false",
"PricingProcedureDeterminationCodeLong": "01",
"SalesDistrictCode": "000001",
"PriceGroupCode": "01",
"BatchSplitAllowedIndicator": "false",
"ManualInvoiceMaintenanceIndicator": "false",
"RebateRelevantIndicator": "false",
"PricingRelevanceIndicator": "false",
"SalesSupportBlockedIndicator": "false",
"RoundingSwitchOffIndicator": "false",
"AgencyBusinessRelevanceIndicator": "false",
"PODRelevanceIndicator": "false",
"PODConfirmationTimeframeDuration": "P023D",
"DocumentIndexAllowedIndicator": "false",
"DeliveryTerms": {
"DeliveryPriorityCode": "00",
"SalesOrderCompleteDeliveryIndicator": "false",
"OrderCombinationAllowedIndicator": "false",
"QuantityTolerance": {
"OverPercentUnlimitedIndicator": "false"
}
}
},
{
"@actionCode": "04",
"@partnerFunctionsListCompleteTransmissionIndicator": "true",
"@salesArrangementTextListCompleteTransmissionIndicator": "true",
"SalesOrganisationID": "IN01",
"DistributionChannelCode": "70",
"DivisionCode": "10",
"DeletedIndicator": "false",
"PricingProcedureDeterminationCodeLong": "01",
"SalesDistrictCode": "000001",
"PriceGroupCode": "01",
"BatchSplitAllowedIndicator": "false",
"ManualInvoiceMaintenanceIndicator": "false",
"RebateRelevantIndicator": "false",
"PricingRelevanceIndicator": "false",
"SalesSupportBlockedIndicator": "false",
"RoundingSwitchOffIndicator": "false",
"AgencyBusinessRelevanceIndicator": "false",
"PODRelevanceIndicator": "false",
"PODConfirmationTimeframeDuration": "P023D",
"DocumentIndexAllowedIndicator": "false",
"DeliveryTerms": {
"DeliveryPriorityCode": "00",
"SalesOrderCompleteDeliveryIndicator": "false",
"OrderCombinationAllowedIndicator": "false",
"QuantityTolerance": {
"OverPercentUnlimitedIndicator": "false"
}
}
}
],
"TaxClassification": {
"@actionCode": "04",
"TaxCountryCode": "IN",
"TaxTypeCode": "JTX4"
}
},
"Supplier": {
"@accountingInformationListCompleteTransmissionIndicator": "true",
"@procurementCardIssuingInstitutesListCompleteTransmissionIndicator": "false",
"@qualitiyManagementListCompleteTransmissionIndicator": "false",
"@procurementArrangementListCompleteTransmissionIndicator": "true",
"@subRangeListCompleteTransmissionIndicator": "true",
"@supplierTextListCompleteTransmissionIndicator": "true",
"@supplierDocumentListCompleteTransmissionIndicator": "true",
"@propertyListCompleteTransmissionIndicator": "true",
"@propertyValuationCompleteTransmissionIndicator": "true",
"InternalID": "IN32",
"MaintenanceProfileCode": "ZRTV",
"DeletionBlockedIndicator": "false",
"DeletedIndicator": "false",
"PostingBlockedIndicator": "false",
"PurchasingBlockedIndicator": "false",
"AlternativePayeeAllowedIndicator": "false",
"EqualizationTaxRelevanceIndicator": "false",
"ValueAddedTaxRelevanceIndicator": "false",
"SellerAssortmentProductGroupLevelRelevanceIndicator": "false",
"PlantLevelRelevanceIndicator": "false",
"ForSocialInsuranceRegisteredIndicator": "false",
"TaxSplitIndicator": "false",
"TaxBasePercentageCode": "0",
"CollectiveNumberingRelevancyIndicator": "false",
"StagingDuration": "P023D",
"AccountingInformation": {
"@actionCode": "04",
"@dunningInformationListCompleteTransmissionIndicator": "true",
"@withholdingTaxListCompleteTransmissionIndicator": "true",
"@accountingTextListCompleteTransmissionIndicator": "true",
"CompanyID": "IN01",
"BlockedIndicator": "false",
"DeletedIndicator": "false",
"GeneralLedgerAccountReference": {
"ID": "2110201001"
},
"InterestCalculationFrequencyCode": "00",
"LocalProcessingIndicator": "false",
"ProbableCheckPaidDuration": "P023D",
"DoubleEntriesCheckIndicator": "false",
"PayItemsSeparatelyIndicator": "false",
"EmployeeID": "00000000",
"PaymentAdvicesByEDIIndicator": "false",
"DeletionBlockedIndicator": "false",
"CustomerSupplierClearingIndicator": "false"
}
},
"AddressInformation": [
{
"@actionCode": "04",
"@addressUsageListCompleteTransmissionIndicator": "true",
"UUID": "024f2eb6-b348-1edc-ba9a-393a4d5ac5e6",
"ValidityPeriod": {
"StartDate": "2022-06-10",
"EndDate": "9999-12-31"
},
"AddressUsage": {
"@actionCode": "04",
"AddressUsageCode": "XXDEFAULT",
"ValidityPeriod": {
"StartDate": "2022-06-10",
"EndDate": "9999-12-31"
},
"DefaultIndicator": "false"
},
"Address": {
"@addressNoteListCompleteTransmissionIndicator": "true",
"@emailListCompleteTransmissionIndicator": "true",
"@facsimileListCompleteTransmissionIndicator": "true",
"@organisationNameListCompleteTransmissionIndicator": "true",
"@personNameListCompleteTransmissionIndicator": "true",
"@postalAddressListCompleteTransmissionIndicator": "true",
"@telephoneListCompleteTransmissionIndicator": "true",
"@webListCompleteTransmissionIndicator": "true",
"CommunicationPreference": {
"@actionCode": "04",
"CorrespondenceLanguageCode": "EN"
},
"PostalAddress": [
{
"@actionCode": "04",
"CountryCode": "IN",
"CityName": "Bengaluru",
"StreetPostalCode": "560066",
"StreetName": "No.62 Prestige Ozone",
"POBoxIDVisibleIndicator": "false",
"TimeZoneCode": "INDIA"
}
]
}
}
],
"Common": [
{
"@actionCode": "04",
"LocationStandardID": {
"@schemeAgencyID": "1",
"$": "0000000000000"
},
"DeletedIndicator": "false",
"ReleasedIndicator": "true",
"BlockedIndicator": "false",
"KeyWordsText": "ADIDAS INDIA MARKETI",
"CorrespondenceBrailleRequiredIndicator": "false",
"CorrespondenceLargePrintRequiredIndicator": "false",
"NaturalPersonIndicator": "false",
"Organisation": {
"Name": {
"FirstLineName": "adidas India Marketing Pvt.Ltd"
}
}
}
],
"Role": [
{
"@actionCode": "04",
"RoleCode": "BPSITE",
"ValidityPeriod": {
"StartDate": "2022-06-10",
"EndDate": "9999-12-31"
}
},
{
"@actionCode": "04",
"RoleCode": "FLCU00",
"ValidityPeriod": {
"StartDate": "2022-06-10",
"EndDate": "9999-12-31"
}
},
{
"@actionCode": "04",
"RoleCode": "FLCU01",
"ValidityPeriod": {
"StartDate": "2022-06-10",
"EndDate": "9999-12-31"
}
},
{
"@actionCode": "04",
"RoleCode": "FLVN00",
"ValidityPeriod": {
"StartDate": "2022-06-10",
"EndDate": "9999-12-31"
}
},
{
"@actionCode": "04",
"RoleCode": "FLVN01",
"ValidityPeriod": {
"StartDate": "2022-06-10",
"EndDate": "9999-12-31"
}
}
],
"Identification": [
{
"@actionCode": "04",
"PartyIdentifierTypeCode": "ZPLANT",
"BusinessPartnerID": "KA01",
"EntryDate": "2022-06-10",
"ValidityPeriod": {
"StartDate": "2022-06-10",
"EndDate": "9999-12-31"
}
}
],
"CreditManagementCreditWorthiness": {
"AffidavitForAssetsMadeIndicator": "false",
"BankruptcyProceedingsInitiatedIndicator": "false",
"ForeclosureProceedingsInitiatedIndicator": "false"
}
}
}
]
}
}
}
}
Ouput I need the below columns in a flattened way, the distributionchannelcode is coming in a array, i wanted to flatten it. I m not able to achieve it
{
"Location" : "027F9B28D8161EDCBA9A80F42F399632",
"DistributionChannelCode" : [ "10", "20", "30", "40", "50", "60", "70" ],
"SAP Company code" : "IN01",
"LocationCountry" : "IN",
"InternationalLocationName" : "xyz"
}
Jolt Spec i tried
[
{
"operation": "shift",
"spec": {
"value": {
"payload": {
"BusinessPartnerSUITEBulkReplicateRequest": {
"BusinessPartnerSUITEReplicateRequestMessage": {
"*": {
"MessageHeader": {
"ID": "Location"
},
"BusinessPartner": {
"Customer": {
"SalesArrangement": {
"*": {
"DistributionChannelCode": "DistributionChannelCode"
}
},
"AccountingInformation": {
"CompanyID": "SAP Company code"
}
},
"AddressInformation": {
"*": {
"Address": {
"PostalAddress": {
"*": {
"CountryCode": "LocationCountry"
}
}
}
}
},
"Common": {
"*": {
"Organisation": {
"Name": {
"FirstLineName": "InternationalLocationName"
}
}
}
}
}
}
}
}
}
}
}
}
]
Can anyone who is a jolt expert, help me get the desired output. I think i m stuck in the last step
CodePudding user response：
You can add this shift transformation spec to the current one
{
"operation": "shift",
"spec": {
"DistributionChannelCode": {
"*": {
"@(2,Location)": "[#2].Location",
"@": "[#2].&2", // replicates the "DistributionChannelCode" tag going tree two levels up
"@(2,SAP Company code)": "[#2].SAP Company code",
"@(2,LocationCountry)": "[#2].LocationCountry",
"@(2,InternationalLocationName)": "[#2].InternationalLocationName"
}
}
}
}
in order to dissipate each attributes to seven different objects, as DistributionChannelCode having seven components.