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

  • Just to make sure I understand, and using the same SQL reference you used in your example - you have your WHERE clause to select the right records, now you just want to control which fields are returned back as you do not want to return all the records that you received in your lookup. E.g. DE has Pkey | Field 1 | Field 2 and you only want to return Pkey | Field 1, right? Jan 4, 2022 at 12:56
  • Yes, that's correct
    – Ruskers
    Jan 4, 2022 at 20:40

2 Answers 2


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:


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>

  //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 = [
      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)) {

    if (hasDontEnumBug) {
      for (i = 0; i < dontEnumsLength; i++) {
        if (hasOwnProperty.call(obj, 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) {




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.

  • 1
    Thank you, this works perfectly
    – Ruskers
    Jan 4, 2022 at 21:22

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 = [
    , "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;

      fieldObj = '';

  } catch(e4) {

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

  return fieldArray;
  • Thanks Adam, have used the version above as the final solution but thanks for the help all the same.
    – Ruskers
    Jan 4, 2022 at 21:22

You must log in to answer this question.

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