Re: [web2py] Re: query and json-field

2018-06-02 Thread Martin Weissenboeck
Last (?!) correction: Replace WHERE extra::text!='{}'::text and (extra->>'umfrage_text_id')::INTEGER != 4 by WHERE CASE WHEN extra::text!='{}'::text THEN (extra->>'umfrage_text_id')::INTEGER = 4 ELSE false END because SQL does not specify the order of evaluation and therefore does not provid

Re: [web2py] Re: query and json-field

2018-06-02 Thread Martin Weissenboeck
Hi Anthony! That is interesting. I have tested the example with coalesce and it worked in the SELECT clause. But I did not find any way to bulid an appropriate WHEN WHEN (extra->>'umfrage_text_id') != '4' => Error: cannot extract element from a scalar WHEN (extra->>'umfrage_text_id') IS NOT N

Re: [web2py] Re: query and json-field

2018-06-01 Thread Anthony
Maybe something like this: http://clarkdave.net/2015/03/navigating-null-safety-with-json-and-postgresql/ Anthony On Friday, June 1, 2018 at 8:44:21 AM UTC-4, mweissen wrote: > > Hi Anthony! > > Of course my exaple is very simplified and - sorry - I did not test it. > Now I have found the soluti

Re: [web2py] Re: query and json-field

2018-06-01 Thread Martin Weissenboeck
Hi Anthony! Of course my exaple is very simplified and - sorry - I did not test it. Now I have found the solution I have written: query1 = db.sendeprotokoll.typ=="UMF" query1 &= *db*("extra->>'umfragetextid'='4'") r1 = db(query1)._select("sendeprotokoll.typ", "sendeprotokoll.extra")