How Do I Break Down a JSON Array Format CSV Column Into Individual Columns Within SFMC Data Extensions?

Question

I have an interesting use-case where I am getting a Data Extension from CDP where one of the columns is in JSON Format.

Now I want to break down the last column of this DE into its individual columns. Note that one subscriber key can have multiple rows hence they need to be added as individual entries post the breakdown of the JSON Array.

The final DE should look something like this:

Now my approach is to write an SSJS Script in Automation Studio to solve this purpose.

Here's my code which doesn't work and throws an error. I need the community's guidance to help solve this use case.

Code:

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

// Define the Data Extension external keys
var inputDEExternalKey = "your_input_data_extension_external_key";
var outputDEExternalKey = "your_output_data_extension_external_key";

// Create a DataExtensionObject for reading the input Data Extension
var inputDE = DataExtension.Init(inputDEExternalKey);
var inputData = inputDE.Rows.Retrieve();

// Create a DataExtensionObject for writing to the output Data Extension
var outputDE = DataExtension.Init(outputDEExternalKey);

// Define the field names for the output Data Extension
var outputFields = ["SubscriberKey", "Id", "Email", "UserDrugProduct", "drug_transformed_qty", "number_of_days_refill_renewal", "drug_name", "LastProduct"];

// Create an array to store the rows in the desired format
var outputRows = [];

// Process the data from the input Data Extension
for (var i = 0; i < inputData.length; i++) {
    var row = inputData[i];
    var subscriberKey = row.SubscriberKey;
    var id = row.Id;
    var email = row.Email;
    var renewalData = Platform.Function.ParseJSON(row.Renewal_Campaign_Active_w_Datecpy);

    if (renewalData && renewalData.length > 0) {
        for (var j = 0; j < renewalData.length; j++) {
            var item = renewalData[j];
            var userDrugProduct = item.UserDrugProduct;
            var drugTransformedQty = item.drug_transformed_qty;
            var daysRefillRenewal = item.number_of_days_refill_renewal;
            var drugName = item.drug_name;
            var lastProduct = item.LastProduct;

            // Create a new row with the extracted data
            var newRow = [subscriberKey, id, email, userDrugProduct, drugTransformedQty, daysRefillRenewal, drugName, lastProduct];
            outputRows.push(newRow);
        }
    }
}

// Write the data to the output Data Extension
outputDE.Rows.Add(outputRows);

Write("<br>Conversion completed. Data saved to " + outputDEExternalKey);
</script>

Answer 1

I think likely the big issue you ran into is that you were storing your newRow as an array instead of an object, which is what Rows.Add requires. Might explore trying it with outputRows being an array of objects instead and see if that solves your error. Something like:

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

// Define the Data Extension external keys
var inputDEExternalKey = "your_input_data_extension_external_key";
var outputDEExternalKey = "your_output_data_extension_external_key";

// Create a DataExtensionObject for reading the input Data Extension
var inputDE = DataExtension.Init(inputDEExternalKey);
var inputData = inputDE.Rows.Retrieve();

// Create a DataExtensionObject for writing to the output Data Extension
var outputDE = DataExtension.Init(outputDEExternalKey);

// Define the field names for the output Data Extension
var outputFields = ["SubscriberKey", "Id", "Email", "UserDrugProduct", "drug_transformed_qty", "number_of_days_refill_renewal", "drug_name", "LastProduct"];

// Create an array to store the rows in the desired format
var outputRows = [];

// Process the data from the input Data Extension
for (var i = 0; i < inputData.length; i++) {
    var row = inputData[i];
    var subscriberKey = row.SubscriberKey;
    var id = row.Id;
    var email = row.Email;
    var renewalData = Platform.Function.ParseJSON(row.Renewal_Campaign_Active_w_Datecpy);

    if (renewalData && renewalData.length > 0) {
        for (var j = 0; j < renewalData.length; j++) {
            var item = renewalData[j];
            var userDrugProduct = item.UserDrugProduct;
            var drugTransformedQty = item.drug_transformed_qty;
            var daysRefillRenewal = item.number_of_days_refill_renewal;
            var drugName = item.drug_name;
            var lastProduct = item.LastProduct;

            // Create a new row with the extracted data
            var newRow = {};
            newRow[outputFields[0]] = subscriberkey;
            newRow[outputFields[1]] = id;
            newRow[outputFields[2]] = email;
            newRow[outputFields[3]] = userDrugProduct;
            newRow[outputFields[4]] = drugTransformedQty;
            newRow[outputFields[5]] = daysRefillRenewal;
            newRow[outputFields[6]] = drugName;
            newRow[outputFields[7]] = lastProduct;
            outputRows.push(newRow);
        }
    }
}

// Write the data to the output Data Extension
outputDE.Rows.Add(outputRows);

Write("<br>Conversion completed. Data saved to " + outputDEExternalKey);
</script>

As you will notice, I got a little 'fancy' and used your outputFields array to name the keys of the object (newRows) - but if you want it a bit simpler you can just manually name them instead.

And also as a note, the Rows.Retrieve() will max out at 2500 rows, so be aware if you have more than that, you will need to adjust your method to account for that limitation.