JAQL Syntax Reference

Properties

Name	Type	Required	Description	Default
datasource	string or object	Yes	States the connection (ElastiCube) name against which to execute the query.	N/A
metadata	object[]	Yes	Contains an array of JAQL elements. A JAQL element is essentially dimension or measure.	N/A
format	string	No	States the expected query result data type; CSV or JSON	"json"
offset	number	No	Cuts the query result by setting the row offset and row count	undefined
count	number	No	Cuts the query result by setting the row offset and row count	undefined
csvSeparator	string	No	Defines the CSV separator that is used when rendering the CSV query result	","
isMaskedResponse	boolean	No	Whether returned values are formatted. When true, values will be objects with a data+text property pair.	true

Datasource

The datasource property tells Sisense which Data Model the query should run against.

TIP

You can use the

get /api​/datasources

endpoint to get a list of objects in this format for each of the Data Models available on your environment.

Properties

Name	Type	Required	Description
title	string	Yes	Name of Data Model to query
fullname	string	No	Internal use (not required)
id	string	No	Internal use (not required)
address	string	No	Internal use (not required)
database	string	No	Internal use (not required)

Example

{
    "title": "Sample Healthcare",
    "fullname": "LocalHost/Sample Healthcare",
    "id": "localhost_aSampleIAAaHealthcare",
    "address": "localHost",
    "database": "aSampleIAAaHealthcare"
}

Metadata Items

This array specifies all of the dimensions (columns), measures (aggregations) and filters that will participate in the query, whether they are part of the result set or not.

Each item in the array (aka MetadataItem) represents one dimension/measure/filter.

Please Note

Each MetadataItem regardless of content has the following structure:

{
    "jaql": {
        // Query properties here
    },
    // Other properties
}

The sections below will describe the contents of the jaql property - there are other properties used internally which do not need to be provided.

Dimensions

Properties

Name	Type	Required	Description
dim	string	Yes	The dimension name
level	string	No	States the date level in a Date dimension
filter	object	No	Defines the element's filter

Supported Date Levels

• years
• quarters
• months
• days
• hours
• minutes
• timestamp

Example

{
    "jaql": {
         "dim": "[Users.CreatedOn (Calendar)]",
        "level": "days"
    }
}

Simple Aggregations

Properties

Name	Type	Required	Description
dim	string	Yes	The dimension name
level	string	No	States the date level in a Date dimension
agg	string	Yes	Defines the measure aggregation over the dimension defined in the dim property
filter	object	No	Defines the element's filter

Supported Aggregations

• avg
• count
• countduplicates
• min
• max
• median
• stdev
• stdevp
• sum
• var
• varp

Example

{
    "jaql": {
        "dim": "[Users.ID]",
        "agg": "count"
    }
}

Formulas

Properties

Name	Type	Required	Description
formula	string	Yes	Defines the formula string
context	object	Yes	Defines the context of the dimensions used in the formula

Example

{
    "jaql": {
        "formula": "count([users]) / 10",
        "context": {
            "users": {
                "dim": "[Users.ID]"
            }
        }
    }
}

Filtering

In JAQL, filters are created by adding a filter property to the MetadataItem as follows:

{
    "jaql": {
        "dim": "[Table.Column]",
        "filter": {
            // Filter properties go here
        }
    }
}

Please Note

For brevity, the sections below describe the inner contents of the filter property without specifying the surrounding Metadata in its entirety

Foreground vs Background Filtering

There are 2 possible behaviors for filters:

Foreground filters are filters applied to a dimention that participates in the query, and are part of the result set.

For example, consider this query:

[{
    "jaql": {
        "dim": "[Customers.Country]"
    }
},
{
    "jaql": {
        "dim": "[Customers.ID]",
        "agg": "count"
    }
}]

This query will return a list of countries, with how many customers exist in each country.

By adding a filter to the participating Country dimension, we create a foreground filter - the result set will include only one row, as the filter only allows the inclusion of one country:

{
    "jaql": {
        "dim": "[Customers.Country]",
        "filter": {
            "members": ["Ukraine"]
        }
    }
}

Background Filters are filters on a dimension that does not participate in the query result, which can be created by specifying the MetadataItem property panel: scope.

For example, this query will return a list of countries with a count of users in each country, but only counting the active users.
There will be no Active column in the result set, and it will not be used to group data.

[{
    "jaql": {
        "dim": "[Customers.Country]"
    }
},
{
    "jaql": {
        "dim": "[Customers.ID]",
        "agg": "count"
    }
},{
    "jaql": {
        "dim": "[Customers.Active]",
        "filter": {
            "members": ["Yes"]
        }
    },
    "panel": "scope"
]

Members

Supported Datatypes: text, number, datetime

Filter by specific unique values of the dimension

Properties

Name	Type	Required	Description
members	string[]	Yes	An array of values

Example

{
    "members": ["USA", "China"]
}

Text Filters

Supported Datatypes: text

Supported Filters

• equals
• doesntEqual
• contains
• doesntContain
• startsWith
• doesntStartWith
• endsWith
• doesntEndWidth
• like

Example

{
    "equals": "USA"
}

Mathematical Filters

Supported Datatypes: number, datetime

Supported Filters

• equals
• doesntEqual
• from
• fromNotEqual
• to
• toNotEqual

Example

{
    "equals": 42
}

Relative Date Filters

Supported Datatypes: datetime

Supported Filters

• last
• next

Properties

Name	Type	Required	Description
count	number	No	How many time units to include. Defaults to 1
offset	number	No	How many time units to skip. Defaults to 0
anchor	string	No	Set a custom date to calculate from. Can also be first or last.

Example

{ 
    "last":{ 
        "count": 5,
        "offset": 10
    }
}

Top/Bottom Filters

Supported Datatypes: text, number, datetime

Supported Filters

• top
• bottom

Properties

Name	Type	Required	Description
top or bottom	number	Yes	How many items to include
by	object	Yes	A JAQL aggregation object (dim+agg or formula+context)

Example

{ 
    "top": 2,
    "by": { 
        "dim": "price",
        "agg": "sum"
    }
}

Negative Filters

Example

{ 
    "exclude": { 
        "members": [ 
            "London",
            "Paris"
        ]
    }
}

Combining Filters

Supported Filters

• and
• or

Example

{ 
    "or":[ 
        { 
            "like": "%acer%"
        },
        { 
            "contains": "toshiba"
        }
    ]
}