GET Request - Retrieving records

19 min

GET requests allow you to retrieve information from the system, either in bulk or for a single record. The API also allows information from related tables to be included in the response.

URL Format

Single record

The format to retrieve a single record is:

https://[instance-url]/v1/[table-name]/[record-id]

Example:

https://sandbox.servicely.ai/v1/Incident/8291a83ece8811ebb1870242ac14001a

Multiple records

The format to retrieve a list of records is:

https://[instance-url]/v1/[table-name]

Example:

https https://sandbox.servicely.ai/v1/Incident

Example: GET Single record

➜  ~ https https://sandbox.servicely.ai/v1/Incident/8291a83ece8811ebb1870242ac14001a

HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Length: 618
Content-Type: application/json
...
X-Rate-Limit-Cost: 1
X-Result-Count: 0
X-Result-More: false
X-Total-Result-Count: 0

{
    "data": {
        "AssignmentGroup": "c0a8016c682a17a381682a2b5c7b000a",
        "Classification": "c0a805c8685e1cde81685e406ffd325f",
        "Closed": false,
        "CreatedBy": "4028818a3b214291013b21504a000000",
        "CreatedOn": "2021-06-16T09:52:33.544Z",
        "Email": "[email protected]",
        ....

Example: Multiple record query

➜  ~ https sandbox.servicely.ai/v1/Incident     
                                    
HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Length: 757
Content-Type: application/json
...
X-Page: 1
X-Page-Size: 200
X-Rate-Limit-Cost: 2
X-Result-Count: 2
X-Result-More: false
X-Total-Result-Count: 2

{
    "data": [
        {
            "AssignmentGroup": "c0a8016c682a17a381682a2b5c7b000a",
            "Classification": "c0a805c8685e1cde81685e406ffd325f",
            "Closed": false,
            "CreatedBy": "4028818a3b214291013b21504a000000",
            ...
            "Urgency": "medium",
            "Version": 2,
            "WorkflowStatus": "new",
            "id": "70a88bdace8811ebb1870242ac14001a"
        },
        {
            "AssignmentGroup": "c0a8016c682a17a381682a2b5c7b000a",
            "Classification": "c0a805c8685e1cde81685e406ffd325f",
            "Closed": false,
            "CreatedBy": "4028818a3b214291013b21504a000000",
            ...
            "Urgency": "high",
            "Version": 4,
            "WorkflowStatus": "new",
            "id": "8291a83ece8811ebb1870242ac14001a"
        }
    ]
}

Simple query

Simple equality queries can be performed by setting the fieldname and required value as URL query parameters.

➜  ~ https -v sandbox.servicely.ai/v1/Incident WorkflowStatus==new

GET /v1/Incident?WorkflowStatus=new HTTP/1.1

HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Length: 624
Content-Type: application/json
...
X-Page: 1
X-Page-Size: 200
X-Rate-Limit-Cost: 1
X-Result-Count: 1
X-Result-More: false
X-Total-Result-Count: 1

{
    "data": [
        {
            "AssignmentGroup": "c0a8016c682a17a381682a2b5c7b000a",
            "Classification": "c0a805c8685e1cde81685e406ffd325f",
            "Closed": false,
            ...
            "Urgency": "medium",
            "Version": 2,
            "WorkflowStatus": "new",
            "id": "70a88bdace8811ebb1870242ac14001a"
        }
    ]
}

Limit fields

The returned fields can be controlled by passing a comma delimited set of field names in the ‘fields’ URL query parameter. This applies to both single and multiple results.

➜  ~ https https://sandbox.servicely.ai/v1/Incident/8291a83ece8811ebb1870242ac14001a fields==id,WorkflowStatus,ShortDescription,AssignmentGroup

HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Length: 183
Content-Type: application/json
...
X-Rate-Limit-Cost: 1
X-Result-Count: 0
X-Result-More: false
X-Total-Result-Count: 0

{
    "data": {
        "AssignmentGroup": "c0a8016c682a17a381682a2b5c7b000a",
        "ShortDescription": "Example incident 2",
        "WorkflowStatus": "work_in_progress",
        "id": "8291a83ece8811ebb1870242ac14001a"
    }
}

Ordering

Results can be sorted by passing an ‘order’ or ‘order_desc’ query parameter with the name of the field to order by.

➜  ~ https demo.servicely.ai/v1/User order==UserName fields==UserName
HTTP/1.1 200 OK
...
{
    "data": [
        {
            "UserName": "admin"
        },
        {
            "UserName": "andrew.venn"
        },
        {
            "UserName": "benlinus"
        },
        {
            "UserName": "charliepace"
        },

➜  ~ https demo.servicely.ai/v1/User order_desc==UserName fields==UserName
HTTP/1.1 200 OK
...
{
    "data": [
        {
            "UserName": "trial.admin"
        },
        {
            "UserName": "sysuser"
        },
        {
            "UserName": "sofi"
        },
        {

Include displayValues

Some fields have a database value and a display value. To include both, you can specify the field in the displayValues argument.

➜  ~ https https://sandbox.servicely.ai/v1/Incident fields==Number displayValues==Classification,AssignmentGroup
HTTP/1.1 200 OK
...
X-Total-Result-Count: 2

{
    "data": [
        {
            "AssignmentGroup": {
                "displayValue": "Support",
                "value": "c0a8016c682a17a381682a2b5c7b000a"
            },
            "Classification": {
                "displayValue": "Other",
                "value": "c0a805c8685e1cde81685e406ffd325f"
            },
            "Number": "INC0000003"
        },...

Include relationship information

By default, only data directly associated with the records being retrieved are returned (i.e. direct fields and the values of one-to-x relationships). To return information from the target of the relationship, specify the relations argument with a comma delimited list of relation names.

➜  ~ https https://sandbox.servicely.ai/v1/Incident relations==AssignmentGroup.Name fields==id,ShortDescription 

HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Length: 200
Content-Type: application/json
...
X-Page: 1
X-Page-Size: 200
X-Rate-Limit-Cost: 4
X-Result-Count: 2
X-Result-More: false
X-Total-Result-Count: 2

{
    "data": [
        {
            "AssignmentGroup": {
                "Name": "Support",
                "id": "c0a8016c682a17a381682a2b5c7b000a"
            },
            "ShortDescription": "Example incident",
            "id": "70a88bdace8811ebb1870242ac14001a"
        },
        ...

Nested relationship information

Nested relations can also be retrieved as part of a GET query. The following example retrieves the name of the Requestor, and also the name and ID of their Manager

➜  ~ https https://sandbox.servicely.ai/v1/Incident relations==Requestor.Name,Requestor.Manager.Name fields==id,ShortDescription 

HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Length: 250
Content-Type: application/json
...
X-Page: 1
X-Page-Size: 200
X-Rate-Limit-Cost: 5
X-Result-Count: 2
X-Result-More: false
X-Total-Result-Count: 2

{
    "data": [
        {
            "Requestor": {
                "Manager": {
                    "Name": "Andrew Venn",
                    "id": "402881923a065eed013a06621fff0001"
                },
                "Name": "Erik Employee",
                "id": "c0a8011645fb1a608145fb3102b10000"
            },
            "ShortDescription": "Example incident",
            "id": "70a88bdace8811ebb1870242ac14001a"
        },
        ...

Paging results

Results can be paged by passing the page and page_size arguments. The command below returns 2 rows from the 5th page of the User table.

➜  ~ https https://sandbox.servicely.ai/v1/User page_size==2 page==5 fields==id,Name     
HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Length: 143
Content-Type: application/json
Date: Wed, 16 Jun 2021 11:29:27 GMT
...
X-Page: 5
X-Page-Size: 2
X-Rate-Limit-Cost: 2
X-Result-Count: 2
X-Result-More: true
X-Total-Result-Count: 14

{
    "data": [
        {
            "Name": "Andrew Venn",
            "id": "402881923a065eed013a06621fff0001"
        },
        {
            "Name": "HR Agent",
            "id": "c0a800046b2a15c6816b2a27dd1c0079"
        }
    ]
}

Result headers

The page results also return the total result count and page information in the HTTP headers.

X-Page: 2                   # 'Page' number 2 - which would be records 6 to 10 with a page size of 5 record
X-Page-Size: 5              # The page size used
X-Result-Count: 5           # The number of results returned. A value less than the page size would indicate the end of the records has been reached.
X-Total-Result-Count: 14    # The number of records in total in the data set

Complex Queries

Complex queries can be achieved by passing a JSON formatted query string to the ‘query’ parameter. The format consists of a JSON Object with a conjunction (and, or, nor) followed by an array of criterions (which can also be conjunctions).

The JSON str

{
  "and": [
    {
      "fieldName": "Priority",
      "operator": "IN",
      "value": ["1", "2"]
    },
    {
      "fieldName": "Closed",
      "operator": "=",
      "value": true
    }
  ]
}

Conjunction	Description
and	All criteria in the array must match
or	Any of the criteria in the array can match
nor	None of the criteria in the array can match

Operators	Description	Arity
startswith	The string value starts with this term	1
=	The value equals this term	1
!=	The value is not equal to this term (does not apply to null values).	1
contains	The string value contains this term	1
doesnotcontain	The string value does not contain this term	1
isempty	The value is empty (i.e. null)	0
isnotempty	The value is not empty (i.e not null)	0
in	The value is one of the supplied terms	many
notIn	The value is not one of the supplied terms	many
<	The value is less than the supplied term	1
>	The value is greater than the supplied term	1
<=	The value is less than or equal to the supplied term	1
>=	The value is greater than or equal to the supplied term	1
between	The value is between the two supplied terms	2

Example

To simplify the encoding of the JSON query, we will place that in a text file for testing: query.json

{
  "and": [
    {
      "fieldName": "Active",
      "operator": "=",
      "value": true
    },
    {
      "fieldName": "Email",
      "operator": "isnotempty"
    } 
  ]      
}

query.json

And execute using HTTPie

➜  https demo.servicely.ai/v1/User fields==Email,Active [email protected]
HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Type: application/json
...
X-Page: 1
X-Page-Size: 200
X-Rate-Limit-Cost: 11
X-Response-Time: 115
X-Result-Count: 11
X-Result-More: false
X-Total-Result-Count: 11

{
    "data": [
        {
            "Active": true,
            "Email": "[email protected]"
        },
        {
            "Active": true,
            "Email": "[email protected]"
        },
        {
            "Active": true,
            "Email": "[email protected]"
        },

Dot walk queries

Queries can also filter through relationships. For example, find all users through the Manager relationship, using the managers Email Address.

{    
  "and": [
    {
      "fieldName": "Manager.Email",
      "operator": "=",
      "value": "[email protected]"
    }
  ]  
}

query.json

➜  https demo.servicely.ai/v1/User fields==Email,Active [email protected]
HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Type: application/json
...
X-Page: 1
X-Page-Size: 200
X-Rate-Limit-Cost: 1
X-Response-Time: 44
X-Result-Count: 1
X-Result-More: false
X-Total-Result-Count: 1

{
    "data": [
        {
            "Active": true,
            "Email": "[email protected]"
        }
    ]
}

Tip: Copying queries from the UI

When developing queries, it is much easier to use the list query builder in the application an copy the query for use in the integration.

By default, the copy button copies a browser link to the query in the application. By using modifier keys when clicking the ‘copy’ button, you can get the query in formats suitable for use in integrations.

Modifier	Name	Description
None	Link	Copy URL link to the page/query in the application to the clipboard
Shift	API Link	Copy URL link to the REST API version of the query to the clipboard
Meta	Formatted JSON	Copy the pretty formatted JSON query to the clipboard
Meta+Shift	Raw JSON	Copy the raw JSON query to the clipboard
Alt+Meta	Command line JSON	Copy the raw JSON string formatted for use from the command line