Re: Proposal: json_populate_record and nested json objects

On Sat, Sep 14, 2013 at 9:27 PM, chris travers <chris@2ndquadrant.com> 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?

Huge +1 on on this.  Couple random thoughts:

*) Hard to see how you would structure this as an extension as you're
adjusting the behaviors of existing functions, unless you wanted to
introduce new function names for testing purposes?

*) Would like to at least consider being able to use casting syntax as
a replacement for "populate_record" and (the missing) "populate_array"
for most usages.

merlin