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

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