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?
Best Wishes,
Chris Travers
http://www.2ndquadrant.com
PostgreSQL Services, Training, and Support
