sqlalchemy filter by json field
I have model with json column
. Example of model and data:
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://...'
db = SQLAlchemy()
db.init_app(app)
app.app_context().push()
class Example(db.Model):
id = db.Column(db.Integer(), nullable=False, primary_key=True, )
json_field = db.Column(db.JSON())
db.create_all()
db.session.add(Example(json_field='id': None))
db.session.add(Example(json_field='id': 1))
db.session.add(Example(json_field='id': 50))
db.session.add(Example(json_field=))
db.session.commit()
Now I try to find records where id == 1
:
query = db.session.query(Example).filter(Example.json_field['id'] == 1)
print(query.all())
And I getting the next error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator
does not exist: json = integer LINE 3: WHERE (example.json_field ->
'id') = 1
The reason. Look at generated query:
SELECT example.id AS example_id, example.json_field AS example_json_field
FROM example
WHERE (example.json_field -> %(json_field_1)s) = %(param_1)s
But in my case correct query should be like this:
SELECT * FROM example WHERE CAST(json_field->>'id' AS INTEGER) = 1;
How can I do this?
I have tried use cast, but unsuccessfully:
print(
db.session.query(Example).filter(
cast(Example.json_field['id'], Integer) == 1
).all()
)
The error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot
cast type json to integer LINE 3: WHERE CAST((example.json_field ->
'id') AS INTEGER) = 1
As you can see where clause
still wrong. Also I need to use range (>
, <=
etc.) conditions. Thanks for help.
python sqlalchemy flask-sqlalchemy
add a comment |
I have model with json column
. Example of model and data:
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://...'
db = SQLAlchemy()
db.init_app(app)
app.app_context().push()
class Example(db.Model):
id = db.Column(db.Integer(), nullable=False, primary_key=True, )
json_field = db.Column(db.JSON())
db.create_all()
db.session.add(Example(json_field='id': None))
db.session.add(Example(json_field='id': 1))
db.session.add(Example(json_field='id': 50))
db.session.add(Example(json_field=))
db.session.commit()
Now I try to find records where id == 1
:
query = db.session.query(Example).filter(Example.json_field['id'] == 1)
print(query.all())
And I getting the next error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator
does not exist: json = integer LINE 3: WHERE (example.json_field ->
'id') = 1
The reason. Look at generated query:
SELECT example.id AS example_id, example.json_field AS example_json_field
FROM example
WHERE (example.json_field -> %(json_field_1)s) = %(param_1)s
But in my case correct query should be like this:
SELECT * FROM example WHERE CAST(json_field->>'id' AS INTEGER) = 1;
How can I do this?
I have tried use cast, but unsuccessfully:
print(
db.session.query(Example).filter(
cast(Example.json_field['id'], Integer) == 1
).all()
)
The error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot
cast type json to integer LINE 3: WHERE CAST((example.json_field ->
'id') AS INTEGER) = 1
As you can see where clause
still wrong. Also I need to use range (>
, <=
etc.) conditions. Thanks for help.
python sqlalchemy flask-sqlalchemy
1
Possible duplicate of Python SQLAlchemy and Postgres - How to query a JSON element
– Bear Brown
Nov 12 at 14:25
@BearBrown this is not duplicate. I need work withintegers
but not withtext
. Also I want to use>
and<
conditions
– Danila Ganchar
Nov 12 at 14:27
add a comment |
I have model with json column
. Example of model and data:
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://...'
db = SQLAlchemy()
db.init_app(app)
app.app_context().push()
class Example(db.Model):
id = db.Column(db.Integer(), nullable=False, primary_key=True, )
json_field = db.Column(db.JSON())
db.create_all()
db.session.add(Example(json_field='id': None))
db.session.add(Example(json_field='id': 1))
db.session.add(Example(json_field='id': 50))
db.session.add(Example(json_field=))
db.session.commit()
Now I try to find records where id == 1
:
query = db.session.query(Example).filter(Example.json_field['id'] == 1)
print(query.all())
And I getting the next error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator
does not exist: json = integer LINE 3: WHERE (example.json_field ->
'id') = 1
The reason. Look at generated query:
SELECT example.id AS example_id, example.json_field AS example_json_field
FROM example
WHERE (example.json_field -> %(json_field_1)s) = %(param_1)s
But in my case correct query should be like this:
SELECT * FROM example WHERE CAST(json_field->>'id' AS INTEGER) = 1;
How can I do this?
I have tried use cast, but unsuccessfully:
print(
db.session.query(Example).filter(
cast(Example.json_field['id'], Integer) == 1
).all()
)
The error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot
cast type json to integer LINE 3: WHERE CAST((example.json_field ->
'id') AS INTEGER) = 1
As you can see where clause
still wrong. Also I need to use range (>
, <=
etc.) conditions. Thanks for help.
python sqlalchemy flask-sqlalchemy
I have model with json column
. Example of model and data:
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://...'
db = SQLAlchemy()
db.init_app(app)
app.app_context().push()
class Example(db.Model):
id = db.Column(db.Integer(), nullable=False, primary_key=True, )
json_field = db.Column(db.JSON())
db.create_all()
db.session.add(Example(json_field='id': None))
db.session.add(Example(json_field='id': 1))
db.session.add(Example(json_field='id': 50))
db.session.add(Example(json_field=))
db.session.commit()
Now I try to find records where id == 1
:
query = db.session.query(Example).filter(Example.json_field['id'] == 1)
print(query.all())
And I getting the next error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator
does not exist: json = integer LINE 3: WHERE (example.json_field ->
'id') = 1
The reason. Look at generated query:
SELECT example.id AS example_id, example.json_field AS example_json_field
FROM example
WHERE (example.json_field -> %(json_field_1)s) = %(param_1)s
But in my case correct query should be like this:
SELECT * FROM example WHERE CAST(json_field->>'id' AS INTEGER) = 1;
How can I do this?
I have tried use cast, but unsuccessfully:
print(
db.session.query(Example).filter(
cast(Example.json_field['id'], Integer) == 1
).all()
)
The error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot
cast type json to integer LINE 3: WHERE CAST((example.json_field ->
'id') AS INTEGER) = 1
As you can see where clause
still wrong. Also I need to use range (>
, <=
etc.) conditions. Thanks for help.
python sqlalchemy flask-sqlalchemy
python sqlalchemy flask-sqlalchemy
edited Nov 13 at 10:32
Antti Haapala
80.6k16152193
80.6k16152193
asked Nov 12 at 14:16
Danila Ganchar
3,73882145
3,73882145
1
Possible duplicate of Python SQLAlchemy and Postgres - How to query a JSON element
– Bear Brown
Nov 12 at 14:25
@BearBrown this is not duplicate. I need work withintegers
but not withtext
. Also I want to use>
and<
conditions
– Danila Ganchar
Nov 12 at 14:27
add a comment |
1
Possible duplicate of Python SQLAlchemy and Postgres - How to query a JSON element
– Bear Brown
Nov 12 at 14:25
@BearBrown this is not duplicate. I need work withintegers
but not withtext
. Also I want to use>
and<
conditions
– Danila Ganchar
Nov 12 at 14:27
1
1
Possible duplicate of Python SQLAlchemy and Postgres - How to query a JSON element
– Bear Brown
Nov 12 at 14:25
Possible duplicate of Python SQLAlchemy and Postgres - How to query a JSON element
– Bear Brown
Nov 12 at 14:25
@BearBrown this is not duplicate. I need work with
integers
but not with text
. Also I want to use >
and <
conditions– Danila Ganchar
Nov 12 at 14:27
@BearBrown this is not duplicate. I need work with
integers
but not with text
. Also I want to use >
and <
conditions– Danila Ganchar
Nov 12 at 14:27
add a comment |
1 Answer
1
active
oldest
votes
Flask-SQLAlchemy's SQLAlchemy
object – commonly named db
– gives access to functions etc. from sqlalchemy
and sqlalchemy.orm
, and so db.JSON
is the generic JSON
type that does not provide the Postgresql specific operators. You should instead use sqlalchemy.dialects.postgresql.JSON
:
from sqlalchemy.dialects.postgresql import JSON
class Example(db.Model):
id = db.Column(db.Integer(), nullable=False, primary_key=True, )
json_field = db.Column(JSON)
With the proper type in place you must explicitly convert the JSON to text first and then cast to an integer:
db.session.query(Example).
filter(Example.json_field['id'].astext.cast(Integer) == 1)
This produces the desired predicate
CAST(json_field->>'id' AS INTEGER) = 1
The same applies to all types that cannot be directly cast from json
. SQLAlchemy used to offer a shortcut for the combination of astext
and cast()
, but it has been removed in version 1.1 and above:
Changed in version 1.1: The
ColumnElement.cast()
operator onJSON
objects now requires that theJSON.Comparator.astext
modifier be called explicitly, if the cast works only from a textual string.
first of all thank you for your time and answer. But I tried this way earlier. And I gettingAttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'astext'
. My env:python 3.6.1
,flask-sqlalchemy 2.3.2
.
– Danila Ganchar
Nov 12 at 19:42
1
Good point, and that is something that I overlooked before and does make this also Flask-SQLAlchemy specific. I'll amend the answer in a moment.
– Ilja Everilä
Nov 12 at 20:27
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53264047%2fsqlalchemy-filter-by-json-field%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Flask-SQLAlchemy's SQLAlchemy
object – commonly named db
– gives access to functions etc. from sqlalchemy
and sqlalchemy.orm
, and so db.JSON
is the generic JSON
type that does not provide the Postgresql specific operators. You should instead use sqlalchemy.dialects.postgresql.JSON
:
from sqlalchemy.dialects.postgresql import JSON
class Example(db.Model):
id = db.Column(db.Integer(), nullable=False, primary_key=True, )
json_field = db.Column(JSON)
With the proper type in place you must explicitly convert the JSON to text first and then cast to an integer:
db.session.query(Example).
filter(Example.json_field['id'].astext.cast(Integer) == 1)
This produces the desired predicate
CAST(json_field->>'id' AS INTEGER) = 1
The same applies to all types that cannot be directly cast from json
. SQLAlchemy used to offer a shortcut for the combination of astext
and cast()
, but it has been removed in version 1.1 and above:
Changed in version 1.1: The
ColumnElement.cast()
operator onJSON
objects now requires that theJSON.Comparator.astext
modifier be called explicitly, if the cast works only from a textual string.
first of all thank you for your time and answer. But I tried this way earlier. And I gettingAttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'astext'
. My env:python 3.6.1
,flask-sqlalchemy 2.3.2
.
– Danila Ganchar
Nov 12 at 19:42
1
Good point, and that is something that I overlooked before and does make this also Flask-SQLAlchemy specific. I'll amend the answer in a moment.
– Ilja Everilä
Nov 12 at 20:27
add a comment |
Flask-SQLAlchemy's SQLAlchemy
object – commonly named db
– gives access to functions etc. from sqlalchemy
and sqlalchemy.orm
, and so db.JSON
is the generic JSON
type that does not provide the Postgresql specific operators. You should instead use sqlalchemy.dialects.postgresql.JSON
:
from sqlalchemy.dialects.postgresql import JSON
class Example(db.Model):
id = db.Column(db.Integer(), nullable=False, primary_key=True, )
json_field = db.Column(JSON)
With the proper type in place you must explicitly convert the JSON to text first and then cast to an integer:
db.session.query(Example).
filter(Example.json_field['id'].astext.cast(Integer) == 1)
This produces the desired predicate
CAST(json_field->>'id' AS INTEGER) = 1
The same applies to all types that cannot be directly cast from json
. SQLAlchemy used to offer a shortcut for the combination of astext
and cast()
, but it has been removed in version 1.1 and above:
Changed in version 1.1: The
ColumnElement.cast()
operator onJSON
objects now requires that theJSON.Comparator.astext
modifier be called explicitly, if the cast works only from a textual string.
first of all thank you for your time and answer. But I tried this way earlier. And I gettingAttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'astext'
. My env:python 3.6.1
,flask-sqlalchemy 2.3.2
.
– Danila Ganchar
Nov 12 at 19:42
1
Good point, and that is something that I overlooked before and does make this also Flask-SQLAlchemy specific. I'll amend the answer in a moment.
– Ilja Everilä
Nov 12 at 20:27
add a comment |
Flask-SQLAlchemy's SQLAlchemy
object – commonly named db
– gives access to functions etc. from sqlalchemy
and sqlalchemy.orm
, and so db.JSON
is the generic JSON
type that does not provide the Postgresql specific operators. You should instead use sqlalchemy.dialects.postgresql.JSON
:
from sqlalchemy.dialects.postgresql import JSON
class Example(db.Model):
id = db.Column(db.Integer(), nullable=False, primary_key=True, )
json_field = db.Column(JSON)
With the proper type in place you must explicitly convert the JSON to text first and then cast to an integer:
db.session.query(Example).
filter(Example.json_field['id'].astext.cast(Integer) == 1)
This produces the desired predicate
CAST(json_field->>'id' AS INTEGER) = 1
The same applies to all types that cannot be directly cast from json
. SQLAlchemy used to offer a shortcut for the combination of astext
and cast()
, but it has been removed in version 1.1 and above:
Changed in version 1.1: The
ColumnElement.cast()
operator onJSON
objects now requires that theJSON.Comparator.astext
modifier be called explicitly, if the cast works only from a textual string.
Flask-SQLAlchemy's SQLAlchemy
object – commonly named db
– gives access to functions etc. from sqlalchemy
and sqlalchemy.orm
, and so db.JSON
is the generic JSON
type that does not provide the Postgresql specific operators. You should instead use sqlalchemy.dialects.postgresql.JSON
:
from sqlalchemy.dialects.postgresql import JSON
class Example(db.Model):
id = db.Column(db.Integer(), nullable=False, primary_key=True, )
json_field = db.Column(JSON)
With the proper type in place you must explicitly convert the JSON to text first and then cast to an integer:
db.session.query(Example).
filter(Example.json_field['id'].astext.cast(Integer) == 1)
This produces the desired predicate
CAST(json_field->>'id' AS INTEGER) = 1
The same applies to all types that cannot be directly cast from json
. SQLAlchemy used to offer a shortcut for the combination of astext
and cast()
, but it has been removed in version 1.1 and above:
Changed in version 1.1: The
ColumnElement.cast()
operator onJSON
objects now requires that theJSON.Comparator.astext
modifier be called explicitly, if the cast works only from a textual string.
edited Nov 12 at 20:57
answered Nov 12 at 16:19
Ilja Everilä
23.4k33559
23.4k33559
first of all thank you for your time and answer. But I tried this way earlier. And I gettingAttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'astext'
. My env:python 3.6.1
,flask-sqlalchemy 2.3.2
.
– Danila Ganchar
Nov 12 at 19:42
1
Good point, and that is something that I overlooked before and does make this also Flask-SQLAlchemy specific. I'll amend the answer in a moment.
– Ilja Everilä
Nov 12 at 20:27
add a comment |
first of all thank you for your time and answer. But I tried this way earlier. And I gettingAttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'astext'
. My env:python 3.6.1
,flask-sqlalchemy 2.3.2
.
– Danila Ganchar
Nov 12 at 19:42
1
Good point, and that is something that I overlooked before and does make this also Flask-SQLAlchemy specific. I'll amend the answer in a moment.
– Ilja Everilä
Nov 12 at 20:27
first of all thank you for your time and answer. But I tried this way earlier. And I getting
AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'astext'
. My env: python 3.6.1
, flask-sqlalchemy 2.3.2
.– Danila Ganchar
Nov 12 at 19:42
first of all thank you for your time and answer. But I tried this way earlier. And I getting
AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'astext'
. My env: python 3.6.1
, flask-sqlalchemy 2.3.2
.– Danila Ganchar
Nov 12 at 19:42
1
1
Good point, and that is something that I overlooked before and does make this also Flask-SQLAlchemy specific. I'll amend the answer in a moment.
– Ilja Everilä
Nov 12 at 20:27
Good point, and that is something that I overlooked before and does make this also Flask-SQLAlchemy specific. I'll amend the answer in a moment.
– Ilja Everilä
Nov 12 at 20:27
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53264047%2fsqlalchemy-filter-by-json-field%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Possible duplicate of Python SQLAlchemy and Postgres - How to query a JSON element
– Bear Brown
Nov 12 at 14:25
@BearBrown this is not duplicate. I need work with
integers
but not withtext
. Also I want to use>
and<
conditions– Danila Ganchar
Nov 12 at 14:27