How to batch delete more than 2500 records (based on data extension)

Question

I'm looking to remove refunded transactions from the master transaction table (they have two ID where they join on) so that we don't email people about their transactions when they have already received a refund.

I've discovered that WSProxy lets you pull more than 2500 records at a time, but am struggling to figure out how I can capture those records that it pulls and have it delete matching records in a DE.
I'm new to WSProxy, and have working knowledge of Javascript, I started by filtering out records that have a Refunded Flag, and then followed documentation to work around the 2500 record limit to pull all records that match the filter through leveraging the getNextBatch feature. From there, I attempted deleteBatch to remove any matching records found here back to the main table.
Nothing happens when I run this script so far.

<script runat="server">
Platform.Load("core", "1.1.1");
var config = {
    name: "completed_transaction_details_test",
    cols: ["Transaction_ID", "Transaction_Detail_ID"],
    filter: {
        Property: "Refunded",
        SimpleOperator: "equals",
        Value: "True"
    }
}

var prox = new Script.Util.WSProxy();
var data = prox.retrieve("DataExtensionObject[" + config.name + "]", config.cols, config.filter);



var records = retrieveRecords(config);
  

  function retrieveRecords(config) {
  
  var prox = new Script.Util.WSProxy();

  var records = [],
      moreData = true,
      reqID = data = null,
      numItems = 0;

  while (moreData) {

      moreData = false;

      if (reqID == null) {
          data = prox.retrieve("DataExtensionObject[" + config.name + "]", config.cols, config.filter);
      } else {
          data = prox.getNextBatch("DataExtensionObject[" + config.name + "]", reqID);
      }

      if (data != null) {
          moreData = data.HasMoreRows;
          reqID = data.RequestID;
          for (var i = 0; i < data.Results.length; i++) {
              var result_list = data.Results[i].Properties;
              var obj = {};
              for (k in result_list) {
                  var key = result_list[k].Name;
                  var val = result_list[k].Value
                  if (key.indexOf("_") != 0) obj[key] = val;
                  numItems++
              }
        

      records.push(obj);
              }
          }
      }
      return records;
  }


  }

var deletedItems = prox.deleteBatch("completed_transaction_details_test", records);
  

</script>

Answer 1

You need to do the delete batch inside the while loop. You also need an array to house the objects that is emptied on each run. To allow you to have the full array as well as the delete array, I added a new var in for you.

Something like below should work. Keep in mind, it is likely to choke out on significant volume due to the 30 min timeout of Script Activities.

<script runat="server">
Platform.Load("core", "1.1.1");
var config = {
    name: "completed_transaction_details_test",
    cols: ["Transaction_ID", "Transaction_Detail_ID"],
    filter: {
        Property: "Refunded",
        SimpleOperator: "equals",
        Value: "True"
    }
}

var records = retrieveRecords(config);
  

  function retrieveRecords(config) {
  
    var prox = new Script.Util.WSProxy();

    var records = [],
        moreData = true,
        reqID = data = null,
        numItems = 0;

    while (moreData) {

        moreData = false;

        if (reqID == null) {
            data = prox.retrieve("DataExtensionObject[" + config.name + "]", config.cols, config.filter);
        } else {
            data = prox.getNextBatch("DataExtensionObject[" + config.name + "]", reqID);
        }

        if (data != null) {
            var arr = []
            moreData = data.HasMoreRows;
            reqID = data.RequestID;
            for (var i = 0; i < data.Results.length; i++) {
                var result_list = data.Results[i].Properties;
                var obj = {};
                for (k in result_list) {
                    var key = result_list[k].Name;
                    var val = result_list[k].Value
                    if (key.indexOf("_") != 0) obj[key] = val;
                    numItems++
                }
              records.push(obj);
              arr.push(obj)
            }
            var deletedItems = prox.deleteBatch("completed_transaction_details_test", arr);
        }
    }
        return records;
  }
  

</script>