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 [email protected].
For more options, visit https://groups.google.com/d/optout.
