Filtering

FastAPI-JSONAPI has a very flexible filtering system. The filtering system is directly attached to the data layer used by the ResourceList manager. These examples show the filtering interface for SQLAlchemy’s data layer but you can use the same interface for your custom data layer’s filtering implementation as well. The only requirement is that you have to use the “filter” query string parameter to filter according to the JSON:API 1.0 specification.

Note

Examples are not urlencoded for a better readability

SQLAlchemy

The filtering system of SQLAlchemy’s data layer has exactly the same interface as the one used in Flask-Restless. So this is a first example:

GET /users?filter=[{"name":"first_name","op":"eq","val":"John"}] HTTP/1.1
Accept: application/vnd.api+json

In this example we want to retrieve user records for people named John. So we can see that the filtering interface completely fits that of SQLAlchemy: a list a filter information.

name:

the name of the field you want to filter on

op:

the operation you want to use (all SQLAlchemy operations are available)

val:

the value that you want to compare. You can replace this by “field” if you want to compare against the value of another field

Example with field:

GET /users?filter=[{"name":"first_name","op":"eq","field":"birth_date"}] HTTP/1.1
Accept: application/vnd.api+json

In this example, we want to retrieve people whose name is equal to their birth_date. This example is absurd, it’s just here to explain the syntax of this kind of filter.

If you want to filter through relationships you can do that:

[
  {
    "name": "group",
    "op": "any",
    "val": {
      "name": "name",
      "op": "ilike",
      "val": "%admin%"
    }
  }
]

GET [{"name":"group","op":"any","val":{"name":"name","op":"ilike","val":"%admin%"}}] HTTP/1.1
Accept: application/vnd.api+json

Note

When you filter on relationships use the “any” operator for “to many” relationships and the “has” operator for “to one” relationships.

There is a shortcut to achieve the same filtering:

GET /users?filter=[{"name":"group.name","op":"ilike","val":"%admin%"}] HTTP/1.1
Accept: application/vnd.api+json

You can also use boolean combination of operations:

[
  {
    "name":"group.name",
    "op":"ilike",
    "val":"%admin%"
  },
  {
    "or": [
      {
        "not": {
          "name": "first_name",
          "op": "eq",
          "val": "John"
        }
      },
      {
        "and": [
          {
            "name": "first_name",
            "op": "like",
            "val": "%Jim%"
          },
          {
            "name": "date_create",
            "op": "gt",
            "val": "1990-01-01"
          }
        ]
      }
    ]
  }
]

GET /users?filter=[{"name":"group.name","op":"ilike","val":"%admin%"},{"or":[{"not":{"name":"first_name","op":"eq","val":"John"}},{"and":[{"name":"first_name","op":"like","val":"%Jim%"},{"name":"date_create","op":"gt","val":"1990-01-01"}]}]}] HTTP/1.1
Accept: application/vnd.api+json

Filtering records by a field that is null

GET /users?filter=[{"name":"name","op":"is_","val":null}] HTTP/1.1
Accept: application/vnd.api+json

Filtering records by a field that is not null

GET /users?filter=[{"name":"name","op":"isnot","val":null}] HTTP/1.1
Accept: application/vnd.api+json

Common available operators:

any: used to filter on “to many” relationships
between: used to filter a field between two values
endswith: checks if field ends with a string
eq: checks if field is equal to something
ge: checks if field is greater than or equal to something
gt: checks if field is greater than something
has: used to filter on “to one” relationships
ilike: checks if field contains a string (case insensitive)
in_: checks if field is in a list of values
is_: checks if field is a value
isnot: checks if field is not a value
like: checks if field contains a string
le: checks if field is less than or equal to something
lt: checks if field is less than something
match: checks if field matches against a string or pattern
ne: checks if field is not equal to something
notilike: checks if field does not contain a string (case insensitive)
notin_: checks if field is not in a list of values
notlike: checks if field does not contain a string
startswith: checks if field starts with a string

Note

Available operators depend on the field type in your model

Simple filters

Simple filters add support for a simplified form of filters and support only the eq operator. Each simple filter is transformed into a full filter and appended to the list of filters.

For example

GET /users?filter[first_name]=John HTTP/1.1
Accept: application/vnd.api+json

equals:

GET /users?filter=[{"name":"first_name","op":"eq","val":"John"}] HTTP/1.1
Accept: application/vnd.api+json

You can also use more than one simple filter in a request:

GET /users?filter[first_name]=John&filter[gender]=male HTTP/1.1
Accept: application/vnd.api+json

which is equal to:

[
   {
      "name":"first_name",
      "op":"eq",
      "val":"John"
   },
   {
      "name":"gender",
      "op":"eq",
      "val":"male"
   }
]

GET /users?filter=[{"name":"first_name","op":"eq","val":"John"},{"name":"gender","op":"eq","val":"male"}] HTTP/1.1

You can also use relationship attribute in a request:

GET /users?filter[group_id]=1 HTTP/1.1
Accept: application/vnd.api+json

which is equal to:

GET /users?filter=[{"name":"group.id","op":"eq","val":"1"}] HTTP/1.1