---
title: "Filtering"
description: "API filtering enables precise data retrieval using logical operators like _gt, _lt, _like, _in, and _between. It supports complex queries and comparisons."
lang: en
languages:
  en: https://docs.vismasoftware.no/businessnxtapi/apireference/features/filtering/index.md
lastmod: 2026-07-10
---

> Documentation index: https://docs.vismasoftware.no/businessnxtapi/apireference/features/llms.txt


# Filtering

Last modified July 10, 2026

> API filtering enables precise data retrieval using logical operators like _gt, _lt, _like, _in, and _between. It supports complex queries and comparisons.


## Overview

Filtering is a key feature of the API. You can specify the filter as a parameter to a connection. Let's look at some examples:

In this first example, we fetch all the general ledger accounts that have the account number greater or equal than 6000 and lower than 7000.

```graphql { title = "Query" }
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    generalLedgerAccount(
      filter: {_and :[
        {accountNo :{_gte : 6000}},
        {accountNo :{_lt : 7000}}
      ]}
    )
    {
      totalCount
      items
      {
        accountNo
        name
      }
    }
  }
}
```

```graphql { title = "Result" }
{
  "data": {
    "useCompany": {
      "generalLedgerAccount": {
        "totalCount": 46,
        "items": [
          {
            "accountNo": 6000,
            "name": "Avskr. bygn. og annen eiendom"
          },
          {
            "accountNo": 6010,
            "name": "Avskr. maskiner, inventar mv."
          },
          ...
        ]
      }
    }
  }
}
```

We can complicate the query a bit, and ask only for those general ledger accounts in the range 6000 - 7000 that start with "Leie". In this case, the filter would look as follows:

```graphql { title = "Query" }
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    generalLedgerAccount(
      filter: {_and :[
        {accountNo :{_gte : 6000}},
        {accountNo :{_lt : 7000}},
        {name :{_like:"Leie%"}}
      ]}
    )
    {
      totalCount
      items
      {
        accountNo
        name
      }
    }
  }
}
```

```graphql { title = "Result" }
{
  "data": {
    "useCompany": {
      "generalLedgerAccount": {
        "totalCount": 6,
        "items": [
          {
            "accountNo": 6300,
            "name": "Leie lokaler"
          },
          {
            "accountNo": 6400,
            "name": "Leie maskiner"
          },
          {
            "accountNo": 6410,
            "name": "Leie inventar"
          },
          {
            "accountNo": 6420,
            "name": "Leie datasystemer"
          },
          {
            "accountNo": 6430,
            "name": "Leie andre kontormaskiner"
          },
          {
            "accountNo": 6440,
            "name": "Leie transportmidler"
          }
        ]
      }
    }
  }
}
```

In the next example, we fetch all the associates that are either customers with the number in the range 10000 - 10010, or suppliers, with the number in the range 50000 - 50010.

```graphql { title = "Query" }
query read($cid : Int!)
{
  useCompany(no: $cid) {
    associate (
      filter:{
        _or:
        [
           {
            _and : [
               {customerNo :{_gt:10000}},
               {customerNo :{_lt:10010}}
            ]
          },
          {
            _and : [
               {supplierNo : {_gt:50000}},
               {supplierNo : {_lt:50010}},
            ]
          }
        ]
      }
    )
    {
      totalCount
      items {
        name
        customerNo
        supplierNo
      }
    }
  }
}
```

```graphql { title = "Result" }
{
  "data": {
    "useCompany": {
      "associate": {
        "totalCount": 18,
        "items": [
          {
            "name": "YoYo Solutions AS",
            "customerNo": 10001,
            "supplierNo": 0
          },
          {
            "name": "Spire Business",
            "customerNo": 10002,
            "supplierNo": 0
          },
          ...
          {
            "name": "Scorpio Services Limited",
            "customerNo": 0,
            "supplierNo": 50001
          },
          {
            "name": "Atlas Systems",
            "customerNo": 0,
            "supplierNo": 50002
          },
          ...
        ]
      }
    }
  }
}
```

Filtering is very similar to an open-source GraphQL implementation for Postgres databases. You can read more about that [in the Hasura docs](https://hasura.io/docs/1.0/graphql/core/queries/query-filters.html#using-multiple-filters-in-the-same-query-and-or). This document can be used for finding more examples.

The filter for each table type (connection) has its own type. For instance, the filter type for the `Associate` table is called `FilterExpression_Associate`. You can see this if you explore the schema with GraphiQL, for instance:

![Filter expression](https://docs.vismasoftware.no/businessnxtapi/apireference/features/filter1.png)

`FilterExpression_Associate` type, in turn, has field for each column in the table. This fields are of a type called *_FilterClause*, such as `Int32_FilterClause`, `LimitedString_FilterClause`, etc. Two additional fields, `_and` and `_or`, of the same `FilterExpression_Associate` allow composing complex filter with the AND and OR logical operators.

![Filter expression type](https://docs.vismasoftware.no/businessnxtapi/apireference/features/filter2.png)

The filter clause type, in turn, has fields representing operators, such as `_lt` or "lower than" or `_like` for the text comparison *like* operator.

![Filter clause type](https://docs.vismasoftware.no/businessnxtapi/apireference/features/filter3.png)

The following table lists the available operators:

| Operator | Description |
| -------- | ----------- |
| `_eq` | equal to `value` |
| `_gt` | greater than `value` |
| `_gte` | greater than or equal to `value` |
| `_in` | in list of values |
| `_is_not_null` | is not `null` |
| `_is_null` | is `null` |
| `_is_off` | is `off` - available for bit field columns only |
| `_is_on` | is `on` - available for bit field columns only |
| `_lt` | less than `value` |
| `_lte` | less than or equal to `value` |
| `_like` | like `value` |
| `_not_eq` | not equal `value` |
| `_not_in` | not in list of values |
| `_not_like` | not like `value` |
| `_between` | between `from` and `to` (inclusive bounds) |
| `_not_between` | not between `from` and `to` (exclusive bounds) |

The filter can also be provided as an argument to the query. The filter value is an object of the filter expression type. This is shown in the following example:

```graphql { title = "Query" }
query GetGLAs(
  $companyNo: Int,
  $filter : FilterExpression_GeneralLedgerAccount)
{
  useCompany(no: $companyNo)
  {
    generalLedgerAccount(filter: $filter)
    {
      totalCount
      items
      {
        accountNo
        name
      }
    }
  }
}
```

```graphql { title = "Variables" }
{
  "companyNo": 9112233,
  "filter": {"_and" :[
    {"accountNo" :{"_gte" : 6000}},
    {"accountNo" :{"_lt" : 7000}}]}
}
```

## \_in and \_not\_in operators

Most of the operators take a single value. The `_in` and `_not_in` operators, however, take an array of values, as show in the following example:

```graphql
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    associate (filter : {customerNo : {_in: [10001,10002,10003]}})
    {
      totalCount
      items
      {
        name
        associateNo
        customerNo
        supplierNo
      }
    }
  }
}
```

These two operators (`_in` and `_not_in`) are helpful for simplifying more complex filters. The following two tables show a filter using these two operators, and the equivalent expression without them.

```graphql { title = "With_in" }
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    associate (filter : {
      customerNo : {_in: [10001,10002,10003]}})
    {
      totalCount
      items
      {
        name
        associateNo
        customerNo
        supplierNo
      }
    }
  }
}
```

```graphql { title = "Without_in" }
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    associate (filter : {_or: [
      customerNo : {_eq: 10001},
      customerNo : {_eq: 10002},
      customerNo : {_eq: 10003}
    ]})
    {
      totalCount
      items
      {
        name
        associateNo
        customerNo
        supplierNo
      }
    }
  }
}
```

```graphql { title = "With_in" }
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    associate (filter : {
      customerNo : {_not_in: [10001,10002,10003]}})
    {
      totalCount
      items
      {
        name
        associateNo
        customerNo
        supplierNo
      }
    }
  }
}
```

```graphql { title = "Without_in" }
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    associate (filter : {_and: [
      customerNo : {_not_eq: 10001},
      customerNo : {_not_eq: 10002},
      customerNo : {_not_eq: 10003}
    ]})
    {
      totalCount
      items
      {
        name
        associateNo
        customerNo
        supplierNo
      }
    }
  }
}
```

If the argument for the `_in` or `_not_in` operators is an empty array, the result will be an empty set.

An example is shown below, where we first query the `Order` table, exporting the value of the `sellerOrBuyer` column to the variable `sellers`. We then query the `Associate` table, filtering for the employees with the numbers in the `sellers` array. Because no order matches the filter, the result is an empty `sellers` array, which results in no data being returned.

```graphql { title = "Query" }
query find_selers($cid: Int!, $sellers: [Int!] = [])
{
  useCompany(no: $cid)
  {
    order(
      filter: {sellerOrBuyer: {_gt: 999}},
      first: 5)
    {
      items
      {
        sellerOrBuyer @export(as: "sellers")
      }
    }

    associate(
      filter: {employeeNo: {_in: $sellers}})
    {
      items
      {
        employeeNo
        name
      }
    }
  }
}
```

```graphql { title = "Result" }
{
  "data": {
    "useCompany": {
      "order": {
        "items": null
      },
      "associate": {
        "items": null
      }
    }
  }
}
```

If the `_in` or `_not_in` operators are used in an `AND` expression, and the argument is an empty array, the resulting filter will be an empty expression, and no data will be returned.

```graphql
order(
  filter: {_and: [
     {changeDateTime : {_gte: {"2024-01-24T12:00:00"}}}
     {orderNo: {_in: []}}
  ]})
{
   items
   {
     orderNo
     dueDate
   }
}
```

This results in an empty filter. No data is returned when a filter is provided but it is empty. On the other hand, when no filter is provided, all the data is returned.

If the `_in` or `_not_in` operators are used in an `OR` expression and the argument is an empty array, the subexpression is removed from the filter expression, which may still produce a valid filter that can return data.

```graphql { title = "Filter" }
filter: {_or: [
     {changeDateTime :
        {_gte: {"2024-01-24T12:00:00"}}}
     {orderNo: {_in: []}}
  ]}
```

```graphql { title = "Equivalent" }
filter: {_or: [
     {changeDateTime :
        {_gte: {"2024-01-24T12:00:00"}}}
  ]}
```

## \_between / \_not\_between operators

These two operators require two values that define the lower and upper bounds of the range. The range is inclusive, meaning that the values that are equal to the lower or upper bound are included in the result.

```graphql
query read_glas($cid : Int!, $pagesize : Int!)
{
  useCompany(no: $cid)
  {
    generalLedgerAccount(
      first: $pagesize,
      filter : {accountNo : {_between : {from: 1000, to: 2000}}})
    {
      totalCount
      items
      {
        accountNo
        name
      }
    }
  }
}
```

These operators are support for fields of the following types:

- numeric (`Int`, `Long`, `Decimal`, etc.)
- Date
- Time
- DateTime

The following example shows an expression using both the `_between` operator (for an int field) and the `_not_between` operator (for a datetime field):

```graphql
query read_glas($cid : Int!)
{
  useCompany(no: $cid)
  {
    generalLedgerAccount(filter: { _and : [
        {accountNo : {
            _not_between : {
                from : 3000,
                to   : 5000
            }
        }},
        {changedDateTime : {
            _between : {
                from : "2024-05-01T12:00:00",
                to   : "2024-05-30T23:59:59"
            }
        }}
      ]})
    {
      totalCount
      items
      {
        accountNo
        name
      }
    }
  }
}
```

## Filtering on joined tables

When reading data, you can fetch data from a table as well as all tables that are joined to it. There are two types of joins:

- `_joinup` fields that refer to one-to-one relationships. Examples include order-to-customer (an order can have a single customer) or order line-to-product (an order line can have a single product).
- `_joindown` fields that refer to one-to-many relationships. Examples include order-to-order lines (an order can have multiple order lines) or batch-to-voucher (a batch can have multiple vouchers).

You can specify filters on these joins. The input types for the `filter` arguments have the same join fields as the output types.

In the following example, we fetch all the orders of a customer indicated by name:

```graphql
query read_orders($cid:Int!, $name : String)
{
  useCompany(no:$cid)
  {   
    order(
      filter : {
        joinup_Associate_via_Customer : {
          name : {_eq : $name}
        }
      }
    )
    {
      totalCount
      items
      {
        orderNo
                
        joinup_Associate_via_Customer
        {
          associateNo
          customerNo
          name
        }
      }
    }
  }
}
```

In the next example, we fetch all the orders that have at least one order line with a product whose description matches the specified one:

```graphql
query read_orders($cid:Int!, $desc : String)
{
  useCompany(no:$cid)
  {   
    order(
      filter : {
        joindown_OrderLine_via_Order : {
          _some : {
            joinup_Product : {
              description : {
                _eq : $desc
              }
            }
          }
        }
      }
    )
    {
      totalCount
      items
      {      
        orderNo             
                
        joindown_OrderLine_via_Order
        {
          items
          {
            lineNo
            productNo
            
            joinup_Product
            {
              productNo
              description
            }
          }
        }
      }
    }
  }
}
```

In the case of `_joindown` fields, the filter selection produces a collection of items, therefore, a collection quantifier *must* be used. The currently supported one is `_some`, which means that at least one item in the collection must match the filter expression.

> [!NOTE]
>
> In the future `_all` and `_none` quantifiers might be supported.

## Comparing with current date/time

The model define various fields for date and time. All the nodes that have the type `Date_FilterClause`, `Time_FilterClause`, `DateTime_FilterClause`, or `Timestampt_FilterClause` can be compared with the current date/time. These GraphQL types, have, in addition for fields defining operators, an additional field called `_cmp` whose type is `ReferenceDateTime_FilterClause`, which looks as follows:

![ReferenceDateTime_FilterClause type](https://docs.vismasoftware.no/businessnxtapi/apireference/features/filter4.png)

This type features nodes for all the comparison operators available for date and time fields. However, their type is `ReferenceDateTime` that has the following fields:

| Name | Type | Description |
| ---- | ---- | ----------- |
| `day` | `ReferenceDays` | The day this moment refers to. One of `YESTERDAY`, `TODAY`, or `TOMORROW`. |
| `dayOffset` | `Int` | The number of days to offset from the current date. For instance `TODAY` with an offset of one is `TOMORROW`, and `TODAY` with an offset of -1 is `YESTERDAY`. This value is optional. The default is 0. |
| `time` | `ReferenceTimePoints` | Can only have the value `NOW`. |
| `minuteOffset` | `Int` | The number of minutes to offset from the current time. This value is optional. The default is 0. |

The following fields of a `ReferenceDateTime` value must be provided, depending on the type of the field being compared:

| Field type | Required fields of `ReferenceDateTime` | Error message if not provided |
| ---------- | --------------------------------------- | ----------------------------- |
| `Date_FilterClause` | `day` | "Error in filter expression. The date value must contain the day field." |
| `Time_FilterClause` | `time` | "Error in filter expression. The time value must contain the time field." |
| `DateTime_FilterClause` | `day` (`time` implied as `NOW`) | "Error in filter expression. The datetime value must contain at least the day." |
| `Timestamp_FilterClause` | `day` (`time` implied as `NOW`) | "Error in filter expression. The datetime value must contain at least the day." |

The following example shows a query that fetches all orders that where changed in the last hour:

```graphql
query read_orders ($cid: Int)
{
  useCompany(no: $cid)
  {
    order(filter :
    {
      _and : [
        {
          changedDateAsDate : {
            _cmp : {
              _eq : {day : TODAY, dayOffset : 0}
            }
          }
        },
        {
          changedTimeAsTime : {
            _cmp : {
              _gte : {time : NOW, minuteOffset: -60}
            }
          }
        }
      ]
    })
    {
      totalCount
      items
      {
        orderNo
      }
    }
  }
}
```

This particular query can be simplified by using the `changedDateTime` field, as shown bellow:

```graphql
query read_orders ($cid: Int)
{
  useCompany(no: $cid)
  {
    order(filter :
    {
      changedDateTime : {
        _cmp : {
          _gte : {
            day : TODAY,
            time : NOW,
            minuteOffset : -60}
        }
      }
    })
    {
       totalCount
       items
       {
          orderNo
       }
    }
  }
}
```

It is recommended to use the timestamp fields (`createdTimestamp` and `changedTimestamp`), available on all tables. However, these fields have the value `NULL` for records created (or modified) before they were added to the model, in which case you'd have to use the `createdDateTime` or `changedDateTime` fields instead for the query to properly work.

```graphql
query read_orders ($cid: Int)
{
  useCompany(no: $cid)
  {
    order(filter :
    {
      changedTimestamp : {
        _cmp : {
          _gte : {
            day : TODAY,
            time : NOW,
            minuteOffset : -60}
        }
      }
    })
    {
       totalCount
       items
       {
          orderNo
       }
    }
  }
}
```

## Having expressions

The same operators described here for filters can be used for `having` clauses in queries that include aggregations.

To learn more about this, see [Grouping: The having argument](https://docs.vismasoftware.no/businessnxtapi/apireference/schema/queries/grouping/index.md#the-having-argument).


---

[View this page](https://docs.vismasoftware.no/businessnxtapi/apireference/features/filtering/)