ACC | How to Leverage queryDef in Adobe Campaign

When it comes to programatically selecting records from the database in Adobe Campaign Classic, queryDef emerges as a crucial tool. As a static SOAP method extensively employed in JavaScript, particularly within workflows or web apps, queryDef offers unparalleled capabilities. However, what many may not realize is that there are three distinct implementations of this function. Join us as we embark on a journey to explore and compare all three implementations, shedding light on their respective strengths and identifying the ultimate choice for maximizing your Adobe Campaign experience.

The older version uses XML for creating the query definitions. You can take a look at the official data oriented adobe campaign API documentation.

var query = xtk.queryDef.create(
  <queryDef schema="xtk:workflow" operation="select">
    <select>
      <node expr="@internalName"/>
    </select>
  </queryDef>
)

var res = query.ExecuteQuery()

for each (var w in res.workflow)
  logInfo(w.@internalName)

Options that can be used with queryDef are listed below:

<queryDef schema="schema_key" operation="operation_type">
  <select>
    <node expr="expression1"/>
    <node expr="expression2"/>
  </select>
  <where>
    <condition expr="expression1"/>
    <condition expr="expression2"/>
  </where>
  <orderBy>
    <node expr="expression1"/>
    <node expr="expression2"/>
   
  </orderBy>
  <groupBy>
    <node expr="expression1"/>
    <node expr="expression2"/>
    
  </groupBy>
  <having>
    <condition expr="expression1"/>
    <condition expr="expression2"/>
 
  </having>
</queryDef>

Additionally you can use subqueries in the where conditions:

<condition setOperator="AND">
  <subQuery schema="xtk:recipient">
    <select>
      <node expr="[@recipient-id]"/>
    </select>
    <where>
      <condition setOperator="OR">
        <condition expr="[@status]='active'"/>
        <condition expr="[@status]='pending'"/>
      </condition>
    </where>
  </subQuery>
  <condition setOperator="AND">
    <condition expr="[@age]&gt;=18"/>
    <condition expr="[@country]='USA'"/>
  </condition>
</condition>

The function offers a high level of versatility, allowing you to define and execute queries similar to those created in the query editor. A useful tip is to first create your query in the query editor and then extract the XML source code of the query section. This XML code can be directly copied into the queryDef XML structure, enabling you to effortlessly create and test complex queries. This streamlined approach empowers you to leverage the full potential of queryDef and efficiently handle various query scenarios.

var query = xtk.queryDef.create(
  <queryDef schema="nms:deliveryLog" operation="select">
    <select>
      <node expr="@id"/>
      <node expr="@eventName"/>
      <node expr="@eventDate"/>
      <node expr="[@messageId]"/>
    </select>
    <where>
      <condition expr="[eventDate]&gt;=date('2022-01-01')" />
    </where>
    <orderBy>
      <node expr="@eventDate" sortDesc="true" />
    </orderBy>
  </queryDef>
);

var res = query.ExecuteQuery();

for each (var log in res.deliveryLog) {
  var logId = log.@id;
  var eventName = log.@eventName;
  var eventDate = log.@eventDate;
  var messageId = log[@messageId];
  
  logInfo("Log ID: " + logId);
  logInfo("Event Name: " + eventName);
  logInfo("Event Date: " + eventDate);
  log

In the provided code snippet, you may observe the usage of a special function called ‘for each’ which is specific to Adobe Campaign. This function allows you to iterate and traverse over data structures, such as result sets, in a convenient manner.

As the programming world gradually shifts away from XML and embraces JSON, it’s worth noting that the new implementation of xtkQueryDef in Adobe Campaign Classic allows you to work with JXON (JSON/XML Object Notation). This means you can leverage the flexibility and simplicity of JSON while still utilizing the power of xtkQueryDef. Embracing JXON provides a modern approach to querying data, aligning with the industry trend and offering enhanced capabilities for data manipulation.

JXON xtkQueryDef function, being based on the same features as XML one, empowers you to create virtually any query you would with the XML queryDef.

var query = NLWS.xtkQueryDef.create({
  queryDef: {
    schema: "nms:recipient",
    operation: "select",
    select: {
      node: [
        { expr: "@id" },
        { expr: "@firstName" },
        { expr: "@lastName" }
      ]
    },
    where: {
      condition: [
        { expr: "[@country]='USA'" },
        { expr: "[@age]>=18" }
      ]
    },
    orderBy: {
      node: { expr: "@lastName", sortDesc: "false" }
    }
  }
});

var res = query.ExecuteQuery();
var profiles = res.getElementsByTagName("recipient");

for each (var profile in profiles) {
  var firstName = profile.getAttribute("firstName");
  var lastName = profile.getAttribute("lastName");
  logInfo("Profile: " + firstName + " " + lastName);
}

While the XML version of query def is currently available, it is considered the older approach for querying data. It’s recommended to focus on the JXON version since XML support may be phased out in the future. However, it’s worth noting that this change is expected to happen in the distant future. Ultimately, the choice between XML and JXON depends on your personal preference and the simplicity of usage. Personally, I find the ‘NLWS.xtkQueryDef’ function simpler to use with XML queries rather than JXON. But again, the decision is yours to make based on your specific requirements and preferences.

It’s also important to note that both versions ultimately return XML data. In Adobe Campaign Classic, specific functions are available to handle XML payloads, enabling you to extract, manipulate, and process the returned XML data efficiently.

Last but certainly not least, there is another variant of the queryDef function that I find particularly fascinating. While delving into the core JavaScript libraries of Adobe Campaign, I came across an undocumented function that instantly captured my interest. What sets this humble function apart is its ability to return the query results in JSON format, allowing you to effortlessly process and manipulate the data using native JavaScript functions. Despite the absence of official documentation, this variant offers the same powerful features as the previously mentioned queryDef functions, while providing the added advantage of JSON compatibility.

You can find the entire implementation under JavaScirpt libraries

• xtk:queryDef.js

To begin using the queryDef.js library, you need to include it in your project and ensure that its dependencies are also included. The library requires the following dependencies:

  loadLibrary('xtk:shared/nl.js');
  NL.require('xtk:queryDef.js');

To create a query, you need to instantiate the NL.QueryDef object. The constructor takes several parameters that define the query:

• schema: The schema on which the query will be executed.
• settings: Additional settings for the query (optional).
• selectExpr: An array of select expressions.
• whereExpr: An array of where conditions.
• orderByExpr: An array of order by expressions.
• conditionLinkExpr: An array of condition links.

Here’s an simple example of how to create a query definition and process results:

var selectExpr = [{expr: "@status"}, {expr:"[operation/@label]", alias:"@campaignLabel"}],
    whereExpr  = {expr: "@internalName = 'DM150'"},
    nlQueryDef = new NL.QueryDef("nms:delivery", selectExpr, whereExpr),
    result = nlQueryDef.create().execute().data;
if (result.length>0){
	result.forEach(function(e,i){//you can also use good old for(;;)
    	logInfo(e.status)
        logInfo(e.campaignLabel)
        
    });
}

The constructor takes several parameters that define the query:

• 'schema‘: The schema on which the query will be executed.
• ‘settings‘: Additional settings for the query (optional).

    var _defaults = {
      lineCount:    200,                          // The number of lines to retrieve
      operation:    NL.QueryDef.OPERATION_SELECT, // Operation type
      expandParam:  true,                         // True to handle enabledIf conditions in the query
      startLine:    0,                            // Start offset
      firstRows:    true                          // True to force indexes
    };

• ‘selectExpr‘: An array of select expressions.
• ‘whereExpr‘: An array of where conditions.
• ‘orderByExpr‘: An array of order by expressions.
• ‘conditionLinkExpr‘: An array of condition links.

When selecting linked records in certain scenarios, if we don’t use an ‘alias’, the attribute will be returned as an XML string. This means that instead of directly accessing the attribute value, we would need to parse and handle the XML structure to extract the desired information.

To avoid the complexity of dealing with XML, we can employ an ‘alias’. By using an ‘alias’, the attribute value will be returned directly as a regular data type (such as a string, number, or boolean), making it easier to work with and manipulate in our code.

Using an ‘alias’ simplifies the process of accessing and utilizing linked record attributes, as we can directly access the attribute value without having to handle XML parsing and manipulation.

On example below you can see the use of all arguments fot the function.

var nlQueryDef = new NL.QueryDef(
  "nms:delivery",
  {
    lineCount: 100,
    operation: NL.QueryDef.OPERATION_GET,
    expandParam: false,
    startLine: 50,
    firstRows: false
  },
  [
    { expr: "@status" },
    { expr: "@status", alias: "@toto" }
  ],
  { expr: "@internalName = 'DM150'" },
  { expr: "@columnName", sortDesc: true },
  [
    {
      alias: "link1",
      xpath: "/path/to/link1",
      schema: "nms:schema1",
      enabledIf: "@condition1"
    },
    {
      alias: "link2",
      xpath: "/path/to/link2",
      schema: "nms:schema2",
      enabledIf: "@condition2",
      whereExpr: [
        { expr: "@columnName = 'value1'" },
        { expr: "@columnName = 'value2'" }
      ]
    }
  ]
);

When going over this library you may notice it is using the NLWS.xtkQueryDef SOAP function. This means all the features from it can be used.

Use NL.QueryDef to create XML conditions from JSON by that can be used in changing of any XML object.

//var confitions = [{expr: "@internalName = 'DM600500'"}];
//var schema = "nms:seedAddress"
var getWhereConditionsFromJson = function(conditions, schema){
    var nlQueryDef = new NL.QueryDef(schema, [{expr:"@id"}], []); // does not really matter what is put here, as we only want to use its createWhereCondition method
          var root = <condition/>;
          //another good find, deep inside the adobe campaign JS libraries, NL.Utils.each function. 
          //It does exaclty the same as native JS Array.forEach() 
          //I did not know that time the ES5 has it 
          //also for oldtimers for(;;) can be always used :)
          NL.Utils.each(conditions, function(idx, condition) {
            root.appendChild(nlQueryDef.createWhereCondition(condition));
          });
    logInfo(root.toXMLString());
    return root;
  },

Now that I have shown you three different ways to use the same SOAP function, it’s up to you to decide which one you prefer. Personally, I would recommend using the last option, ‘NL.QueryDef’. This method utilizes JSON as the input format and also transforms the XML result into JSON. This can make the process more streamlined and consistent, as both the input and output formats are in JSON, allowing for easier data handling and integration with other systems.