Re: Proposal: json_populate_record and nested json objects

On 09/14/2013 10:27 PM, chris travers wrote:
> Hi all;
> Currently json_populate_record and json_populate_recordset cannot work 
> with nested json objects.  This creates two fundamental problems when 
> trying to use JSON as an interface format.
> The first problem is you can't easily embed a json data type in an 
> json object and have it populate a record.  This means that storing 
> extended attributes in the database is somewhat problematic if you 
> accept the whole row in as a json object.
> The second problem is that nested data structures and json don't go 
> together well.  You can't have  a composite type which has as an 
> attribute an array of another composite type and populate this from a 
> json object.  This makes json largely an alternative to hstore for 
> interfaces in its current shape.
> I would propose handling the json_populate_record and friends as such:
> 1. Don't throw errors initially as a pre-check if the json object is 
> nested.
> 2. If one comes to a nested fragment, check the attribute type it is 
> going into first.
>     2.1 If it is a json type, put the nested fragment there.
>     2.2 If it is a composite type (i.e. anything in pg_class), push it 
> through another json_populate_record run
>     2.3 If it is neither, then see if a json::[type] cast exists, if 
> so call it.
>     2.4 Otherwise raise an exception
> I have a few questions before I go on to look at creating a patch.
> 1.  Are there any problems anyone spots with this approach?
> 2.  Is anyone working on something like this?
> 3.  Would it be preferable to build something like this first as an 
> extension (perhaps with different function names) or first as a patch?
>


Well, you could fairly easily build it as an extension as a POC. The 
main point of the API this is built on was to allow for extensions.

The logic changes might be a bit tricky. I'll be interested to see what 
you come up with.

If we're going to do this we should make these handle arrays as well as 
objects.

cheers

andrew