1

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

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: SS2

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>
2
  • What is the error you get? Sep 21, 2023 at 18:55
  • @Gortonington, it is a runtime error
    – aryashah2k
    Sep 22, 2023 at 2:51

1 Answer 1

2

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.

3
  • I tried the above workaround, the script doesn't throw the same error as before but the Output DE does not get populated with the broken down individual records either. I am unable to understand the cause for this. The output DE, for which I pass an external key in the above code, still has 0 records after script run successfull
    – aryashah2k
    Sep 22, 2023 at 3:01
  • 1
    @aryashah2k - Run it on a couldpage and write the outputRows variable to the page to make sure its built the right way for Rows.Add to accept. Also I would wrap it in a try{...} catch(e) { } function to get better error debugging and handling. Sep 22, 2023 at 18:56
  • Sure, let me give it a try. I'm still finding a better solution to solve this use case effectively : )
    – aryashah2k
    Sep 23, 2023 at 16:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .