You are here

REST API Query Parameter

Subscribe to Syndicate
You can retrieve records using a query that contains one or more search terms by adding a single parameter q= to the api endpoint. Overall the syntax of the q= parameter is shown below and is explained afterwards. A query is:
  1. a condition which is a field name followed by a ':', followed by the search value, with or without quotes.
  2. multiple conditions can be used by putting a '+' between each of them
  3. single or double quotes are required if the value contains a space

 

Format

q=first_name:john+last_name:smith+-company:ibm

 

You can:
  • add multiple search conditions, separated by a '+' as long as the fields are recognized for the endpoint
  • where each each search condition is generally in the form field:value
  • negate any query by prefacing the field name with a '-'. Example a <= search, when negated, becomes >
  • put () around searches if you want to set the order of precedence. Example: (field1:value1+field2:value2)|(field3:value3+field4:value4)

 

Search Parameters by Variable Type

Conditions used in queries vary based on variable type. The following is a generalized list:

Variable Type parameter in URL Meaning
Numeric field:value field equals value (null can be used as value)
  field:>value field greater than value
  field:>=value field greater than or equal to value
  field:<value field less than value
  field:<=value field less than or equal to value
  field:value1..value2 field must be between value1 and value2
  field:[value1, value2, value3] field must be one of value1, value2 or value3

No space is required because fields are numeric

Character field:=value field equals value using case sensitive match (null can be used as value).

eg first_name:=Bob will find Bob, but not bob

  field:='value' field equals value using case sensitive match and where value can have spaces

eg first_name:='Bob and Betty' will find Bob and Betty, but not bob And Betty

  field:*value* field contains value where * is a wildcard that marches any characters (case insensitive) before and/or after value.

eg first_name:*bob* will find Bob, bob, bobby, and bebob

field:value field is like the value using case insensitive search

eg first_name:bob will find Bob, bob but not bobby

  field:'value' field is like the value using case insensitive search where value can have spaces

eg first_name:'bob 5' will find Bob 5, bob 5 but not bobby

  field:[value1, value2, value3] field is one of value1 or value2, or value3 (case insensitive).

eg first_name:[bob, ted, alice] will find bob, Bob, ted, tED, AliCe

note: a space is required after each comma

  field:[value1*, value2*, value3] field is one of value1 or value2, or value3* (case insensitive and value3 starts with)

eg first_name:[bob, ted, ale*] will find bob, Bob, ted, alex, alecia, alexandra

note: a space is required after each comma

  field:=[value1, value2, value3] field is one of value1 or value2, or value3 (case sensitive).

eg first_name:=[Bob, Ted, alice] will find Bob, Ted, Alice, bur not bob, ted or alice

  field:[value1, value2, value3] field is like one value1, value2, or value3 (case insensitive). This uses postgres 'ilike' comparator

eg first_name:[Bob, Ted, alice] will find Bob, Ted, Alice, bob, ted, alice, teddy, bobbie, boBBie, fested ...

note: a space is required after each comma

  search:["value1", "value2"] does a postgres text search on the data in the endpoint.

eg search:["Bob", "art"] will find Bob, Bobbie, Art and Arts Management Systems (searches in all name and company fields").

Boolean field:value field equals, which is either true, false or null
Date field:value field equals value, value specifed in yyyy-mm-dd format, or null
  field:>value field greater than value in yyyy-mm-dd format
  field:>=value field greater than or equal to value in yyyy-mm-dd format
  field:<value field less than value in yyyy-mm-dd format
  field:<=value field less than or equal to value in yyyy-mm-dd format
  field:value1..value2 field must be between value1 (yyyy-mm-dd format) and value2 (yyyy-mm-dd format)
  field:[value1, value2, value3] field must be one of value1 (yyyy-mm-dd format), value2 (yyyy-mm-dd format) or value3 (yyyy-mm-dd format)

Packed Seq Fields/Packed Lists

These are special de-normalized lists of record connections. Examples of these fields are 'bought for plays', 'belongs to mail lists', 'associated with tags' where the specific record can be associated with multiple other records. A patron can belong to many mail lists. A play, donation campaign, or patron can be associated with many tags and searching for records with these markers can provide access to an arbitrary group of data, such as searching for all events with a tag.

Packed Lists field:[value1, value2, value3] field is one of value1, value2 or value3 where all values are internal record numbers that you would have found by some other means.

eg tag_ids:[5,6,7] will find all records with tag ids of 5, 6 or 7

  field:=[value1, value2, value3] field contains all seq #s of value1, value2 or value3 where all values are internal record numbers that you would have found by some other means.

eg tag_ids:=[5,6,7] will find all records marked with tag ids of 5, 6 and 7

 

Some Examples using the q= parameter are:

URL for API Purpose
https://127.0.0.1/api/v1/patrons?q=first_name:john+last_name:smith patrons with first name like john and last name like smith
https://127.0.0.1/api/v1/patrons?q=first_name:john+last_name:smith patrons with first name like john and last name like smith
href="https://127.0.0.1/api/v1/patrons?q=last_name:smith+first_name:'j%' patrons with last name of smith and a first name that starts with 'j'
href="https://127.0.0.1/api/v1/patrons?q=last_name:smith+-first_name:jane patrons with last name of smith and a first name that is NOT jane
https://127.0.0.1/api/v1/events?q=year:2016 events for the 2016 season
https://127.0.0.1/api/v1/events?q=year:>=2017 events for the 2017 season or later.
https://127.0.0.1/api/v1/events?q=title:'rolling stones concert' events called rolling stones concert
https://127.0.0.1/api/v1/events?q=title:'%stones%' events containing 'stones' anywhere in the event title
https://127.0.0.1/api/v1/events?q=active:false events that are no longer active
https://127.0.0.1/api/v1/performances?q=perform_date:>2017-12-01 performances after December 1, 2017
https://127.0.0.1/api/v1/performances?q=perform_date:>2017-12-01+-onsale_internet:null performances after December 1, 2017 that do not have an limitation to the onsale date on the internet (i.e. it is null)
https://127.0.0.1/api/v1/carts?q=status_id:4+date_checkout:>=2016-01-01+date_checkout:<=2016-06-30 carts that were checked out without issue between January 1, 2016 and June 30, 2016
https://127.0.0.1/api/v1/patrons?q=search:=['david','darwin'] patrons where first name, last name, company EQUALS 'david' or 'darwn'
https://127.0.0.1/api/v1/patrons?q=search:['david','darwin'] patrons where first name, last name, company STARTS WITH 'david' or 'darwn'
https://127.0.0.1/api/v1/patrons?q=search:'da+mc' patrons where there is both 'da' and 'mc' somewhere in the first, last, company or other text searcheable fields.
https://127.0.0.1/api/v1/events?only=id+tag_ids&q=tag_ids:5 events with the tag id of 5 on the play record.
https://127.0.0.1/api/v1/events?only=id+tag_ids&q=tag_ids:[3,4,5,6,7] events with the tag id of 3,4,5,6, or 7 on the play record.
https://127.0.0.1/api/v1/events?only=id+tag_ids&q=tag_ids:=[3,5] events with the tag id of 3 and 5 on the play record.
https://127.0.0.1/api/v1/tickets?q=event_id:51+order_id:>0 &group=event.title+performance.series_code+promotion.title +price_code+marketing.primary_address.city+ marketing.primary_address.province+ marketing.primary_address.country+ marketing.primary_address.postal_code_short &agg=count+sum(total_cost) all tickets sold to a performance and the city/country and postal/zip code