Search inside Dict

[ced (Cédric Krier)]

Rational

We have a Dict but it is not really possible to use them in a domain because it does string comparison.
It will be a good improvement to be able to make domain on the key value.

Both back-ends have function to manipulate JSON data: PostgreSQL JSON function and SQLite JSON1 extension.

For now, there is only Oracle which supports the SQL/JSON standard. So an had-hoc solution for the Tryton limited usage is preferable.

Proposal

I propose to extend the domain syntax to allow the ‘.’ notation (like for relation field) to access the value of a specific key. For example ('dict.key', '=', value) will search for record with the JSON object having key value equals to value.
This can be implemented in PostgreSQL with the operator dict->>key = value and in SQLite with json_extract(dict, '$.key') = value.

On the client side, the domain evaluation can be adapted to test the value of the key. The domain inversion can just ignore such case. And finally the domain parser could have a syntax for this but it is not mandatory (as it can be too expensive to retrieve all the key strings).

Implementation

https://bugs.tryton.org/issue7905

[edbo]

Whow, this is VERY nice! I’m using Tryton for storing data about sensors and systems. The “realtime” sensor data is stored on different intervals and to minimize extra rows, I’m storing the data of the different sensor into a Dict als “sensorname” -> “value”.
To get the values out of the database I had to write my own SQL-query, but this is becoming more Tryton like

<“offtopic”>
The only thing missing now is getting the date from a datetime stamp in the database. E.g. you have a datetime column called “storedate” with the value 2018-12-04 23:00:10. In PostgreSQL you can get only the date by calling storedate::date or only the time bij calling storedate::time. But I think this is something for the python-sql module.
<"/offtopic">