JsonQl
JsonQl is a query language that operates on rows of JSON. The syntax is reminiscent of SQL mixed with JavaScript, and is designed to be easy to pick up.
If you have suggestions, please get in touch: JsonQl is still a work in progress.
Examples
Page views today
Get number of page views today by first filtering events with a timestamp larger than or equal to fixture {{TIMESTAMP_TODAY_START}}, where event is equal to page_view, and count the number of results.
Language
There are three reserved variables: rows, results, and each. The rows variable will always contain all the initial rows, throughout the entire query. Most methods alter the results variable — but not when using expressions.
New lines end the current method unless inside a block, where } is used.
Expressions
Some methods allow expressions. Expressions are query blocks wrapped in brackets like this: {count}. Using an expression ensures the results variable is not altered. One such operation is set. Compare these two queries:
In the first query, results becomes the number of rows in the current results. In the latter, results is not altered, but instead, a new variable eventsCount is created and set to the number of rows in the current results.
It is also possible to nest multiple expressions. Consider the following example:
Here, we set variable uniqueVisitorIdCount to the count of the result of unqiue visitorId.
Fixtures
These are uppercased static variables wrapped in double brackets that are replaced dynamically to make queries easier to read and write. A fixture looks like this: {{TIMESTAMP_CURRENT}}.
Available fixtures
| Current time7/11/2021, 10:11:13 AM |
| Five minutes ago7/11/2021, 10:06:00 AM |
| Fifteen minutes ago7/11/2021, 9:56:00 AM |
| Thirty minutes ago7/11/2021, 9:41:00 AM |
| One hour ago7/11/2021, 9:11:00 AM |
| Twelve hours ago7/10/2021, 10:11:00 PM |
| Twenty-four hours ago7/10/2021, 10:11:00 AM |
| Start of current day7/11/2021, 12:00:00 AM |
| End of current day7/11/2021, 11:59:59 PM |
| Start of yesterday7/10/2021, 12:00:00 AM |
| End of yesterday7/10/2021, 11:59:59 PM |
| Start of current week7/5/2021, 12:00:00 AM |
| End of current week7/11/2021, 11:59:59 PM |
| Start of previous week6/28/2021, 12:00:00 AM |
| End of previous week6/28/2021, 12:00:00 AM |
| Start of current month7/1/2021, 12:00:00 AM |
| End of current month7/31/2021, 11:59:59 PM |
| Start of previous month6/1/2021, 12:00:00 AM |
| End of previous month6/30/2021, 12:00:00 AM |
| Start of current 7 day period7/5/2021, 12:00:00 AM |
| End of current 7 day period7/11/2021, 11:59:59 PM |
| Start of previous 7 day period6/28/2021, 12:00:00 AM |
| End of previous 7 day period7/4/2021, 11:59:59 PM |
Methods
where
Filters the current results. It is possible to chain multiple “or” filters with ||. To do an “and” filter, simply write a new where query on the next line.
Parameters
where [variable] ?
where [variable] [operator] [expression]
where [statement] || [statement] ...variable
The variable to filter by.
operator
Filtering operator. Valid operators are existence ?, non-existence !?, equal to ==, not equal to !=, contains *=, not contains *!=, starts with ^=, not starts with ^!=, ends with $=, not ends with $!=, larger than >, larger than or equal >=, less than <, less than or equal <=, includes in, and not includes !in.
expression
Static value or query expression returning a static value.
limit
Limit the current results.
unique
Sets results to an array of objects containing unique occurrences of a variable within the current results.
The results after running unique visitorId might look like this:
[{"visitorId":1},{"visitorId":2}]sort
Sort the current results.
Parameters
sort [type] [variable] [direction]type
The type to sort. Valid types are number.
variable
Name of the variable to sort by.
direction
Direction to sort. Valid directions are ascending and descending.
get
Get a variable, or multiple variables separated by comma, and set that as the current results. If more than one variable, or suffixed by a comma, it sets an object.
Parameters
get [variable]
get [variable], [variable] ...variable
Name of the variable or variables to get.
set
Set a variable. This does not override the current results.
Parameters
set [variable] = [expression]variable
The name of the variable to set. Within an each block, the variable name can be prefixed with each. to set a value on the iterated rows.
expression
The static value to set, or an expression returning a static value.
each
Iterate on an array. Within the block, each is the current index. It is not possible to nest multiple each blocks.
Parameters
each [variable?] {}variable
Optionally set the variable to iterate on. If not set, iteration will be done on the current results.
date
Format timestamps.
Parameters
date [expression] "[format]"expression
A static millisecond timestamp, or an expression returning a static millisecond timestamp.
format
Date format template wrapped in double quotes.
count
Set results to the count the number of rows in the current results.
Parameters
count [expression?]expression
An optional value. Either, a static variable name pointing to an array, an expression returning an array of items, or an expression returning a static variable name pointing to an array. If not set, count will be done on the current results.
math
average
Set results to the average sum of the variable on all rows in the current results.
Parameters
math average [variable]variable
Static variable pointing to a number on each index in the current results.
pluck
Pluck isolates properties in results indices, removing the other properties, reducing the total size of the returned results array.
Parameters
pluck [variable]
pluck [variable], [variable] ...variable
Name of the variable or variables to pluck.