Marketing Automation, Salesforce Marketing Cloud

Query data extensions with 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 problem-solving, process automation, and driving innovation for clients' marketing platforms.

I hold certifications in Adobe Campaign v6 (3x certified) and Salesforce Marketing Cloud (5x certified), as well as 1x Salesforce Associate certified.

Moreover, I serve as a community advisor for Adobe Campaign, providing expert insights and guidance.

Beyond my professional pursuits, I explore various programming languages, CMSs, and frameworks, 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

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

Workflow stuck in pending start status

1 minute read

Have you ever found yourself waiting for a campaign workflow to start, only to experience delays or long waiting times? I’ve got a handy trick to share with you that can help you initiate the workflow immediately or significantly faster. I know that campaign managers schedule sometimes can be tight and waiting for pending start […]

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

How to refresh data extension records

1 minute read

You might have noticed that records are not refreshed automatically, which makes sense. However, there is no refresh button. Here are some helpful tips I’ve discovered on how to refresh records within the data extension while it’s open. Email studio In email studio it is failry easy you simply navigate to another folder and return […]

Continue reading
SFMC tips and tricks
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

SMFC TIP | How to handle form submission

1 minute read

On many occasions when handling any form submission single cloud page application is used. This is great as all of error handling, form submission and processing is on one page so it is actually simpler to implement. But here are some thing you need to take into consideration. Handle GET form submissions I have seen […]

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

How to send 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 […]

Continue reading
preference center
Marketing Automation, Salesforce Marketing Cloud

SFMC | Multi-cloud preference center

5 minutes read

With majority of the implementations the out of the box center is not sufficient for the client and we are tasked to build custom preference center for them. We can assume that the preference center is only applicable for known subscribers most likely coming from email communication.  When we send email in salesforce marketing cloud, […]

Continue reading