Re: json accessors

On 11/30/2012 03:38 PM, Andrew Dunstan wrote:
>
> 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
Why not json_each(json) => setof (text, json) ? with no erroring out ?

if the json does represent text it is easy to convert to text on the
query side.

> * 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
json_unnest_each => setof (int, text, json)
> * populate_record(record, json) => record
> errors if the json isn't a flat object
errors if the values are not castable to records field types

nb! some nonflatness is castable. especially to json or hstore or record
types

> * populate_recordset(record, json) => setof record
> errors if the json is not an array of flat objects
ditto
> 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