Re: json accessors

On 11/29/2012 06:34 PM, Merlin Moncure wrote:
> On Thu, Nov 29, 2012 at 4:14 PM, Andrew Dunstan <andrew(at)dunslane(dot)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
errors if the json is not an array
* json_unnest_each => setof (int, text, text)
errors if the array is not an array of flat 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