REST API query schema

Many REST APIs allow passing a query as a parameter to limit the scope of the data returned or modified by the API. The query parameter is a JSON object representing a SQL query to be applied against a collection. The structure of the query JSON object is as follows:

Query structure

CODE

  interface APIQuery {
    SELECTCOLUMNS?: string[];
    SORT?: QuerySortDirections[];
    FILTERS?: TriggerQueryFilter[][];
    PAGESIZE?: number;
    PAGENUM?: number;
    DISTINCT?: string;
    GROUPBY?: string[];
    RAW?: string;
  }

As indicated by the ? characters, SELECTCOLUMNS, SORT, FILTERS, PAGESIZE, PAGENUM, DISTINCT, GROUPBY, and RAW are all optional.

SELECTCOLUMNS attribute

The SELECTCOLUMNS attribute allows the developer to limit the data columns returned by the query. In SQL parlance, SELECTCOLUMNS is equivalent to the column select list in a SQL statement:

select column1, column2, … columnN from my_table;

If SELECTCOLUMNS is not specified, all columns from the database table will be returned, similar to a SQL statement of the form:

select * from mytable;

Example

The SQL query “select name, address, mobile_number from contacts_list;” would be represented in the APIQuery structure as

JSON

{
  "SELECTCOLUMNS": ["name", "address", "mobile_number"]
}

SORT attribute

The SORT attribute allows the developer to specify the sorting that should be applied to the returned data. The SORT attribute is equivalent to the SQL ORDER BY clause.

CODE

  enum QuerySortDirections {
    QUERY_SORT_ASCENDING = "ASC",
    QUERY_SORT_DESCENDING = "DESC",
  }

CODE

  type QuerySortDirections = {
    [key in QuerySortDirections]?: string;
  };

Example

The SQL query “select * from sales_records ORDER BY customer_id ASC order_date DESC;” would be represented in the APIQuery structure as:

JSON

{
  "SORT": [
    {
      "ASC": "customer_id"
    },
    {
      "DESC": "order_date"
    }
  ]
}

FILTERS attribute

The FILTERS attribute allows the developer to supply conditions to the query to limit the returned or updated data. The FILTERS attribute is equivalent to the SQL WHERE clause.

CODE

  enum QueryFilterConditions {
    QUERY_EQUAL = "EQ",
    QUERY_NOTEQUAL = "NEQ",
    QUERY_GREATERTHAN = "GT",
    QUERY_GREATERTHAN_EQUAL = "GTE",
    QUERY_LESSTHAN = "LT",
    QUERY_LESSTHAN_EQUAL = "LTE",
    QUERY_MATCHES = "RE",
  }

CODE

  type TriggerQueryFilter = {
    [key in QueryConditions]?: Record<string, QueryValue>;
  };

If using QUERY_MATCHES, the supplied regular expression must be in PCRE syntax

Example

The SQL query “select * from sales_records where subtotal > 1000.00;” would be represented in the APIQuery structure as:

JSON

{
  "FILTERS": [
    [
      {"GT": { "subtotal": 1000.00}}
    ]
  ]
}

Filters and logical operators

Currently, only one method supports applying logical operators to a query. The FILTERS attribute consists of an array of arrays. The elements in the outer array represent filter conditions that should be OR’d together, while elements in the inner array represent conditions that should be AND’d together.

Example

The SQL query “select * from sales_records where subtotal < 100.00 OR subtotal > 1000.00;” would be represented in the APIQuery structure as:

JSON

{
  "FILTERS": [
    [
      {"LT": { "subtotal": 100.00}}
    ],
    [
      {"GT": { "subtotal": 1000.00}}
    ]
  ]
}

Example

The SQL query “select * from employees where (hire_date >= ‘1/1/2023’ and hire_date <= ’12/31/2023’) OR (termination_date >= ‘1/1/2023’ and termination_date <= ’12/31/2023’);” would be represented in the APIQuery structure as:

JSON

{
  "FILTERS": [
    [
      {"GTE": { "hire_date": '1/1/2023'}},
      {"LTE": { "hire_date": '12/31/2023'}}
    ],
    [
      {"GTE": { "termination_date": '1/1/2023'}},
      {"LTE": { "termination_date": '12/31/2023'}}
    ]
  ]
}

DISTINCT attribute

The DISTINCT attribute allows the developer to filter out duplicate rows retrieved from a collection. The DISTINCT attribute is equivalent to the SQL DISTINCT clause.

The SQL query “SELECT DISTINCT salary FROM employees;” would be represented in the APIQuery structure as:

JSON

{
  "DISTINCT": "salary"
}

GROUPBY attribute

The GROUPBY attribute allows the developer to group a query’s result set by one or more columns. The GROUPBY attribute is equivalent to the SQL GROUPBY clause.

The SQL query “select * from sales_records GROUP BY customer_id, purchase_date;” would be represented in the APIQuery structure as:

JSON

{
  "GROUPBY": ["customer_id", "purchase_date"]
}

RAW attribute

The RAW attribute allows the developer to specify a raw SQL query to execute against the database.

The SQL query “select * from sales_records ;” would be represented in the APIQuery structure with the RAW attribute as:

JSON

{
  "RAW": "select * from sales_records;"
}