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.

  1. Examples
    1. Page views today
    2. Dashboard widgets
  2. Language
    1. Expressions
    2. Fixtures
    3. Methods
      1. where
      2. limit
      3. unique
      4. sort
      5. get
      6. set
      7. each
      8. date
      9. count
      10. math
      11. pluck

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.

where timestamp >= {{TIMESTAMP_TODAY_START}}
where event == page_view
count

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:

count
set eventsCount = {count}

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:

set uniqueVisitorIdCount = {count {unique visitorId}}

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

TIMESTAMP_CURRENT

Current time2/21/2021, 11:51:47 PM

TIMESTAMP_FIVE_MINUTES_AGO

Five minutes ago2/21/2021, 11:46:00 PM

TIMESTAMP_FIFTEEN_MINUTES_AGO

Fifteen minutes ago2/21/2021, 11:36:00 PM

TIMESTAMP_THIRTY_MINUTES_AGO

Thirty minutes ago2/21/2021, 11:21:00 PM

TIMESTAMP_SIXTY_MINUTES_AGO

One hour ago2/21/2021, 10:51:00 PM

TIMESTAMP_TWELVE_HOURS_AGO

Twelve hours ago2/21/2021, 11:51:00 AM

TIMESTAMP_TWENTY_FOUR_HOURS_AGO

Twenty-four hours ago2/20/2021, 11:51:00 PM

TIMESTAMP_TODAY_START

Start of current day2/21/2021, 12:00:00 AM

TIMESTAMP_TODAY_END

End of current day2/21/2021, 11:59:59 PM

TIMESTAMP_YESTERDAY_START

Start of yesterday2/20/2021, 12:00:00 AM

TIMESTAMP_YESTERDAY_END

End of yesterday2/20/2021, 11:59:59 PM

TIMESTAMP_CURRENT_WEEK_START

Start of current week2/15/2021, 12:00:00 AM

TIMESTAMP_CURRENT_WEEK_END

End of current week2/21/2021, 11:59:59 PM

TIMESTAMP_PREVIOUS_WEEK_START

Start of previous week2/8/2021, 12:00:00 AM

TIMESTAMP_PREVIOUS_WEEK_END

End of previous week2/8/2021, 12:00:00 AM

TIMESTAMP_CURRENT_MONTH_START

Start of current month2/1/2021, 12:00:00 AM

TIMESTAMP_CURRENT_MONTH_END

End of current month2/28/2021, 11:59:59 PM

TIMESTAMP_PREVIOUS_MONTH_START

Start of previous month1/1/2021, 12:00:00 AM

TIMESTAMP_PREVIOUS_MONTH_END

End of previous month1/31/2021, 12:00:00 AM

TIMESTAMP_SEVEN_DAYS_START

Start of current 7 day period2/15/2021, 12:00:00 AM

TIMESTAMP_SEVEN_DAYS_END

End of current 7 day period2/21/2021, 11:59:59 PM

TIMESTAMP_PREVIOUS_SEVEN_DAYS_START

Start of previous 7 day period2/8/2021, 12:00:00 AM

TIMESTAMP_PREVIOUS_SEVEN_DAYS_END

End of previous 7 day period2/14/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.

where timestamp ?
where timestamp >= 1
where timestamp == 53 || timestamp ^= 12

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.

limit 10

unique

Sets results to an array of objects containing unique occurrences of a variable within the current results.

unique visitorId

The results after running unique visitorId might look like this:

[{"visitorId":1},{"visitorId":2}]

sort

Sort the current results.

sort number timestamp ascending

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.

get results
get events,

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.

set foo = bar
set events = {get 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.

each {
    set each.foo = bar
}

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.

date 1584487665170 "DD-MM-YYYY"

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.

count

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.

math average timestamp

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.

pluck timestamp
pluck timestamp, visitorId

Parameters

pluck [variable]
pluck [variable], [variable] ...

variable
Name of the variable or variables to pluck.