Adobe Campaign, Marketing Automation

ACC | How to Leverage queryDef in Adobe Campaign

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.

xtk.queryDef.create()

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.

NLWS.xtkQueryDef()

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);
}

xtk.queryDef VS NLWS.xtkQueryDef

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.

NL.QueryDef()

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

Getting Started

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');

Query Definition

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). You can see the defaults used in the library below.
    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
    };

NL.QueryDef.OPERATION_SELECT is only available in the intialized QueryDef Object. If you are settings the operation use following options: “select”, “get”, “count”. If you leave it out operation “select” is used.

  • 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.

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

var nlQueryDef = new NL.QueryDef(
"nms:delivery",
{
lineCount: 10000,
operation: 'select', // select, get, count
//expandParam: false,
//startLine: 50,
//firstRows: false
},//settings
[
  { expr: "@status" },
  { expr: "@label" },
],//select
[],//where, [{ expr: "@internalName = 'DM150'" }]
[{ expr: "@internalName", sortDesc: true }]//order by
);

var result = nlQueryDef.create().execute().data;
logInfo(JSON.stringify(result))
if(result.length>0) {
  result.forEach(function(e,i){
    logInfo("Delivery status: " + e.status);
     logInfo("Delivery label: " + e.label);
  });
}

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.

Tips

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.

Oh hi there 👋
I have a FREE e-book for you.

Sign up now to get an in-depth analysis of Adobe and Salesforce Marketing Clouds!

We don’t spam! Read our privacy policy for more info.

#jsapi #programming #SQL #workflow
Marcel Szimonisz
Marcel Szimonisz
MarTech consultant As a marketing automation consultant, I specialize in solving problems, automating processes, and driving innovation in my clients' marketing platforms.

I hold certifications in Adobe Campaign v6 (3x certified) and Salesforce Marketing Cloud (5x certified).

Additionally, I serve as a community advisor for Adobe Campaign, offering expert insights and guidance.

In addition to my professional pursuits, I also enjoy exploring various programming languages, CMSs, and frameworks, further enhancing my technical expertise and staying at the forefront of industry advancements.
Take a look at our subscription offering in case you are looking for a Marketing Automation Consultant.

2 thoughts on “ACC | How to Leverage queryDef in Adobe Campaign

Leave a comment

Your email address will not be published. Required fields are marked *

Similar posts that you may find useful

Salesforce Marketing Cloud Tips
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

SFMC TIP | Proof email previewed but not received

2 minutes read

There are many possible issues, and I will try to list all those I have come across during my times when I wondered where my email is. Contact is unsubscribed If the contact you are trying to preview an email with is on one of the global unsubscribe lists, or has unsubscribed or bounced status … Read more

Continue reading
Email marketing terminology
Marketing Automation

Email marketing (automation) basics

4 minutes read

Email marketing automation has become a crucial aspect of every campaign manager’s day-to-day work. Whether you’re using any email marketing automation platform, understanding the fundamentals is essential. This guide covers the basics that are shared across all platforms, ensuring you have a solid foundation in email marketing automation. Multichannel interaction Reach your recipients through various … Read more

Continue reading
Adobe Campaign Classic tips
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

ACC TIP | Troubleshooting web applications

1 minute read

When building any web application (webapp), you may have experienced a syntax error on, for example, line 200. This can be perplexing, especially when your custom JavaScript code does not even have 200 lines. Why is that? The reason for this discrepancy is that the web applications you create in the visual editor are compiled … Read more

Continue reading
Adobe Campaign post
Adobe Campaign, Marketing Automation

ACC | Implementing DKIM

2 minutes read

Have you ever wondered how to implement DKIM in Adobe Campaign Classic, look no further here is how you can do it. Implementing DKIM for Adobe Campaign Classic, gave many hard times as there is no official documentation you can follow step by step. But nothing is lost, it is easier than you may think. … Read more

Continue reading
Salesforce Marketing Cloud Tips
Marketing Automation, Salesforce Marketing Cloud

SFMC TIP | Email Out Of Memory Exception

1 minute read

Sometimes, it happens that when you try to preview your email in Salesforce Marketing Cloud, you could encounter a system.OutOfMemoryException. But don’t worry; it’s not your laptop’s fault, even if it’s as old as mine. This error typically means that your template personalization script is overly complicated and needs some attention. You have two options: … Read more

Continue reading