Re: json accessors - Mailing list pgsql-hackers

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@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
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