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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
