Re: json accessors - Mailing list pgsql-hackers

On 11/29/2012 06:34 PM, Merlin Moncure wrote:
> On Thu, Nov 29, 2012 at 4:14 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>> There are many things wrong with this. First, converting to hstore so you
>> can call populate_record is quite horrible and ugly and inefficient. And
>> it's dependent on having hstore loaded - you can't have an hstore_to_jon in
>> core because hstore itself isn't in core. If you want a populate_record that
>> takes data from json we should have one coded direct. I'm happy to add it to
>> the list as long as everyone understands the limitations. Given a function
>> to unnest the json array, which I already suggested upthread, you could do
>> what you suggested above much more elegantly and directly.
> I wasn't suggesting you added the hstore stuff and I understand
> perfectly well the awkwardness of the hstore route.  That said, this
> is how people are going to use your api so it doesn't hurt to go
> through the motions; I'm just feeling out how code in the wild would
> shape up.
>
> Anyways, my example was busted since you'd need an extra step to move
> the set returning output from the json array unnest() into a
> 'populate_record' type function call.
>
> So, AIUI I think you're proposing (i'm assuming optional quotes)
> following my example above:
>
> INSERT INTO foo(a,b)
> SELECT
>    json_get_as_text(v, 'a')::int,
>    json_get_as_text(v, 'b')::int
> FROM
>    json_each(<document>) v;  /* gives you array of json (a,b) records  */
>
> a hypothetical 'json_to_record (cribbing usage from populate_record)'
> variant might look like (please note, I'm not saying 'write this now',
> just feeling it out)::
>
> INSERT INTO foo(a,b)
> SELECT  r.*
> FROM
>    json_each(<document>) v,
> LATERAL
>    json_to_record(null::foo, v) r;
>
> you're right: that's pretty clean.
>
> An json_object_each(json), => key, value couldn't hurt either -- this
> would handle those oddball cases of really wide objects that you
> occasionally see in json.  Plus as_text variants of both each and
> object_each.  If you're buying json_object_each, I think you can scrap
> json_object_keys().
>


OK, so based on this discussion, I'm thinking of the following:
 * keep the original functions and operators. json_keys is still   required for the case where the json is not flat. *
json_each(json)=> setof (text, text)   errors if the json is not a flat object * json_unnest(json) => setof json
errorsif the json is not an array * json_unnest_each => setof (int, text, text)   errors if the array is not an array
offlat objects * populate_record(record, json) => record   errors if the json isn't a flat object *
populate_recordset(record,json) => setof record   errors if the json is not an array of flat objects
 

Note that I've added a couple of things to deal with json that 
represents a recordset (i.e. an array of objects). This is a very common 
pattern and one well worth optimizing for.

I think that would let you do a lot of what you want pretty cleanly.

cheers

andrew