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.
