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.
