Re: Proposal: json_populate_record and nested json objects

On Sat, Sep 14, 2013 at 9:27 PM, chris travers <chris(at)2ndquadrant(dot)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