Query data extensions with 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",...},
]
