Re: Context lenses to set/get values in json values. - Mailing list pgsql-hackers

<div dir="ltr"><br /><div class="gmail_extra"><br /><div class="gmail_quote">On Wed, Oct 8, 2014 at 4:25 PM, Andrew
Dunstan<span dir="ltr"><<a href="mailto:andrew@dunslane.net" target="_blank">andrew@dunslane.net</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span
class=""><br/> On 10/08/2014 04:38 AM, Paweł Cesar Sanjuan Szklarz wrote:<br /></span><blockquote class="gmail_quote"
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class=""> Hello.<br /><br /> I am interested
inthe json type on postgresql. I would like to implement additional operations on the json structure that may
extract/inserttable like information from the json tree structure.<br /> I have a implementation on javascript that
showsthis type of operations. You can see examples in this page<br /><a href="https://github.com/paweld2/eelnss/wiki"
target="_blank">https://github.com/paweld2/<u></u>eelnss/wiki</a><br/><br /> 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
singlejson value, for example:<br /></span> select * from json_populate_records_with_<u></u>clen(null::myrowtype_users,
'app.users.{:uID}.(email,<ahref="http://data.name" target="_blank">data.<u></u>name</a> <<a href="http://data.name"
target="_blank">http://data.name</a>>,isActive)','... nested json value ...')<span class=""><br /><br /> may
return<br/> uID  |  email                          | name              | isActive<br />
------------------------------<u></u>------------------------------<u></u>--------------<br/></span> "u1" | "<a
href="mailto:admin@pmsoft.eu"target="_blank">admin@pmsoft.eu</a> <mailto:<a href="mailto:admin@pmsoft.eu"
target="_blank">admin@pmsoft.eu</a>>"   | "administrator" | true<br /> "u2" | "<a href="mailto:normal@pmsoft.eu"
target="_blank">normal@pmsoft.eu</a><mailto:<a href="mailto:normal@pmsoft.eu"
target="_blank">normal@pmsoft.eu</a>>"  | "user"               | true<br /> "u3" | "<a
href="mailto:testUser@pmsoft.eu"target="_blank">testUser@pmsoft.eu</a> <mailto:<a href="mailto:testUser@pmsoft.eu"
target="_blank">testUser@pmsoft.eu</a>>"| "testUser"        | false<span class=""><br /><br /><br /> Also, assuming
thatwe have a table User as above (uID, email, name, isActive), with context lenses it is very simple to map the table
toa json object. I assume that a similar api to table_to_xml,query_to_xml may be provided:<br /><br /></span>
table_to_json(Person, 'app.users.{:uID}.(email,<a href="http://data.name" target="_blank">data.<u></u>name</a> <<a
href="http://data.name"target="_blank">http://data.name</a>>,isActive)')<u></u>;<br /> query_to_json( 'select * from
Personwhere ... ', 'app.users.{:uID}.(email,<a href="http://data.name" target="_blank">data.<u></u>name</a> <<a
href="http://data.name"target="_blank">http://data.name</a>>,isActive)')<u></u>;<span class=""><br /><br /><br /> I
don'tknow the details about the integration of functions/operators to sql queries, but because context lenses maps
betweentables and tree objects, it may be possible to use a column json value as a separate table in the queries.
Assumethe table<br /> create table Person {<br />   pID  Integer<br />   address Json<br /> }<br /> then it  may be
possibleto query:<br /></span> select * from Person as P left join ( select * from
json_populate_records_with_<u></u>clen(null::addressType,'addres.(street.number, street.local,city.code,<a
href="http://city.name"target="_blank">city.<u></u>name</a> <<a href="http://city.name"
target="_blank">http://city.name</a>>)',P.address);<span class=""><br /><br /> A final api for such functions needs
tobe defined. If such functions may be usefull, I can try to prepare a implementation in postgres base code.<br /><br
/><br/></span></blockquote><br /><br /> I don't think we need to import Mongo type notation here. But there is probably
agood case for some functions like:<br /><br />    json_table_agg(anyrecord) -> json<br /><br /> which would work
likejson_agg() but would return an array of arrays instead of an array of objects. The caller would be assumed to know
whichfield is which in the array. That should take care of both the table_to_json and query_to_json suggestions
above.<br/><br /> In the other direction, we could have something like:<br /><br />    
json_populate_recordset_from_<u></u>table(baseanyrecord, fields text[], jsontable json) -> setof record<br /><br />
wherejsontable is an array of arrays of values  and fields is a corresponding array of field names.<br /><br /> I'm not
surehow mainstream any of this is. Maybe an extension would be more appropriate?<br /><br /> cheers<span
class="HOEnZb"><fontcolor="#888888"><br /><br /> andrew<br /><br /></font></span></blockquote></div><br /></div><div
class="gmail_extra">Hello.</div><divclass="gmail_extra"><br /></div><div class="gmail_extra">My personal interest is to
sendupdates to a single json value in the server. Which is the best way to make a update to a json value in postgres
withouta full update of the already stored value??  the -> operator extract a internal value, but to update the
valueI don't see any operator.</div><div class="gmail_extra"><br /></div><div class="gmail_extra">I was not familiar
withthe extensions, but it looks like the best way to start is to create a extension with possible implementations of
newfunctions. I will do so.</div><div class="gmail_extra"><br /></div><div class="gmail_extra">In my project I
consideredto use mongo, but in my case the core part of the model match perfectly a relational schema. I have some leaf
conceptsthat will change frequently, and to avoid migrations I store that information in a json value. To make changes
insuch leaf values I would like to have a "context lenses like api" in the server. I will start with some toy extension
andtry to feel if this make sense.</div><div class="gmail_extra"><br /></div><div
class="gmail_extra">Regards.</div><divclass="gmail_extra">Pawel.</div></div>