Your SQL itself isn't going to work; there's no 'item' column in your
select statement. You should read
http://www.postgresql.org/docs/9.4/interactive/functions-json.html to
see what operators you have. (I think you want '@>', not LIKE.)
In SQLAlchemy, this would be done as
.where(mytable.c['data'].contains('third')).
On Thu, Dec 31, 2015, at 03:27 AM, Sami Pietilä wrote:
> Hi,
>
> I am looking for an example how to use sqlalchemy core to search an item
> from a column having a jsonb list.
>
> I have PosgreSQL database having following example table from which I am
> searching an item:
> create table jtable (data jsonb);
> insert into jtable (data) values ('["first","second"]');
> insert into jtable (data) values ('["third","fourth"]');
> select * from jtable, jsonb_array_elements_text(data) item where item
> LIKE
> '%third%';
>
> I am trying to use sqlalchemy to do the search with a select like:
> sql_command = select(tables['jtable'].c['data'].where(...)), but I don't
> know how to write where().
>
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
