Custom SQL filtering
Sometimes you need custom filtering that’s not supported natively. You can define new filtering rules as in this example:
Prepare pydantic schema which is used in ApplicationBuilder as schema
from typing import Annotated, Optional
import orjson as json
from pydantic import BaseModel, Field
from pydantic.fields import FieldInfo
from sqlalchemy.orm import InstrumentedAttribute
from sqlalchemy.sql.expression import BinaryExpression
from fastapi_jsonapi.exceptions import InvalidFilters
from fastapi_jsonapi.types_metadata.custom_filter_sql import CustomFilterSQLA
def _get_sqlite_json_ilike_expression(
model_column: InstrumentedAttribute,
value: list,
operator: str,
) -> BinaryExpression:
try:
target_field, regex = value
except ValueError:
msg = f'The "value" field has to be list of two values for op `{operator}`'
raise InvalidFilters(msg)
if isinstance(regex, (list, dict)):
regex = json.dumps(regex).decode()
elif isinstance(regex, bool):
return model_column.op("->>")(target_field).is_(regex)
else:
regex = f"{regex}"
return model_column.op("->>")(target_field).ilike(regex)
class SQLiteJSONIlikeFilterSQL(CustomFilterSQLA):
def get_expression(
self,
schema_field: FieldInfo,
model_column: InstrumentedAttribute,
value: list[str],
operator: str,
) -> BinaryExpression:
return _get_sqlite_json_ilike_expression(model_column, value, operator)
sql_filter_sqlite_json_ilike = SQLiteJSONIlikeFilterSQL(op="sqlite_json_ilike")
class PictureSchema(BaseModel):
"""
Now you can use `jsonb_contains` sql filter for this resource
"""
name: str
meta: Annotated[Optional[dict], sql_filter_sqlite_json_ilike] = Field(
default_factory=dict,
description="Any additional info in JSON format.",
example={"location": "Moscow", "spam": "eggs"},
)
Declare models as usual, create routes as usual.
Search for objects
Note
Note that url has to be quoted. It’s unquoted only for an example
Request:
GET /pictures?filter=[{"name":"picture","op":"sqlite_json_ilike","val":["meta": "Moscow"]}] HTTP/1.1
Accept: application/vnd.api+json
Filter value has to be a valid JSON:
[
{
"name":"picture",
"op":"sqlite_json_ilike",
"val":["meta": "Moscow"]
}
]