Query Parameters Format – Documentation and Support | Knowi

Query parameters must start with $c9_.

$c9<name>$(defaultValue)

$c9<name>$(defaultValue)

Example: $c9borough$(Manhattan)$[Borough]$ Here the parameter name is $c9borough, the default value is Manhattan (can be empty brackets) and a custom label display on the filter at the widget/dashboard level. The label is optional and will default to the query parameter if not specified.

More complex example, with multiple values and types:

$c9<name>$(<default>)$[<label>]${list|<quotation>|<separator>}$

List of strings

Use case: User selects multiple values on the filter for an attribute, and they need to be mapped into a query.

$c9<name>$(<default>)$[<label>]${list|<quotation>|<separator>}$

Each object value in list will be presented as a string.

Example

$c9_myMacro123$(Some value)$[<label>]${list|"|,}$

Example of query with macro:

db['restaurants'].find({
  "borough" : { $in: [$c9_myMacro123$("Bronx")$[My label]${list|"|,}$ ] }
})

If the user selects 2 strings, say _Queens_ and _Brooklyn_, those shall be mapped as the following during runtime:

db['restaurants'].find({
  "borough" : { $in: ["Queens", "Brooklyn"] }
})

Single Date object

$c9<name>$(<default>)$[<label>]${date|<date format>}$

Value is a date and it will be formatted into a custom format string from field of a macro. This will result in Date/Calendar picker on the UI, which will be translated into the appropriate date format into the query at runtime.

Supported formats:

yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
yyyy-MM-dd'T'HH:mm:ss'Z'
yyyy-MM-dd'T'HH:mm:ss.SSS zzz //D + T + Millis + ZZ (GMT, UTC, etc)
yyyy-MM-dd'T'HH:mm:ss zzz //D + T + ZZ (GMT, UTC, etc)
yyyy-MM-dd HH:mm:ss.SSS zzz //D + T + Millis + ZZ (GMT, UTC, etc)
yyyy-MM-dd HH:mm:ss zzz //D + T + ZZ (GMT, UTC, etc)
yyyy/MM/dd HH:mm:ss.SSS zzz //D + T + Millis + ZZ (GMT, UTC, etc)
yyyy/MM/dd HH:mm:ss zzz //D + T + ZZ (GMT, UTC, etc)

yyyy-MM-dd'T'HH:mm:ss.SSSZZZ //D + T + Millis + ZZ (XXXX)
yyyy-MM-dd'T'HH:mm:ssZZZ //D + T + ZZ (XXXX)
yyyy-MM-dd HH:mm:ss.SSSZZZ //D + T + Millis + ZZ (XXXX)
yyyy-MM-dd HH:mm:ssZZZ //D + T + ZZ (XXXX)
yyyy/MM/dd HH:mm:ss.SSSZZZ //D + T + Millis + ZZ (XXXX)
yyyy/MM/dd HH:mm:ssZZZ //D + T + ZZ (XXXX)

yyyy-MM-dd'T'HH:mm:ss.SSSSSSXXX //D + T + Millis + ZZ (XX:XX)
yyyy-MM-dd'T'HH:mm:ssXXX //D + T + ZZ (XX:XX)
yyyy-MM-dd'T'HH:mm:ssX //D + T + ZZ (XX)
yyyy-MM-dd HH:mm:ss.SSSXXX //D + T + Millis + ZZ (XX:XX)
yyyy-MM-dd HH:mm:ssXXX //D + T + ZZ (XX:XX)
yyyy-MM-dd HH:mm:ssX //D + T + ZZ (XX)
yyyy/MM/dd HH:mm:ss.SSSXXX //D + T + Millis + ZZ (XX:XX)
yyyy/MM/dd HH:mm:ssXXX //D + T + ZZ (XX:XX)
yyyy/MM/dd HH:mm:ssX //D + T + ZZ (XX)

yyyy-MM-dd'T'HH:mm:ss.SSS //D + T + Millis
yyyy-MM-dd'T'HH:mm:ss //D + T
yyyy-MM-dd HH:mm:ss.SSS //D + T + Millis
yyyy-MM-dd HH:mm:ss //D + T
yyyy-MM-dd //D
yyyy/MM/dd HH:mm:ss.SSS //D + T + Millis
yyyy/MM/dd HH:mm:ss //D + T
yyyy/MM/dd hh:mm a
yyyy/MM/dd HH:mm //D + T (up to minutes)
yyyy/MM/dd //D

// Formats starting with MM and yyyy
MM-dd-yyyy"
MM/dd/yyyy HH:mm:ss zzz
MM/dd/yyyy hh:mm a
MM/dd/yyyy HH:mm
MM/dd/yyyy

// Formats starting with MM and yy
MM/dd/yy HH:mm:ss.SSS
MM/dd/yy HH:mm:ss zzz
MM/dd/yy HH:mm:ss
MM/dd/yy hh:mm a
MM/dd/yy HH:mm
MM/dd/yy

Example

Macro:

$c9_myMacro123$(2014-01-01T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'}$

Example of query with macro:

db['pagehits'].find({
  "lastAccessTime" : { $gt: { $date : "$c9_myMacro123$(2014-01-01T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'}$" } } 
})

The filter value for field "My date" is date 2015-05-05.

The result query with applied macro and filter value:

db['pagehits'].find({
  "lastAccessTime" : { $gt: { $date : "2015-05-05T00:00:00Z" } } 
})

The optional default value "2014-04-08T01:01:01Z" of macro will be used in case there is no provided filter with "My date" set. It will be used as string as-is without changing format.

Date Range

$c9_<name>$(<default>)$[<label>]${date|<date format>|<from|to>}$

Value is a date range (the start date and end date) and each date will be formatted as string using custom format from field of a macro. The macro with "from" keyword will get the start range value, and macro with "to" keyword will get the end range value.

Supported formats:

Example

Macro:

$c9_myMacro123$(2014-01-01T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'|from}$
$c9_myMacro123$(2014-03-03T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'|to}$

Example of query with macro:

db['pagehits'].find({
  "lastAccessTime" :  { 
    $gt: { $date : "$c9_myMacro123$(2014-01-01T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'|from}$" },
    $lt: { $date : "$c9_myMacro123$(2014-03-03T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'|to}$" }
    }
})

The filter value for the field "My date" is the date range from 2015-06-06 to 2015-08-08.

The result query with applied macro and filter value:

db['pagehits'].find({
  "lastAccessTime" : {
    $gt: { $date : "2015-06-06T00:00:00Z" },
    $lt: { $date : "2015-08-08T00:00:00Z" }
  }
})

The optional default value "2014-01-01T01:01:01Z" of the macro will be used in case there is no provided filter with "My date" set. It will be used as string as-is without changing the format, in "from" macro. The optional default value "2014-03-03T01:01:01Z" will be used the same way but in the "to" macro.

List of strings

Single Date object

Date Range

Related articles