NOLS API query language documentation

Summary

We use a domain specific language (DSL) embedded in HTTP query strings to provide an interface for querying the datasource, analogous to SQL statements. Simple and powerful, but does not provide a lot of debugging feedback.

The body of the query, no matter how simple or complex is the value of the 'query' (or shortened form 'q') parameter.

.../some_resource/?query=<a really long and complicated set of conditions>&_fields=some_field&_limit=5

I'll be using courses for most of the examples, but the logic applies to almost any endpoint that returns a list in the nols-api.

Reserved Characters: ? & = ! ( ) [ ] { } > < ^ |

Exact Matches

Straightforward query string (case-insensitive):

.../courses/?query=code=ssr
.../courses/?q=code=SSR
.../courses/?query=code=SsR

will all select courses that have the code ssr exactly (no partials).

Wildcard Matches

Simple partial matching (case-insensitive):

'*' matches any string of zero or greater length.

Wildcards can only appear at the start or end of the value, not in the middle.

.../courses/?query=code=wa

matches courses of the form (no wildcards): WA-1-02/02/2004, WA-10/20/2007 , etc.

.../courses/?query=code=wa*

matches courses that start with wa: WA-1-02/02/2004, WAFA-02/06/2004, WAD-06/16/2004, etc.

.../courses/?query=code=*wa

matches courses that end with wa: CWA-02/16/2001, WA-1-02/02/2004 , etc.

.../courses/?query=code=*Wa*

matches courses that contain wa: CWA-02/16/2001, WA-1-02/02/2004, WAFA-02/06/2004, WAD-06/16/2004, etc.

Null Matches

Query for null values (case-insensitive):

.../courses/?query=end_date=null

matches courses that have no end date.

Boolean Matches

Query for true/false values (case-insensitive):

.../courses/expedition/?q=has_self_screen_med=true
.../courses/expedition/?query=has_self_screen_med=false

works only on fields that are boolean values. For other types of fields it is interpreted as the string 'true' or 'false'.

Logical Operators

Work the way you expect them to, with normal precedence: AND is higher than OR.

  • '^' is the AND operator
  • '|' is the OR operator
  • '(' ')' use parentheses to override normal precedence
.../courses/?query=(code=ssr|code=fsr)^capacity>15

will match all spring/fall semesters in the rockies with capacity > 15. Without parentheses it would match all SSRs regardless of capacity and all FSRs with capacity > 15.

Inequality Operators

Standard ones you learned in algebra:

  • '<' is the LESS THAN operator
  • '<=' is the LESS THAN OR EQUAL operator
  • '>' is the GREATER THAN operator
  • '>=' is the GREATER THAN OR EQUAL operator
.../courses/?query=capacity>=10^capacity<14

matches courses that have capacity greater than or equal to 10 and less than 14: 10, 11, 12, 13.

.../courses/?query=start_date>=2014-01-01^start_date<=2014-01-31

matches courses that started in January of 2014. Of course, you could do this more simply by:

.../courses/?query=start_date=2014-01*

.../courses/?query=start_date<1968
.../courses/?query=start_date<=1967-12-31

do the same thing, match courses that started before 1968.

List Matches

Query for any of a list values (case-sensitive):

.../courses/?query=code=[SSR,WAD,ACS]

matches courses that have any of the three codes: ACS-07/06/2016, SSR-1-02/14/2017, WAD-06/16/2004, etc.

Matching With Dates

All the examples so far have had naive dates of the ISO-8601 form "YYYY-MM-DD" i.e., no time or timezone information. Datetime values are perfectly acceptable in the ECMA-262 format: "YYYY-MM-DDTHH:mm:ss.sssZ". Bear in mind that any missing values are filled in with best guesses: months and days are assumed to be 1 and hours, minutes, seconds are assumed to be 0. Timezone is assumed to be UTC.

Inequality matches work the way you expect, wildcards work almost the way you expect. '2015*' will match anything in the year 2015, '1967-01-12*' will match anything on January 12, 1967, but '1967-01-12T11*' will match nothing, ever. Stick to inequalities if you're interested in hourly slices or smaller.

And while we're on the subject, dates that are treated as dates with no time (like course start_date) are actually stored in the database as having a time of midnight. Unfortunately for us, our database currently uses local (Mountain) time not UTC, so things don't work the way you might like.

.../courses/?query=start_date=1960-01-01

Should match ROPE-01/01/1960, but no dice, because midnight is shifted to UTC, blah blah blah. For dates that have no time component (or shouldn't), stick with wildcards:

.../courses/?query=start_date=1960-01-01*

Will do what you want it to.

Negative Matches

Negation of any of the queries above:

.../courses/?query=code!=ss*
.../courses/?query=code!=[SSR,WAD,ACS]
.../courses/?query=capacity!=0
.../courses/?query=end_date!=null

You get the idea.

.../courses/?query=capacity!<=5

won't work, but you could write it like this instead:

.../courses/?query=capacity>5

Escaping Characters

You can escape reserved characters ? & = ! ( ) [ ] { } > < ^ | with '\' to use as data:

.../people/query=?name_last=*\(JT\)*

Will search for last names that contain '(JT)'.

.../people/query=?name_last=*(JT)*

Will raise an error.

But wait! There's more...

You can combine all these things into all kinds of interesting queries:

.../courses/expedition/?query=sponsor=[Rocky%20Mountain,Teton+Valley]^capacity=0^start_date=2014*&_fields=name,sponsor

To see the Rocky Mountain and Teton Valley courses in 2014 that were planned but never ran.

Gotchas

  • Beginning wildcard queries are not efficient. Any '*value' match will ignore database indexing and do a lot of work, so use them with caution, especially with large resources (think people or courses or event-logs).

  • List searches are case-sensitive. They are the only one that is, so if your list search is not getting the results you want, check your capitalization.

.../addresses/?query=status=[permanent,temporary]^city=lander

will return nothing because the values are Permanent and Temporary.

  • You do have to spell correctly. But if you enter a field nols-api doesn't understand, it will respond with a message saying what it does understand.
.../emails/?query=addrss=*nols.edu

produces:

"Cannot resolve keyword 'addrss' into field. Choices are: id, person, address, created_at, created_by, note, status, updated_at, updated_by"

the choices might be different from the fields that are in the resource. That's because some of them just aren't searchable (derived fields, identify fields) and are filtered out.

  • Related fields (foreign keys) have a particular syntax. If you want to search for emails for a given person:
.../emails/?query=person=10000658

Note that you are searching by the foreign key id, not the full resource locator (https://.../people/10000658/).

Most often you would use a nested URL which is shorter and more clear:

.../people/10000658/emails/

but there are a few cases where a nested URL doesn't exist.

  • Diacritical marks in people names can trip you up:
.../people/query=?name_last=pape^name_first=Åsa

(matching diacritics) will find Åsa Pape.

.../people/query=?name_last=pape^name_first=Asa

(normalized first name) will not.

Use normalized name fields with normalized data:

.../people/query=?search_last=pape^search_first=Asa

will find Åsa Pape.

It is on you to use the proper data with the proper field:

.../people/query=?search_last=pape^search_first=Åsa

won't find anything because Åsa is not normalized.

Way Cool Super (Nested) Search

Take the syntax above and super-size it by calling nested resources. BAM!

  • <resource name> ':{' <sub query> '}' indicates a nested query using resource name
.../people/?query=addresses:{city=seattle^status=[Permanent,Temporary]}^search_last=h*

will find people who have active addresses in Seattle and whose last name starts with H.

.../people/?q=addresses:{region=KY}^classifications:{type=current+instructor}

will find current instructors who have (or had) an address in Kentucky. And yeah, the database stores the state abbreviation, not the state name :-(

.../courses/?query=exp-applications:{status=completed^person=<some id>}|wm-applications:{status=enrolled^person=<some id>}

will find completed expedition and wild med courses for a given person. Well, sort of: it won't find pre-electronic enrollment entries (graduaterostertab), but that whole mess is being revisited anyway.

Remember that these queries can use a lot of processing time/power. Be kind to your fellow users.

This works for people and courses only, but you can search most of the nested resources (if you have permission on those resources): emails, phones, addresses, certifications, incidents, event-logs, wm-applications, exp-applications, reservations, etc.

Nested Search Gotchas

  • You can't search for the absence of something. In other words, you can't search for people who don't have emails or certifications or whatever. If you use a resource in a query ('emails:{status..}', 'certifications:{updated_at...}') you are inherently filtering by the set of people who have that resource.
  • Nested resources are indicated by resource names not resource field names. In other words, use 'event-logs' or 'wm-applications' (hyphen) not 'event_logs' or 'wm_applications' (underscore).