[sqlalchemy] Not able to filter json column filter in flask-sqlalchemy

[s***@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:***@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?
--
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 ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

Post by s***@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
#I am using Postgresql backend
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =
db = SQLAlchemy(app)
#this is my test class
__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()
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

"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"'

Post by s***@invicto.in
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
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
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 ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[s***@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?
--
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 ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

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.

Post by s***@invicto.in
Hi Mike,
*from sqlalchemy.dialects import postgresql *
*class Student(db.Model): # ...
data_test=db.Column(postgresql.JSON) *
*a = Student.query.filter(Student.data_test["foo"].astext =="bar").first()*
*a =
Student.query.filter(Student.data_test["foo"].astext.cast(String)=="bar").first()*
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()*
AttributeError: type object 'Student' has no attribute 'c'
Where am I going wrong?
--
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
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
АМтПМ
--
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 ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[s***@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
--
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 ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

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

Post by s***@invicto.in
Hi,
my 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)
*a = Student.query.filter(cast(Student.__table__.c.data_test["foo"],
String) =="bar").**first**()*
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 ->
'bar', 'data_test_1': 'foo'}]
*a = Student.query.filter(Student.data_test.op('->>')('foo') ==
'bar').first()*
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 ->>
{'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
--
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
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
АМтПМ
--
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 ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[s***@invicto.in]

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

Thank you so much.
--
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 ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[s***@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())
--
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 ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.