How to Query Data Extensions with SSJS and AMPscript in Salesforce Marketing Cloud

Querying data from Data Extensions in Salesforce Marketing Cloud can be done with both AMPscript and Server-Side JavaScript (SSJS). While AMPscript offers simple functions like Lookup or LookupRows, SSJS provides more flexibility for complex filtering through the Rows.Retrieve() method. In this guide, we’ll compare both approaches and show how to use filters and logical operators effectively.

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 suffice 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();

TIP: make the name and external key of data extension same so you don’t need to care what function is using one or the other.

AMPscript: Simple Queries for Most Use Cases

AMPscript is usually sufficient for basic lookups and personalization tasks.
You can retrieve specific records from a Data Extension using its name (not external key).

Lookup() – Retrieve a single field value

Lookup(DEName, FieldToReturn, FieldToMatch, ValueToMatch)

• “Subscribers” – Data Extension name (not external key)
• “FirstName” – field you want to return
• “Email” – field to match
• “john@example.com” – value to match

Returns:

• The value of the first matching row’s FirstName field (string).
• If no match is found, it returns an empty string.

%%[
SET @FirstName = Lookup("Subscribers", "FirstName", "Email", "john@example.com")

IF NOT EMPTY(@FirstName) THEN Output(@FirstName) ELSE Output("Hello") ENDIF
]%%

%%[IF NOT EMPTY(@FirstName) THEN]%%
%%=v(@FirstName)=%%
%%[ELSE]%%
Hello
%%[ENDIF]%%

This works perfectly for simple conditions, like matching a single column for personalization.

LookupRows() – Retrieve multiple rows

LookupRows(DEName, FieldToMatch, ValueToMatch)

%%[
SET @rows = LookupRows("Subscribers", "Country", "Slovakia", "Status", "Active")
SET @rowCount = RowCount(@rows)
IF RowCount(@rows) > 0 THEN
   FOR @counter = 1 to @rowCount do
       SET @row = Row(@rows, 1)
       SET @email = Field(@row, "Email")
       SET @firstName = Field(@row, "FirstName")
   NEXT @counter
ENDIF
]%%

LookupOrderedRows() – Retrieve and sort rows

LookupOrderedRows(DEName, RowCount, OrderBy, FieldToMatch, ValueToMatch)

SET @rows = LookupOrderedRows("Orders", 3, "OrderDate DESC", "CustomerID", "12345")

Also we have case sensitive functions for LookupOrderedRowsCS() and LookupRowsCS to search exact matches or case sensitive matches.

However, when you need to perform more complex filtering or nested logic, AMPscript quickly becomes limited — that’s where SSJS steps in.

SSJS: Advanced Queries with 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",...},
]

Share With Others