Query data

Basics and examples of querying in Codat's APIs

The Codat APIAPI A set of rules and protocols that allows different software applications to communicate with each other. Codat provides APIs for accessing financial data from accounting, banking, and commerce platforms. uses a simple, flexible query language to allow you to filter response data.

Use URL encoding

Include a URL encoded query parameter with your request to filter data returned from the APIAPI A set of rules and protocols that allows different software applications to communicate with each other. Codat provides APIs for accessing financial data from accounting, banking, and commerce platforms..

The below query functionality will only work when searching for companyCompany In Codat, a company represents your customer's business entity. Companies can have multiple connections to different data sources. data (e.g. invoices, customers, etc), they will not work on settings or metadata endpoints such as listing integrations, companies or data connectionsConnection A link between a Codat company and a data source (like an accounting platform). Each connection represents authorized access to pull or push data from that platform..

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	Name	Encoded	Number	String	Date
=	Equals	%3d	✔	✔	✔
!=	Not equals	%21%3d	✔	✔	✔
~	Contains	%7E	❌	✔	❌
>	Greater than	%3e	✔	❌	✔
<	Less than	%3c	✔	❌	✔
>=	Greater than or equal to	%3e%3d	✔	❌	✔
<=	Less than or equal to	%3c%3d	✔	❌	✔
&&	AND	%26%26	-	-	-
||	OR	%7C%7C	-	-	-
{, }	Logical separator	%7B, %7D	-	-	-

• 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). This is only applicable to objects within our data endpoints. We do not support querying inside arrays.

Combining queries

To combine AND and OR queries, use curly braces ({ and }). For example:

query={totalAmount > 100 || totalAmount < 50} && status != paid

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 APIAPI A set of rules and protocols that allows different software applications to communicate with each other. Codat provides APIs for accessing financial data from accounting, banking, and commerce platforms. will return an error message.

Querying on null fields

We do not currently support querying on null fields.

Getting all vs one item

Our GET /{dataType} endpoints typically return an array of items of that given data type. If you want to retrieve just a single data type by an ID, you can use a query. For example:

query=id%253D81be41e9-5c2c-4064-829c-bca43b5e6f59.

Example queries

Invoices with amounts outstanding

Query: amountDue > 0

HTTP

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

C#

using CodatLending;
using CodatLending.Models.Shared;
using CodatLending.Models.Operations;

var codatLending = new CodatLendingSDK(
    security: new Security() {
        AuthHeader = "Basic BASE_64_ENCODED(API_KEY)",
    }
);

var res = await codatLending.AccountsReceivable.Invoices.ListAsync(new ListAccountingInvoicesRequest() {
    CompanyId = "{companyId}",
    Query = "amountDue>0",
});

TypeScript

import { CodatLending } from "@codat/lending";

const codatLending = new CodatLending({
  authHeader: "Basic BASE_64_ENCODED(API_KEY)",
});

codatLending.accountsReceivable.invoices
  .list({
    companyId: "{companyId}",
    query: "amountDue>0&&totalAmount<1000",
  })
  .then((res) => {
    // handle response
  });

Python

from codat_lending import CodatLending
from codat_lending.models import operations, shared

codat_lending = CodatLending(
    security=shared.Security(
        auth_header="Basic BASE_64_ENCODED(API_KEY)",
    ),
)

res = codat_lending.accounts_receivable.invoices.list(
    request=operations.ListAccountingInvoicesRequest(
        company_id='8a210b68-6988-11ed-a1eb-0242ac120002',
        query='amountDue>0',
    )
)

if res:
    # handle response
    pass

Go

package main

import(
	"context"
	"log"
	"github.com/codatio/client-sdk-go/lending"
	"github.com/codatio/client-sdk-go/lending/pkg/models/shared"
	"github.com/codatio/client-sdk-go/lending/pkg/models/operations"
)

func main() {
    codatLending := codatlending.New(
        codatlending.WithSecurity(shared.Security{
            AuthHeader: "Basic BASE_64_ENCODED(API_KEY)",
        }),
    )

    ctx := context.Background()
    res, err := codatLending.AccountsReceivable.Invoices.List(ctx, operations.ListAccountingInvoicesRequest{
        CompanyID: "{companyId}",
        Query: codatlending.String("amountDue>0"),
    })
    if err != nil {
        log.Fatal(err)
    }

    if res.AccountingInvoices != nil {
        // handle response
    }
}

Invoices in GBP

Query: currency = GBP

HTTP

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

C#

using CodatLending;
using CodatLending.Models.Shared;
using CodatLending.Models.Operations;

var codatLending = new CodatLendingSDK(
    security: new Security() {
        AuthHeader = "Basic BASE_64_ENCODED(API_KEY)",
    }
);

var res = await codatLending.AccountsReceivable.Invoices.ListAsync(new ListAccountingInvoicesRequest() {
    CompanyId = "{companyId}",
    Query = "currency=GBP",
});

TypeScript

import { CodatLending } from "@codat/lending";

const codatLending = new CodatLending({
  authHeader: "Basic BASE_64_ENCODED(API_KEY)",
});

codatLending.accountsReceivable.invoices
  .list({
    companyId: "{companyId}",
    query: "currency=GBP",
  })
  .then((res) => {
    // handle response
  });

Python

from codat_lending import CodatLending
from codat_lending.models import operations, shared

codat_lending = CodatLending(
    security=shared.Security(
        auth_header="Basic BASE_64_ENCODED(API_KEY)",
    ),
)

res = codat_lending.accounts_receivable.invoices.list(
    request=operations.ListAccountingInvoicesRequest(
    company_id='8a210b68-6988-11ed-a1eb-0242ac120002',
    query='currency=GBP',
))

if res:
    # handle response
    pass

Go

package main

import(
	"context"
	"log"
	"github.com/codatio/client-sdk-go/lending"
	"github.com/codatio/client-sdk-go/lending/pkg/models/shared"
	"github.com/codatio/client-sdk-go/lending/pkg/models/operations"
)

func main() {
    codatLending := codatlending.New(
        codatlending.WithSecurity(shared.Security{
            AuthHeader: "Basic BASE_64_ENCODED(API_KEY)",
        }),
    )

    ctx := context.Background()
    res, err := codatLending.AccountsReceivable.Invoices.List(ctx, operations.ListAccountingInvoicesRequest{
        CompanyID: "{companyId}",
        Query: codatlending.String("currency=GBP"),
    })
    if err != nil {
        log.Fatal(err)
    }

    if res.AccountingInvoices != nil {
        // handle response
    }
}

Invoices for a specific customer

Query: customerRef.id = 61

HTTP

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

C#

using CodatLending;
using CodatLending.Models.Shared;
using CodatLending.Models.Operations;

var codatLending = new CodatLendingSDK(
    security: new Security() {
        AuthHeader = "Basic BASE_64_ENCODED(API_KEY)",
    }
);

var res = await codatLending.AccountsReceivable.Invoices.ListAsync(new ListAccountingInvoicesRequest() {
    CompanyId = "{companyId}",
    Query = "customerRef.id=61",
});

TypeScript

import { CodatLending } from "@codat/lending";

const codatLending = new CodatLending({
  authHeader: "Basic BASE_64_ENCODED(API_KEY)",
});

codatLending.accountsReceivable.invoices
  .list({
    companyId: "{companyId}",
    query: "customerRef.id=61",
  })
  .then((res) => {
    // handle response
  });

Python

from codat_lending import CodatLending
from codat_lending.models import operations, shared

codat_lending = CodatLending(
    security=shared.Security(
        auth_header="Basic BASE_64_ENCODED(API_KEY)",
    ),
)

res = codat_lending.accounts_receivable.invoices.list(
    request=operations.ListAccountingInvoicesRequest(
    company_id='8a210b68-6988-11ed-a1eb-0242ac120002',
    query='customerRef.id=61',
))

if res:
    # handle response
    pass

Go

package main

import(
	"context"
	"log"
	"github.com/codatio/client-sdk-go/lending"
	"github.com/codatio/client-sdk-go/lending/pkg/models/shared"
	"github.com/codatio/client-sdk-go/lending/pkg/models/operations"
)

func main() {
    codatLending := codatlending.New(
        codatlending.WithSecurity(shared.Security{
            AuthHeader: "Basic BASE_64_ENCODED(API_KEY)",
        }),
    )

    ctx := context.Background()
    res, err := codatLending.AccountsReceivable.Invoices.List(ctx, operations.ListAccountingInvoicesRequest{
        CompanyID: "{companyId}",
        Query: codatlending.String("customerRef.id=61"),
    })
    if err != nil {
        log.Fatal(err)
    }

    if res.AccountingInvoices != nil {
        // handle response
    }
}

Outstanding Invoices worth less than 1000

Query: amountDue > 0 && totalAmount < 1000

HTTP

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

C#

using CodatLending;
using CodatLending.Models.Shared;
using CodatLending.Models.Operations;

var codatLending = new CodatLendingSDK(
    security: new Security() {
        AuthHeader = "Basic BASE_64_ENCODED(API_KEY)",
    }
);

var res = await codatLending.AccountsReceivable.Invoices.ListAsync(new ListAccountingInvoicesRequest() {
    CompanyId = "{companyId}",
    Query = "amountDue>0&&totalAmount<1000",
});

TypeScript

import { CodatLending } from "@codat/lending";

const codatLending = new CodatLending({
  authHeader: "Basic BASE_64_ENCODED(API_KEY)",
});

codatLending.accountsReceivable.invoices
  .list({
    companyId: "{companyId}",
    query: "amountDue>0&&totalAmount<1000",
  })
  .then((res) => {
    // handle response
  });

Python

from codat_lending import CodatLending
from codat_lending.models import operations, shared

codat_lending = CodatLending(
    security=shared.Security(
        auth_header="Basic BASE_64_ENCODED(API_KEY)",
    ),
)

res = codat_lending.accounts_receivable.invoices.list(
    request=operations.ListAccountingInvoicesRequest(
    company_id='8a210b68-6988-11ed-a1eb-0242ac120002',
    query='amountDue>0&&totalAmount<1000',
))

if res:
    # handle response
    pass

Go

package main

import(
	"context"
	"log"
	"github.com/codatio/client-sdk-go/lending"
	"github.com/codatio/client-sdk-go/lending/pkg/models/shared"
	"github.com/codatio/client-sdk-go/lending/pkg/models/operations"
)

func main() {
    codatLending := codatlending.New(
        codatlending.WithSecurity(shared.Security{
            AuthHeader: "Basic BASE_64_ENCODED(API_KEY)",
        }),
    )

    ctx := context.Background()
    res, err := codatLending.AccountsReceivable.Invoices.List(ctx, operations.ListAccountingInvoicesRequest{
        CompanyID: "{companyId}",
        Query: codatlending.String("amountDue>0&&totalAmount<1000"),
    })
    if err != nil {
        log.Fatal(err)
    }

    if res.AccountingInvoices != nil {
        // handle response
    }
}

Invoices that are due after a certain date

e.g. "2021-01-28" (YYYY-MM-DD format)

Query: dueDate > 2021-01-28

HTTP

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

C#

using CodatLending;
using CodatLending.Models.Shared;
using CodatLending.Models.Operations;

var codatLending = new CodatLendingSDK(
    security: new Security() {
        AuthHeader = "Basic BASE_64_ENCODED(API_KEY)",
    }
);

var res = await codatLending.AccountsReceivable.Invoices.ListAsync(new ListAccountingInvoicesRequest() {
    CompanyId = "{companyId}",
    Query = "dueDate>2021-01-28",
});

TypeScript

import { CodatLending } from "@codat/lending";

const codatLending = new CodatLending({
  authHeader: "Basic BASE_64_ENCODED(API_KEY)",
});

codatLending.accountsReceivable.invoices
  .list({
    companyId: "bae71d36-ff47-420a-b4a6-f8c9ddf41140",
    query: "dueDate>2021-01-28",
  })
  .then((res) => {
    // handle response
  });

Python

from codat_lending import CodatLending
from codat_lending.models import operations, shared

codat_lending = CodatLending(
    security=shared.Security(
        auth_header="Basic BASE_64_ENCODED(API_KEY)",
    ),
)

res = codat_lending.accounts_receivable.invoices.list(
    request=operations.ListAccountingInvoicesRequest(
    company_id='8a210b68-6988-11ed-a1eb-0242ac120002',
    query='dueDate>2021-01-28',
))

if res:
    # handle response
    pass

Go

package main

import(
	"context"
	"log"
	"github.com/codatio/client-sdk-go/lending"
	"github.com/codatio/client-sdk-go/lending/pkg/models/shared"
	"github.com/codatio/client-sdk-go/lending/pkg/models/operations"
)

func main() {
    codatLending := codatlending.New(
        codatlending.WithSecurity(shared.Security{
            AuthHeader: "Basic BASE_64_ENCODED(API_KEY)",
        }),
    )

    ctx := context.Background()
    res, err := codatLending.AccountsReceivable.Invoices.List(ctx, operations.ListAccountingInvoicesRequest{
        CompanyID: "{companyId}",
        Query: codatlending.String("dueDate>2021-01-28"),
    })
    if err != nil {
        log.Fatal(err)
    }

    if res.AccountingInvoices != nil {
        // handle response
    }
}

Invoices deleted in the source platform

Query: metadata.isDeleted!=true

Codat identifies records that have been deleted in the source accounting software between successive data syncsSync The process of fetching the latest data from a connected data source. Syncs can be triggered manually or run automatically on a schedule. using the isDeleted flag. You may need to exclude these records from the results.

HTTP

GET /companies/{companyId}/data/invoices?query=metadata.isDeleted%21%3dtrue

C#

using CodatLending;
using CodatLending.Models.Shared;
using CodatLending.Models.Operations;

var codatLending = new CodatLendingSDK(
    security: new Security() {
        AuthHeader = "Basic BASE_64_ENCODED(API_KEY)",
    }
);

var res = await codatLending.AccountsReceivable.Invoices.ListAsync(new ListAccountingInvoicesRequest() {
    CompanyId = "{companyId}",
    Query = "metadata.isDeleted!=true",
});

TypeScript

import { CodatLending } from "@codat/lending";

const codatLending = new CodatLending({
  authHeader: "Basic BASE_64_ENCODED(API_KEY)",
});

codatLending.accountsReceivable.invoices
  .list({
    companyId: "{companyId}",
    query: "metadata.isDeleted!=true",
  })
  .then((res) => {
    // handle response
  });

Python

from codat_lending import CodatLending
from codat_lending.models import operations, shared

codat_lending = CodatLending(
    security=shared.Security(
        auth_header="Basic BASE_64_ENCODED(API_KEY)",
    ),
)

res = codat_lending.accounts_receivable.invoices.list(
    request=operations.ListAccountingInvoicesRequest(
    company_id='8a210b68-6988-11ed-a1eb-0242ac120002',
    query='metadata.isDeleted!=true',
))

if res:
    # handle response
    pass

Go

package main

import(
	"context"
	"log"
	"github.com/codatio/client-sdk-go/lending"
	"github.com/codatio/client-sdk-go/lending/pkg/models/shared"
	"github.com/codatio/client-sdk-go/lending/pkg/models/operations"
)

func main() {
    codatLending := codatlending.New(
        codatlending.WithSecurity(shared.Security{
            AuthHeader: "Basic BASE_64_ENCODED(API_KEY)",
        }),
    )

    ctx := context.Background()
    res, err := codatLending.AccountsReceivable.Invoices.List(ctx, operations.ListAccountingInvoicesRequest{
        CompanyID: "{companyId}",
        Query: codatlending.String("metadata.isDeleted!=true"),
    })
    if err != nil {
        log.Fatal(err)
    }

    if res.AccountingInvoices != nil {
        // handle response
    }
}

Companies with "Pending" status connectionsConnection A link between a Codat company and a data source (like an accounting platform). Each connection represents authorized access to pull or push data from that platform.

Query: dataConnections.status=PendingAuth

Note: the page size value is obligatory for querying.

C#

using CodatPlatform;
using CodatPlatform.Models.Shared;
using CodatPlatform.Models.Operations;

var sdk = new CodatPlatformSDK(
    security: new Security() {
        AuthHeader = "Basic BASE_64_ENCODED(API_KEY)",
    }
);

var res = await sdk.Companies.ListAsync(new ListCompaniesRequest() {
    Query = "dataConnections.status=PendingAuth",
});

// handle response

HTTP

GET /companies?query=dataConnections.status=PendingAuth

TypeScript

import { CodatPlatform } from "@codat/platform";

const codatPlatform = new CodatPlatform({
  authHeader: "Basic BASE_64_ENCODED(API_KEY)",
});

codatPlatform.companies
  .list({
    query: "dataConnections.status=PendingAuth",
  })
  .then((res) => {
    // handle response
  });

Python

from codat_platform import CodatPlatform
from codat_platform.models import operations, shared

codat_platform = CodatPlatform(
    security=shared.Security(
        auth_header="Basic BASE_64_ENCODED(API_KEY)",
    ),
)

res = codat_platform.companies.list(
    request=operations.ListCompaniesRequest(
        query='dataConnections.status=PendingAuth',
    )
)

if res:
    # handle response
    pass

Go

package main

import(
	"context"
	"log"
	"github.com/codatio/client-sdk-go/platform"
	"github.com/codatio/client-sdk-go/platform/pkg/models/shared"
	"github.com/codatio/client-sdk-go/platform/pkg/models/operations"
)

func main() {
    codatPlatform := codatplatform.New(
        codatplatform.WithSecurity(shared.Security{
            AuthHeader: "Basic BASE_64_ENCODED(API_KEY)",
        }),
    )

    ctx := context.Background()
    res, err := codatPlatform.Companies.List(ctx, operations.ListCompaniesRequest{
        Query: codatplatform.String("dataConnections.status=PendingAuth"),
    })
    if err != nil {
        log.Fatal(err)
    }

    if res.Companies != nil {
        // handle response
    }
}

Companies with no connectionsConnection A link between a Codat company and a data source (like an accounting platform). Each connection represents authorized access to pull or push data from that platform.

Query: dataConnections.status!=PendingAuth&&dataConnections.status!=Linked&&dataConnections.status!=Deauthorized&&dataConnections.status!=Unlinked

• The page size value is obligatory for querying.
• The response will exclude companies that had connectionsConnection A link between a Codat company and a data source (like an accounting platform). Each connection represents authorized access to pull or push data from that platform. but they were deleted.

C#

using CodatPlatform;
using CodatPlatform.Models.Shared;
using CodatPlatform.Models.Operations;

var sdk = new CodatPlatformSDK(
    security: new Security() {
        AuthHeader = "Basic BASE_64_ENCODED(API_KEY)",
    }
);

var res = await sdk.Companies.ListAsync(new ListCompaniesRequest() {
    Query = "dataConnections.status!=PendingAuth&&dataConnections.status!=Linked&&dataConnections.status!=Deauthorized&&dataConnections.status!=Unlinked",
});

// handle response

HTTP

GET /companies?query=dataConnections.status=PendingAuth

TypeScript

import { CodatPlatform } from "@codat/platform";

const codatPlatform = new CodatPlatform({
  authHeader: "Basic BASE_64_ENCODED(API_KEY)",
});

codatPlatform.companies
  .list({
    query:
      "dataConnections.status!=PendingAuth&&dataConnections.status!=Linked&&dataConnections.status!=Deauthorized&&dataConnections.status!=Unlinked",
  })
  .then((res) => {
    // handle response
  });

Python

from codat_platform import CodatPlatform
from codat_platform.models import operations, shared

codat_platform = CodatPlatform(
    security=shared.Security(
        auth_header="Basic BASE_64_ENCODED(API_KEY)",
    ),
)

res = codat_platform.companies.list(
    request=operations.ListCompaniesRequest(
        query='dataConnections.status!=PendingAuth&&dataConnections.status!=Linked&&dataConnections.status!=Deauthorized&&dataConnections.status!=Unlinked',
    )
)

if res:
    # handle response
    pass

Go

package main

import(
	"context"
	"log"
	"github.com/codatio/client-sdk-go/platform"
	"github.com/codatio/client-sdk-go/platform/pkg/models/shared"
	"github.com/codatio/client-sdk-go/platform/pkg/models/operations"
)

func main() {
    codatPlatform := codatplatform.New(
        codatplatform.WithSecurity(shared.Security{
            AuthHeader: "Basic BASE_64_ENCODED(API_KEY)",
        }),
    )

    ctx := context.Background()
    res, err := codatPlatform.Companies.List(ctx, operations.ListCompaniesRequest{
        Query: codatplatform.String("dataConnections.status!=PendingAuth&&dataConnections.status!=Linked&&dataConnections.status!=Deauthorized&&dataConnections.status!=Unlinked"),
    })
    if err != nil {
        log.Fatal(err)
    }

    if res.Companies != nil {
        // handle response
    }
}

Queries that won't work

Although you can query properties of objects, you can't query arrays.

✅ Objects: Invoices > customerRef.id

GET /invoices?page=1&pageSize=100&query=customerRef.id%3Def6f54c1-eb45-4956-b8cd-1be82ad665f2

❌ Arrays: Invoices > lineItems

GET /invoices?page=1&pageSize=100&query=lineItems.unitAmount%3D700