Query params and operators

In this guide, we will go into detail on the query parameters that can be passed to the Case Keeper Core API. It is important for data security to modify each request so that the response contains only the required data. Filtering and paginating records also improves the speed of your application.

Pagination

To use pagination in our API, you can use the limit, offset and page query parameters.

Examples:

Return a set of data that skips 4 pages of 10 items and returns the next 10 items:
  GET /proprietors?page=5&limit=10

Use a defined offset:
  GET /proprietors?limit=10&offset=40

Fields

You can add a csv fields array with a list of fields you want the backend to return. All nested fields have to be defined in the array. If you'd like to only receive a field of a nested entity which has a shared name, you can use a path documentation.

Examples:

Return only id,firstName,lastName and email of the proprietors:
  GET /proprietors?fields=id,firstName,lastName,email
  returns: [{ "id": "xxx", "firstName": "John", "lastName": "Doe", "email": "john@mail.com" }]
  
Return the meetingRoomId of a proprietor's case:
  GET /proprietors?fields=id,case,meetingRoomId
  returns: [{ "id": "xxx", "case": { "id": "yyy", "meetingRoomId": "zzz" }}]
  
Returns only the id of the proprietor's case:
  GET /proprietors?fields=case.id
  returns: [{ "case": { "id": "yyy" }}]

Search

You can search through several string type fields within a table. However, you must specify which fields you're searching through with an additional searchFields query parameter. The search is a shorter query than using the $like operator, but essentially generates the same database query.

Examples:

Search through names from proprietors:
  GET /proprietors?search=James&searchFields=firstName,middleName,lastName

Order

To change the order of the records within a response, you can use the order query param. Optionally, you can add a direction and nulls operator. Defaults are direction-asc with nulls-first. Order can also be applied to fields of nested entities.

Examples:

Sort proprietors by created date: 
  GET /proprietors?order=createdAt

Sort proprietors by first name desc having null values at the end: 
  GET /proprietors?order=firstName-direction-desc&order=firstName-nulls-last

Sort proprietors by case status and first name:
  GET /proprietors?order=case-status&order=firstName

Filter

When we filter, we often need an option to filter with partial values, or a value must be between certain boundaries. In our API, we use a $ dollar sign to identify an operator. Our operators are named intuitively to make it easy to understand a query.

We also added a feature to make regular queries easier to write in frontend languages by making it possible for the parameter name to be a string rather than requiring an object to be parsed. This is done by adding a - symbol between what would be a nested field.

These three examples perform the same query:

?firstName-$like=Brecht (MAC string notation)
?firstName[$like]=Brecht (URLSearchParams parsed object notation)
?firstName={"$like":"Brecht"} (JSON notation)

All of the operators below are supported by the Case Keeper Core API. Some other backend modules may not support filtering with operators due to not using the same NodeJS database library. Please contact our support for feature requests.

Operator	Example query	Description
`$eq`	`?firstName-$eq=James`	(default) Strict equal operator, value must be string or number.
`$like`	`?firstName-$like=Jam`	Partial non case sensitive match for a string field.
`$moreThan`	`?width-$moreThan=4`	Return records with a field value higher than the query.
`$lessThan`	`?createdAt-$lessThan=2022-10-31T10:00:00.000Z`	Return records with a field value lower than the query.
`$between`	`?processes-$overlaps= frontIdCard,backIdCard`	Filter an array field that contains all the input values.
`$in`	`?province-$in=Songkhla,Yala`	Filter a string field with a set of possible values.
`$isNull`	`?verifiedAt-$isNull`	Return records that have a null value in the field.
`$not`	`?verifiedAt-$not-$isNull`	Return all records that do not match a query for specified field.
`$jsonEq`	`?config-$jsonEq-required="true"`	Strict equal operator on a json nested field. Input value must be parsed to a string. (add double quotes to booleans and numbers)
`$jsonIn`	`?config-$jsonIn-notifyType=sms,email`	Filter a nested json field by a set of possible values.
`$jsonLike`	`?frontIdCardResult-$jsonLike-citizenId=4444`	Filter a nested json field by a subset of a string match.

OR queries

In some cases, you need to perform several queries and return records that match either of the queries. Let’s say you want a list of records that are not expired or do not have an expiry date. For this, we have a dedicated query field. The or query input must be an array using a parsed URLSearchParam notation, but the individual queries do support our string notation.

Any query params that are not within the or input, shall be parsed as an additional required query.

Examples:

Filter proprietors with a first name contains James or a last name contains Doe:
  GET /proprietors?or[0][firstName-$like]=James&or[1][lastName-$like]=Doe

Filter beneficiaries with a first name James or a last name Doe:
  GET /proprietors?or[0][firstName]=Brecht&or[1][lastName]=Pallemans&proprietorType=beneficiary