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
--------------------------------------------------------------------------
"u1" | "ad...@pmsoft.eu" | "administrator" | true
"u2" | "nor...@pmsoft.eu" | "user" | true
"u3" | "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,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.
