# Query

## /v1/api

<mark style="color:green;">`POST`</mark> `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 | <p>Authentication token<br>JS e.g. `Bearer ${authResponse.jwt}`</p> |

#### Request Body

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

{% tabs %}
{% tab title="200 Rows successfully retrieved" %}

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

{% endtab %}

{% tab title="400 Could not execute this query." %}

```
{
  "code": 400,
  "status": "ERROR",
  "title": "Invalid Request",
  "message": "reason for the error"
}
```

{% endtab %}
{% endtabs %}

### Sample Payload

```javascript
{
  "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

```sql
   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

```typescript
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>}`