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.

Tips

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"
        }
    ]
}