Re: json accessors

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().

merlin