API statusProduct roadmapSwagger docsBack to codat.io

Querying

Suggest Edits

The Codat API uses a simple, flexible query language to allow you to filter response data. Include a URL encoded query parameter with your request to filter data returned from the API.

The below query functionality will only work when searching for company data (eg invoices, customers etc.), they will not work on settings or metadata endpoints such as listing integrations, companies or data connections.

Query format

  • The query takes the form of {propertyName}={value}.
  • You can also include comparison operators, such as greater than, less than or equal to. The following table shows comparison operators that are supported for numeric, date, and string data types.

Operator

Number

String

Date

Equals =

✔

✔

✔

Not equals !=

✔

✔

✔

Contains ~

❌

✔

❌

Greater than >

✔

❌

✔

Less than <

✔

❌

✔

Greater than or equal to >=

✔

❌

✔

Less than or equal to <=

✔

❌

✔

  • Separate multiple query clauses with ampersands (&&) for AND queries or pipes (||) for OR queries.
  • Access sub-properties by separating them from the property with a dot (see Invoices to a particular customer example below).

📘

Combining queries

You can't currently combine AND and OR queries, for example, value>10&&outstanding>0||value>100). In this case, the OR parameter is actually included as an AND parameter.

👍

Query length limits

The total length of your query should be under 2048 characters in order to be valid. If your query is longer, the API will return an error message.

Example queries

Invoices with amounts outstanding

GET /companies/{companyId}/data/invoices?query=amountDue%3e0

import {InvoicesQuery} from 'codat-queries';
import { api as codat } from 'codat';

var query = new InvoicesQuery(companyId, 'amountDue>0')
  .run(codat.uat(apiKey));

var request = new RestRequest("companies/{companyId}/data/invoices", Method.GET);
request.AddUrlSegment("companyId", companyId);
request.AddUrlSegment("query", "amountDue>0");
request.AddHeader("Authorization", $"Basic {encodedApiKey}");
var response = client.Execute(request);
var info = response.Data;

GBP Invoices

GET /companies/{companyId}/data/invoices?query=currency%3dGBP

import {InvoicesQuery} from 'codat-queries';
import { api as codat } from 'codat';

var query = new InvoicesQuery(companyId, 'currency=GBP')
  .run(codat.uat(apiKey));

var request = new RestRequest("companies/{companyId}/data/invoices", Method.GET);
request.AddUrlSegment("companyId", companyId);
request.AddUrlSegment("query", "currency=GBP");
request.AddHeader("Authorization", $"Basic {encodedApiKey}");
var response = client.Execute(request);
var info = response.Data;

Invoices to a particular customer

GET /companies/{companyId}/data/invoices?query=customerRef.id%3d61

import {InvoicesQuery} from 'codat-queries';
import { api as codat } from 'codat';

var query = new InvoicesQuery(companyId, 'customerRef.id=61')
  .run(codat.uat(apiKey));

var request = new RestRequest("companies/{companyId}/data/invoices", Method.GET);
request.AddUrlSegment("companyId", companyId);
request.AddUrlSegment("query", "customerRef.id=61");
request.AddHeader("Authorization", $"Basic {encodedApiKey}");
var response = client.Execute(request);
var info = response.Data;

Outstanding Invoices of value less than 1000

GET /companies/{companyId}/data/invoices?query=amountDue%3e0%26%26totalAmount%3c1000

import {InvoicesQuery} from 'codat-queries';
import { api as codat } from 'codat';

var query = new InvoicesQuery(companyId, 'amountDue>0&&totalAmount<1000')
  .run(codat.uat(apiKey));

var request = new RestRequest("companies/{companyId}/data/invoices", Method.GET);
request.AddUrlSegment("companyId", companyId);
request.AddUrlSegment("query", "amountDue>0&&totalAmount<1000");
request.AddHeader("Authorization", $"Basic {encodedApiKey}");
var response = client.Execute(request);
var info = response.Data;

Invoices that are due after a certain date (YYYY-MM-DD) e.g. "2021-01-28"

GET /companies/{companyId}/data/invoices?query=dueDate%3E2021-01-28

import {InvoicesQuery} from 'codat-queries';
import { api as codat } from 'codat';

var query = new InvoicesQuery(companyId,'dueDate>2021-01-28')
  .run(codat.uat(apiKey));

var request = new RestRequest("companies/{companyId}/data/invoices", Method.GET);
request.AddUrlSegment("companyId", companyId);
request.AddUrlSegment("query", "dueDate>2021-01-28");
request.AddHeader("Authorization", $"Basic {encodedApiKey}");
var response = client.Execute(request);
var info = response.Data;

For companies whose status is "Pending" (with data connection established)

GET /companies?page=1&pageSize=100&query=dataConnections.status=PendingAuth

Note: the page size value is obligatory for querying.

var request = new RestRequest("companies", Method.GET);
request.AddUrlSegment("page", 1);
request.AddUrlSegment("query", "dataConnections.status=PendingAuth");
request.AddHeader("Authorization", $"Basic {encodedApiKey}");
var response = client.Execute(request);
var info = response.Data;

For companies with no data connection established

GET /companies?page=1&pageSize=100&query=dataConnections.count=0

Note: The page size value is obligatory for querying.

var request = new RestRequest("companies", Method.GET);
request.AddUrlSegment("page", 1);
request.AddUrlSegment("query", "dataConnections.count=0");
request.AddHeader("Authorization", $"Basic {encodedApiKey}");
var response = client.Execute(request);
var info = response.Data;

Updated 6 months ago

Did this page help you?