Filtering

How to filter data

Atlas supports both simple and complex filtering. A simple filter is a key-value pair. A complex filter is a structured json object that allows you to join multiple conditions together using one of several operators.

Resources that support complex filtering will have a /filter endpoint where you can deliver a json body in a POST request.

Simple filters

Simple filters are key-value pairs that can be provided as either query string parameters or as json in the body of a POST request to the /filter endpoint of a resource.

As a query string parameter:

GET /v2/api-section/resource-name?field=value

As a json payload:

POST /api-section/resource-name

{
    "field": "value"
}

For example, here's how we can filter on accounts that are tax deferred using query string parameters:

GET /v2/account-management/accounts?is_tax_deferred=true

And as a json payload to the filter endpoint:

POST /v2/account-management/accounts

{
    "is_tax_deferred": true
}

A key-value expression is sufficient in this case because the field being filtered can only take on two values: true or false. However more complex use cases arise from other types of data. You might want to filter a string field for the inclusion of a value, or whether it starts or ends with a value. Likewise you might want to filter numeric data for being within a certain range or above / below a value. These use cases are supported by complex filters.

Complex filters

Complex filters enable multiple conditions and comparison operators. These filters are expressed as json and must be provided as a POST to the /filter endpoint of a resource. If a resource doesn't contain expose a /filter endpoint then it doesn't support complex filtering.

The payload looks like this:

{
  "field_name": {
    "any_or_all": "all", // required only if more than 1 condition is provided
    "conditions": [{
      "value": <value1>,
      "op": <comparison operator>
    }, {
      "value": <value2>,
      "op": <comparison operator>,
    }, {
      ...
    }]
  }
}

Comparison operators depend on the data type of field_name:

Op valueDescriptionSupported data types
eqEquals
Case sensitive for strings
String, Integer, Float, Date/time
neqNot equals
Case sensitive for strings
String, Integer, Float, Date/time
iexactCase insensitive matchString
containsContains provided value, case sensitiveString
icontainsContains provided value, case insensitiveString
startswithStarts with provided value, case sensitiveString
istartswithStarts with provided value, case insensitiveString
endswithEnds with provided value, case sensitiveString
iendswithEnds with provided value, case insensitiveString
ltLess than provided valueInteger, Float, Date/time
lteLess than or equal to provided valueInteger, Float, Date/time
gtGreater than provided valueInteger, Float, Date/time
gteGreater than or equal to provided valueInteger, Float, Date/time

Joining conditions

The any_or_all field is optional when specifying a single condition. It's required when multiple conditions are provided and, as the name suggests, can take on the value of all or any, interpreted as:

  • all will AND the conditions together when querying the database. All the provided conditions must be true among the results.
  • any will OR the conditions together when querying the database. Any of the conditions must be true among the results.

Mixing with simple filters

A complex filter is a superset of a simple filter. Instead of field_name providing the filter json object above it can simply provide a value:

{
  "field_name": <value>
}

Here field_name will be consumed as a simple filter, requiring an exact match to the data in the database to be true. This way you can mix simple with complex filters:

{
  "field_1": "simple value",
  "field_2": {
    "conditions": [{
      "value": 5,
      "op": "gte"
    }]
  }
}

Between two values (a range)

You can use complex filters to query when a value is between two other values:

{
  "field_name": {
    "any_or_all": "all",
    "conditions": [{
      "value": 5,
      "op": "gte",
    }, {
      "value": 150,
      "op": "lte"
    }]
  }
}

This query will find all objects whose field_name is between 5 and 150, inclusive of those values.

Examples

Get accounts with the name "Jane Smith"

POST /v2/account-management/accounts/filter

{
    "name": "Jane Smith"
}

Accounts ending with "Smith"

POST /v2/account-management/accounts/filter

{
    "name": {
        "conditions": [{
            "value": "Smith",
            "op": "endswith"
        }]
    }
}

Find account balances between $1,000 and $10,000

POST /v2/data/luca/account-balances/filter

{
    "beginning_period_value": {
        "any_or_all": "all",
        "conditions": [{
      "value": 1000,
      "op": "gte"
    }, {
        "value": 10000,
        "op": "lte" 
    }]
}