URQL Manual

Owned by Steven Revill, created
Last updated: Nov 25, 2021
5 min read

uSmart Resource Query Language

Introduction

The **U**Smart **R**esource **Q**uery **L**anguage (URQL) allows you to search for specific records within an application programming interface (API) resource. We've based URQL loosely on

[RQL](GitHub - persvr/rql: Resource Query Language ).

 

URQL Rules

The URQL grammar is based around standard URI delimiters. The standard rules for encoding strings with URL encoding (%xx) are observed. URQL also supersets FIQL. Therefore we can write a query that finds resources with a "price" property below 10 with a "lt" operator using FIQL syntax:

    price=lt=10

Which is identical (and sugar for call operator syntax known as the normalized form):

    lt(price,10)

One can combine conditions with multiple operators with "&":

    foo=3&price=lt=10

Is the same as:

    eq(foo,3)&lt(price,10)

Which is also the same as:

    and(eq(foo,3),lt(price,10))

The | operator can be used to indicate an "or" operation. We can also use paranthesis to group expressions. For example:

    (foo=3|foo=bar)&price=lt=10

Which is the same as:

    and(or(eq(foo,3),eq(foo,bar)),lt(price,10))

Values in queries can be strings (using URL encoding), numbers, booleans, null, undefined, and dates (in ISO UTC format without colon encoding). We can also denote arrays with paranthesis enclosed, comma separated values. For example to find the objects where foo can be the number 3, the string bar, the boolean true, or the date for the first day of the century we could write an array with the "in" operator:

    foo=in=(3,bar,true,2000-01-01T00:00:00Z)

We can also explicitly specify primitive types in queries. To explicitly specify a string "3",

we can do:

    foo=string:3 

Another common operator is sort. We can use the sort operator to sort by a specified property. To sort by foo in ascending order:

    price=lt=10&sort(+foo)

We can also do multiple property sorts. To sort by price in ascending order and rating in descending order:

    sort(+price,-rating)

Special Time Example

When writing time based queries you may have to ensure you follow these rules:

  • using "T" at the end of the date element,

  • using the full time format i.e. down to 000Z

  • not using the term "=" before the date as was done here in the original "=2019-01-01 00:00:00.000+0000"

Query operators

As a guide where ‘property’ is mentioned this represents the column header on your data source or a key represented in the json provided through the API you have created on your dataset. ‘Value’ represents the properties value to be matched on for your query.

Filter query

These are queries that filter and can be chained together to return what you require from your data source:

Equals `eq(property,value)` -

The eq operator filters for objects where the specified property's value is equal to the provided value.

find WHERE ‘property’ is ‘value’ and return only those results.

In `in(property,(valueA,ValueB,…))`

The in operator works like eq however takes a value array denoted by the curly braces enclosing a comma separated list. It will return all results where any of the values is contained within the property specified.

Find WHERE ‘property’ IN (valueA, valueB, …);

Less than / Lower than ` lt(property,value) `

The lt operator filters for results where the specified property's value is less than the provided value.

Find where ‘property’ < ‘value’ and return those results.

Less than or Equal to `le(property,value,*interval,*start_of_interval) ` -

Less than or equal to will filter for results where the specified property's value is less than or equal to the provided value. The operator can also accept date math : (property,value,-1M,M)

Greater than `gt(property,value) `

Filters for results where the specified property's value is greater than the provided value.

Find where ‘property’ > ‘value’ and return those results.

Greater than or Equal to `ge(property,value,interval,start_of_interval) `

Greater than or equal to will filter for results where the specified property's value is greater than or equal to the provided value. The operator can also accept date math : (property,value,-1M,M)

Not equal to `ne(property,value)`

Not equal to filters for results where the specified property's value is not equal to the provided value

Find where ‘property’ is NOT equal to ‘value’ and return those results.

Freetext query

Freetext queries are used to match ‘keyword’ typings mainly, excluding the ‘Regex’ operator. You can find the typings of your column on the dataset page within page 3 of main info.

Regex Matcher `re(property,value) ` -

Filters for objects where the specified property's value is in the regular expression value

  • `match(property,valueA valueB ...) ` - Matches objects where the specified property's value contains any of the provided, space separated, values (ordered by number of words matching)

  • `matchphrase(property,valueA valueB ...)) ` - Matches objects where the specified property's value contains the exact phrase made up of comma-separated values

  • `matchphrase(property,valueA valueB ...,slopFactor)) ` - Matches objects where the specified property's value contains the comma-separated values in the order specified, but they may be separated by the slopFactor number of other words

Aggregation query

  • `count(property)` or `value_count(property)` - Count the number of occurrences of a property

  • `sum(property)` - Sum all values of this property

  • `avg(property)` - Calculate the average value of this property

  • `min(property)` - Find the minimum value of this property

  • `max(property)` - Find the maximum value of this property

  • `stats(property)` - Get a full statistical breakdown for this property (value_count, sum, avg, min, max)

  • `aggregate(groupingPropertyA,groupingPropertyB,...,operation(property),limit)` - aggregate values of a property grouped by one or more other properties; for 'operation' use value_count, sum, avg, min, max; if multiple operations are required, provide them in a comma separate list enclosed by brackets; to limit number of returned aggregate rows to top X set 'limit' to an integer (default value is 100000)

Special Operations

Spatial query

  • `withinBoundingBox(geometryProperty,(minX,maxX,minY,maxY)) ` - Filters for objects where the specified geometry property's coordinates fall completely within the bounding box as defined by minimum and maximum X and Y values

 Combining queries

  • `and(query,query,...) ` - Applies all the given queries

  • `or(query,query,...) ` - The union of the given queries

Sorting, selecting return values and paging

  • `sort(+|-propertyA,+|-propertyB,...) ` - Sorts by the given property/properties in order specified by the prefix (+ for ascending, - for descending)

  • `select(property,property,...) ` - Trims each object down to the set of properties defined in the arguments

  • `limit(numberOfRecords,offset) ` - Returns the given range of objects from the result set (numberOfRecords = how many records should be returned, offset = how many records should be skipped; to return a stream of all records call 'limit(-1)'.)

Cacheing

Cacheing can be used to help improve query return reliability and speed up the return of data from a dataset. This is done by adding the term cache=true

cache=true

. An example of that being used is shown below:

  • https://api.usmart.io/org/92610836-6c2b-4a26-a0a0-b903bde0dc46/4dd7a850-858c-4e65-a456-772f76e93ec1/latest/urql?limit(-1)&gt(Timestamp,Date1)&cache=true

Others

  • `format(ouputFormat) ` - Sets the output format - valid values are 'json' (default) and 'csv' (comma separated text file).

 

Related articles