AMPscript does not natively support parsing JSON directly, but you can use SSJS (Server-Side JavaScript) in Salesforce Marketing Cloud (SFMC) to parse the JSON and then pass the parsed data back to AMPscript. This allows you to use the parsed data in your email content or automation.

Steps to Create an AMPscript Rowset from JSON Payload

Here’s a step-by-step guide to creating a rowset from a JSON payload:

1. Use SSJS to Parse the JSON Payload
2. Pass Parsed Data to AMPscript
3. Manipulate the Rowset in AMPscript

1. SSJS: Parse JSON and Create Rowset

Example: JSON Payload

Let’s assume you have a JSON payload like this:

{

  “products”: [

    {“name”: “Shirt”, “price”: 19.99},

    {“name”: “Jeans”, “price”: 39.99},

    {“name”: “Jacket”, “price”: 59.99}

  ]

}

SSJS Code to Parse JSON and Create Rowset

<script runat=”server”>

  Platform.Load(“Core”, “1”);

  // JSON payload

  var jsonPayload = ‘{“products”:[{“name”:”Shirt”,”price”:19.99},{“name”:”Jeans”,”price”:39.99},{“name”:”Jacket”,”price”:59.99}]}’;

  // Parse the JSON string into a JavaScript object

  var jsonObject = Platform.Function.ParseJSON(jsonPayload);

  // Extract the products array

  var products = jsonObject.products;

  // Initialize an empty array to store product data

  var productRows = [];

  // Loop through the products and create a rowset

  for (var i = 0; i < products.length; i++) {

    var product = products[i];

    productRows.push([product.name, product.price]);

  }

  // Convert the productRows array to a rowset

  var productRowset = Platform.Function.CreateObject(“Rowset”);

  for (var j = 0; j < productRows.length; j++) {

    var row = Platform.Function.CreateObject(“Row”);

    Platform.Function.AddObject(row, “Name”, productRows[j][0]);

    Platform.Function.AddObject(row, “Price”, productRows[j][1]);

    Platform.Function.AddObject(productRowset, row);

  }

  // Pass the rowset to AMPscript by writing the result to the output

  var rowsetString = Platform.Function.GetObjectString(productRowset);

  Write(rowsetString);

</script>

Explanation:

• The JSON payload is parsed into a JavaScript object.
• Each product’s name and price are pushed into an array.
• The Rowset is created and populated with each product’s data.
• Finally, the rowset is passed to AMPscript by converting the rowset to a string and writing it to the output.

2. AMPscript: Retrieve and Use the Rowset

Once the rowset is created in SSJS, you can retrieve and use it in AMPscript. To do this, you will need to pass the rowset data from SSJS to AMPscript and then manipulate the rowset in AMPscript.

2.AMPscript Code to Retrieve and Loop Through the Rowset

%%[

  SET @productRowset = TreatAsContent(CloudPagesURL(“YOUR_CLOUD_PAGE_URL”))

  SET @rowCount = RowCount(@productRowset)

  IF @rowCount > 0 THEN

    FOR @i = 1 TO @rowCount DO

      SET @row = Row(@productRowset, @i)

      SET @productName = Field(@row, “Name”)

      SET @productPrice = Field(@row, “Price”)

      OUTPUTLINE(“Product Name: ” & @productName & ” | Price: $” & @productPrice)

    NEXT @i

  ELSE

    OUTPUTLINE(“No products available.”)

  ENDIF

]%%

Explanation:

• TreatAsContent() function is used to pass the rowset from SSJS to AMPscript.
• RowCount() is used to count how many rows were returned.
• Row() and Field() are used to extract the individual values for each product in the rowset (product name and price).
• The FOR loop iterates over the rowset to output each product’s name and price.

3. CloudPages Integration (SSJS + AMPscript)

      Since AMPscript does not directly parse JSON, CloudPages are often used to create the SSJS logic and send the result back to            AMPscript.

• SSJS is used in the CloudPage to make the API call, parse the JSON, and convert it into a rowset.
• AMPscript retrieves this data and displays it in emails or landing pages.

Summary of Key Steps:

1. Parse JSON using SSJS in a CloudPage.
2. Create a rowset from the JSON data in SSJS.
3. Pass the rowset to AMPscript using TreatAsContent().
4. Manipulate and display the rowset data in your email or landing page using AMPscript.