On 11/29/2012 06:34 PM, Merlin Moncure wrote:
On Thu, Nov 29, 2012 at 4:14 PM, Andrew Dunstan <and...@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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
