Parsing Related-Attribute in Salesforce Marketing Cloud using SQL – JSON_VALUE

I’d like to share a solution I recently worked on. If you work in Salesforce Marketing Cloud (aka Salesforce Marketing Engagement), you’ve probably struggled to extract the data from the JSON fields at some point too. In Salesforce Marketing Cloud (SFMC), it can be challenging to extract field values when data is pushed from a related-attribute type from CDP.

Below approach helps simplify the process of extracting the data from the JSON field and store them in different fields within a Data Extension.

Example – Extract the value of “Campaign_Name” from the JSON field (Campaign_Details) and store it in another field called Camp_Name in the data extension.

Sample of Campaign_Details field data (JSON)-

			
[
{
  "Campaign_ID": 12345,
  "Campaign_End_Date": "2026-06-30T06:00:00.000Z",
  "Campaign_Name": "Bananas are berries",
  "Campaign_Start_Date": "2026-01-01T12:00:00.000Z",
  "Camapign_points": 60
}
]

		

Traditional Approach: Mostly developers rely on AMPscript or SSJS with BuildRowsetFromJSON inside a Script Activity to parse the JSON and update the Data Extension. While functional, this method can be time‑consuming, harder to maintain, and limited in scalability.

Simplified Approach: Instead, we can leverage SQL Query Activity (using JSON_VALUE function) to directly parse JSON and extract the required field. Assuming the source data extension name “Customer_360_Campaign_data”

Sample query:

			
SELECT JSON_VALUE(Campaign_Details, '$[0].Campaign_Name') 
AS Camp_Name 
FROM [Customer_360_Campaign_data]

Output: Bananas are berries

This solution is cleaner, faster, and avoids the burden of scripting. It also makes ongoing maintenance easier, since the logic is contained within a single query rather than multiple script blocks. By adopting this approach, we can streamline the workflows and ensure data is consistently formatted for downstream use cases such as segmentation, personalization, and reporting.

Share this: