Skip to end of metadata
Go to start of metadata

In this section, you can review the main JAQL properties to use in your queries, beginning with the mandatory and commonly used properties, and followed by optional properties that you can add to your query. 

Main Properties

datasourceStates the connection (ElastiCube) name against which to execute the query.
metadataContains an array of JAQL elements. A JAQL element is essentially dimension or measure.
typeDefines the metadata element type for dimension measure

Dimension Members

dimThe dimension name. 
levelStates the date level in a Date dimension. Available levels are: years, quarters, months, days and timestamp

 

The example below returns all members of the product dimension.

Example of Main Properties
{ "datasource": "sample-data", "metadata": [ {  "jaql": { "dim": "product" } } ] } 

 

The example below returns all years of the date dimension.

Example of Main Properties
{ "datasource": "sample-data", "metadata": [ {  "jaql": { "dim": "date", "level": "years" } } ] } 

Aggregations

The following aggregation method applies a simple aggregation function over a dimension. 

agg 

Defines the measure aggregation over the dimension defined in the dim property. Available aggregation types are: 

  • avg 

  • count 

  • countduplicates

  • min

  • max 

  • median

  • stdev
  • stdevp
  • sum

  • var
  • varp

The example below returns a single value, which is the sum aggregation on the d1 dimension.

Example with Simple Aggregation
{  
   "datasource":"sample-data",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"table1.column3",
            "agg":"sum"
         }
      }
   ]
}{  
   "datasource":"sample-data",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"table1.column1"
         }
      }      {  
         "jaql":{  
            "dim":"table1.column3",
            "agg":"sum"
         }
      }
   ]
} 

Formulas

The following methods apply advanced arithmetic operations over dimensions and measures.

formula Defines the formula string. For a complete list of supported functions and formula syntax visit Sisense Documentation.
contextDefines the context of the dimensions used in the formula. 

The example below calculates the profit, which is defined as the sum of sales minus the sum of cost:

Example with Formula and Context
{ "datasource": "x", "metadata": [{
			"title": "profit",
			"formula": "[sales] - [cost]",
			"context": {
				"[sales]" : 
					{ 
						"dim": "sales",
						"agg": "sum"
					},
						"[cost]" :
							{
								"dim": "cost",
								"agg": "sum"
							}
						}
					}]
}

Filtering

Measures and dimensions can be applied with filters, each with their own limitations. The filter definition must reside inside the filter attribute of the element.

filter Defines the element's filter.
Example with Filter
{
	"datasource": "x",
	"metadata": [
		{  "jaql": { "dim": "d1",
		"filter": {...}
	} }
}

Below is a list of available filters with their syntactic sugars for shorter and easier queries along with their restrictions.

members

The members filter is an array of strings where each of them represent a member.

Note

Date and time members should always be specified with culture and format in order to be parsed by the server. To learn more, click here.

Example
Formatted JSON Data{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"store city",
            "filter":{  
               "members":[  
                  "Haifa",
                  "Tel Aviv"
               ]
            }
         }
      },
      {  
         "jaql":{  
            "dim":"price",
            "agg":"sum"
         }
      }
   ]
}

Filters dimensions and measures with the equal/not equal to operators. 

Operators:

  • equals
  • doesntEqual

Note

The operators support textual dimensions only.

Example
{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"store city",
            "filter":{  
               "equals":"Haifa"
            }
         }
      },
      {  
         "jaql":{  
            "dim":"price",
            "agg":"sum"
         }
      }
   ]
}

The following operators related to strings are:

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

The queries below return all members that contain acer; the like keyword behaves as in SQL.

{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"product",
            "filter":{  
               "contains":"acer"
            }
         }
      }
   ]
} 

The queries below return all members that start with acer.

{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"product",
            "filter":{  
               "startsWith":"acer"
            }
         }
      }
   ]
}

The queries below return all members that end with 2400.

Formatted JSON Data{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"product",
            "filter":{  
               "endsWith":"400"
            }
         }
      }
   ]
}// result:{  
   "headers":[  
      "Filter Product by "
   ],
   "values":[  
      [  
         "Acer 2400"
      ],
      [  
         "Dell P400"
      ]
   ]
}

Filters numeric/time dimensions and measures with from and/or to.

If both from and to are defined, the filter includes the range from..to. In addition, fromNotEqual and toNotEqual can be applied instead of from and to.

The Operators are:

  • from
  • fromNotEqual
  • to
  • toNotEqual

Note

The operators are not supported by textual dimensions.


The queries below return all members between 6 and 10.

Example
{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"price",
            "filter":{  
               ">=":3800,
               "<=":4821
            }
         }
      }
   ]
}// or with the full operator names{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "dim":"price",
         "filter":{  
            "from":3800,
            "to":4821
         }
      }
   ]
}

The queries below are defined with value filtering over their measures; they'll both return all members where their measured value is greater or equal to 6.

Example
Formatted JSON Data{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"product"
         }
      },
      {  
         "jaql":{  
            "dim":"price",
            "agg":"sum",
            "filter":{  
               ">=":2044104
            }
         }
      }
   ]
}

exclude

Exclude any input filter.  

Note

The operator cannot be used on measures.


Example
// excluding London and Paris from store city dimension{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"store city",
            "filter":{  
               "exclude":{  
                  "filter":{  
                     "members":[  
                        "London",
                        "Paris"
                     ]
                  }
               }
            }
         }
      },
      {  
         "jaql":{  
            "dim":"price",
            "agg":"sum"
         }
      }
   ]
}// exclude of top 3{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"store city",
            "filter":{  
               "exclude":{  
                  "filter":{  
                     "top":3,
                     "by":{  
                        "dim":"price",
                        "agg":"sum"
                     }
                  }
               }
            }
         }
      },
      {  
         "jaql":{  
            "dim":"price",
            "agg":"sum"
         }
      }
   ]
}

top, bottom

The ranking functions can be attached to any dimension, and must be defined with top/bottom (number) and by (measure) attributes.

Both samples below return the top 10 members from d1 that has the best sum d2 ranking. The first sample uses a dimensional filter with an explicit measure definition, while the second uses measure filtering with dynamic dimension assignment.

Example
// via dimension{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"product",
            "filter":{  
               "top":2,
               "by":{  
                  "dim":"price",
                  "agg":"sum"
               }
            }
         }
      },
      {  
         "jaql":{  
            "dim":"price",
            "agg":"sum"
         }
      }
   ]
}// via measure{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"product"
         }
      },
      {  
         "jaql":{  
            "dim":"price",
            "agg":"sum",
            "filter":{  
               "top":2
            }
         }
      }
   ]
}

This example returns rankings 6 to 10 (second 'top 5').

Example
{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"product"
         }
      },
      {  
         "jaql":{  
            "dim":"price",
            "agg":"sum",
            "filter":{  
               "top":10,
               "filter":{  
                  "bottom":5
               }
            }
         }
      }
   ]
}

and, or

Combines a set of filters with and/or operators to create complex filter criteria.

The sample below returns all products that contain either toshiba or acer.

Example
{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"product",
            "filter":{  
               "or":[  
                  {  
                     "filter":{  
                        "like":"%acer%"
                     }
                  },
                  {  
                     "filter":{  
                        "contains":"toshiba"
                     }
                  }
               ]
            }
         }
      }
   ]
}

Note

The operators are not supported by measure filters.

attributes


Filters a dimension with a set of filters from another dimension.

The sample below returns all customers who purchased Toshiba or Acer.

Example
{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"customers",
            "filter":{  
               "attributes":[  
                  {  
                     "dim":"products",
                     "filter":{  
                        "members":[  
                           "Toshiba",
                           "Acer"
                        ]
                     }
                  }
               ]
            }
         }
      }
   ]
}

measure


Filters a dimension members by a measured value.


Sisense 6.0+

The sample below returns all customers who purchased more than a total of 1000.

Example
{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"customers",
            "filter":{  
               "measure":{  
                  "dim":"sales",
                  "agg":"sum"
               },
               ">":1000
            }
         }
      }
   ]
}

 

It is also possible to compare the measure with another. The sample below will return all customers who ordered more of Product A than they've from Product B

Example
{ 
	"datasource": "laptopsales", 
	"metadata": [ 
 
		{ 
			"jaql": { 
				"dim": "customers", 
				"filter": { 
					"measure": { 
						"formula": "(A, B)",
						"context": {
							"A": {
								"dim": "orderid", 
								"agg": "count" 
							},
							"B": {
								"dim": "product", 
								"filter": {
									"members": [ "Product A" ]
								}
							}
							}
					}, 
 
					">":  { 
							"formula": "(A, B)",
							"context": {
							"A": {
								"dim": "orderid", 
								"agg": "count" 
							},
							"B": {
								"dim": "product", 
								"filter": {
									"members": [ "Product B" ]
									}
							}
							}
					}
				} 
			} 
		} ] 
}

last

Filters date-time dimensions with last X days/months/quarters/years.

The query below returns this month.

Example
{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"date",
            "level":"months",
            "format":"yyyy MM",
            "filter":{  
               "last":{  
                  "count":1,
                  "offset":0
               }
            }
         }
      }
   ]
}

Notes

  • An optional anchor attribute is available to set a custom date instead of the current time.
  • count is by default set to 1 if not specified.
  • offset is by default set to 0 if not specified.
  • Anchor can be set to “first” or “last” to state the first/last dates in the dimension.

next

Filters date-time dimensions with next X days/months/quarters/years.

The query below returns the next month (relative to current month). 

Example
{  
   "datasource":"laptopsales",
   "metadata":[  
      {  
         "jaql":{  
            "dim":"date",
            "level":"quarter",
            "format":"yyyy QQ",
            "filter":{  
               "next":{  
                  "count":1,
                  "offset":1
               }
            }
         }
      }
   ]
}

Notes

  • An optional anchor attribute is available to set a custom date instead of the current time.
  • count is by default set to 1 if not specified.
  • offset is by default set to 0 if not specified.
  • Anchor can be set to “first” or “last” to state the first/last dates in the dimension.

Additional Element Attributes

sort

 

default undefined

 


Defines the element's sorting rules: asc/desc

Example
{
    "datasource": "x",
    "metadata": [
        { 
			"jaql": {
				"dim": "d1" 
			}
		},
        { 
			"jaql": {
				"dim": "d2", 

	            "agg": "sum",
	            "sort": "asc" 
			}
		}
    ]
}

valueIfNull

default undefined

Sisense 6.0+

Measures Only

Defines the numeric value to select t if a measured value results in null.

includeNulls

 default false

Sisense 6.0.1+

Measures Only

Defines whether null values are removed from aggregated and/or calculated values.

Available values are: true or false.

Additional Global Attributes

format 

 default "json"

States the expected query result data type; CSV or JSON.

offset and count 

 

default undefined

Cuts the query result by setting the row offset and row count.

csvSeparator 

default ","

Defines the CSV separator that is used when rendering the CSV query result.

isMaskedResponse 

default true

Defines whether the returned values are returned formatted (Date-time/ numeric) when format is defined for a JAQL element. When true, result values will always be objects with a data/text property pair.

 


  • No labels