How to retrieve and parse JSON files with AMPscript and SSJS in SFMC

This article showcases how to retrieve and parse JSON files with AMPscript and server-side JavaScript in Salesforce Marketing Cloud.

How times have changed

Being able to parse a JSON file is one of the fundamental pillars of modern web development and API integration.

But, for reasons unknown, this feature was absent in AMPscript, the key personalization language in Salesforce Marketing Cloud.

Until today, the Summer Release ’23!

Let’s have look at how it works and compare it to the server-side JavaScript method.

Before we start

Where do we usually see JSON files in Marketing Cloud? Data Extension records and API response objects!

For the purpose of this article, we are going to emulate the latter. But how? By creating a JSON Code Resource page and sending an HTTP GET request to retrieve its content.

Here are the URL and the content of our page:

https://mySFMCdomain.com/customers.json

{
    "Customers" : [
        {
            "FirstName": "Jon",
            "LastName": "Snow",
            "EmailAddress": "jon.snow@crowmail.com"
        },
        {
            "FirstName": "Silverfox",
            "LastName": "Kristofferson",
            "EmailAddress": "kristofferson@foxmail.com"
        }
    ]
}

Parsing JSON with AMPscript

Parsing a JSON file with AMPscript has never been easier with the new BuildRowsetFromJson method!

The goal is, of course, transform the JSON data into a Rawset that we can later loop through and render its values.

For that, we need to use a special syntax $.MyKey[Value] that tells AMPscript which array of objects we are going to turn into rows.

In the example below, we are targeting the root key of the JSON object called Customers ($.Customers) that contains an array (symbols [ ]) and we are going to retrieve all the objects within this array (symbol *) .

%%[

SET @JSON = HTTPGet("https://mySFMCdomain.com/customers.json", false, 0, @CallStatus)

SET @CustomerRows = BuildRowsetFromJson(@JSON, "$.Customers[*]", 1)

SET @CustomersCount = RowCount(@CustomerRows) 

FOR @i = 1 TO @CustomersCount DO

    SET @CustomerRow = ROW(@CustomerRows, @i)
    SET @FirstName = FIELD(@CustomerRow, "FirstName") 
    SET @LastName = FIELD(@CustomerRow, "LastName") 
    SET @EmailAddress = FIELD(@CustomerRow, "EmailAddress") 

]%%

    <ul>
        <li>%%=v(@FirstName)=%%</li>
        <li>%%=v(@LastName)=%%</li>
        <li>%%=v(@EmailAddress)=%%</li>
    </ul>

%%[ NEXT @i ]%%

Parsing JSON with SSJS

Parsing a JSON with server-side JavaScript is easier but less accessible and comes with a small caveat when it comes to retrieving the JSON file with an HTTP GET call.

The response of the call returns an object of the following structure and contains our JSON data in the shape of a String value.

{
    "Status": 0,
    "Content": "{\r\n \"Customers\" : [\r\n {\r\n \"FirstName\": \"Jon\",\r\n \"LastName\": \"Snow\",\r\n \"EmailAddress\": \"jon.snow@crowmail.com\"\r\n },\r\n {\r\n \"FirstName\": \"Silverfox\",\r\n \"LastName\": \"Kristofferson\",\r\n \"EmailAddress\": \"kriskris@foxmail.com\"\r\n }\r\n ]\r\n}"
}

Therefore, we need to parse the data twice using Platform.Function.ParseJSON function: first to isolate the JSON we are targeting and second, to transform it from a String value to an Object.

<script runat='server'>

    Platform.Load('core', '1');

    var json = HTTP.Get('https://mySFMCdomain.com/customers.json');

    var parsedJSON = Platform.Function.ParseJSON(Stringify(json));
  
    var content = Platform.Function.ParseJSON(parsedJSON.Content);

    var customers = content.Customers;

    for(var k in customers) {

</script>

	<ul>
		<li><ctrl:eval>customers[k].FirstName</ctrl:eval></li>
		<li><ctrl:eval>customers[k].LastName</ctrl:eval></li>
		<li><ctrl:eval>customers[k].EmailAddress</ctrl:eval></li>
	</ul>

<script runat='server'>

    } 
    
</script>

Use case: Open Weather API

Have you ever considered to use the weather conditions in your email personalization?

That becomes easily accessible thanks to the Open Weather API and BuildRowsetFromJson method!

Please first create an account and replace the URL parameters in the endpoint below:

%%[

SET @JSON = HTTPGet("https://api.openweathermap.org/data/2.5/weather?id=000000000&units=metric&appid=123456789123456789", false, 0, @CallStatus)

SET @WeatherRows = BuildRowsetFromJson(@JSON, "$.weather[*]", 1)

SET @WeatherRow = ROW(@WeatherRows, 1)
SET @Weather = FIELD(@WeatherRow, "main")

]%%

%%[IF @Weather == "Rain" THEN]%%

    <p>
		Don't forget your umbrella!
	</p>

%%[ELSE]%%

    <p>
		Throw your ubrella under the bridge!
	</p>

%%[ENDIF]%%

Conclusion

Thanks to the JSON parsing method in AMPscript, API calls can finally be used by the less technical people and it therefore opens a new door for creative expression and fresh ideas.

Don’t be afraid to experiment and good luck out there!

Have I missed anything?

Please poke me with a sharp comment below or use the contact form.