How to Parse JSON and Upsert Data?

Question

I’m trying to parse JSON data stored in a field (Product2) in a Salesforce Marketing Cloud Data Extension (DE) and then upsert the parsed data into another DE. I have written a Server-Side JavaScript (SSJS) script to retrieve the JSON from each row, parse it using Platform.Function.ParseJSON(), extract specific fields (like ProductCode, environment, etc.), and use UpsertData to insert or update the records in the target DE.

However, despite including logging for debugging, the script isn’t successfully parsing the JSON or inserting the data, and I’m not seeing any errors indicating what is going wrong. Can you help identify why the JSON isn’t being parsed or inserted as expected, and suggest a way to resolve the issue?

Here’s the SSJS code I’m working with:

<script runat="server" language="javascript">
Platform.Load("Core", "1");

// Initialize the source DE and retrieve rows
var sourceDE = DataExtension.Init("YourSourceDEName"); // Replace with actual DE name
var rows = sourceDE.Rows.Retrieve();

// Loop through each row to process the JSON data
for (var i = 0; i < rows.length; i++) {
    var id = rows[i].Id; // Assuming Id is the primary key
    var emailAddress = rows[i].EmailAddress;
    var jsonData = rows[i].Product2; // Replace 'Product2' with actual field name containing JSON

    Write("<br>Processing record with Id: " + id + "<br>");

    // Parse the JSON data
    try {
        var json = Platform.Function.ParseJSON(jsonData);

        // Extract values from the JSON object
        var environment = json[0].environment;
        var subCategoryCode = json[0].subCategoryCode;
        var attribute1 = json[0].attribute1;
        var productCode = json[0].ProductCode;
        var attribute2 = json[0].attribute2;
        var attribute3 = json[0].attribute3;
        var attribute4 = json[0].attribute4;
        var attribute5 = json[0].attribute5;
        var attribute6 = json[0].attribute6;
        var environmentCode = json[0].environmentCode;

        // Log extracted values
        Write("<br>Environment: " + environment);
        Write("<br>SubCategory Code: " + subCategoryCode);
        Write("<br>Attribute1: " + attribute1);
        Write("<br>ProductCode: " + productCode);
        Write("<br>Attribute2: " + attribute2);
        Write("<br>Attribute3: " + attribute3);
        Write("<br>Attribute4: " + attribute4);
        Write("<br>Attribute5: " + attribute5);
        Write("<br>Attribute6: " + attribute6);
        Write("<br>Environment Code: " + environmentCode);

        // Perform UpsertData operation
        var targetDE = "YourTargetDEName"; // Replace with actual DE name
        var upsertResult = Platform.Function.UpsertData(
            targetDE, 
            ["Id"], // Primary key field(s)
            [id], // Corresponding values for primary key
            ["EmailAddress", "ProductCode", "Environment", "CategoryCode", "SubCategoryCode", "Attribute1", "Attribute2", "Attribute3", "Attribute4", "Attribute5", "Attribute6", "EnvironmentCode"], 
            [emailAddress, productCode, environment, subCategoryCode, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, environmentCode]
        );

        // Log the result of Upsert operation
        Write("<br>Upsert Result: " + upsertResult);

    } catch (e) {
        Write("<br>Error parsing JSON for Id: " + id + ". Error: " + e + "<br>");
    }
}
</script>

How can I troubleshoot and resolve this issue effectively?

Answer:

When working with JSON parsing in Salesforce Marketing Cloud and performing data operations like upsert, there are a few common issues that can arise, as well as alternative approaches to consider.

1. Possible Issue with JSON Parsing in SSJS:In the SSJS script you provided, you’re using Platform.Function.ParseJSON() to parse the JSON data. While this method works, it may not be efficient or reliable when processing large datasets or when the JSON format is not correctly structured.One potential issue could be with the structure of the JSON itself. If the JSON data is not well-formed or does not match the expected structure (e.g., an array or object), the parsing will fail or return null. In your case, you’re accessing the first element (json[0].environment), so if the JSON is not properly structured, this would cause issues.
2. Using SQL as an Alternative Approach:A more efficient approach would be to use SQL, which has built-in functions like JSON_VALUE to easily parse JSON fields and extract specific values. SQL performs better than SSJS, especially when handling large datasets, and you can also avoid some of the limitations of SSJS, such as row retrieval limits (2500 rows at a time).Here’s an example of how you can retrieve and parse JSON using SQL, assuming your Product2 field contains a JSON array like this:

[{"related_attribute1":"2023-04-01T00:00:00.000Z","related_attribute2":"25280722","related_attribute3":"25280722"}]

The SQL query would look like this:

SELECT
     JSON_VALUE(RelatedAttributes, '$[0].related_attribute1') AS RelatedAttr1,
     JSON_VALUE(RelatedAttributes, '$[0].related_attribute2') AS RelatedAttr2,
     JSON_VALUE(RelatedAttributes, '$[0].related_attribute3') AS RelatedAttr3
FROM 
YOUR_DATA_EXTENSION

This method is simpler and more efficient for handling large amounts of data. It also scales better and ensures better performance compared to SSJS

3. Upsert Data Using SQL:After parsing the JSON data with SQL, you can easily use the Upsert operation to insert or update records in the target Data Extension based on the extracted fields. This can be done with an INSERT or UPDATE statement in SQL, depending on the data structure in your target DE.

4. Tips for Debugging and Improving SSJS:If you decide to continue with SSJS for smaller datasets, make sure to:

• Validate the structure of the JSON field before parsing it.
• Add additional logging to check if the JSON is well-formed and if each field is being correctly parsed.
• Ensure that the UpsertData operation is executed correctly by validating the parameters (field names and values).
• Consider limiting the number of records fetched or retrieved to ensure the script runs within the platform’s limits (2500 rows).

In conclusion, using SQL to parse JSON data in Salesforce Marketing Cloud provides a more efficient, reliable, and scalable solution compared to using SSJS, especially when dealing with large datasets. However, if SSJS is necessary for your use case, ensure that the JSON data is correctly structured and use debugging logs to troubleshoot any issues.

Job-Oriented Salesforce Training with 100% Money Back Assurance

Our Salesforce Course is designed to provide a thorough understanding of the Salesforce platform, equipping you with the essential skills to thrive in the CRM industry. The curriculum includes vital modules such as Salesforce Admin, Developer, and AI, combining foundational knowledge with hands-on practice. By engaging in real-world projects and assignments, you’ll develop the expertise to address complex business challenges using Salesforce solutions. Our expert instructors ensure you gain both technical skills and industry insights necessary for success in the Salesforce ecosystem.

Along with technical knowledge, our Top 10 Salesforce training institutes in Hyderabad offers personalized mentorship, exam preparation, and interview coaching to enhance your career prospects. You’ll have access to comprehensive study materials, live project experience, and dedicated support throughout your learning journey. Upon completing the course, you’ll be well-prepared for certification exams and equipped with the practical skills employers value. Begin your Salesforce career with us and unlock countless career opportunities. Sign up for a Free Demo today!