Slicing Data from JSON

[JanosIT]

Hello SnapLogic Community,

I'm a Salesforce administrator working with SnapLogic to process data from an Oracle server. My pipeline calls a function that returns a multi-level JSON file. I need to extract data from this JSON and insert it into various Salesforce objects (master record and related child records).

Current Approach & Challenges:

My current solution involves copying the JSON into multiple pipeline branches. Within each branch, I use JSON Splitter snaps to break the data apart based on each available fields; then I use Join snaps to merge the data. Here's why this isn't ideal:

• Scalability: This approach becomes unwieldy with a large number of fields, requiring excessive branches.
• Error Potential: If fields lack values, the Join snap may misalign data during reassembly.

Example:

Input JSON:

{
  "Contacts": [
    {
      "First Name": "John",
      "Last Name": "Smith",
      "Email": "john.smith@abc.com",
      "Phone": "1234567890"
    },
    {
      "First Name": "Jane",
      "Last Name": "Taylor",
      "Email": "jane.taylor@cba.com",
      "Phone": "0987654321"
    }
  ]
}

Desired Output (CSV/table Format):

First Name,Last Name,Email,Phone
John,Smith,john.smith@abc.com,1234567890
Jane,Taylor,jane.taylor@cba.com,0987654321

Goal:

I'm looking for a more robust and scalable way to handle this JSON parsing and Salesforce insertion. My main aim is to keep data associated within each record intact without the complex splitting and merging that introduces error risks.

Request for Help:

Can anyone please suggest alternative solutions or workarounds to achieve my goal? I'd greatly appreciate any ideas that don't require any advanced coding skills. I have been trying to use expressions in a Mapper snap, with no success, and experimented with different snaps but I haven't been able to achieve the expected results (only the splitting/joining worked, but it's not ideal, and I believe there must be an other way, something I haven't tried yet).

Thank you in advance for your help and insights!

[Aleksandar_A]

Hello @JanosIT,

If possible could you provide a sample structure of the data that gets in the Salesforce Create snap?

If so, it would be easier for me to try to help you with the needed JSON formatting.

Regards,

Aleksandar.

Aleksandar.

[JanosIT]

Hi Aleksandar,

The expected data structure that gets into the Salesforce Create snap should look like this:

[
  {
    "FName__c": "John",
    "LName__c": "Smith",
    "Email__c": "john.smith@abc.com",
    "Phone__c": "1234567890",
    "Deal__c": "a0P3L000003KTVnUAO"
  },
  {
    "FName__c": "Jane",
    "LName__c": "Taylor",
    "Email__c": "jane.taylor@cba.com",
    "Phone__c": "0987654321",
    "Deal__c": "a0P3L000003KTVnUAO"
  }
]

So basically I want to retain the original structure of that particular "Contacts" part of the JSON, to insert it as separate child objects after adding the a new field (the ID of the parent) to each records, but when I use the Mapper snap to replace the keys with Salesforce fields, I get this:

[
  {
    "FName__c":["John","Jane"],
    "LName__c":["Smith","Taylor"],
    "Email__c":["john.smith@abc.com","jane.taylor@cba.com"],
    "Phone__c":["1234567890","0987654321"],
    "Deal__c": "a0P3L000003KTVnUAO"
  }
]

[JanosIT · Accepted solution]

Thank you, I actually just found the solution by using a single JSON Splitter and then a mapper to add the ID.