Query Guide

Query Types

simple - queries a field containing a value

1 {"field" : "value"}

$like - queries a field containing a like value. Values are searched by the LIKE condition. LIKE condition is case insensitive and will match any string/number containing the searched for value.

1 {"field" : {"$like" : "value"}}

$not - queries a field not containing a value.

1 {"field" : {"$not" : "value"}}

$in - queries a field containing multiple values. ANY condition must be true.

1 {"field" : {"$in" : ["value1", "value2", ...]}}

$nin - (not in) queries a field not containing multiple values. ALL conditions must be true.

1 {"field" : {"$nin" : [value1, value2, ...]}}

$or - queries multiple fields containing multiple values. ANY condition must be true.

1 {"$or": [{"field1": "value1"}, {"field2": "value2"}, ...]}

$and - queries multiple fields containing multiple values. ALL conditions must be true.

1 {"$and": [{"field1": "value1"}, {"field2": "value2"}, ...]}

$and:like - queries multiple fields containing multiple values. ALL conditions must be true. Values are searched by the LIKE condition. LIKE condition is case insensitive and will match any string/number containing the searched for value.

1 {"$and:like": [{"field1": "value1"}, {"field2": "value2"}, ...]}

$table.field - queries joined data fields for multiple values. ALL conditions must be true.

1 {"$table.field": [{"table1.field1": "value1"}, {"table2.field2": "value2"}, ...]}

$table.field:like - queries joined data fields for multiple values. ALL conditions must be true. Values are searched by the LIKE condition. LIKE condition is case insensitive and will match any string/number containing the searched for value.

1 {"$table.field": [{"table1.field1": ["value1","value2","value3"...]}...}

$table.field - MULTIPLE VALUES - Executes like other table.field queries but will return all results exactly matching any of the given values. Executes like sql IN(value1,value2,value3)

1 {"$table.field:like": [{"table1.field1": ["value1","value2","value3"...]}...}

$table.field:like - MULTIPLE VALUES - Executes like other table.field queries but will return all results including any of the given values. Executes a sql regex expression.

1 {"$table.field:like": [{"table1.field1": "value1"}, {"table2.field2": "value2"}, ...]}

$area - queries services that exist within geographical areas. $table.field is an optional search parameter that allows added filtering by taxonomy terms.
This query is only available for services and services/complete

1 {"$area": {"type":"value", "name":"value", "$table.field":[{"table1.field1": "value1"}, {"table2.field2": "value2"},...]}}

$area:like - queries services that exist within geographical areas. It will match on any LIKE area ‘name’ value. $table.field is an optional search parameter that allows added filtering by taxonomy terms.
This query is only available for services and services/complete

1 {"$area:like": {"type":"value", "name":"value", "$table.field":[{"table1.field1": "value1"}, {"table2.field2": "value2"},...]}}

distance_info - this is a subquery of $table.field, $table.field:like, $area, $area:like
This query is only available for services/complete
This will return distance information of the services location relative to the center of the input location
the distance_info accepts three options.

  1. {“type“:”city”, ”name”:”detroit”}

  2. {“type“:”zipcode”, ”name”:”49999”}

  3. {“lat“:”44.99999”, ”lng”:”-82.99999”}

distance_info for table.field queries.

1 {"$table.field": [{"distance_info": {"type":"value","name":"value"}}", {"table2.field2": "value2"}, ...]}
1 {"$table.field:like": [{"distance_info": {"type":"value","name":"value"}}, {"table2.field2": "value2"}, ...]}

distance_info for area queries.

1 {"$area": {"type":"value", "name":"value", "distance_info":{"type":"value","name":"value"}, $table.field:[...]}}
1 {"$area:like": {"type":"value", "name":"value", "distance_info":{"type":"value","name":"value"}, $table.field:[...]}}

 

 

Query Errors

Each query type will return a detailed message designed to help create a valid query.

Example:
API: locations/
Query Type: simple: {"field" : "value"}
Query: {"fail":"1"}
Error Description: In the detail section of the error message it will provide the invalid input, in this case ‘fail’ and also provide all the valid fields that could have been used in place of ‘fail’.
Sample Error message:

1 2 3 4 5 6 { "type": "cie-directory/validation-error", "title": "Invalid Query Key: Query types: simple|$in|$nin", "detail": "Key 'fail' is not a field for table 'location'. Valid fields: 'id,organization_id,name,alternate_name,description,transportation,latitude,longitude,region'", "instance": "c079323f-7f2b-4c61-98af-179860a0da65" }

 

Query Specific Examples

Call: locations/
Note: All API calls (organizations, services, locations, contacts) will be similar to this example.
Response:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 [ { "id": 969, "organization_id": 969, "name": "CHILDREN'S ADVOCACY CENTER OF SOUTHWEST MICHIGAN", "alternate_name": "BERRIEN COUNTY COUNCIL FOR CHILDREN,CHILDRENS ADVOCACY CENTER OF SOUTHWEST MICHIGAN", "description": null, "transportation": "0", "latitude": "42.0225356", "longitude": "86.4205098", "region": "GRYP" } ... ]

 

Each root field can be queried using: simple, $not, $in, $nin, $or, $and
The root fields in the above response are: id, organiazation, name, alternate_name, description, transportation, latitude, longitude, region

simple: return only locations with id 969
query: {"id" : "969"}

$not: return all location with region not equal to “GRYP”
query: {"region" : {"$not" : "GRYP"}}

$in: return all only locations with id 969 and 970
query: {"id" : {"$in" : ["969", "971", "973"]}}

$nin: return all locations with id not equal to 969 and 970
query: {"id" : {"$in" : ["969", "971", "973"]}}

$or: return all locations with region equal to GRYP OR transportation equal to 0
query: {"$or": [{"region": "GRYP"}, {"transportation": "0"}]}

$and: return all locations with region equal to GRYP AND transportation equal to 0
query: {"$and": [{"region": "GRYP"}, {"transportation": "0"}]}

 

Query Specific Example ‘table.field’

NOTE: Joined field querying can be non-performant and so is restricted to specific calls and specific joined fields. Currently this query is only available for locations/complete.
NOTE: If a desired query is not supported please request that it be added to the query white list.

 

Call: locations/complete

Response:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 [ { "id": 969, "organization_id": 969, "name": "CHILDREN'S ADVOCACY CENTER OF SOUTHWEST MICHIGAN", "alternate_name": "BERRIEN COUNTY COUNCIL FOR CHILDREN,CHILDRENS ADVOCACY CENTER OF SOUTHWEST MICHIGAN", "description": null, "transportation": "0", "latitude": "42.0225356", "longitude": "86.4205098", "region": "GRYP", "postal_address": [...], "physical_address": [ { "id": "2951", "location_id": "969", "attention": "CHILDREN'S ADVOCACY CENTER OF SOUTHWEST MICHIGAN", "address_1": "4938 Niles Road", "city": "Saint Joseph", "region": "Berrien", "state_province": "MI", "postal_code": "49085", "country": "USA" } ], "phones": [...], "services": [ { ... "name":"NUTRITION EDUCATION" ... } ... ], "accessibility_for_disabilities": [...] } ... ]

 

Fields within joined tables can be queried using: $table.field
Joined tables in the above response are: postal_address, physical_address, phones, services, accessibility_for_disabilities
Fields within the joined table physical_address are: id, location_id, attention, address_1, city, region, state_province, postal_code, country
NOTE: Joined fields need to be white listed to be available for query.

$table.field: return all locations with physical_address.region equal to Berrien AND service.name equal to NUTRITION EDUCATION
query: {"$table.field":[{"physical_address.region": "Berrien"},{"service.name":"NUTRITION EDUCATION"}]}

 

 

 

Query Detailed Descriptions

The query field allows specific filters to be applied to the data.
Below examples are valid queries for the /organizations and /organizations/complete calls

Query Type

Generic Example

Specific Example:

Example Description

Result Description

simple

{"field" : "value"}

{"legal_status": "0"}

  • Object 'key' (legal_status) is a field in the table organizations

  • Object 'value' (0) is the value to be searched for in the field.

The query will return all organizations with legal_status equal to 0.

$not

{"field" : {"type" : "value"}}

{"id" : {"$not" : "969"}}

  • Object 'field' (id) is a field in the table organizations

  • Object 'type' ($not) is the type of query

  • Object 'value' (969) the field will be searched for values NOT equal to this

The query will return all organizations with id NOT equal to 969.

$in

{"field" : {"type" : ["value1", "value2", ...]}}

{"id" : {"$in" : ["969", "971", "973"]}}

  • Object 'field' (id) is a field in the table organizations

  • Object 'type' ($in) is the type of query

  • Object 'value1' (969) is one element in an array of values to be searched for in the field.

The query will return all organizations with id equal to 969, 971 or 973.

$nin

{"field" : {"type" : ["value1", "value2", ...]}}

{"$nin" : ["969", "971", "3"]}}

  • Object 'field' (id) is a field in the table organizations

  • Object 'type' ($nin) is the type of query

  • Object 'value1' (969) the field will be searched for values not including this or any values in the array.

The query will return all organizations with id NOT equal to 969, 971 or 973.

$or

{"type": [{"field1": "value1"}, {"field2": "value2"}, ...]}

{"$or": [{"id": "969"}, {"legal_status": "0"}]}

  • Object 'type' ($or) is the type of query

  • Object 'field1' ($id) is a field in the table organizations

  • Object 'value1' ("969") is value to be searched for in the 'id' field

The query will return all organizations with id equal to 969 OR legal_status equal to 0

$and

{"type": [{"field1": "value1"}, {"field2": "value2"}, ...]}

{"$and": [{"id": "969"}, {"legal_status": "0"}]}

  • Object 'type' ($and) is the type of query

  • Object 'field1' ($id) is a field in the table organizations

  • Object 'value1' ("969") is value to be searched for in the 'id' field

The query will return all organizations with id equal to 969 AND legal_status equal to 1

$table.field query is only available for specific calls, tables and fields

$table.field

{"$type": [{"tableName.field": "value"}, {"tableName.field": "value"}, ...]}

{"$table.field":[{"physical_address.region": "Berrien"},{"service.name":"NUTRITION EDUCATION"}]}

  • Object 'type' ($table.field) is the type of query

  • Object 'tableName' (physical_address) is an external table that returns in the locations call as an array.

  • Object 'field' (postal_address) is a field of the tableName provided.

  • Object 'value' (49022) is the value to be searched for in the field provided.