On Wed, Oct 8, 2014 at 4:25 PM, Andrew Dunstan <and...@dunslane.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" | "ad...@pmsoft.eu <mailto:ad...@pmsoft.eu>" | "administrator" | >> true >> "u2" | "nor...@pmsoft.eu <mailto:nor...@pmsoft.eu>" | "user" >> | true >> "u3" | "testu...@pmsoft.eu <mailto:testu...@pmsoft.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.
