SQLAlchemy filtering & sorting utility

Table of contents

• SQLAlchemy filtering & sorting utility
  • Purpose
  • Features
  • Usage
    • Filtering
      • Filtering operators
    • Sorting
    • Comparison operators
      • PostgreSQL specific JSON operators
  • Versions tested
  • Install

Purpose

This repository was developed to provide a simple JSON format interface to the SQLAlchemy query API to query on json SQL fields, which can be used by front-end applications to generate automatically SQL filtering queries with minimum effort in the back-end service implementation.

Features

Some of the sqlalchemy-filtering utility features include:

Category	Feature	PostgreSQL	MySQL	SQLite
Filtering	Ability to filter simple SQL fields	Yes	Yes	Yes
	Ability to filter json SQL fields	Yes	Yes (Beta)	No
	Ability to filter join queries	No	No	No
Sorting	Ability to sort simple SQL fields	Yes	Yes	Yes
	Ability to sort json SQL fields	Yes	Yes (Beta)	No
	Ability to sort on joined fields	No	No	No

Usage

Given the following SQLAlchemy models:

from sqlalchemy import Column, Integer, String, DateTime, Float
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import functions

Base = declarative_base()


class UserInfo(Base):
    __tablename__ = "user_info"

    id = Column(Integer, primary_key=True, index=True)
    details = Column(JSONB)
    creation_date = Column(DateTime, nullable=False, server_default=functions.now())


class Ratings(Base):
    __tablename__ = "ratings"

    id = Column(Integer, primary_key=True, index=True)
    creation_date = Column(DateTime, nullable=False, server_default=functions.now())
    movie_name = Column(String)
    rating = Column(Float)

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from tests import models

engine = create_engine(
  'postgresql://postgres:password@localhost:5432/filter'
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

db = SessionLocal()

query = db.query(models.UserInfo)

Assuming we have records in the database with the following structure:

1. ID: id (type: integer)
2. Details: details (type: json)

{
  "skin": "White",
  "extra": {
    "test": "value"
  },
  "gender": "Male",
  "height": 188,
  "last_name": "Walker",
  "first_name": "Paul",
  "user_details": [
    {
      "skill": "Fighting",
      "rating": 7
    },
    {
      "skill": "Driving",
      "rating": 10
    }
  ]
}

3. Creation date: creation_date (type: timestamp)

Filtering

# Case 1: Flat request on JSON column
obj = {
    "filter": [
        {
            ## (PostgreSQL) It returns all users that have skill 'Fighting' with rating 10
            "field": "details",
            "node": "user_details",
            "operator": "@>",
            "value": "[{\"skill\":\"Fighting\",\"rating\":10}]",
        },
        {
            ## (PostgreSQL) It returns all users that have skill 'Fighting' and any rating (in any skill) with rating 10
            "field": "details",
            "node": "user_details",
            "operator": "@>",
            "value": "[{\"skill\":\"Fighting\"},{\"rating\":10}]",
        }
    ]
}

# Case2: Nested request in JSON nodes
obj = {
    "filter": [
        {
            "field": "details",
            "node": "extra",
            "value": {
                "field": "test",
                "operator": "==",
                "value": "value"
            }
        }
    ]
}

from sqlalchemy_filtering.filter_util import filter_apply
from sqlalchemy_filtering.operators import SQLDialect
from sqlalchemy_filtering.validators import FilterRequest

from tests import models

query = filter_apply(query=query, entity=models.UserInfo, obj=FilterRequest(obj), dialect=SQLDialect.POSTGRESQL)

Filtering operators

Filtering operators AND (and_), OR (or_) and NOT (not_) are supported and can be used all together.

obj3 = {
    "filter": {
        "not": [
            {
                "field": "movie_name",
                "operator": "==",
                "value": "The Dark Knight"
            },
            {
                "field": "rating",
                "operator": "==",
                "value": 7
            }
        ]

    }
}

Sorting

# Case 1: Sort request on JSON column
obj = {
    "sort": [
        {
            "field": "details",
            "node": "height",
            "direction": "desc",
            "nullsLast": True
        }
    ]
}

# Case 2: Sort request on inner JSON node column
obj = {
    "sort": [
        {
            "field": "details",
            "node": "extra.test",
            "direction": "desc",
            "nullsLast": True
        }
    ]
}

# Case 3: Sort request on simple column
obj = {
    "sort": [
        {
            "field": "creation_date",
            "direction": "desc",
            "nullsLast": True
        }
    ]
}

from sqlalchemy_filtering.sort_util import sort_apply
from sqlalchemy_filtering.validators import SortRequest

from tests import models

query = sort_apply(query=query, entity=models.UserInfo, obj=SortRequest(obj))

Comparison operators

The list of the available comparison operators can be found below:

• is (aliases: ==, eq)
• is_not (aliases: !=, ne)
• is_null
• is_not_null
• > (alias: gt)
• < (alias: lt)
• >= (alias: ge)
• <= (alias: le)
• like
• not_like
• ilike
• not_ilike
• in
• not_in
• contains
• any
• match
• starts_with

PostgreSQL specific JSON operators

• @>
• <@
• @?
• @@

Versions tested

System	Version
PostgreSQL	9.2, 12.7
MySQL	8.0.20, 8.0.27
SQLite	3.37
SQLAlchemy	>= 1.4.0

Install

This Python project is available in the PyPi package index and can be installed using pip.

pip install sqlalchemy-filtering==0.1.2