Parsing JSON IN Data Extension - SSJS

Question

I'm trying to parse JSON data stored in a field (Product2) within a Salesforce Marketing Cloud Data Extension and then upsert the parsed data into another Data Extension. I've written a Server-Side JavaScript (SSJS) script that retrieves the JSON from each row, parses it using Platform.Function.ParseJSON(), extracts specific fields (like ProductCode, environment, etc.), and uses UpsertData to insert or update the records in the target Data Extension. Despite including logging for debugging, the script isn't successfully parsing the JSON or inserting the data, and I'm not seeing any errors that indicate what's going wrong. What could be causing this issue, and how can I ensure the JSON is properly parsed and the data is inserted?

SSJS:

    <script runat="server" language="javascript">
    Platform.Load("Core", "1");
    
    // Initialize the source DE and retrieve rows
    var sourceDE = DataExtension.Init("YourSourceDEName"); // Replace with your 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 the 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 the extracted values for debugging
            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 your DE name
            var upsertResult = Platform.Function.UpsertData(
                targetDE, 
                ["Id"], // Primary key field(s) in your DE
                [id], // Corresponding value(s) 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 the Upsert operation
            Write("<br>Upsert Result: " + upsertResult);
    
        } catch (e) {
            Write("<br>Error parsing JSON for Id: " + id + ". Error: " + e + "<br>");
        }
    }
    
    </script>

Answer 1

Since you want to parse JSON from one DE and into another, I will suggest using SQL for this - as it has the built in JSON_VALUE function. Let me give you an example:

Given this JSON in your DE field:

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

You can fetch the attributes 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 is easier to maintain, and will also perform better than SSJS (which is also limited to fetch 2500 rows at a time)