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

On Fri, Sep 13, 2013 at 9:30 AM, Chris Travers <chris.travers@gmail.com> wrote:
>
>
>
> On Fri, Sep 13, 2013 at 6:37 AM, Merlin Moncure <mmoncure@gmail.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.

Yup.  As things stand currently, it's better *not* to make
serialization-driving composite types which when learning the json
stuff I did heavily; it was a habit I learned (and had to unlearn)
from libpqtypes which solves a lot of the same problems for C clients.
 You can *almost* send json back to the database with the same
elegance as it can be sent out of the database.  I see a big future
for postgres-json with many roadmap improvements; it's a complete game
change for service call driven database interaction and forward
thinking developers should realize it essentially obsoletes ORMS.

merlin