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 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.
