.png){kind=logo}
GET Endpoint (POST)
- 07 Nov 2023
- 10 Minutes to read
-
Print
-
DarkLight
GET Endpoint (POST)
- Updated on 07 Nov 2023
- 10 Minutes to read
-
Print
-
DarkLight
Article Summary
Share feedback
Thanks for sharing your feedback!
GET Endpoint (POST)
Prerequisites
- Knowledge of basic SQL (preferred)
- Valid Access Token, ability to fetch records from Entities List and Schema endpoints
- Access to Clause GUI Editor Utility
- For SaaS Clients: https://yourCompanyName.CobbleStone.software/API2/Web
- For Deployed Clients (typically): https://yourCompanyName/API2/Web
Initial Preparation
The Endpoint is accessed through the url: {{baseURL}}/CSSAPI/v2/{{entityName}}/Get
- baseURL – your company’s ContractInsight URL
- entityName – name of the database tables
The HTTP request type is POST. Content type for this request is set to application/json.
Introduction
The GET Endpoint is designed in a way that it enables the client to query any Entity/Table (the ones the client is exposed to or have permissions to access) in Contract Insight database and fetch up to 1000 records per request.
Things to keep in mind when making calls to Get Endpoint:
- Get Endpoint only supports fetching 1000 records or less per request. To fetch more records, the client must program the request and set special attributes in the request body to offset by the number of records already retrieved and fetch desired number of records for the next batch.
- Get Endpoint only supports single table query requests.
- Get Endpoint will fetch and return only those fields that are not of type “binary”. To fetch files (csv or doc files for instance) associated with an Entity record, other endpoints had been created for this purpose and are described in this document.
- COUNT (*) is the only aggregate function available at the time of the writing.
Request format
The following format must be used in the request body when making calls to Get Endpoint:
| Continued from first panel |
- Fields represent a collection of the column names you specify when fetching records from the table with SQL. When collection is empty, all columns will be selected (SELECT *). Each field in the collection is an object that has 2 keys: Attribute and Alias. Attribute represents the field name in the database and Alias represents a friendly name that client can specify. To use an aggregate function such as COUNT, SUM, AVERAGE etc. (COUNT only supported at this time), we need to set the Attribute to that function in quotes.
- Clause is what defines the criteria for our request. It corresponds to a WHERE clause in SQL. WHERE clause includes predicates (expressions that result in TRUE or FALSE). And predicates can be chained and nested by means of using logical operators such as “OR” and “AND”. Clause object can be skipped altogether when you don’t need to filter the records. Filtering records will improve speed.
- Condition is always set to “AND” in the beginning when no rules are added or there is only one rule. If you need to chain conditions, that is achieved by adding rules.
- “Rules” attribute holds an array of objects that are chained by logical operator specified in “Condition” attribute of the “Clause” object. Each rule can be looked at as a Clause in itself where condition can be set, and a list of rules specified. That enables us to group predicates together.
For instance, we will see an example of building the clause containing grouped predicates for the following SQL query:
SELECT vendorname, street1, street2 FROM tblCustomers WHERE street1 <> 'Test' OR (vendorname LIKE('%chocolate%') AND street1 = 'Test');
- OrderByTag corresponds to OrderBystatement in SQL.
- Fields is a collection of strings representing columns in a table. We need to make sure to type column names as they appear in ContractInsight database
- Direction is ASC by default. We can change it to DESC.
- GroupByTag is a collection of strings that representing field names in the Entity. It corresponds to GroupBy phase in SQL.
- StartIndex corresponds to the simplest form of OFFSET function in SQL. It is set to zero if we need to fetch records starting with the first one. In other words, you may skip first N records in the resulting set.
- Length corresponds to the simplest form of FETCH function in SQL. In other words, you can take N records from resulting set starting with the record number specified by StartIndex attribute.
The request body will be translated into SQL SELECT statement by the REST API and the query will be run against the database. The returned collection of records will contain aliases (when provided) of the fields specified. The number of records will depend on the criteria set in the Clause attribute (if present) of the request object. Records are returned in JSON format.
Clause Fields Description
- Condition – refers to logical AND or logical OR. With no or only one condition, it is set to AND. We recommend using our GUI Clause editing tool to build clauses of different complexity and see how Condition will appear in the resulting clause.
- Field – the field against which the predicate is checked. For instance, column ActiveYN in tblCustomers table.
- Id – must match Field. This field is used internally by the REST API.[2]
- Type – is the field type such as Boolean, int, string etc.
- Operator – operator used in the predicate such as logical operator, comparison operator, NULL check etc
- Value – is the actual value of the tested field. For instance, in ActiveYN = true, “true” is the Value for Field ActiveYN. The value field may hold a collection of items separated by comma. For instance, if we checking for a value that is in a range of values, we would write “value”: “33, 55, 39”.
- Rules – is a collection of Clause objects that are chained together to form our WHERE Clause. The Rules can have Rules nested in them to form groups. This will become clear when we get to see the examples.
Supported Operators List
Below is the list of supported operators used in predicates of our WHERE clause:
Values used in request | GET Endpoint SQL Equivalent | Comments |
IN | IN | “value”: “33,40“ “value”: “1/1/2022, 2/2/2022” |
NOT_IN | NOT IN | “value”: “33,40” “value”: “1/1/2022, 2/2/2022” |
EQUAL | = | -- |
IS_EMPTY | = ‘’ | Works only with strings. |
NOT_EQUAL | <> | -- |
IS_NOT_EMPTY | <>’’ | Works only with strings. |
BETWEEN | BETWEEN | “value”: “33,40” “value”: “1/1/2022, 2/2/2022” |
NOT_BETWEEN | NOT BETWEEN | “value”: “33,40” “value”: “1/1/2022, 2/2/2022” |
LESS | < | -- |
LESS_OR_EQUAL | <= | -- |
GREATER | > | -- |
GREATER_OR_EQUAL | >= | -- |
BEGINS_WITH | LIKE<value%> | -- |
NOT_BEGINS_WITH | NOT LIKE<value%> | -- |
CONTAINS | LIKE<%value%> | -- |
NOT_CONTAINS | NOT LIKE<%value%> | -- |
ENDS_WITH | LIKE<%value> | -- |
NOT_ENDS_WITH | NOT LIKE<%value> | -- |
IS_NULL | IS NULL | -- |
IS_NOT_NULL | IS NOT NULL | -- |
GUI Clause Editor
CobbleStone® provides its own Clause Building utility to provide visual representation of the WHERE clause (“Clause” property in the request body) in the request body. Below are the Clause Editor’s URLs:
- For SaaS Clients: https://yourCompanyName.CobbleStone.software/API2/Web
- For Deployed Clients (typically) https://yourCompanyName/API2 (or /API)/Web
To log into the GUI Editor, you will need to enter your application client_id and client_secret. Authorization and Authentication flow happens in the same manner as authenticating with Postman for instance. Every time, the user logs in through the program, an authorization token is issued and later used to make requests to the Rest API. Please note that if you already have an application registered through ContractInsight and use it to test end points with Postman (or other programs), it is recommended that you create a separate application and use this new set of generated credentials for Clause Editor to avoid authorization token mismatch. This will enable you to build the clauses and test the end points simultaneously.
Clause building process
| First, log in with client_id and client_secret. You will be directed to the page with entity list. Entity names refer to “EntityNameDisplay” property of objects returned from EntityList endpoint. After selecting the entity of interest, clause builder will show |
 Rules Example
| Predicates are added with the clause builder:
|
 Rules and Groups Example
| The “Rules and Groups example” on the left is equivalent to the following SQL Statement:
In the above example, 3 rules are chained with OR. The third rule is a group with 2 nested rules chained with AND.
|
| After you are finished creating your clause, press “Edit Filter” and you will see your Clause section in the pop-up window.
You may copy the clause and paste it in the request body in Postman (or another program).
Clause Editor is symmetrical. You can construct or modify your clause in the request body in Postman, then paste it in this window, click “Edit Filter” button of the pop-up window (in green font) and see visual structure of the expression. |
GET Endpoint in Action (Postman Examples)
Examples below include queries against tblCustomers table.
Task 1: Get all vendors from the database.
In this task, we are selecting all fields from tblCustomers.
Request Body | |||
|
|
| |
SQL Equivalent |
Result (Partial) | ||
|
| ||
Task 2: Get a vendor by name
In this example, we are selecting all fields from tblCustomers.
Request Body | |
continued on other side |
|
SQL Equivalent | Result (Partial) |
| |
Task 3: Get a vendor by name and address
Let’s filter vendor list by name and street1 column value. We will make use of “CONTAINS” operator in this example for the street1 column filter. We are selecting VendorName and Street1 fields. For this task, VendorName must equal “CobbleStone® Systems” and Street1 must contain “Floor”.
Request Body | |
|
|
SQL Equivalent | Result (Partial) |
| |
Task 4: Getting number of active vendors by state
For this task, let’s say we need to get a count of vendors/customers by state.
Request Body | |
|
|
SQL Equivalent | Result (Partial) |
| |
Task 5: Grouped predicates
Let’s say we need to pull customers records with the following conditions:
Street1 field must NOT EQUAL “Test” OR the following condition must be met: VendorName must CONTAIN “chocolate” AND Street1 must EQUAL “Test”. SQL for this task may be written as such:
SELECT vendorname, street1, street2 FROM tblCustomers WHERE street1 <> 'Test' OR (vendorname LIKE('%chocolate%') AND street1 = 'Test');
Request Body | |
|
|
SQL Equivalent | Result (Partial) |
|
|
Task 6 Select active contracts between dates
Let’s select contracts between January, 2021 and September 30, 2022.
Request Body | |
|
|
SQL Equivalent | Result (Partial) |
|
|
Was this article helpful?
