How to run a Flask-restless api filter query on JSONB content

Question

I am having trouble trying to perform flask-restless api queries on data that is contained inside a JSONB object. I'm going to guess it's because the underlying query doesn't know how to cast the data field so it doesn't know how to generate the query properly, but I'm not sure.

The query that is run when I perform an ajax search:

http://192.168.163.129:1080/api/subscribers?page=1&results_per_page=10&q={"filters":[{"name":"data","op":"has","val":{"name":"full_name","op":"eq","val":"%Steve%"}}],"order_by":[{"field":"created","direction":"asc"}]}

This is the traceback I get when I run the query.

2016-12-29 22:26:33,068 - app - ERROR - mapper
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/flask_restless/views.py", line 1172, in _search
    result = search(self.session, self.model, search_params)
  File "/usr/local/lib/python2.7/dist-packages/flask_restless/search.py", line 587, in search
    query = create_query(session, model, search_params, _ignore_order_by)
  File "/usr/local/lib/python2.7/dist-packages/flask_restless/search.py", line 549, in create_query
    _ignore_order_by)
  File "/usr/local/lib/python2.7/dist-packages/flask_restless/search.py", line 480, in create_query
    filters = [create_filt(model, filt) for filt in search_params.filters]
  File "/usr/local/lib/python2.7/dist-packages/flask_restless/search.py", line 438, in _create_filter
    return create_op(model, fname, filt.operator, val, relation)
  File "/usr/local/lib/python2.7/dist-packages/flask_restless/search.py", line 411, in _create_operation
    return opfunc(field, argument, fieldname)
  File "/usr/local/lib/python2.7/dist-packages/flask_restless/search.py", line 106, in <lambda>
    'has': lambda f, a, fn: f.has(_sub_operator(f, a, fn)),
  File "/usr/local/lib/python2.7/dist-packages/flask_restless/search.py", line 37, in _sub_operator
    submodel = model.property.mapper.class_
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 833, in __getattr__
    return self._fallback_getattr(key)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 811, in _fallback_getattr
    raise AttributeError(key)
AttributeError: mapper

API Code:

manager.create_api(Subscribers, methods=['GET'], results_per_page=10,
                   preprocessors=dict(GET_SINGLE=[subscribers_preprocessor],
                                      GET_MANY=[subscribers_preprocessor]))

The pre-processor is a simple authentication processor.

Any idea how to properly format a filter for filtering on JSONB postgres fields?

Answer 1

The answer at this point is, you can't with flask-restless as is.

I did some modification to flask-restless to split a specially formatted query string and cast the query correctly for jsonb. Kind of a pain, and probably not scalable.

It is marked for enhancement on flask-restless git.

https://github.com/jfinkels/flask-restless/issues/623