[sqlalchemy] JSONB double casting

[Brian Findlay]

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

[Michael Bayer]

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.

Re: [sqlalchemy] JSONB double casting

[Brian Findlay]

`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.