How to compare 2 JSON Bodies dynamically and Create a single JSON Body

[Hoconnell]

Hello all,

I'm trying to update a current JSON object with an alteration - The alteration is not always going to be known (the exact field).

But essentially, I have JSON 1 (the original) and JSON 2 (the new).

1. For all fields in JSON 2, they should overwrite fields in JSON 1 if they are present. If they are not present, they should be added.
2. For all fields that are in JSON 1 that are not present in JSON 2, they remain and be present in the output.

I thought I had this completed with the mapper and it works most of the time:

sl.zip($Mambu, $SalesForce).map(x => x[0].extend(x[1]))

Where Mambu is JSON 1 & SalesForce is JSON 2.

However, where there are multiple layers in the JSON, they get overwritten incorrectly:

Example: - Mambu - JSON 1

[
{
"encodedKey": "8a98864e8dd0a566018dd148cc160221",
"id": "IVX973",
"holderType": "GROUP",
"holderKey": "8a9887c28dc736f4018dc76546350020",
"amount": 312312,
"availableCreditAmount": 312312,
"consumedCreditAmount": 0,
"state": "PENDING_APPROVAL",
"creationDate": "2024-02-22T14:47:05Z",
"lastModifiedDate": "2024-02-22T15:11:14Z",
"startDate": "2024-02-22T12:00:00Z",
"expireDate": "2040-01-01T00:00:00Z",
"exposureLimitType": "OUTSTANDING_AMOUNT",
"currency": {
"currencyCode": "GBP",
"code": "GBP"
},
"_Prod_ID": {
"Approved_Funding_Channel": "Direct",

"Approved_Funding_Channel2": "Programme",

"Approved_Funding_Channel3": "Rental"
},
"_Product_Group_": {
"Flexi_Inventory_": "TRUE",
"Pay_As_Sold_": "TRUE"
}
}
]

Example - SalesForce - JSON 2

{
"_Prod_ID": {
"Approved_Funding_Channel": "Program"
}
}

But this gives me an output of:

{
"encodedKey": "8a98864e8dd0a566018dd148cc160221",
"id": "IVX973",
"holderType": "GROUP",
"holderKey": "8a9887c28dc736f4018dc76546350020",
"amount": 312312,
"availableCreditAmount": 312312,
"consumedCreditAmount": 0,
"state": "PENDING_APPROVAL",
"creationDate": "2024-02-22T14:47:05Z",
"lastModifiedDate": "2024-02-22T15:11:14Z",
"startDate": "2024-02-22T12:00:00Z",
"expireDate": "2040-01-01T00:00:00Z",
"exposureLimitType": "OUTSTANDING_AMOUNT",
"currency": {
"currencyCode": "GBP",
"code": "GBP"
},

"_Prod_ID": {
"Approved_Funding_Channel": "Program"
},

"_Product_Group_": {
"Flexi_Inventory_": "TRUE",
"Pay_As_Sold_": "TRUE"
}
}
]

and overwrites all of "_Prod_ID" rather than the single field that I would like it to. Is there something I can do to do this? especially when i don't know what is going to be updated from JSON 2.

Thank you for your help,

[Aleksandar_A]

Hello @Hoconnell,

You can leverage the merge function. Please refer to the following documentation link object.merge() docs .

Also, attached below is a sample pipeline merging both given JSONs.

Please let me know if this helps you.

Regards,

Aleksandar.

[Michael]

@Hoconnell did you see the reply below from @Aleksandar_A; if so, did it work for you? Also, our new AutoSync product has automatic schema drift capabilities whereby existing fields would be overwritten and a new column is created for detected new fields. The beauty of AutoSync that it is a sidecar service that runs on its own resources, and you don't have to create a pipeline. You tell it what you want it to do and a pipeline is created/deployed for you.