Filtering API example

from typing import Any, Union

from pydantic.fields import Field, ModelField
from sqlalchemy.orm import InstrumentedAttribute
from sqlalchemy.sql.elements import BinaryExpression, BooleanClauseList

from fastapi_jsonapi.schema_base import BaseModel


def jsonb_contains_sql_filter(
    schema_field: ModelField,
    model_column: InstrumentedAttribute,
    value: dict[Any, Any],
    operator: str,
) -> Union[BinaryExpression, BooleanClauseList]:
    """
    Any SQLA (or Tortoise) magic here

    :param schema_field:
    :param model_column:
    :param value: any dict
    :param operator: value 'jsonb_contains'
    :return: one sqla filter expression
    """
    return model_column.op("@>")(value)


class PictureSchema(BaseModel):
    """
    Now you can use `jsonb_contains` sql filter for this resource
    """

    name: str
    meta: dict[Any, Any] = Field(
        default_factory=dict,
        description="Any additional info in JSON format.",
        example={"location": "Moscow", "spam": "eggs"},
        _jsonb_contains_sql_filter_=jsonb_contains_sql_filter,
    )

Filter by jsonb contains

[
  {
    "name": "words",
    "op": "jsonb_contains",
    "val": {"location": "Moscow", "spam": "eggs"}
  }
]

Request:

GET /photos?filter=%5B%7B%22name%22%3A%22words%22%2C%22op%22%3A%22jsonb_contains%22%2C%22val%22%3A%7B%22location%22%3A%22Moscow%22%2C%22spam%22%3A%22eggs%22%7D%7D%5D%0A HTTP/1.1
Content-Type: application/vnd.api+json

Response:

HTTP/1.1 200 OK
Content-Type: application/vnd.api+json

{
  "data": [
    {
      "attributes": {
        "name": "pic-qwerty",
        "words": {
          "location": "Moscow",
          "spam": "eggs",
          "foo": "bar",
          "qwe": "abc"
        }
      },
      "id": "7",
      "type": "photo"
    }
  ],
  "jsonapi": {
    "version": "1.0"
  },
  "meta": {
    "count": 1
  }
}

Other examples

# pseudo-code

class User:
    name: str = ...
    words: list[str] = ...

Filter by word

[
  {
    "name": "words",
    "op": "in",
    "val": "spam"
  }
]

Request:

GET /users?filter=%5B%7B%22name%22%3A%22words%22%2C%22op%22%3A%22in%22%2C%22val%22%3A%22spam%22%7D%5D HTTP/1.1
Content-Type: application/vnd.api+json

Response:

HTTP/1.1 200 OK
Content-Type: application/vnd.api+json

{
  "data": [
    {
      "attributes": {
        "name": "Sam",
        "words": [
          "spam",
          "eggs",
          "green-apple"
        ]
      },
      "id": "2",
      "links": {
        "self": "/users/2"
      },
      "type": "user"
    }
  ],
  "jsonapi": {
    "version": "1.0"
  },
  "links": {
    "self": "http://localhost:5000/users?filter=%5B%7B%22name%22%3A%22words%22%2C%22op%22%3A%22in%22%2C%22val%22%3A%22spam%22%7D%5D"
  },
  "meta": {
    "count": 1
  }
}

Filter by words

[
  {
    "name": "words",
    "op": "in",
    "val": ["bar", "eggs"]
  }
]

Request:

GET /users?filter=%5B%7B%22name%22%3A%22words%22%2C%22op%22%3A%22in%22%2C%22val%22%3A%5B%22bar%22%2C%22eggs%22%5D%7D%5D HTTP/1.1
Content-Type: application/vnd.api+json

Response:

HTTP/1.1 200 OK
Content-Type: application/vnd.api+json

{
  "data": [
    {
      "attributes": {
        "name": "John",
        "words": [
          "foo",
          "bar",
          "green-grass"
        ]
      },
      "id": "1",
      "links": {
        "self": "/users/1"
      },
      "type": "user"
    },
    {
      "attributes": {
        "name": "Sam",
        "words": [
          "spam",
          "eggs",
          "green-apple"
        ]
      },
      "id": "2",
      "links": {
        "self": "/users/2"
      },
      "type": "user"
    }
  ],
  "jsonapi": {
    "version": "1.0"
  },
  "links": {
    "self": "http://localhost:5000/users?filter=%5B%7B%22name%22%3A%22words%22%2C%22op%22%3A%22in%22%2C%22val%22%3A%5B%22bar%22%2C%22eggs%22%5D%7D%5D"
  },
  "meta": {
    "count": 2
  }
}