Re: Best way to populate nested composite type from JSON`

On Fri, Sep 13, 2013 at 6:37 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

>
>
> It would certainly be better if we could straight up deserialize json
> into a nested structure. For now, my advise is to try and structure
> your json and the receiving types/tables to not be nested. Using your
> example, I was able to do that by breaking the object in the json and
> eploiting hte 'best effort' json->object mapping (if you wanted to
> support receiving more than one entry at a time, you would need to
> include relation information to hook journal_entry to trans_lines.
>

> First, let's state the problem:
> CREATE TYPE trans_line AS (
> description text,
> amount numeric,
> account_id int
> );
>
> CREATE TYPE journal_entry AS(
> reference text,
> description text,
> post_date date,
> lines trans_line[]
> );
>
> /* make some test data */
> postgres=# SELECT row('a', 'b', now(), array[row('c', 1.0, 2),
> row('d', 3.0, 4)]::trans_line[])::journal_entry;
> row
> --------------------------------------------------
> (a,b,2013-09-13,"{""(c,1.0,2)"",""(d,3.0,4)""}")
>
>
> /* uh oh */
> postgres=# select json_populate_record(null::journal_entry,
> row_to_json(row('a', 'b', now(), array[row('c', 1.0, 2), row('d', 3.0,
> 4)]::trans_line[])::journal_entry));
> ERROR: cannot call json_populate_record on a nested object
>
> /* json -> record mapping is 'best effort' */
> postgres=# select json_populate_record(null::journal_entry, '{}');
> json_populate_record
> ----------------------
> (,,,)
>

Right. My first thinking was to use json_each and hstore to do an initial
filter and processing of the json object. In essence I should be able to
take a json object, break it apart into pieces, filter, reassemble as
hstore, and then cast to json. The result is that the nested portions
could be filtered out and processed separately. The casting function could
then have some knowledge of which elements might need to be nested and how
deeply.

>
> /* so, push the entry and the lines into sub-elements of a wrapping
> object, de-serialize separately and map back together. */
>
>
> WITH in_json AS
> (
> SELECT row_to_json(q) AS data
> FROM
> (
> SELECT
> q::journal_entry AS journal_entries,
> ARRAY[ROW('c', 1.0, 2), ROW('d', 3.0, 4)]::trans_line[] AS
> trans_lines
> FROM
> (
> SELECT
> 'a',
> 'b',
> now(),
> null::trans_line[]
> ) q
> ) q
> ),
> je AS
> (
> SELECT
> q.*
> FROM in_json
> CROSS JOIN LATERAL
> json_populate_record(null::journal_entry, data->'journal_entries') q
> ),
> tl AS
> (
> SELECT
> q.*
> FROM in_json
> CROSS JOIN LATERAL
> json_populate_recordset(null::trans_line, data->'trans_lines') q
> )
> SELECT je.*, ARRAY(SELECT tl FROM tl) AS lines2 FROM je;
>
> reference | description | post_date | lines | lines2
> -----------+-------------+------------+-------+---------------------------
> a | b | 2013-09-13 | | {"(c,1.0,2)","(d,3.0,4)"}
>
> that's somewhat tedious, but not too bad I think (the query above
> looks longer than it would be in practice since the json construction
> would presumably be on the client). But the basic M.O. is to send
> lists of records back to the server and relate them on the fly.
>

Ok, but what you are looking at there is structuring your JSON such that
every branch is equally nested. What I am trying to do is have composite
types which can be detected and used by code generators to generate
client-side data objects. Having a requirement like this is a bit more
complex and makes the data structures a bit less natural so I guess the
next question is how to try to simply isolate and override these in the
JSON itself. The simple solution might be to use json_each() I suppose.

> merlin
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml