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

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:

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