Merge JSON Lists in OmniStudio DataRaptor

OmniStudio DataRaptor Transforms include powerful no-code functions to merge JSON Lists arrays: LISTMERGE and LISTMERGEPRIMARY. This document explains their syntax, usage, examples, and best practices.

Why Merge JSON Lists?

• Combine records from multiple sources (e.g., Current, Archived, Prospective).
• Overwrite field values on duplicate keys to surface the latest data.
• Optionally enrich only the primary list without adding new records.

Also Read: How to use “Upsert Key” in Dataraptor Load?

LISTMERGE

Merges all input lists, overwriting on matching keys, and includes all unique records.

1. Syntax:

LISTMERGE("mergeKey", LIST(%List1%), LIST(%List2%), …, LIST(%ListN%))

Example Input

{
  "CurrentAccounts": [
    {
      "Id": "0011t00000A1bXwAAJ",
      "Name": "Acme Corporation",
      "Status": "Active",
      "Industry": "Manufacturing"
    },
    {
      "Id": "0011t00000A1bXxAAJ",
      "Name": "Beta Technologies",
      "Status": "Active",
      "Industry": "Technology"
    }
  ],
  "ArchivedAccounts": [
    {
      "Id": "0011t00000A1bXwAAJ",
      "Name": "Acme Corporation",
      "Status": "Archived",
      "ArchivedDate": "2023-12-31"
    }
  ],
  "ProspectiveAccounts": [
    {
      "Id": "0011t00000A1bXyAAJ",
      "Name": "Gamma Financial",
      "Status": "Prospect",
      "PotentialValue": 500000
    }
  ]
}

Result

"MergedAccounts": [
    {
      "Name": "Beta Technologies",
      "Id": "0011t00000A1bXxAAJ",
      "Industry": "Technology",
      "Status": "Active"
    },
    {
      "Name": "Gamma Financial",
      "Id": "0011t00000A1bXyAAJ",
      "PotentialValue": 500000,
      "Status": "Prospect"
    },
    {
      "Name": "Acme Corporation",
      "ArchivedDate": "2023-12-31",
      "Id": "0011t00000A1bXwAAJ",
      "Industry": "Manufacturing",
      "Status": "Archived"
    }
  ]

LISTMERGEPRIMARY

Enriches only records from the first list, without adding new merge keys.

1. Syntax:

LISTMERGEPRIMARY("mergeKey", LIST(%PrimaryList%), LIST(%AuxList1%), …)

Result

"MergedAccountsPrimary": [
    {
      "Name": "Acme Corporation",
      "ArchivedDate": "2023-12-31",
      "Id": "0011t00000A1bXwAAJ",
      "Industry": "Manufacturing",
      "Status": "Archived"
    },
    {
      "Name": "Beta Technologies",
      "Id": "0011t00000A1bXxAAJ",
      "Industry": "Technology",
      "Status": "Active"
    }
  ]

Step-by-Step Implementation

1. Create a Transform DataRaptor named DRTMergeAccounts.
   
2. Define List outputs: MergedAccounts and MergedPrimaryAccounts.

3. Add a Transform Rule for MergedAccounts using LISTMERGE.

4. Add a Transform Rule for MergedPrimaryAccounts using LISTMERGEPRIMARY.

5. Test with sample JSON in the Test tab and verify outputs.

6. Integrate into an Integration Procedure or OmniScript via a Transform action.

Tips & Best Practices

• Use a consistent, unique merge key (e.g., Id) across all lists.
• Validate JSON structure in the Test tab before deploying.
• Monitor list sizes to maintain DataRaptor performance.
• Order your input lists carefully—later ones overwrite earlier ones.
• Document your formulas and field mappings clearly.