Possible to query JSON postgres field?

[vfr292]

Hi,

I'm doing something like:

class JsonData(Base):
    __tablename__ = 'json_datas'
    id = Column(Integer, primary_key=True)
    Column(MutableDict.as_mutable(pg_JSONB)) #from sqlalchemy.ext.mutable
    nested_mutable_json = Column(NestedMutableJson)

value = dict(key = 'test')

jd = JsonData(nested_mutable_json = value, jsonb_mutable_dict = value)
session.add(jd)
session.commit()

print(jd.nested_mutable_json) #looks OK
print(jd.nested_mutable_json['key']) #looks OK

jd = session.query(JsonData).filter(
    JsonData.jsonb_mutable_dict['key'].astext == 'test'
).one() #worked OK

print('instance retrieved with jsonb_mutable_dict')

jd = session.query(JsonData).filter(
    JsonData.nested_mutable_json['key'].astext == 'test'
).one()

print('test_search_by_key_value for nested_mutable_json')

Doing the filter query worked with the sqlalchemy MutableDict. However, it failes for the NestedMutableJson withthe error below:

Traceback (most recent call last):
  File "/Users/Victor/Documents/Python/Project/src/scripts/review_postgres_sql-alchmey_json_field_properties.py", line 173, in <module>
    test_search_by_key_value_explicit()
  File "/Users/Victor/Documents/Python/Project/src/scripts/review_postgres_sql-alchmey_json_field_properties.py", line 118, in test_search_by_key_value_explicit
    JsonData.nested_mutable_json['key'].astext == 'test'
  File "/Users/Victor/Documents/Python/Project/venv_project/lib/python3.6/site-packages/sqlalchemy/sql/operators.py", line 371, in __getitem__
    review_postgres_sqleturn self.operate(getitem, index)
  File "/Users/Victor/Documents/Python/Project/venv_project/lib/python3.6/site-packages/sqlalchemy/orm/attributes.py", line 175, in operate
    return op(self.comparator, *other, **kwargs)
  File "/Users/Victor/Documents/Python/Project/venv_project/lib/python3.6/site-packages/sqlalchemy/sql/operators.py", line 371, in __getitem__
    return self.operate(getitem, index)
  File "/Users/Victor/Documents/Python/Project/venv_project/lib/python3.6/site-packages/sqlalchemy/orm/properties.py", line 270, in operate
    return op(self.__clause_element__(), *other, **kwargs)
  File "/Users/Victor/Documents/Python/Project/venv_project/lib/python3.6/site-packages/sqlalchemy/sql/operators.py", line 371, in __getitem__
    return self.operate(getitem, index)
  File "/Users/Victor/Documents/Python/Project/venv_project/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 686, in operate
    return op(self.comparator, *other, **kwargs)
  File "/Users/Victor/Documents/Python/Project/venv_project/lib/python3.6/site-packages/sqlalchemy/sql/operators.py", line 371, in __getitem__
    return self.operate(getitem, index)
  File "/Users/Victor/Documents/Python/Project/venv_project/lib/python3.6/site-packages/sqlalchemy/sql/type_api.py", line 815, in operate
    op, *other, **kwargs)
  File "<string>", line 1, in <lambda>
  File "/Users/Victor/Documents/Python/Project/venv_project/lib/python3.6/site-packages/sqlalchemy/sql/type_api.py", line 63, in operate
    return o[0](self.expr, op, *(other + o[1:]), **kwargs)
  File "/Users/Victor/Documents/Python/Project/venv_project/lib/python3.6/site-packages/sqlalchemy/sql/default_comparator.py", line 177, in _getitem_impl
    _unsupported_impl(expr, op, other, **kw)
  File "/Users/Victor/Documents/Python/Project/venv_project/lib/python3.6/site-packages/sqlalchemy/sql/default_comparator.py", line 182, in _unsupported_impl
    "this expression" % op.__name__)
NotImplementedError: Operator 'getitem' is not supported on this expression

Is it possible to search like I do with the sqlalchemy MutableDict? If so. would it be possible to provide an example?

Thank you!

~Victor

[edelooff]

I don't think this is fixable for the NestedMutableJson class, because it would require a different base class, or include the comparators from Postgres' JSONB depending on the engine that's connected to and that sounds like a huge source of confusion and possible bugs.

However, what I think should work is taking the NestedMutable class and applying it to SQLAlchemy's JSONB type, like this:

from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy_json import NestedMutable

class JsonData(Base):
    __tablename__ = 'json_datas'
    id = Column(Integer, primary_key=True)
    jsonb_nested_mutable = Column(NestedMutable.as_mutable(JSONB))

[vfr292]

Hi, thank you! That worked

As I was going through different JSON implementation options I wrote a script to test their behaviors. I made a gist of it and it can be found here:

https://gist.github.com/vfr292/41530bfb56778ffc26fe53c605f40feb

[edelooff]

Closing this as it's resolved.