Thread: Context lenses to set/get values in json values.
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,isActive)', '... nested json value ...')
may return
uID | email | name | isActive
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,isActive)', '... nested json value ...')
may return
uID | email | name | isActive
--------------------------------------------------------------------------
"u1" | "admin@pmsoft.eu" | "administrator" | true
"u2" | "normal@pmsoft.eu" | "user" | true
"u3" | "testUser@pmsoft.eu" | "testUser" | false
"u1" | "admin@pmsoft.eu" | "administrator" | true
"u2" | "normal@pmsoft.eu" | "user" | true
"u3" | "testUser@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,isActive)');
query_to_json( 'select * from Person where ... ', 'app.users.{:uID}.(email,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)', 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.
Regards.
Pawel Cesar Sanjuan Szklarz.
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@pmsoft.eu <mailto:admin@pmsoft.eu>" | "administrator" > | true > "u2" | "normal@pmsoft.eu <mailto:normal@pmsoft.eu>" | "user" > | true > "u3" | "testUser@pmsoft.eu <mailto:testUser@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
<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>
On 10/08/2014 12:13 PM, Paweł Cesar Sanjuan Szklarz wrote: > > > > 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? > > > > > 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. > > There is work already being done on providing update operations. cheers andrew
On 8 October 2014 18:39, Andrew Dunstan <andrew@dunslane.net> wrote: > > On 10/08/2014 12:13 PM, Paweł Cesar Sanjuan Szklarz wrote: >> >> >> >> >> 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? >> >> >> >> >> 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. >> >> > > There is work already being done on providing update operations. I've been looking out for that. Has there been a discussion on how that would look yet that you could point me to? -- Thom
On 10/08/2014 02:04 PM, Thom Brown wrote: >> There is work already being done on providing update operations. > I've been looking out for that. Has there been a discussion on how > that would look yet that you could point me to? > https://github.com/erthalion/jsonbx Note that a) it's an extension, and b) it's jsonb only. cheers andrew
On 8 October 2014 at 20:39, Andrew Dunstan <andrew@dunslane.net> wrote:
On 10/08/2014 02:04 PM, Thom Brown wrote:There is work already being done on providing update operations.I've been looking out for that. Has there been a discussion on how
that would look yet that you could point me to?
https://github.com/erthalion/jsonbx
Note that a) it's an extension, and b) it's jsonb only.
Is the intention to get these in-core, or to remain in an extension? These appear to be candidates as first class citizens.
Thom