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 provide a shortcut on evaluation of the and operator. Martin 2018-06-02 10:10 GMT+02:00 Martin Weissenboeck <mweis...@gmail.com>: > 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 NULL => Error > > WHEN extra != NULL and ... ist not allowed - there is no comparison > operator for json > > > Therefore I filled every empty json field with '{}' > > This WHERE does the job: > > WHERE extra::text!='{}'::text and (extra->>'umfrage_text_id')::INTEGER != > 4 > > Thank you again for your hints.! > Martin > > > > 2018-06-01 16:47 GMT+02:00 Anthony <abasta...@gmail.com>: > >> Maybe something like this: http://clarkdave.net/201 >> 5/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 solution >>> >>> I have written: >>> >>> query1 = db.sendeprotokoll.typ=="UMF" >>> query1 &= *db*("extra->>'umfragetextid'='4'") >>> r1 = db(query1)._select("sendeprotokoll.typ", >>> "sendeprotokoll.extra") >>> >>> But it should be: >>> >>> query2 = db.sendeprotokoll.typ=="UMF" >>> query2 &= "extra->>'umfragetextid'='4'" >>> r2 = db(query2)._select("sendeprotokoll.typ", >>> "sendeprotokoll.extra") >>> >>> >>> query1 : >>> <Query (("sendeprotokoll"."typ" = 'UMF') AND <Set >>> extra->>'umfragetextid'='4'>)> >>> query2 : >>> <Query (("sendeprotokoll"."typ" = 'UMF') AND >>> extra->>'umfragetextid'='4')> >>> r1 : >>> SELECT "sendeprotokoll"."typ", "sendeprotokoll"."extra" FROM >>> "sendeprotokoll" WHERE ((("sendeprotokoll"."typ" = 'UMF') AND <Set >>> extra->>'umfragetextid'='4'>)); >>> r2 : >>> SELECT "sendeprotokoll"."typ", "sendeprotokoll"."extra" FROM >>> "sendeprotokoll" WHERE ((("sendeprotokoll"."typ" = 'UMF') AND >>> extra->>'umfragetextid'='4')); >>> >>> Ok, problem solved, thank you! >>> >>> ------------------------------------------------- >>> >>> But there is another question. >>> I want to use the json-query alone: >>> >>> query3 = "extra->>'umfragetextid'='4'" >>> r3 = db(query3)._select("sendeprotokoll.typ", "sendeprotokoll.extra") >>> >>> r3: SELECT "sendeprotokoll"."typ", "sendeprotokoll"."extra" FROM >>> "sendeprotokoll" WHERE extra->>'umfragetextid'='4'; >>> >>> Looks good? >>> >>> But with >>> r3 = db(query3).select("sendeprotokoll.typ", "sendeprotokoll.extra") >>> I get a new error message and it comes from >>> *dal/pydal/adapters/base.py* >>> >>> Ticket ID >>> >>> 195.230.45.151.2018-06-01.14-28-47.268ac848-705b-4300-ae59-bdb12d065fa4 >>> <class 'psycopg2.DataError'> cannot extract element from a scalarVersion >>> web2py™ Version 2.15.4-stable+timestamp.2017.09.02.04.02.22 >>> Python Python 2.7.12: /usr/local/bin/uwsgi (prefix: /usr/local) >>> >>> Function argument list >>> >>> (self=<pydal.adapters.postgres.PostgrePsyco object>, *args=('SELECT >>> "sendeprotokoll"."typ", "sendeprotokoll"....endeprotokoll" WHERE >>> extra->>\'umfragetextid\'=\'4\';',), **kwargs={}) >>> Code listing >>> >>> 407. >>> 408. >>> 409. >>> 410. >>> 411. >>> 412. >>> >>> 413. >>> 414. >>> 415. >>> 416. >>> >>> def execute(self, *args, **kwargs): >>> command = self.filter_sql_command(args[0]) >>> handlers = self._build_handlers_for_execution() >>> for handler in handlers: >>> handler.before_execute(command) >>> rv = self.cursor.execute(command, *args[1:], **kwargs) >>> >>> for handler in handlers: >>> handler.after_execute(command) >>> return rv >>> >>> Variables >>> rv undefined >>> self <pydal.adapters.postgres.PostgrePsyco object> >>> args ('SELECT "sendeprotokoll"."typ", "sendeprotokoll"....endeprotokoll" >>> WHERE extra->>\'umfragetextid\'=\'4\';',) >>> self.cursor <cursor object at 0x7f25d425d050; closed: 0> >>> command 'SELECT "sendeprotokoll"."typ", "sendeprotokoll"....endeprotokoll" >>> WHERE extra->>\'umfragetextid\'=\'4\';' >>> self.cursor.execute <built-in method execute of >>> psycopg2._psycopg.cursor object> >>> kwargs {} >>> >>> What is wrong? >>> >>> >>> >>> >>> >>> 2018-05-31 23:14 GMT+02:00 Anthony: >>> >>>> "<set..." is the string representation of a DAL Set object. >>>> >>>> Can you show your exact code? Here's what I get: >>>> >>>> >>> db('j->>"x" = 1')._select(db.j.id) >>>> 'SELECT "j"."id" FROM "j" WHERE j->>"x" = 1;' >>>> >>>> Anthony >>>> >>>> On Thursday, May 31, 2018 at 5:01:08 PM UTC-4, mweissen wrote: >>>>> >>>>> Let's say I have a table like >>>>> db.define_table("mytable", Field("j", "json")) >>>>> with some data like db.mytable.insert(j={"x":1}) >>>>> >>>>> Now I want to find all records with x==1. >>>>> Of course >>>>> db(db.mytable.j["x"]==1) >>>>> does not work (but it would be fine). >>>>> >>>>> I have tried some queries like >>>>> db.mytable.j.like("%1%") >>>>> db('(j-->x)=1') >>>>> >>>>> but nothing did work. web2py creates always a sql-statement with >>>>> .... WHERE <Set(j-->X)=1>... >>>>> >>>>> Postgresql does not understand <set - there is an error message >>>>> syntax error at or near "<" Where does this "<set..>" come from? >>>>> Any ideas? >>>>> >>>>> Regards, Martin >>>>> >>>>> -- >>>> 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. >>>> >>> >>> >>> >>> -- >> 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. >> > > > -- 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.