Not able to filter json column filter in flask-sqlalchemy

[shrey....@invicto.in]

Hi,

I am using flask-sqlalchemy in my project, but I am not able to understand how to query(filter_by) on a json column

test_example:

#I am using Postgresql backend

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:postgres@localhost:5432/test_db'

db = SQLAlchemy(app)

#this is my test class

class Student(db.Model):

__tablename__        = 'students'

id=db.Column(db.Integer, primary_key=True, autoincrement=True)

name=db.Column(db.String(200))

roll_no=db.Column(db.Integer)

data_test=db.Column(db.JSON) 

#to put data in table is used 

s= Student(name='shrey',roll_no=100, data_test={'foo':'bar'})

db.session.add(s)

db.session.commit()

#I read in some links and i tried this 

a = Student.query.filter(Student.data_test["foo"].astext =="bar").first()

here the error I am getting is :

Traceback (most recent call last):

  File "sqlalchemyjson.py", line 44, in <module>

    a = Student.query.filter(Student.data_test["foo"].astext =="bar").first()

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line 682, in __getattr__

    key)

AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'astext'

I tried few other operations also, but nothing worked 


can someone help me on this?

[Mike Bayer]

"astext" is part of the psycopg2 variant of JSON, use it like this:

from sqlalchemy dialects import postgresql

class Student(db.Model):
# ...
data_test=db.Column(postgresql.JSON)


if you're using plain JSON, you should use cast:

from sqlalchemy import cast

cast(
data_table.c.data['some_key'], String
) == '"some_value"'

>
>
> can someone help me on this?
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[shrey....@invicto.in]

Hi Mike,

as you said I tried this:

 
from sqlalchemy.dialects import postgresql 

class Student(db.Model): 
    # ... 
   data_test=db.Column(postgresql.JSON) 

and I tried querying like this:

a = Student.query.filter(Student.data_test["foo"].astext =="bar").first()

tried this as well:
a = Student.query.filter(Student.data_test["foo"].astext.cast(String)=="bar").first()

But still I am getting error:

Traceback (most recent call last):

  File "sqlalchemyjson.py", line 46, in <module>

    a = Student.query.filter(Student.data_test["foo"].astext =="bar").first()

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2690, in first

    ret = list(self[0:1])

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2482, in __getitem__

    return list(res)

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2790, in __iter__

    return self._execute_and_instances(context)

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2813, in _execute_and_instances

    result = conn.execute(querycontext.statement, self._params)

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute

    return meth(self, multiparams, params)

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection

    return connection._execute_clauseelement(self, multiparams, params)

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement

    compiled_sql, distilled_params

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context

    context)

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception

    exc_info

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context

    context)

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: json ->> unknown

LINE 3: WHERE (students.data_test ->> 'foo') = 'bar'  

When I use plain json, what should be my query?

a = Student.query.filter(cast(Student.c.data_test["foo"], String) =="bar").first()

I am getting this:
AttributeError: type object 'Student' has no attribute 'c'

Where am I going wrong?

[Антонио Антуан]

you can call "->>" (and any other) operator directly:
Student.data_test.op('->>')('foo') == 'bar'

if you want to call cast use this:

cast(Student.data_test['foo'], String) == 'bar' 

"c" (the shortcut for "columns") allows for "Table" instances. If you use declarative style, you can not to use it directly, but if you want:

Student.__table__.c.data_test.... 

Should repeat: there is no need to use __table__ attribute directly with declarative style in most cases. 

чт, 7 дек. 2017 г., 9:15 <shrey....@invicto.in>:

--

SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

Антон

[shrey....@invicto.in]

Hi,

I tried using direct  plain JSON:

my model

class Student(db.Model):

__tablename__        = 'students'

id=db.Column(db.Integer, primary_key=True,autoincrement=True)

name=db.Column(db.String(200))

roll_no=db.Column(db.Integer)

data_test=db.Column(db.JSON)

I tried this:

a = Student.query.filter(cast(Student.__table__.c.data_test["foo"], String) =="bar").first()

error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: json -> unknown

LINE 3: WHERE CAST((students.data_test -> 'foo') AS VARCHAR) = 'bar'

                                       ^

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

 [SQL: 'SELECT students.id AS students_id, students.name AS students_name, students.roll_no AS students_roll_no, students.data_test AS students_data_test \nFROM students \nWHERE CAST((students.data_test -> %(data_test_1)s) AS VARCHAR) = %(param_1)s'] [parameters: {'param_1': 'bar', 'data_test_1': 'foo'}]

tried this:

a = Student.query.filter(Student.data_test.op('->>')('foo') == 'bar').first()

error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: json ->> unknown

LINE 3: WHERE (students.data_test ->> 'foo') = 'bar' 

                                  ^

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

 [SQL: 'SELECT students.id AS students_id, students.name AS students_name, students.roll_no AS students_roll_no, students.data_test AS students_data_test \nFROM students \nWHERE (students.data_test ->> %(data_test_1)s) = %(param_1)s \n LIMIT %(param_2)s'] [parameters: {'param_1': 'bar', 'data_test_1': 'foo', 'param_2': 1}]

is this some versioning issue?

I am not able to understand, where am i going wrong?

Thanks for any help in this

[Антонио Антуан]

What is the version of your PostgreSQL?

Here is an example. Works perfectly for me (pg9.6 and pg10).

https://gist.github.com/aCLr/7d794eabbf972a60a15f40b2d3965508

чт, 7 дек. 2017 г. в 16:05, <shrey....@invicto.in>:

--

SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

Антон

[shrey....@invicto.in]

VERSION it was,  was working with pg9.2, upgraded to pg9.6 and everything works fine now.

Thank you so much.

[shrey....@invicto.in]

One more doubt is, is there a way to filter on the full json

something like this:

in your example only:

print(Session.query(Student).filter(Student.data_test =={'foo':'bar'}).first())