postgresql, jsob and like operator

[Michal Nowikowski]

Hello,

I'm using Postgresql and JSONB column.
I would like to write a query using ORM that will generate such SQL query:

    SELECT id,name,details FROM Device WHERE details::text LIKE '%99%';

where details is of JSONB type and it is converted to text and then like operator is used.
Generally I would like to check if somewhere inside details as a text there is a given substring.
Could you help with that?

Regards,
Michal

[Jonathan Vanasco]

I don't think you can get that exact query staying within the ORM's cross-platform functionality -- I don't think there is anything that can generate the `::` version of casting... but I think something like this should produce the same output:

r = session.query( Device.id, Device.name, Device.details )\

    .filter( sqlalchemy.sql.expression.cast(Device.details, sqlalchemy.types.String).like('99') )\

    .all()

I'm doing this from memory, so i could be off -- but the general idea is that you'll `cast` into a `String`, and then run the `like` operator on that column.  The API docs will have the correct format.

That should generate something like :

   + SELECT id,name,details FROM Device WHERE cast(details as text) LIKE '%99%';

Instead of:

   - SELECT id,name,details FROM Device WHERE details::text LIKE '%99%';

If you need to use the `details::text` format, you could use a text literal in the WHERE clause.  see http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-textual-sql

[Michal Nowikowski]

Great, it works.
Thank you very much.

Regards,
Michal

[Mike Bayer]

On 12/01/2015 12:49 PM, Jonathan Vanasco wrote:
> I don't think you can get that exact query staying within the ORM's
> cross-platform functionality -- I don't think there is anything that can
> generate the `::` version of casting... but I think something like this
> should produce the same output:
>
> r = session.query( Device.id, Device.name, Device.details )\
> .filter( sqlalchemy.sql.expression.cast(Device.details,
> sqlalchemy.types.String).like('99') )\
> .all()
>
> I'm doing this from memory, so i could be off -- but the general idea is
> that you'll `cast` into a `String`, and then run the `like` operator on
> that column. The API docs will have the correct format.

see also the JSON operators which have some built-in text casting stuff:

http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html?highlight=json#sqlalchemy.dialects.postgresql.JSON

this is also getting improved in 1.1.

>
>
> That should generate something like :
>
> + SELECT id,name,details FROM Device WHERE cast(details
> as text) LIKE '%99%';
>
> Instead of:
>
> - SELECT id,name,details FROM Device WHERE details::text LIKE '%99%';
>
> If you need to use the `details::text` format, you could use a text
> literal in the WHERE clause.
> see http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-textual-sql
>

> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

On Tuesday, December 1, 2015 at 7:12:15 PM UTC-5, Michael Bayer wrote:

see also the JSON operators which have some built-in text casting stuff:

http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html?highlight=json#sqlalchemy.dialects.postgresql.JSON

this is also getting improved in 1.1. 

Oh yeah.  I naively assumed this was a search that purposefully wanted to avoid the Postgres JSON operators.  If you can use one of the postgres native searches, that will be MUCH faster.