Skip to content

Querying Data

The following query strings are available to sort, filter, and provide pagination of resources requested via the API. It is possible to use these on most of the endpoints provided that are tables that just access database resources. Any Remote Procedure Call endpoints will not do anything with these query string options.

Filtering

Queries can by made on any field in the model using =, <, >, >=, <= symbols. Incorrectly formatted queries are ignored. Queries currently will not work on fields named 'format', 'count', 'limit', ' offset', 'page', 'sort', and 'fields'. Use & for queries with logical AND, use either , or | for logical OR. The order of operations is the same as for general Boolean logic, with AND taking higher precedence than OR. As a result, queries like ?f1=1&f2=2.1,f2=2.2&f3=3 are translate as "( f1 = 1 AND f2 = 2.1) OR (f2 = 2.2 OR f3 = 3)".

Nesting of queries is supported using parentheses ( and ). This allows for specifying complex conditions for fetching data. These subqueries can be nested arbitrarily deep, e.g. /weather?(city=victoria|city=nanaimo)&(weather=sunny|(weather=rain&precipitation<0.5))

Array queries are also supported, with the syntax being ?fieldname={val1,val2,val3}. These style queries are directly translated to SQL as SELECT * FROM tablename WHERE fieldname = val1 OR fieldname = val2 OR fieldname = val3.

If values contain commas or ampersands that might confuse the interpreter, surround your values with double or single quotes to ensure that those characters are preserved as a single value. To escape a double quote in the value, use single quotes (e.g. ?foo='"bar"').

GET /workareas?name=Hakai
GET /events?start_t>2016-03-01&end_t<=2016-04-01
GET /events?start_t>2016-03-01,start_t=2016-04-01

Fuzzy matching

Functionality is available for doing fuzzy string matches with the ~~* comparator. For example, foo~~*bar will return all records where foo = 'bar', 'Bar', 'BAR', 'bAr', ...

You may also pass wildcard values like the ones Postgres' ILIKE method expects (see here). For example device_model~~*RBR% will match records where device_model === RBRconcerto and RBRmaestro.

Array contains

If one wishes to see if a comma separated text string contains the values of another array in whatever order, this can be performed with the @> comparator. For example, field_staff@>{Emma,midoli} will return rows where field_staff contains **at least both of ** 'midoli' and emma'. It will not return queries where not all of the passed values are contained. This type of query is case insensitive by default. The current implementation of this functionality requires that the database column is a comma separated string and will not work on columns that are of array type.

e.g.

// GET eims/views/output/chlorophyll?survey@>{KWAK}&fields=survey&limit=-1&distinct

// returns:
[
  {"survey": "KWAK,KWAK_PP"},
  {"survey": "RVRS,KWAK"},
  {"survey": "KWAK"}
]

// GET eims/views/output/chlorophyll?survey@>{KWAK,"KWAK_PP"}&fields=survey&limit=-1&distinct
// Note: double quotes around the values are optional
// returns:
  [
  {"survey": "KWAK,KWAK_PP"}
  ]

Array overlap

If one wishes to see if a comma separated text string overlaps the values of another array in whatever order, this can be performed with the && comparator. For example, field_staff&&{Emma,midoli} will return rows where field_staff contains at least one of 'midoli' or 'emma'. This type of query is case insensitive by default. The current implementation of this functionality requires that the database column is a comma separated string and will not work on columns that are of array type.

e.g.

// GET /eims/views/output/chlorophyll?survey&&{"KWAK",KWAK_PP}&fields=survey&limit=-1&distinct
// Note: double quotes around the values are optional

// returns:
[
  {"survey": "KWAK,KWAK_PP"},
  {"survey": "KWAK_PP"},
  {"survey": "RVRS,KWAK"},
  {"survey": "KWAK"}
]

Null queries

A field can be tested to see if it is null by doing fieldname=null or fieldname!=null. You may test for a string "null" by wrapping the right hand side of the query in double or single quotes e.g ?foo='null'.

Sorting

Sort in reverse by name, then by start time.

GET /workareas?sort=-name,start_t

Field selection

GET /organizations?fields=name,id

Pagination

Use limit and page or limit and offset. The default limit is 20, default offset is 0. There is a default limit because it is good practice to use pagination in applications to allow them to scale better. The limit can be shutoff by passing a value <= 0.

page will calculate the offset for you, assuming that each page has a number of records equal to the specified limit.

GET /samples?offset=10&limit=5

GET /samples?page=0&limit=5

Column metadata

Get metadata about columns in table samples

GET /samples?meta

Distinct values

Get distinct combinations of sample_type and event_id

GET /samples?fields=sample_type,event_id&distinct

Count values

Get the count of rows matching a query. Currently only supports the first column in fields if distinct is also specified.

GET /samples?sample_type='zooplankton'&count

Date querying

Date or DateTime fields in the database can be queried using the special .DATEPART syntax. That is, you can query on specific parts of the date value, like the year, month, day, or day of week.

GET /samples?sample_date.year=2021
GET /samples?sample_date.month=2  // (1 - 12)
GET /samples?sample_date.day=30  // (1 - 31)
GET /samples?sample_date.hour=13  // (0 - 23)
GET /samples?sample_date.minute=59  // (0 - 59)
GET /samples?sample_date.second=40  // (0 - 59)
GET /samples?sample_date.dow=0  // Day of week (0 - 6; Sunday is 0)
GET /samples?sample_date.doy=364  // Day of year (1 - 365/366)

You can also query on dates using ISO date strings.

GET /samples?sample_date>2011-10-05T14:48:00.000Z

Time zone selection

By default, data served through the hakai-excel-downloader (.xlsx formats) is converted to local time (UTC/GMT -7/8 hours), while json and .csv are in UTC. The desired time zone can be specified via the Portal in the Data Tools Download page. Navigate to the Data Tools Download page, select the 'Options' menu and select the 'Time Zone' check box. A Time zone row will appear below the search filters. Begin typing the desired time zone location, or select from the drop down. The selected time zone will be added to the API query.