Hotelgyms Documentation

Querying the API

This documentation explains how to use the QuickAPI Query Language (QQL) supported by all our CSV APIs, which follows the json:api specification. We can use a variety of operators to construct queries against our data.

Query Parameter Families

The way to query our data and filter it using query arguments is via a Query Parameter Family.

That means that all arguments must belong to a family to be used, that is, if we have a column in our CSV called is_active, for example, we do not do it by sending the name of the column at the first level of the argument.

GET /myEndpoint?is_active=true # 🙅‍♂️ `is_active` cannot be at the top level
GET /myEndpoint?is_active=true # 🙅‍♂️ `is_active` cannot be at the top level

Instead, if we want to filter that column, it must belong to the filter family:

GET /myEndpoint?filter[is_active]=true # đź‘Ť
GET /myEndpoint?filter[is_active]=true # đź‘Ť

Note that is_active is now inside two square brackets [].

The square brackets always enclose the column name, followed by the value we want to filter, of course, right after the filter Query Family.

We can also use dots (.) to separate the query family from the column name, and the operator:

GET /myEndpoint?filter.Year.eq=2000
GET /myEndpoint?filter.Year.eq=2000

If we do not specify an operator, by default it will be assumed that it is an equality comparison eq.

# Both give the same result
GET /myEndpoint?filter.Year=2000
GET /myEndpoint?filter.Year.eq=2000
# Both give the same result
GET /myEndpoint?filter.Year=2000
GET /myEndpoint?filter.Year.eq=2000

We can also combine many filters in a single query:

GET /users?
  filter[name][eq]=Alice&
  filter[age][between]=25:35&
  filter[email][notlike]=@spam.com
GET /users?
  filter[name][eq]=Alice&
  filter[age][between]=25:35&
  filter[email][notlike]=@spam.com

This returns users named "Alice" between ages 25 and 35 whose email does not contain @spam.com.

Supported Query Parameter Families

  • filter

    All of our CSV APIs support the following filtering operators:

    • eq: Equal to
    • ne: Not equal to
    • between: Between two values (inclusive)
    • notbetween: Outside a range
    • like: Case-insensitive substring match
    • notlike: Case-insensitive substring exclusion
    • startswith: Value starts with a given string
    • endswith: Value ends with a given string
    • null: Check for null
    • gt: Greater than
    • gte: Greater than or equal to
    • lt: Less than
    • lte: Less than or equal to
    • in: Match one of several values
    • notin: Exclude a list of values

  • page

    The page query family will help us to manage the pagination of the responses.

    • number.

      • Represents the current page number.
      • Starts from 1.
      • Determines which subset of the data is retrieved.

    • size.

      • Represents the number of records per page.
      • Controls how many items are returned in a single API response.
      • Default to 20.
      • Maximum of 200.

    The following formula is used to fetch the correct subset of data:

    offset = (page - 1) * pageSize
    offset = (page - 1) * pageSize
    • Page 1 → offset = (1–1) * 5 = 0 (fetch 1–5)
    • Page 2 → offset = (2–1) * 5 = 5 (fetch 6–10)
    • Page 3 → offset = (3–1) * 5 = 10 (fetch 11–15)

    Example:

      GET /myEndpoint?page[number]=1&page[size]=200
      GET /myEndpoint?page[number]=1&page[size]=200
     
      # Use the page query family using dots
      GET /myEndpoint?page.number=50&page.size=5
      # Use the page query family using dots
      GET /myEndpoint?page.number=50&page.size=5

  • fields

    Controls which columns of our datasource the API will return. If this argument is not specified, all columns will be returned by default.

    • include

      The value of any fields[type] parameter must be a comma-separated list that refers to the name(s) of the fields to be returned.

      # Return only the id, age, and name columns.
      GET /myUsersEndpoint?fields[include]=id,age,name
      # Return only the id, age, and name columns.
      GET /myUsersEndpoint?fields[include]=id,age,name
       
      {
        "data": [
          {
            "id": 1,
            "age": 44,
            "name": "Rick"
          },
          {
            "id": 2,
            "age": 50,
            "name": "Carol"
          }
        ]
      }
      {
        "data": [
          {
            "id": 1,
            "age": 44,
            "name": "Rick"
          },
          {
            "id": 2,
            "age": 50,
            "name": "Carol"
          }
        ]
      }
       
      # Return all columns.
      GET /myUsersEndpoint
      # Return all columns.
      GET /myUsersEndpoint

      When we send the parameter include but do not specify columns, we will get an error:

      # Bad Request. No columns to select
      GET /myUsersEndpoint?fields[include]=
      # Bad Request. No columns to select
      GET /myUsersEndpoint?fields[include]=

      If no include or exclude parameter is specified within the fields family, include will be used by default:

      # These fields will be included if we omit the [include] operation
      GET /myUsersEndpoint?fields=id,name

      # Is equivalent to:
      GET /myUsersEndpoint?fields[include]=id,name
      # These fields will be included if we omit the [include] operation
      GET /myUsersEndpoint?fields=id,name

      # Is equivalent to:
      GET /myUsersEndpoint?fields[include]=id,name

    • exclude

      Exclude these fields from the final answer.

      # Will return all fields except «password_hash»
      GET /myUsersEndpoint?fields[exclude]=password_hash
      # Will return all fields except «password_hash»
      GET /myUsersEndpoint?fields[exclude]=password_hash
       
      {
        "data": [
          {
            "id": 1,
            "age": 44,
            "name": "Rick",
            "description": "The Ones Who Live",
            "is_active": true
          },
          {
            "id": 2,
            "age": 50,
            "name": "Carol",
            "description": "Fierce Warrior",
            "is_active": false
          }
        ]
      }
      {
        "data": [
          {
            "id": 1,
            "age": 44,
            "name": "Rick",
            "description": "The Ones Who Live",
            "is_active": true
          },
          {
            "id": 2,
            "age": 50,
            "name": "Carol",
            "description": "Fierce Warrior",
            "is_active": false
          }
        ]
      }

      Unlike include, exclude does not have a short or abbreviated form for using it, so we have to specify the complete family and operation (e.g. fields[exclude]) we want to perform to use it:

      GET /myEndpoint?fields=city,name # <- abbreviated form of `fields[include]`

      GET /myEndpoint?fields[exclude]=id,age # <- exclude does not have an abbreviated form
      GET /myEndpoint?fields=city,name # <- abbreviated form of `fields[include]`

      GET /myEndpoint?fields[exclude]=id,age # <- exclude does not have an abbreviated form

      This also means that if one of the two full forms of fields is present (either &fields[include]= or &fields[exclude]=), the short form of include will be ignored.

      # «city» and «name» are ignored and «id» and «age» are returned:
      GET /myEndpoint?fields=city,name&fields[include]=id,age

      # The same applies to exclude:
      GET /myEndpoint?fields=id,age&fields[exclude]=name
      # This will return ALL available columns except «name»
      # «city» and «name» are ignored and «id» and «age» are returned:
      GET /myEndpoint?fields=city,name&fields[include]=id,age

      # The same applies to exclude:
      GET /myEndpoint?fields=id,age&fields[exclude]=name
      # This will return ALL available columns except «name»

      When both include and exclude properties are provided, the exclude property will take precedence. This means if a field is listed in both include and exclude, it will be excluded from the result set. For example, specifying the exact same fields in both include and exclude will result in an empty set of columns, causing an error:

      # Bad Request. No columns to select
      GET /myUsersEndpoint?fields[include]=password_hash&fields[exclude]=password_hash
      # Bad Request. No columns to select
      GET /myUsersEndpoint?fields[include]=password_hash&fields[exclude]=password_hash

      In other words, if we only include in the answer the columns a, b, and c, and we also exclude the column c, we will only end up with a and b:

      # This answer will only include "a" and "b"
      GET /myUsersEndpoint?fields[include]=a,b,c&fields[exclude]=c
      # This answer will only include "a" and "b"
      GET /myUsersEndpoint?fields[include]=a,b,c&fields[exclude]=c

  • sort

    The sort query family enables sorting data by one or more criteria.

    • The sort parameter accepts a comma-separated list of fields.
    • Prefix a field with - (a minus sign) to indicate descending order.
    • Fields without a prefix are sorted in ascending order by default.

    Example:

    # Sorts rows by `title` in ascending order (A–Z):
    GET /myEndpoint?sort=title
    # Sorts rows by `title` in ascending order (A–Z):
    GET /myEndpoint?sort=title
     
    # Sorts rows by `createdAt` in descending order (newest first):
    GET /myEndpoint?sort=-createdAt
    # Sorts rows by `createdAt` in descending order (newest first):
    GET /myEndpoint?sort=-createdAt

    The following query sorts the data by «country» in ascending order and by the column «name» in descending order. This means that it orders it by Country, but if some rows have the same Country, it orders them by the column «name»:

    # Sort by several columns, comma-separated
    GET /myEndpoint?sort=country,-name
    # Sort by several columns, comma-separated
    GET /myEndpoint?sort=country,-name

Meta

The top level key meta is used for:

  • Include non-standard, extra information related to your datasource.
  • Details such as pagination info, record counts, processing times, datasource schema, or any other contextual data.

Example:

{
  "data": [
    {
      "id": "1",
      "type": "articles"
    }
  ],
  "meta": {
    "rows": 100,
    "page": 1,
    "pageSize": 10,
    "elapsed": 0.077545,
    "schema": [
      {
        "cid": 0,
        "name": "id",
        "type": "BIGINT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      },
      {
        "cid": 1,
        "name": "type",
        "type": "TEXT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      }
    ]
  }
}
{
  "data": [
    {
      "id": "1",
      "type": "articles"
    }
  ],
  "meta": {
    "rows": 100,
    "page": 1,
    "pageSize": 10,
    "elapsed": 0.077545,
    "schema": [
      {
        "cid": 0,
        "name": "id",
        "type": "BIGINT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      },
      {
        "cid": 1,
        "name": "type",
        "type": "TEXT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      }
    ]
  }
}

Although we can use the top level meta key to know the schema of our datasource, like column name, type, if it accepts null or not, for example, we can also use this endpoint separately, when we are only interested in knowing the schema of our data without actually issuing a query to our datasource:

GET /api/myCSVEndpoint/schema
GET /api/myCSVEndpoint/schema

Just add /schema to the end of the URL.

Example response:

[
    {
        "cid": 0,
        "name": "poll_id",
        "type": "BIGINT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
    },
    {
        "cid": 1,
        "name": "pollster_id",
        "type": "BIGINT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
    },
    {
        "cid": 2,
        "name": "pollster",
        "type": "TEXT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
    }
]
[
    {
        "cid": 0,
        "name": "poll_id",
        "type": "BIGINT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
    },
    {
        "cid": 1,
        "name": "pollster_id",
        "type": "BIGINT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
    },
    {
        "cid": 2,
        "name": "pollster",
        "type": "TEXT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
    }
]
logoPowered by QuickAPI.io