DataSense Query Language

In Anypoint Connectors which support the query functionality, the DataSense Query Editor allows you to easily query and retrieve complex data stored in a remote application. To define your query, you can proceed in one of two ways:

use the Query Editor’s graphical Query Builder, which displays objects and fields available via the connector
type your query directly into the Query Text field, using the remote application’s native query language or the DataSense Query Language

To facilitate the latter, this document describes the DataSense Query Language for use in the Query Editor.

Assumptions

This document assumes that you are familiar with the DataSense Query Editor. The query language described in this document is used in conjunction with the Query Editor in the editor’s properties panel.

Queries

The DataSense Query Language is, in fact, a basic subset of SQL. The tables below list the supported language elements and operators.

Supported Language Elements

Language element Purpose Examples

Language element	Purpose	Examples
`SELECT` statement	Select fields from an object	`SELECT AccountID` (To select multiple fields, separate them by commas:) `SELECT AccountID,FirstName,Lastname`
`FROM` clause	Specify which object to select from	`SELECT AccountID,FirstName,Lastname FROM Contact`
`WHERE` clause	Define a comparison predicate to eliminate results that do not match	`SELECT AccountID,FirstName,Lastname FROM Contact WHERE AccountID ⇒ '500'`
`ORDER BY` clause	Specify a field to use for sorting results	`SELECT AccountID,FirstName,Lastname FROM Contact WHERE AccountID ⇒ '500' ORDER BY LastName`
`OFFSET` clause	Set offset (number of results to ignore) for query results	`SELECT AccountID,FirstName,Lastname FROM Contact WHERE AccountID ⇒ '500' ORDER BY LastName OFFSET 250`
`LIMIT` clause	Set maximum number of results to return	`SELECT AccountID,FirstName,Lastname FROM Contact WHERE AccountID ⇒ '500' ORDER BY LastName OFFSET 250 LIMIT 1000`
`AND` conditional operator	Concatenates search filters defined by the `WHERE` clause, returning match if the conditions defined in both filters are true	`SELECT AccountID,FirstName,Lastname FROM Contact WHERE (AccountID ⇒ '500' AND IsDeleted = false) ORDER BY LastName OFFSET 250 LIMIT 1000`
`OR` conditional operator	Concatenates search filters defined by the `WHERE` clause, returning match if one of the conditions defined in the filters is true	`SELECT AccountID,FirstName,Lastname FROM Contact WHERE (AccountID ⇒ '500' AND IsDeleted = false) OR Employee_Still_With_Company_c = false ORDER BY LastName OFFSET 250 LIMIT 1000`

SELECT statement

Select fields from an object

SELECT AccountID

(To select multiple fields, separate them by commas:)

SELECT AccountID,FirstName,Lastname

FROM clause

Specify which object to select from

SELECT AccountID,FirstName,Lastname FROM Contact

WHERE clause

Define a comparison predicate to eliminate results that do not match

SELECT AccountID,FirstName,Lastname FROM Contact WHERE AccountID ⇒ '500'

ORDER BY clause

Specify a field to use for sorting results

SELECT AccountID,FirstName,Lastname FROM Contact WHERE AccountID ⇒ '500' ORDER BY LastName

OFFSET clause

Set offset (number of results to ignore) for query results

SELECT AccountID,FirstName,Lastname FROM Contact WHERE AccountID ⇒ '500' ORDER BY LastName OFFSET 250

LIMIT clause

Set maximum number of results to return

SELECT AccountID,FirstName,Lastname FROM Contact WHERE AccountID ⇒ '500' ORDER BY LastName OFFSET 250 LIMIT 1000

AND conditional operator

Concatenates search filters defined by the WHERE clause, returning match if the conditions defined in both filters are true

SELECT AccountID,FirstName,Lastname FROM Contact WHERE (AccountID ⇒ '500' AND IsDeleted = false) ORDER BY LastName OFFSET 250 LIMIT 1000

OR conditional operator

Concatenates search filters defined by the WHERE clause, returning match if one of the conditions defined in the filters is true

SELECT AccountID,FirstName,Lastname FROM Contact WHERE (AccountID ⇒ '500' AND IsDeleted = false) OR Employee_Still_With_Company_c = false ORDER BY LastName OFFSET 250 LIMIT 1000

Due to the complexity of graphical representation, the graphical Query Builder cannot display the OR conditional operator, although the DataSense query language supports it. To use the graphical Query Builder to construct a query that contains one or more OR operators, proceed as follows:

Build your query with the graphical Query Builder, using it to select from the available objects and fields.
Click OK to close the Query Builder window. Studio displays your finished query in the Query Text field.
Edit your query in the Query Text field to include the OR operator. Your query will work, though you will not be able to see your query in the graphical editor.

Arithmetic Operators

equals to

less than

⇐

less or equal than

greater than

greater or equal than

not equal to

LIKE

includes specified pattern

Other

, (comma)

Use to separate items in a list

( )

Use to delimit filter operations articulated by the AND conditional operator

' (single quote)

Use to:

** Escape numbers if they are part of returned results and not an operational parameter (such as for the OFFSET or LIMIT clauses)
- Escape field values composed of multiple words (as in fullName = 'Mr. John Smith')

Supported Data Types

Data Type Usage Example Notes

Data Type	Usage Example	Notes
String	`SELECT FullName FROM Account WHERE LastName = Smith`
Date	`SELECT Name FROM Account WHERE Birth > 1999-01-01T23:01:01+01:00` `SELECT Name FROM Account WHERE Birth > 1999-01-01T23:01:01Z`	The date format is the ISO 8601 format, which supports time zones
Number	`SELECT Name FROM Account WHERE Age > 30`
Boolean	`SELECT Name FROM Account WHERE Registered = true`
Null	`SELECT Name FROM Account WHERE Address = null`

String

SELECT FullName FROM Account WHERE LastName = Smith

Date

SELECT Name FROM Account WHERE Birth > 1999-01-01T23:01:01+01:00

SELECT Name FROM Account WHERE Birth > 1999-01-01T23:01:01Z

The date format is the ISO 8601 format, which supports time zones

Number

SELECT Name FROM Account WHERE Age > 30

Boolean

SELECT Name FROM Account WHERE Registered = true

Null

SELECT Name FROM Account WHERE Address = null

Limitations

The DataSense query language currently supports only the elements and operators listed in the tables above. Any other elements are currently not supported.