Searching from jsonb list

[Sami Pietilä]

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

[Jon Rosebaugh]

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')).

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

[Sami Pietilä]

Perhaps there is something wrong with my select. I tried with following select and got an error message below:

select([database.tables['jtable'].c['data']]).where(database.tables['jtable'].c['data'].contains('third'))

--- Error Message ---

sqlalchemy.exc.DataError: (psycopg2.DataError) invalid input syntax for type json

LINE 3: WHERE jtable.data @> 'third'

                             ^

DETAIL:  Token "third" is invalid.

CONTEXT:  JSON data, line 1: third

 [SQL: 'SELECT jtable.data \nFROM jtable \nWHERE jtable.data @> %(data_1)s'] [parameters: {'data_1': 'third'}]

[Jon Rosebaugh]

The @> operator (contains) is defined only for JSONB; your original
message said you were using JSONB. However, this error says your column
is just JSON. These two types are not the same thing; you should use
JSONB if you have a choice.

[Sami Pietilä]

Hi,

I think the data column is jsonb type.

wsysdb=> select * from jtable;

        data         

---------------------

 ["first", "second"]

 ["third", "fourth"]

(2 rows)

wsysdb=> \d+ jtable;

                       Table "public.jtable"

 Column | Type  | Modifiers | Storage  | Stats target | Description 

--------+-------+-----------+----------+--------------+-------------

 data   | jsonb |           | extended |              | 

[Jon Rosebaugh]

Aha; the arrow in your message appeared to be pointing at the wrong
thing (due to variable-width fonts). My apologies.

Looks like you'll need to perform a cast to convert your string to a
JSONB string.

> > > email to sqlalchemy+...@googlegroups.com <javascript:>.

> > > To post to this group, send email to sqlal...@googlegroups.com

> > <javascript:>.

[Jonathan Vanasco]

the postgres dialect for sqlalchemy supports JSON/JSONB column types , which have some methods.  the docs have examples for all.

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB 

[Sami Pietilä]

Can I have an example how such a cast is done for the string "third" in this case?

[Sami Pietilä]

I think I got "contains" and "cast" working for case sensitive exact string [.contains(cast(word, JSONB)))]. However I would like to match for case insensitive substrings like using [.ilike('%'+word+'%'))]. How can I specify to "contains" to match case insensitive? I also tried to use "%"+word+"%" with contains function but it did not seem to work. 

 

[Mike Bayer]

call func.lower(expr) on the expression that you're comparing towards so
that it will render the SQL LOWER() function, then compare to a lower
case Python value.

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

[Sami Pietilä]

I am not exactly sure where to add sql.func.lower(). Following command gave an error that lower() for jsonb does not exist.

sql_command = select([self.tables[subsection].c[id_label]]).where(sql.func.lower(self.tables[subsection].c[column_name]).contains(cast(word.lower(), JSONB)))

ProgrammingError: (psycopg2.ProgrammingError) function lower(jsonb) does not exist

LINE 3: WHERE (lower(baserecords.first_names) LIKE '%' || CAST('"las...

               ^

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

 [SQL: "SELECT baserecords._id \nFROM baserecords \nWHERE (lower(baserecords.first_names) LIKE '%%' || CAST(%(param_1)s AS JSONB) || '%%')"] [parameters: {'param_1': '"last"'}]

[Mike Bayer]

if it's JSONB then you'd need to CAST it as a CHAR first:

func.lower(cast(column, CHAR)).contains(word.lower())

try it in SQL first at the psql command line to work it out fully.

[Sami Pietilä]

The generated SQL does not seem to be able to return any items. The generated SQL looks like this.

Section: baserecords, Column name: first_names
SELECT baserecords._id
FROM baserecords
WHERE (lower(CAST(baserecords.last_names AS CHAR)) LIKE '%%' || :lower_1 || '%%')

I think casting JSONB column to CHAR does something different than expected. I am not sure, if postgres compares anymore with JSONB list items. With original example table jtable I tried following query and it returned following results.
select CAST(jtable.data AS CHAR) from jtable;
 data
------
 [
 [
(2 rows)

[Mike Bayer]

this is more of a "what SQL will work on Postgresql" question, I'd ask
on StackOverflow or the PG list just in terms of the SQL query to use.
Once you get a string SQL query that works we can do it through SQLAlchemy.

[Sami Pietilä]

I have been using this kind of sql to search case insensitive strings from jsonb lists in postgresql.  jsonb_array_elements_text unpacks the list items as normal table rows from which text can be matched with ilike:

select * from jtable, jsonb_array_elements_text(jtable.data) where value ILIKE '%Firs%';

[Mike Bayer]

you can use that, that's func.jsonb_array_elements_text(column)

[Sami Pietilä]

Sounds good. I was able to write following sql sentense, but I could not figure out how to refer to 'value' (which is created by the jsonb_array_elements_text) named column in where().

sql_sentense = select([database.tables['jtable'], 'value']).select_from(func.jsonb_array_elements_text(database.tables['jtable'].c['data'])).where('value'.contains('firs'))

Are there somewhere examples from sqlalchemy func.jsonb_array_elements_text usage?  I also tried to call func.lower(func.jsonb_array_elements_text(..)) to make 'value' column values as lower case, but it did not seem to work.

[Mike Bayer]

that style of PG function use isn't directly supported yet and there is
a recipe to achieve this at
https://bitbucket.org/zzzeek/sqlalchemy/issues/3566/figure-out-how-to-support-all-of-pgs#comment-22842678

[Sami Pietilä]

Ok. Thank you!