Re: json function question

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wednesday, February 24, 2016, Andrew Dunstan <andrew@dunslane.net> wrote:
>> Having json(b)_populate_record recursively process nested complex objects
>> would be a large undertaking. One thing to consider is that json arrays are
>> quite different from Postgres arrays: they are essentially one-dimensional
>> heterogenous lists, not multi-dimensional homogeneous matrices. So while a
>> Postgres array that's been converted to a json array should in principle be
>> convertible back, an arbitrary json array could easily not be.

> An arbitrary json array should be one-dimensional and homogeneous - seems
> like that should be easy to import.  The true concern is that not all
> PostgreSQL arrays are capable of being represented in json.

I think we can put it on the user's head that the target Postgres array
type specified in json(b)_populate_record's arguments must be capable of
absorbing all elements of the matching JSON array.

Andrew raises a larger point: if the goal is that
json_populate_record(row_to_json()) be an identity with "deep" conversion
of container types, that puts constraints on row_to_json's behavior, which
we could not change without creating backwards-compatibility issues.
However, it looks to me like row_to_json already does pretty much the
right thing with nested array/record types:

regression=# select row_to_json(row(1,array[2,3],'(0,1)'::int8_tbl,array[(1,2),(3,4)]::int8_tbl[]));
                                   row_to_json
---------------------------------------------------------------------------------
 {"f1":1,"f2":[2,3],"f3":{"q1":0,"q2":1},"f4":[{"q1":1,"q2":2},{"q1":3,"q2":4}]}
(1 row)

So the complaint here is that json_populate_record fails to be an inverse
of row_to_json.

I'm not sure about Andrew's estimate that it'd be a large amount of work
to fix this.  It would definitely require some restructuring of the code
to make populate_record_worker (or some portion thereof) recursive, and
probably some entirely new code for array conversion; and making
json_populate_recordset behave similarly might take refactoring too.

            regards, tom lane