Re: Context lenses to set/get values in json values.

On Wed, Oct 8, 2014 at 4:25 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> On 10/08/2014 04:38 AM, Paweł Cesar Sanjuan Szklarz wrote:
>
>> Hello.
>>
>> I am interested in the json type on postgresql. I would like to implement
>> additional operations on the json structure that may extract/insert table
>> like information from the json tree structure.
>> I have a implementation on javascript that shows this type of operations.
>> You can see examples in this page
>> https://github.com/paweld2/eelnss/wiki
>>
>> Following the examples in the previous page, it may by possible to
>> implement a function similar to json_populate_record to extract multiple
>> records from a single json value, for example:
>> select * from json_populate_records_with_clen(null::myrowtype_users,
>> 'app.users.{:uID}.(email,data.name <http://data.name>,isActive)', '...
>> nested json value ...')
>>
>> may return
>> uID | email | name | isActive
>> ------------------------------------------------------------
>> --------------
>> "u1" | "admin(at)pmsoft(dot)eu <mailto:admin(at)pmsoft(dot)eu>" | "administrator" |
>> true
>> "u2" | "normal(at)pmsoft(dot)eu <mailto:normal(at)pmsoft(dot)eu>" | "user"
>> | true
>> "u3" | "testUser(at)pmsoft(dot)eu <mailto:testUser(at)pmsoft(dot)eu>" | "testUser"
>> | false
>>
>>
>> Also, assuming that we have a table User as above (uID, email, name,
>> isActive), with context lenses it is very simple to map the table to a json
>> object. I assume that a similar api to table_to_xml,query_to_xml may be
>> provided:
>>
>> table_to_json( Person, 'app.users.{:uID}.(email,data.name <
>> http://data.name>,isActive)');
>> query_to_json( 'select * from Person where ... ',
>> 'app.users.{:uID}.(email,data.name <http://data.name>,isActive)');
>>
>>
>> I don't know the details about the integration of functions/operators to
>> sql queries, but because context lenses maps between tables and tree
>> objects, it may be possible to use a column json value as a separate table
>> in the queries. Assume the table
>> create table Person {
>> pID Integer
>> address Json
>> }
>> then it may be possible to query:
>> select * from Person as P left join ( select * from
>> json_populate_records_with_clen(null::addressType,
>> 'addres.(street.number, street.local,city.code,city.name <
>> http://city.name>)', P.address);
>>
>> A final api for such functions needs to be defined. If such functions may
>> be usefull, I can try to prepare a implementation in postgres base code.
>>
>>
>>
>
> I don't think we need to import Mongo type notation here. But there is
> probably a good case for some functions like:
>
> json_table_agg(anyrecord) -> json
>
> which would work like json_agg() but would return an array of arrays
> instead of an array of objects. The caller would be assumed to know which
> field is which in the array. That should take care of both the
> table_to_json and query_to_json suggestions above.
>
> In the other direction, we could have something like:
>
> json_populate_recordset_from_table(base anyrecord, fields text[],
> jsontable json) -> setof record
>
> where jsontable is an array of arrays of values and fields is a
> corresponding array of field names.
>
> I'm not sure how mainstream any of this is. Maybe an extension would be
> more appropriate?
>
> cheers
>
> andrew
>
>
Hello.

My personal interest is to send updates to a single json value in the
server. Which is the best way to make a update to a json value in postgres
without a full update of the already stored value?? the -> operator
extract a internal value, but to update the value I don't see any operator.

I was not familiar with the extensions, but it looks like the best way to
start is to create a extension with possible implementations of new
functions. I will do so.

In my project I considered to use mongo, but in my case the core part of
the model match perfectly a relational schema. I have some leaf concepts
that will change frequently, and to avoid migrations I store that
information in a json value. To make changes in such leaf values I would
like to have a "context lenses like api" in the server. I will start with
some toy extension and try to feel if this make sense.

Regards.
Pawel.