Re: Best way to populate nested composite type from JSON` - Mailing list pgsql-general

On Thu, Sep 12, 2013 at 7:25 PM, Chris Travers <chris.travers@gmail.com> wrote:
>
>
>
> On Thu, Sep 12, 2013 at 8:47 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>
>>
>>
>> Can we see a hypothetical example?  json best practices for me are to
>> use standard tables and than serialize/deserialize json as it goes
>> through the door and not use nested composite types in your actual
>> table definitions.  I think it should all fit if you do it right: you
>> json_[each/array_elements] the wrapping json then insert it into
>> un-nested actual tables.  If you must have a final destination as a
>> nested composite, I think at the worst case you might have to make
>> some transition composites...
>
>
> Here is what I am thinking (roughly):
>
> CREATE TYPE trans_line (
>      description text,
>      amount numeric,
>      account_id int
> );
>
> CREATE TYPE journal_entry (
>       reference text,
>       description text,
>       post_date date,
>       lines trans_line[]
> );
>
> CREATE FUNCTION save(journal_entry) RETURNS .... LANGUAGE PLPGSQL AS
> $$
> ....
> $$;
>
> What I would like to be able to do is accept that complex data type in as a
> JSON object and convert it to the record format.  The data could then be
> broken apart, inserted checked for validity (make sure the transaction is
> balanced etc) and then inserted into the correct tables.  My idea was to
> create a json::journal_entry cast and use that.  Otherwise I get to have fun
> with row and array constructors and system tables.
>
> However one can't pass nested JSON objects through json_populate_record().
> My question is basically how to get around that.

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
----------------------
 (,,,)

/* 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.

merlin