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

On Fri, Sep 13, 2013 at 9:30 AM, Chris Travers <chris(dot)travers(at)gmail(dot)com> wrote:
>
>
>
> 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.

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