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