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.
