Thank you Anthony for your reply! In fact, I don't store data in json, I use it in views only. I mean something like:
SELECT person.id, person.name, row_to_json(alias.*) as person_alias FROM person, alias WHERE person.id = alias.person_id I've found yet another solution - I can create a dummy view just for query using alias field: SELECT person.id, person.name, alias.id, alias.alias FROM person, alias WHERE person.id = alias.person_id and make the trick: ids_select_query = db(db.dummy_vw.alias == v &/| any_extra_query )._select( db.dummy_vw.person.id) # note _select() - just SQL-string, no DB-processing rows = db(db.person_vw.id.belongs(ids_select_query)).select() On Sunday, February 28, 2016 at 4:53:06 PM UTC+3, Anthony wrote: > > Most databases do not have native JSON types with the ability to query > within the JSON columns, so the DAL does not include that functionality -- > it just stores and retrieves the JSON. > > You should be able to construct the appropriate Postgres WHERE clause > manually and submit that as the query: > > db('JSON where clause').select(db.table.ALL) > > Note, do not include "WHERE" in the query -- the DAL will add that. Also, > because this query is opaque to the DAL, you must specify fields to select > in the .select() call so the DAL knows what table to query. > > Anthony > > On Sunday, February 28, 2016 at 7:56:21 AM UTC-5, Val K wrote: >> >> Hi! >> Is there any way to produce query using JSON field? >> It seems that web2py treats args of that query as string: >> db(db.tabl.json_fld==[1])._select() - "... WHERE tabl.json_fld=='[1]'" >> db(db.tabl.json_fld[0]==1)._select() - "... WHERE >> (SUBSTR(tabl.json_fld,1,(2 - 1)) = '1')" >> >> I use PostgreSQL and I have to force Postgres to hold JSON as plane text? >> >> >> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.