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/wikiFollowing 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
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.
