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

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