JOIN_JSON

Description

Returns a filtered array containing objects from the provided JSON array that match the specified field name and field value criteria.

Syntax

JOIN_JSON(json_array_string, ...[joining_field_name, joining_field_value])

Parameters

Parameter	Data Type	Required	Description
json_array_string	String	Yes	The JSON array of objects to filter.
… joining_field_name	String	No	The field name to use as the filter criterion. Multiple pairs allowed. Left Join if any pairs are included; Cross Join if all are omitted.
… joining_field_value	Object	No	Field value to match for filtering.

Examples

JOIN_JSON('["red", "green", "blue"]')
-- Performs a cross join, as no joining criteria are provided.
-- Each record in the current context is joined with every value in the JSON array.

JOIN_JSON('[
  {
    "Id": "0068K000007tDwxQAE", 
    "AccountId": "0018K00000kQI8RQAW",
    "OwnerId": "0055e000006XrYrAAK", 
    "Type": "New Business",
    "ShippingCountry": "Canada",
    "ShippingState": "Quebec"
  },
  {
    "Id": "0068K000007tDwyQAE", 
    "AccountId": "0018K00000kQI8SQAW",
    "OwnerId": "0055e000006WuzOAAS", 
    "Type": "New Business",
    "ShippingCountry": "Canada",
    "ShippingState": "Vancouver"
  }
]', "ShippingCountry", ShippingCountry__c)
-- Performs a left join on ShippingCountry.
-- Each record joins with JSON objects where ShippingCountry__c equals ShippingCountry.
-- If no match is found, the original record remains unchanged.
-- Joined fields can be accessed using $Joiner (e.g., $Joiner.Id, $Joiner.ShippingState).

JOIN_JSON('[
  {
    "Id": "0068K000007tDwxQAE", 
    "AccountId": "0018K00000kQI8RQAW",
    "OwnerId": "0055e000006XrYrAAK", 
    "Type": "New Business",
    "ShippingCountry": "Canada",
    "ShippingState": "Quebec"
  },
  {
    "Id": "0068K000007tDwyQAE", 
    "AccountId": "0018K00000kQI8SQAW",
    "OwnerId": "0055e000006WuzOAAS", 
    "Type": "New Business",
    "ShippingCountry": "Canada",
    "ShippingState": "Vancouver"
  }
]', "ShippingCountry", ShippingCountry__c, "ShippingState", ShippingState__c)
-- Performs a left join on ShippingCountry and ShippingState.
-- Each record joins only with JSON objects that match both fields.
-- If no matche is found, the original record remains unchanged.
-- Joined fields can be accessed using $Joiner (e.g., $Joiner.Id, $Joiner.ShippingState).

Tips

Defines this function during the Scoping stage to retrieve and prepare data for subsequent use in the Mapping stage.
Be careful to provide joining pairs when using this function. Omitting joining criteria can result in cross joins, creating excessively large record sets. Follow Salesforce best practices to prevent exceeding system limits.

Related Formula

$JOINER