Marketing Automation, Salesforce Marketing Cloud

SFMC | Query data extension in SSJS and AMPScript

Queries in SSJS and AMPScript

There’s another topic for which official documentation often lacks sufficient information, but it can be incredibly useful when needed.

It’s important to note that in AMPScript, we use the data extension name to reference the table for any query. On the other hand, in SSJS, we utilize the data extension’s external key to reference the table. Understanding this distinction can save you hours of debugging.

For many cases we will sufice with simple AMPScript Lookup or LookupRows. But when you need to set more complex queries we would need to use SSJS Rows.Retrieve method of DataExtension object.

var  dataExtension = DataExtension.Init("external_key"),
     data = dataExtension.Rows.Retrieve();


The Rows.Retrieve method accepts a single argument known as the “filter,” which allows us to construct our filtering conditions.

var dataExtension = DataExtension.Init("external_key"),
    filter = {Property:"Domain",SimpleOperator:"like",Value:"martechnotes.com"},
    data = dataExtension.Rows.Retrieve(filter);

You can select from multiple filter simple operators:

  • equals
  • notEquals
  • greaterThan
  • lessThan
  • isNull
  • isNotNull
  • greaterThanOrEqual
  • lessThanOrEqual
  • between
  • IN
  • like

Logical operators that can be used are:

  • AND
  • OR

You can also create complex filters wherever they are needed:

var 
filter = {
	leftOperand: {
      Property:"Domain",SimpleOperator:"like",Value:"https://martechnotes.com"
    }
  	LogicalOperator: "AND",//OR
  	rightOperand:{
  		leftOperand: {
      		Property:"Domain",SimpleOperator:"like",Value:"/path1"
		},
  		LogicalOperator: "OR",//AND
  		rightOperand:{
  			Property:"Domain",SimpleOperator:"like",Value:"/path2"
    	}
	}
},
data =  dataExtension.Rows.Retrieve(filter);
  

As you can see we can create as many nested filters as needed.

The results of each query are returned as an array of objects, consisting of the returned rows in JSON format.

[
  {Domain:"https://martechnotes.com/path1",...},
  {Domain:"https://martechnotes.com/path2",...},
]

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.

#AMPScript #JavaScript #programming #ssjs
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.

Leave a comment

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

Similar posts that you may find useful

SFMC tips and tricks
Salesforce Marketing Cloud, SFMC Tips & Tricks

SMFC TIP | How to query filtered data extension

less than a minute read

Easy way to segment your data is to use filtered data extension. Here we can use user interface to set up conditions as we like. When you want to add this newly filtered data extension to the SQL activity you will find that data extension cannot be found in the file browser. But it is … Read more

Continue reading
Marketing automation tips
Marketing Automation

TIP | Sending SMS with Unicode Characters

1 minute read

Are you planning to send SMS messages with Unicode characters? If so, you may be wondering why your messages are split into multiple messages even though they don’t exceed the character limit. Here’s what you need to know about sending SMS messages with Unicode characters. What are Unicode Characters? Unicode is a character encoding standard … Read more

Continue reading
how to publish cloud page immediatelly
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

SFMC TIP | How to publish cloud page immediately

2 minutes read

You know the struggle when developing a cloud page application and trying to debug some issues or make changes. You publish the cloud page and wait and wait. Sometimes it takes ages, and other times it is right away. Why does this happen? How can we make it work immediately? Why we have to endure … 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 journey exit criteria and goals
Marketing Automation, Salesforce Marketing Cloud

SFMC | How to use journey exit criteria and goals

3 minutes read

I might be really slow learning but I learned about this feature two years into working with salesforce marketing cloud, and I said to myself what a nice feature to have. Let me tell you what it is and give you some real life examples when to use it. Exit criteria and goals share a … Read more

Continue reading