API Generic query API
The Generic API, is a Restful API created to provide uniform access to any model entity.
It defines generic endpoints, so the API can survive future changes in the model without any upgrade of the API code.
Please beware !
Generic API bypasses all functional rules, it is thus required to use with precaution. Recommended usage is to only read data, we will not provide support if used in write mode.
Generic API doesn’t support spaces in custom field codes as space is used to separate field name and operators.
Available entities
Basically speaking, all Opencell entities can be queried using Generic API.
You can download the list of these entities on the Functional data model page or by querying the “entities” generic API (see below).
Available generic endpoints
HTTP Verb | Resource Path | Payload | Description | Response code |
|---|---|---|---|---|
GET | https://{host}/opencell/api/rest/v2/generic/openapi.json | none | returns the open api documentation of the api |
|
GET | https://{host}/opencell/api/rest/v2/version | none | returns a list of the Opencell Entreprise Edition component versions |
|
GET | https://{host}/opencell/api/rest/v2/generic/entities | As URL parameters
| returns a list of entities. These entities are accessible through generic API using their short names (see below). |
|
POST | https://{host}/opencell/api/rest/v2/generic/count/{modelName} |
| counts all {modelName} instances
|
|
POST | https://{host}/opencell/api/rest/v2/generic/all/{modelName} |
| returns all {modelName} instances e.g.
|
|
POST | https://{host}/opencell/api/rest/v2/generic/{modelName}/{ID} |
| returns the {modelName} instance with ID equal to {ID} e.g.
|
|
POST | https://{host}/opencell/api/rest/v2/generic/{modelName} |
JSON
| create a new the instance of {modelName} e.g.
|
|
PUT | https://{host}/opencell/api/rest/v2/generic/{modelName}/{ID} |
JSON
| update the {modelName} instance with ID equal to {ID} e.g.
|
|
DELETE | https://{host}/opencell/api/rest/v2/generic/{modelName}/{ID} | none | delete the {modelName} instance with ID equal to {ID} e.g.
|
|
Response customization
The following options allow to customize fields returned by the API nested entities depth.
Payload JSON object attributes | Description | Example |
|---|---|---|
CODE
| The list of the model attributes you want to exclusively include in the response. Please, note that you can include sub-fields when you have sub-models included in your response, by mentioning there path, separated by a dot, into e.g
by this payload, you can load customer’s id and code, in addition to it’s referenced sub-model customerCategory’s exoneratedFromTaxes. For this to work you need to fully load the customerCategory using | Payload
JSON
The response will then include the instance(s) id, code and description even if the model has other attributes. Response
JSON
Payload
JSON
Response will then include all non null instance(s) attributes. Response
JSON
|
CODE
| A list of fields to be excluded from response. This won’t work for nested entities, only for the main queried entity. Please note, that you should not use both | We want to fetch provider data, except for custom fields: Payload
CODE
Response Will contain all fields from Provider entity except for the custom fields ( |
CODE
| Represent the list of the model attributes that are also models themselves (sub-models e.g CustomerAccount’s BillingAccounts), you want to fully load and include in the response. If you did not mention a sub-model in nestedEntities list, only your sub-model ID will be included in the response instead of the whole object. You should note, that e.g You can load Customer → CustomerAccount → BillingAccount → UserAccount
JSON
If you use both Fields from | You need to fetch all TradingCurrencies, with there referenced/sub-model Payload
JSON
Response will then include all non null sub-model attributes of that sub-model Response
JSON
The same example without any nested entity to include Payload
JSON
Response
JSON
The response will then include only the ID of any sub-model. |
Paging and Filtering
In general, you can use filters to filter out you response, such as returning only instances that meet you filters conditions, the syntax is the following :
{
...
"filters": {
"[prefix-condition-keyword] field-Name" : value,
"[prefix-condition-keyword] field-Name" : value
}
...
}Prefix condition keywords
Condition keyword | Description |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Not equal. |
| If everything else fails, you can filter using a JPQL request. |
| useful to match a text with multiple text fields
|
Nested filters
The example below returns all wallet operation concerning “chairs”, but rated by different charges.
POST https://{host}/opencell/api/rest/v2/generic/all/walletoperation
{
"joinType": "INNER",
"filters": {
"$operator": "OR",
"code": "CH_CHAIR",
"$filter1": {
"$operator": "AND",
"code": "CH_MISC",
"wildcardOrIgnoreCase parameter2": "chair"
},
"$filter2": {
"$operator": "AND",
"code": "CH_OTHER",
"wildcardOrIgnoreCase parameter2": "chair"
}
}
}Property | Values | Description |
|---|---|---|
|
| Define the join type for nested entities. INNER: returns rows when there is a match in both tables. default: |
|
| Defines how individual items in the filter are combined. default: |
|
| A nested filter. It has the same grammer as any usual filter. |
Filtering examples
Example | Description |
|---|---|
JSON
| Here we filter by |
JSON
| This filter, applied on an account entity (such as Customer) will return all accounts where |
JSON
| Here we filter by nested entity |
JSON
| Here we filter by nested entity |
JSON
| Check if provided date is between to dates (this exemple if for wallet operation’s recurring start/end dates) |
JSON
| It is also possible to combine filters. |
JSON
| It is also possible to use SQL filtering like in the example, here we use an SQL sub query |
JSON
| SQL filter to retrieve invoices linked to invoice id=3 |
JSON
| this syntax is used to filter by Custom fields One thing to note though, is that, filtering by Custom field of complex types, such as list, maps and matrices is not supported. |
JSON
| To filter on custom field, with a list of values One thing to note though, is that, filtering by Custom field of complex types, such as list, maps and matrices is not supported. |
JSON
| To filter on custom field in a nested entity using a case-insensitive text search. |
GroupBy and Having clauses
Example | Description |
|---|---|
| By using the group by attribute, we can group rows returned that have the same values into summary rows, like "find the sum of amount without tax and amount tax having a same user account" by We can also group the rows by multiples data fields inside of an array as follows :
|
JSON
| The having clause is used to filter the rows and retrieve only those that match the requirement. It is a conditional statement that returns the results for the group created by the group by clause. Aggregation is allowed for multiple fields. For example, we want to look for walletOperations group by user account which have sum of amount without tax and amount tax greater than 250. |
JSON
| Available aggregation functions:
These are the same as typical SQL aggregation functions. |
JSON
| We can also combine different other paging and filtering criteria such as limit, offset and sortBy and sortOrder. And the fields that we want to sort by, we need to add them into genericFields to retrieve them in result |
Behaviour adaptation for huge entities
If the entity you are querying is declared as a “huge volume entity”, generic API will not return the total number of records matching the filter (no total property in response).
These greatly improves API response time as a count is a costly operation.
If you really need to know the total number of results, you can use the forceCount parameter:
{
"forceCount": true
}