# 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

# Metadata Items

Supported Date Levels

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

# 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

Example

{
    "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

{
    "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

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

# Filtering

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

# Attribute Filters

Supported Datatypes: text, number, datetime

Example

{
    "attributes": [ 
        { 
            "dim": "products",
            "filter": { 
                "members": [ 
                    "Toshiba",
                    "Acer"
                ]
            }
        }
    ]
}

# Negative Filters

Example

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

# Combining Filters

Supported Filters

  • and
  • or

Example

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

# Additional References

LEARN

COMMUNITY

ABOUT US

Copyright © 2020 Sisense Inc. All rights reserved.

Submit Feedback Legal Privacy