Re: [sqlalchemy] Loading of dependent objects performance issue
Thank you Michael you perfectly answered the question ! I just removed the custom primaryjoin condition and it works as expected -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] JSONB double casting
I'm having some difficulty using SQLAlchemy's jsonb operators to produce my
desired SQL.
Intended SQL:
SELECT *
FROM foo
WHERE foo.data-'key1' ? 'a'
...where `foo.data` is formatted like this:
{
'key1': ['a', 'b', 'c'],
'key2': ['d', 'e', 'f']
}
So, I'm trying to find records where the array associated with `key1`
contains some value, 'a' in this case.
I thought it'd be a straightforward query, like:
sess.query(Foo).filter(Foo.data['key1'].has_key('a')).all()
But this is yielding:
AttributeError: Neither 'JSONElement' object nor 'Comparator' object
has an attribute 'has_key'
So I changed the query to:
sess.query(Foo).filter(Foo.data['key1'].cast(JSONB).has_key('a')).all()
But this query produces the following SQL statement:
SELECT *
FROM foo
WHERE (foo.data-'key1')::JSONB ? 'a'
Here, the `-` operator is casting the jsonb value associated with key
`key1` to text, which I then have to cast back to jsonb in order to use the
`?` operator (jsonb-specific) to check if a string is contained in the
first value.
Any ideas?
Thanks.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] JSONB double casting
Brian Findlay brian.m.find...@gmail.com wrote:
I'm having some difficulty using SQLAlchemy's jsonb operators to produce my
desired SQL.
Intended SQL:
SELECT *
FROM foo
WHERE foo.data-'key1' ? 'a'
...where `foo.data` is formatted like this:
{
'key1': ['a', 'b', 'c'],
'key2': ['d', 'e', 'f']
}
So, I'm trying to find records where the array associated with `key1`
contains some value, 'a' in this case.
I thought it'd be a straightforward query, like:
sess.query(Foo).filter(Foo.data['key1'].has_key('a')).all()
But this is yielding:
AttributeError: Neither 'JSONElement' object nor 'Comparator' object has
an attribute 'has_key'
So I changed the query to:
sess.query(Foo).filter(Foo.data['key1'].cast(JSONB).has_key('a')).all()
But this query produces the following SQL statement:
SELECT *
FROM foo
WHERE (foo.data-'key1')::JSONB ? ‘a'
try using the type_coerce() function instead of cast, it should give you the
has_key() but won’t change the SQL. (type_cast(Foo.data[‘key’],
JSONB).has_key())
just a guess.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: JSONB double casting
Test data attached. Perhaps I'm doing something else wrong?
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import Session
from sqlalchemy.schema import Column
from sqlalchemy.types import Integer
from sqlalchemy.dialects.postgresql import JSONB
Base = declarative_base()
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
data = Column(JSONB)
database_url = postgresql://brian@10.0.1.10:5432/test
e = create_engine(database_url, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
# Insert data
user1 = Foo(id=1, data={'key1': ['a', 'b', 'c'], 'key2': ['d', 'e', 'f']})
user2 = Foo(id=2, data={'key1': ['g', 'h', 'i'], 'key2': ['j', 'k', 'l']})
user3 = Foo(id=3, data={'key1': ['m', 'n', 'o'], 'key2': ['p', 'q', 'r']})
sess.add_all([user1, user2, user3])
sess.commit()
# Tests
# stmt1 = sess.query(Foo).filter(Foo.data['key1'].has_key('a')).all()
# stmt2 = sess.query(Foo).filter(Foo.data['key1'].cast(JSONB).has_key('a')).all()
Re: [sqlalchemy] JSONB double casting
`type_coerce()` did the trick. Thanks, Mike! On Wednesday, March 18, 2015 at 12:55:57 PM UTC-4, Michael Bayer wrote: try using the type_coerce() function instead of cast, it should give you the has_key() but won’t change the SQL. (type_cast(Foo.data[‘key’], JSONB).has_key()) just a guess. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
