Re: json accessors

On 11/29/2012 02:07 AM, Hannu Krosing wrote:
> On 11/29/2012 01:10 AM, Merlin Moncure wrote:
>> On Wed, Nov 28, 2012 at 2:44 PM, Andrew Dunstan <andrew@dunslane.net> 
>> wrote:
> ...
>>
>>>> *) have you considered something like
>>>> anyelement from_json(anyelement, json)
>>>> or
>>>> select <json>::some_type;  (this may or many not be possible given our
>>>> casting mechanics; i don't know).
>>> I have no idea what the semantics of this would be.
>> Yeah, there's a lot of nuance there.
> One way to tackle it would give the argument element as a template
> and the result will the same template filled in from json filled
>
> create table tab1(id serial primary key, ts timestamp default now(), 
> data text);
>
> insert into tab1 select from_json(row(null,null,null)::tab1, 
> '{"data":"the data"}');
> insert into tab1 select from_json(row(null,null,null)::tab1, 
> '{"id":-1, "ts":null, "data":""}');
> insert into tab1 select from_json(t.*,'{"data":"more data"}') from 
> tab1 t where id = -1;
>
> hannu=# select row_to_json(t.*) from tab1 t;
>                           row_to_json
> ---------------------------------------------------------------
>  {"id":1,"ts":"2012-11-29 02:01:48.379172","data":"the data"}
>  {"id":-1,"ts":null, "data":""}
>  {"id":2,"ts":"2012-11-29 02:02:34.600164","data":"more data"}
> (3 rows)
>
> if extracting the defaults from table def proves too tricky for first 
> iteration, then
> just set the missing fields to NULL or even better, carry over the 
> values from template;
You could even do a template-less row_from_json which returns a records 
with all fields converted to
the JSON-encodable types and hope that the next conversions will be done 
by postgreSQL  as needed.

insert into tab1 select row_from_json('{"id":100, "ts":"2012-12-21", 
"data":"End of Everything"}');

insert into tab1
select * from row_from_json(
'[{"id":101, "ts":"2012-12-22", "data":"1st day after End of Everything"}  {"id":102, "ts":"2012-12-22", "data":"2nd
dayafter End of Everything"}
 
]');

Hannu
>
> ------------------------------
> Hannu
>
> PS: good work so far :)
>
> Hannu
>
>