Filtering fields for a JSON output in SSJS

Question

I'm building a SSJS custom API endpoint as a code resource page and have the following code which works fine but I need to limit the fields that are returned from the Data Extension and then placed in the JSON output - it currently returns all fields in the datarows and hence all fields are produced in the JSON array under Data Returned - that's just a dummy name for now.

I can't find a way to do this in the retrieve process, i.e. akin to a SELECT [certain fields only] FROM and am not sure how such a request might be crafted using a loop on the datarows results.

Any ideas / help - and ideally a working example of a loop if possible - would be much appreciated.

var JsonOut;

//initiate DE using it's External Key
var My_DE = DataExtension.Init("EXTERNAL KEY HERE");

//define filter criteria
var filter1 = {
    Property: "Event_Active",
    SimpleOperator: "equals",
    Value: "True"
  };

  var filter2 = {
    Property: "Event_Date",
    SimpleOperator: "greaterThan",
    Value: "1 January 2022"
  };  

  var complexFilter = {
      LeftOperand: filter1,
      LogicalOperator: "AND",
      RightOperand: filter2
    }

//retrieve data
var dataRows = My_DE.Rows.Retrieve(complexFilter);
if (dataRows.length > 0){

//build the return JSON
JsonOut = {
    "Result" : "200 - Success", 
    "Task:" : "Extract",
    "ActiveAfterDate:" : "1 January 2022",
    "Data Returned" : Platform.Function.ParseJSON(Stringify(dataRows))
}

//else conditions etc removed

Write(Platform.Function.Stringify(JsonOut));

Answer 1

So, my initial thought was similar to Adam Spriggs, but I then thought about that it had potential to be a lot of work if you had multiple DEs that might have different fields you need or you only know the fields you need and might not know the ones you need to remove.

To that extent, I did some research and saw the two methods we needed to make this easy, but of course they are not native to SFMC SSJS:

Object.keys()
Object.entries()

The good news is that each one includes a polyfill (or way to create these methods if they are not included in your current JS version) so by adding those in to the top of our script, we can still use this method to get you the results you want.

See below for a sample script:

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

  //vars to fill for script to run
  var fieldArr = ["SubscriberKey","FirstName"]
  var srcDE = "mySourceDE"
  
  //filters for retrieve
  var filter1 = {
    Property: "Event_Active",
    SimpleOperator: "equals",
    Value: "True"
  };

  var filter2 = {
    Property: "Event_Date",
    SimpleOperator: "greaterThan",
    Value: "1 January 2022"
  };  

  var myFilter = {
      LeftOperand: filter1,
      LogicalOperator: "AND",
      RightOperand: filter2
  }


  //polyfill for Object.keys

  Object.keys = (function() {
  'use strict';
  var hasOwnProperty = Object.prototype.hasOwnProperty,
      hasDontEnumBug = !({ toString: null }).propertyIsEnumerable('toString'),
      dontEnums = [
        'toString',
        'toLocaleString',
        'valueOf',
        'hasOwnProperty',
        'isPrototypeOf',
        'propertyIsEnumerable',
        'constructor'
      ],
      dontEnumsLength = dontEnums.length;

  return function(obj) {
    if (typeof obj !== 'function' && (typeof obj !== 'object' || obj === null)) {
      throw new TypeError('Object.keys called on non-object');
    }

    var result = [], prop, i;

    for (prop in obj) {
      if (hasOwnProperty.call(obj, prop)) {
        result.push(prop);
      }
    }

    if (hasDontEnumBug) {
      for (i = 0; i < dontEnumsLength; i++) {
        if (hasOwnProperty.call(obj, dontEnums[i])) {
          result.push(dontEnums[i]);
        }
      }
    }
    return result;
   };
  }());
  
  //Object.entries polyfill
  Object.entries = function( obj ){
    var ownProps = Object.keys( obj ),
        i = ownProps.length,
        resArray = new Array(i); // preallocate the Array
    while (i--)
      resArray[i] = [ownProps[i], obj[ownProps[i]]];

    return resArray;
  };
  
  var newArr = []
  
  var myDE = DataExtension.Init(srcDE);
  var data = myDE.Rows.Retrieve(myFilter);

  for(i=0;i<data.length;i++) {
    var newObj = {}
    var obj = data[i]
    var resArr = Object.entries(obj)
  
    for (a=0;a<resArr.length;a++) {
      var key = resArr[a][0];
      var val = resArr[a][1]
    
      for (x=0;x<fieldArr.length;x++) {
        if (fieldArr[x] == key) {
          newObj[key] = val
        }
      }
    }
    
    if (newObj.length > 0) {
      newArr.push(newObj)
    }

  }

  Write(Stringify(newArr));

</script>

Now the polyfills can likely be trimmed down to be more efficient as not all of what is in there is capable to be used in SFMC SSJS and such, but this works and will let you choose which fields you want to keep (via fieldArray), the de you want to target (via srcDE), and the filter you want to put on the retrieve (via myFilter) and the rest will be done programmatically.

Answer 2

Why not just use the JS delete operator to remove the properties in the result you don't want? It does work in SFMC's SSJS.

I've used when retrieving and DataExtensionField objects in order to recreate them in another DataExtension.

Here's an example in my getDEFields function:

function getDEFields(customerKey) {

  var cols = [
      "CustomerKey"
    , "Name"
    , "DefaultValue"
    , "MaxLength"
    , "IsRequired"
    , "Ordinal"
    , "IsPrimaryKey"
    , "FieldType"
    , "Scale"
    , "Client.ID"
    , "DataExtension.CustomerKey"
    , "StorageType"
  ];

  var filter = {
      Property: "DataExtension.CustomerKey",
      SimpleOperator: "equals",
      Value: customerKey
  };

  var fieldArray = [];

  try {

    var deFields = prox.retrieve("DataExtensionField", cols, filter);
    var deFieldArray = deFields.Results;

    for (var a = 0; a < deFieldArray.length; a++) {

      var fieldObj = deFields.Results[a]

      // Fields that need to be removed prior to creation of new DE

      if (fieldObj.MaxLength == "" || fieldObj.MaxLength == 0) {
        delete fieldObj.MaxLength;
      }

      delete fieldObj.DataType;
      delete fieldObj.IsCreatable;
      delete fieldObj.IsUpdatable;
      delete fieldObj.IsRetrievable;
      delete fieldObj.IsQueryable;
      delete fieldObj.IsFilterable;
      delete fieldObj.IsPartnerProperty;
      delete fieldObj.IsAccountProperty;
      delete fieldObj.PartnerMap;
      delete fieldObj.Markups;
      delete fieldObj.Label;

      if (fieldObj.MinLength == "" || fieldObj.MinLength == 0) {
        delete fieldObj.MinLength;
      }

      delete fieldObj.CreatedDate;
      delete fieldObj.ModifiedDate;
      delete fieldObj.IsRestrictedPicklist;
      delete fieldObj.PicklistItems;
      delete fieldObj.IsSendTime;
      delete fieldObj.DisplayOrder;
      delete fieldObj.References;
      delete fieldObj.RelationshipName;
      delete fieldObj.Status;
      delete fieldObj.IsContextSpecific;
      delete fieldObj.PartnerProperties;

      fieldArray.push(fieldObj);
      fieldObj = '';
    }

  } catch(e4) {

    if (debug) {Write("<br>e4: " + Stringify(e4))}
    return [];

  }
  return fieldArray;
}