Data 360: Parsing JSON in Related Fields within Data Extensions

Below is a sample AmpScript code snippet that demonstrates how to parse JSON data and use the extracted values in an email. You can modify this code based on your specific use case and field names.

%%[
/* Declare variables */
var @subscriberKey, @jsonData, @rowSet, @rowCount, @currentRow, @fieldValue
var @imageURL1, @imageURL2, @imageURL3

/* Get subscriber key and JSON data from the data extension */
SET @subscriberKey = AttributeValue("SubscriberKey")
SET @jsonData = AttributeValue("RelatedFieldName") /* Replace 'RelatedFieldName' with the actual field name */

/* Parse the JSON data to create a rowset for iteration */
SET @rowSet = BuildRowsetFromJSON(@jsonData, "$.[]", 0)
SET @rowCount = RowCount(@rowSet)

/* Process each row in the JSON data */
IF @rowCount > 0 THEN
FOR @i = 1 TO @rowCount DO
SET @currentRow = Row(@rowSet, @i)
SET @fieldValue = Field(@currentRow, "YourFieldName") /* Replace 'YourFieldName' with the field you want to extract */

/* Example: Match field value to assign specific URLs or content */
IF @fieldValue == "Condition1" THEN
SET @imageURL1 = "https://your-image-url-1.com" /* Replace with your URL */
ENDIF
IF @fieldValue == "Condition2" THEN
SET @imageURL2 = "https://your-image-url-2.com" /* Replace with your URL */
ENDIF
IF @fieldValue == "Condition3" THEN
SET @imageURL3 = "https://your-image-url-3.com" /* Replace with your URL */
ENDIF
NEXT @i
ENDIF
]%%

Explanation:

1. Variables:
   1. @subscriberKey: Represents the unique identifier for the subscriber.
   2. @jsonData: Holds the JSON data extracted from the related field in the data extension.
   3. @rowSet and @rowCount: Used to parse and count rows in the JSON data.
   4. @currentRow and @fieldValue: Handle individual rows and extract specific field values.
2. Customization Points:
   1. Replace RelatedFieldName with the name of the data extension field containing JSON.
   2. Replace YourFieldName with the specific JSON field you want to extract.
   3. Modify the conditions (Condition1, Condition2, etc.) and URLs to match your business logic.

The script iterates through each row in the JSON data, extracts the required field, and performs actions (e.g., assigns URLs) based on the field's value.