Query

Query one or more rows from the database

/v1/api

POST https://next.cloudio.io/v1/api

This endpoint allows you to perform a query against one or more data sources

Query Parameters

Name	Type	Description
csrf	string	Auth response csrf value

Headers

Name	Type	Description
Authentication	string	Authentication token JS e.g. `Bearer ${authResponse.jwt}`

Request Body

Name	Type	Description
JSON Payload	string	See below for the structure/type of the body payload

200 Rows successfully retrieved

{
  "status": "OK",
  "data": {
    "EmployeesAlias": {
      "data": [
        {
          "name": "a",
          "_rs": "Q"
        },
        {
          "name": "a",
          "_rs": "Q"
        }
      ],
      "queryElapsed": 6,
      "rowsFetched": 2
    }
  }
}

400 Could not execute this query.

Sample Payload

{
  "EmployeesAlias": {
    "ds": "Employees",
    "query": {
      "filter": [
        { "name": { "is": "Steve" } },
        { "salary": { "eq": 12345 } },
        { "lastUpdateDate": { "after": "2020-11-30T23:59:59.999Z" } }
      ],
      "projection": { "name": 1 }, // same as `selectAttributeCodeList: ["name"]`
      "sort": { "name": 1, "lastUpdateDate": -1 },
      "offset": 0,
      "limit": 10
    }
  }
}

SQL generated for the above query request

   SELECT NAME `name` FROM EMPLOYEES x
    WHERE (NAME = ? AND SALARY = ? AND LAST_UPDATE_DATE >= ?) 
 ORDER BY x.NAME ASC, x.LAST_UPDATE_DATE DESC
    LIMIT ?
   OFFSET ?

Query Type

type Query<T> = {
    aggregateList?: Aggregate<T>[];
    data?: SchemaMemberValue<T>;
    fetchDistinct?: boolean;
    filter?: Filters<T>;
    groupByAttributeCodeList?: (keyof T)[];
    limit: number;
    offset?: number;
    params?: SchemaMemberValue<T>;
    projection?: SchemaMember<T, number>;
    selectAttributeCodeList?: (keyof T)[];
    sort?: SchemaMember<T, number>;
}

type Filters<T> = (SingleFilter<T> | NestedFilter<T>)[]

Single Filters

String Filter

String filter can be used to filter attributes of the following types Email, Phone, String, URL, UserID & YN

e.g. {attributeCode: {operator: value}}

String Filter Operators

Operator	Value Type	Description
is	string	String equals
not	string	String not equals
empty	empty string	Value is NULL
notEmpty	empty string	Value is not NULL
nct	string	String Not contains
like	string	String contains
sw	string	String starts with
ew	string	String ends with
in	string[]	String equals any of the given array of strings
nin	string[]	String not equals any of the given array of strings
hasAll	string[]	String contains all of the given array of partial strings
hasAny	string[]	String contains any of the given array of partial strings
notAny	string[]	String not contains any of the given array of partial strings

Number Filter

Number filter can be used to filter attributes of the following types Decimal, Double, Integer & Percent

e.g. {attributeCode: {operator: value}}

Number Filter Operators

Operator	Value Type	Description
eq	number	Number equals
ne	number	Number not equals
null	1 - any number	Value is NULL
notNull	1 - any number	Value is not NULL
gt	number	Number greater than the given number
gte	number	Number greater than or equals to the given number
lt	number	Number less than the given number
lte	number	Number less than or equals to the given number
in	number[]	Number equals any of the given array of numbers
nin	number[]	Number not equals any of the given array of numbers
bn	number[]	Number between the given set of two numbers

Date Filter

Date filter can be used to filter attributes of the following types Date & DateTime. All date filter values must be of ISO date format e.g. 2020-11-30T23:59:59.999Z

e.g. {attributeCode: {operator: value}}

Date Filter Operators

Operator	Value Type	Description
on	date string	Date is on the given date
notOn	date string	Date is not on the given date
empty	empty string	Value is NULL
notEmpty	empty string	Value is not NULL
after	date string	Date is after the given date
before	date string	Date is before the given date
onOrAfter	date string	Date is on or after the given date
onOrBefore	date string	Date is on or before the given date
today	empty string	Date is today
yesterday	empty string	Date is yesterday
last7days	empty string	Date is in the last 7 days
last14days	empty string	Date is in the last 14 days
last28days	empty string	Date is in the last 28 days
thisWeek	empty string	Date falls in the current week
thisMonth	empty string	Date falls in the current month
thisQuarter	empty string	Date falls in the current quarter
thisYear	empty string	Date falls in the current year
bn	date string[]	Date between the given set of two dates

Nested Filters

allOf Filter

Use this to match all of the given filters. Similar to AND clause in SQL.

e.g. {allOf: Filters<T>}

anyOf Filter

Use this to match any one of the given filters. Similar to OR clause in SQL.

e.g. {anyOf: Filters<T>}

noneOf Filter

Use this to exclude all rows that matches any of the given filters. Similar to NOT(OR) clause in SQL.

e.g. {noneOf: Filters<T>}