How to Parse JSON in Salesforce Marketing Cloud in 4 ways (including the new BuildRowsetFromJSON)?

A breakdown of the most commonly requested topic in the SFMC community.

How to Parse JSON in Salesforce Marketing Cloud in 4 ways (including the new BuildRowsetFromJSON)?
Photo by Tingey Injury Law Firm / Unsplash

How to Parse JSON in SFMC?” has been one of the most commonly asked queries in the SFMC online community group. It’s a common challenge that many developers and admins face when trying to parse JSON data that feeds either in an Email, SMS, CloudPages or Script Activity in an Automation.

In this post, we will explore different methods to parse JSON in SFMC and simplify the challenge:

1️⃣ Server-Side JavaScript (SSJS):

Our most preferred method to parse JSON is using Server Side JavaScript (SSJS) because it is a general-purpose programming language (with an exception to SFMC core library functions). It provides developers with complete control over the JSON parsing process and allows for more complex logic to be implemented.

While it may be our most preferred option, it has a steep learning curve if you’re not familiar with native JavaScript, specifically the ECMA 3 standard released in 1999.

If you’re keen to review the specs document for the ECMAScript Edition 3, the link is here.

📝 For Example:

🔥 Output:

Name: Evil Morty
Species: Human
Gender: Male

2️⃣ Guide Template Language (GTL):

Another method uses the infamous GTL which is a templating language that simplifies the process of rendering JSON data. GTL leverages the widely adopted Handlebars and Mustache template languages however, it provides the data instead of scripts to provide personalized content in SFMC.

Simply, GTL provides syntax to map dynamic content (html, text) against the JSON data which can personalize the message or extract data from JSON.

With complex documentation and a steep learning curve, GTL still has its usefulness, mostly around parsing JSON with basic nested loops but in all honesty, it is better to learn Server-side JavaScript instead as it has a multitude of other uses that make the language much more valuable than GTL. In short, GTL only has a single use, parsing a JSON for use in AMPScript.

Gortonington has published a fun article that covers the origin, capabilities and use case for GTL — link here.

📝 For Example:

🔥Output:

Name: Not Human
Company: Altus Digital
Title: Intern SFMC Developer
Email: [email protected]

3️⃣ BuildRowsetFromString (AMPScript):

The most popular programmatic script AMPScript didn’t have a native capability to resolve JSON until the new function was introduced by Salesforce*.

The BuildRowsetFromString() function returns a rowset to extract the key and value pairs by splitting apart JSON using a delimiter string.

Amongst all methods, this one is probably the most averse option for us since JSON needs to be treated as a string. The most common way is to split the JSON apart and then build a rowset that can be looped to output the values.

*Refer to 4️⃣ for the newest AMPScript function to parse JSON. 🥳

📝 For Example:

🔥 Output

name: Not Human
company: Altus Digital
title: Intern SFMC Developer
email: [email protected]

4️⃣ [New] BuildRowsetFromJSON (AMPScript):

Lastly, a new function BuildRowsetFromJSON() was introduced in the Summer release 23 on June. This new addition complements the existing BuildRowSetFromXML and BuildRowSetFromString functions.

Introduction

The BuildRowsetFromJSON()function allows users to parse JSON data into a rowset, a format that’s more manageable within the platform. It can now interact with a JSON object or array more efficiently and elegantly than before, eliminating the need to use SSJS or splitting JSON into strings.

Why was it released?

  • General Utility: It can be used to parse JSON data sources like third-party APIs, JSON payloads from webhook, or even JSON stored within SFMC such as in Data Extensions.
  • Developer Efficiency: The function significantly improves developer efficiency by eliminating the need to switch to other languages or use cumbersome methods to parse JSON making it more readable and concise. Nonetheless, we still prefer SSJS for complex CloudPage and Script Activity implementations like custom preference center, interactions with SOAP Objects in SFMC, etc.
  • Salesforce Data Cloud Adoption: Besides Salesforce quoting in the release article “use the function to get related attributes from Data Cloud our assumption has also been influenced by Salesforce’s relentless promotion of Data Cloud to bring adoption and cognition in the Salesforce ecosystem.

How Does It Work?

The BuildRowSetFromJSON function takes three arguments that follow the syntax: BuildRowSetFromJSON(1, 2, 3)

  1. JSON String: source JSON data that you want to parse.
  2. JSONPath argument: a query language for JSON that allows you to select nodes in a JSON document, specifying the data that you want to extract. (Note: The property currently does not have the ability to filter JSON objects.)
  3. Boolean value: determines whether to return an empty rowset if an error occurs. If set to 1, it will return an empty rowset on error. (Note: The property did not work as described in the help article.)

📝 For Example (Simple JSON):

🔥 Output

Name: Not Human
Company: Altus Digital
Title: Intern SFMC Developer
Email: [email protected]

📝 For Example (Complex JSON):

🔥 Output

Name: AD Robot
Company: Altus Digital
Title: Intern SFMC Developer
Email: [email protected]

Certifications: 2
Salesforce Marketing Cloud Developer - Salesforce - 2023
Salesforce Marketing Cloud Consultant - Salesforce - 2023

Hobbies: 
Coding
Watching Rick and Morty
Posting Memes

👉 Verdict:

The release of the BuildRowsSetFromJSON function has expanded the options to parse JSON in SFMC making the BuildRowsetFromString function redundant to parse JSON since it dramatically reduces the effort and simplifies the solution. The BuildRowsSetFromJSON function will become the top choice for developers looking to parse JSON for simple implementations however for complex implementations, SSJS still prevails.