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

[shrey . chauhan]

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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[shrey . chauhan]

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

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

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

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, :

> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[shrey . chauhan]

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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

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

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 :

> 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 
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[shrey . chauhan]

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 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.