From: | Paweł Cesar Sanjuan Szklarz <paweld2(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Context lenses to set/get values in json values. |
Date: | 2014-10-08 08:38:22 |
Message-ID: | CAGReoCQi_Eg1mGYq4D-PiVm1_-LXjmXHAe2dNwXVYYbDcLLU5A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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" | "admin(at)pmsoft(dot)eu" | "administrator" | true
"u2" | "normal(at)pmsoft(dot)eu" | "user" | true
"u3" | "testUser(at)pmsoft(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | furuyao | 2014-10-08 08:47:16 | Re: pg_receivexlog --status-interval add fsync feedback |
Previous Message | Marti Raudsepp | 2014-10-08 08:36:49 | Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} |