JSON fields

[Goomba41]

Is it possible to support the filtering function by the key values of JSON fields?
SQLAlchemy has a JSON_EXTRACT function and I can filter Model.json_column ['key'] == value

[juliotrigo]

Hi @Goomba41 , thanks for your request.

It is currently not supported by the library but it'd be a nice addition to it if we can integrate it.

I am going to start looking into it and will keep you posted here.

[btoro]

@Goomba41,

What I ended up doing is using the hybrid_property decorators to "recreate" the fields from the JSON field. This then allows the filter function to work.

class report(Base):
    """ Table to store reports. """
    __tablename__ = "report"

    id = Column(Integer, primary_key=True, autoincrement=True)
    report = Column(JSONB)

    @hybrid_property
    def created(self):
        return self.report['created']

Its not ideal since you have to define them with each change, but it works

[tiru1930]

@juliotrigo is there any progress on this

[tiru1930]

@juliotrigo @Goomba41 @btoro

Looks like these changes might work for jsonb parsing

in sorting.py and filtering.py , I have added below code

if self.sort_spec.get("jsonb_path", None):
      jsonb_keys = self.sort_spec.get("jsonb_path").split("__")
      print(jsonb_keys)
      sqlalchemy_field = sqlalchemy_field.cast(JSONB)[tuple(jsonb_keys)]



filter_spec = [
{"or": [
  {'field': 'metrics', 'op': '==', 'value': '0.8', 'jsonb_path': "metrics__f1"},
  {'field': 'id', 'op': '==', 'value': '1'}
]}
]

sort_spec = [
{'field': 'metrics', 'direction': 'desc', "nullslast": True, 'jsonb_path': "metrics__f1"},
{'field': 'metrics', 'direction': 'asc', "nullslast": True, 'jsonb_path': "metrics__accuracy"},
]

[itsAlexK]

Here's a simple patch for anyone who ends up here for JSONB

import sqlalchemy_filters
import types
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy_filters import (  # noqa
    apply_filters,
    apply_loads,
    apply_pagination,
    apply_sort,
    filters,
    loads,
    models,
    pagination,
    sorting,
)


class JSONPatchField(models.Field):

    def get_sqlalchemy_field(self):

        sub_field_names = None

        if "." in self.field_name:
            field_name, *sub_field_names = self.field_name.split(".")
        else:
            field_name = self.field_name

        if field_name not in self._get_valid_field_names():
            raise models.FieldNotFound(
                "Model {} has no column `{}`.".format(self.model, field_name)
            )
        sqlalchemy_field = getattr(self.model, field_name)

        # If it's a hybrid method, then we call it so that we can work with
        # the result of the execution and not with the method object itself
        if isinstance(sqlalchemy_field, types.MethodType):
            sqlalchemy_field = sqlalchemy_field()
        elif isinstance(sqlalchemy_field.type, JSONB) and sub_field_names:
            sqlalchemy_field = sqlalchemy_field[sub_field_names]
            sqlalchemy_field = sqlalchemy_field.astext
        return sqlalchemy_field


sqlalchemy_filters.filters.Field = JSONPatchField
sqlalchemy_filters.loads.Field = JSONPatchField
sqlalchemy_filters.pagination.Field = JSONPatchField
sqlalchemy_filters.sorting.Field = JSONPatchField