Re: json function question - Mailing list pgsql-general

Dan S <strd911@gmail.com> writes:
> I have this table, data and query:

> create table test
> (
>     id int,
>     txt text,
>     txt_arr text[],
>     f float
> );

> insert into test
> values
> (1,'jkl','{abc,def,fgh}',3.14159),(2,'hij','{abc,def,fgh}',3.14159),(2,null,null,null),(3,'def',null,0);

> select j, json_populate_record(null::test, j)
> from
> (
>     select to_json(t) as j from test t
> ) r;

> ERROR:  malformed array literal: "["abc","def","fgh"]"
> DETAIL:  "[" must introduce explicitly-specified array dimensions.

> Is it a bug or how am I supposed to use the populate function ?

AFAICS, json_populate_record has no intelligence about nested container
situations.  It'll basically just push the JSON text representation of any
field of the top-level object at the input converter for the corresponding
composite-type column.  That doesn't work if you're trying to convert a
JSON array to a Postgres array, and it wouldn't work for sub-object to
composite column either, because of syntax discrepancies.

Ideally this would work for arbitrarily-deeply-nested array+record
structures, but it looks like a less than trivial amount of work to make
that happen.

> If I try an equivalent example with hstore it works well.

hstore hasn't got any concept of substructure in its field values, so
it's hard to see how you'd create an "equivalent" situation.

One problem with fixing this is avoiding backwards-compatibility breakage,
but I think we could do that by saying that we only change behavior when
(a) json sub-value is an array and target Postgres type is an array type,
or (b) json sub-value is an object and target Postgres type is a composite
type.  In both cases, current code would fail outright, so there's no
existing use-cases to protect.  For other target Postgres types, we'd
continue to do it as today, so for example conversion to a JSON column
type would continue to work as it does now.

I'm not sure if anything besides json[b]_populate_record needs to change
similarly, but we ought to look at all those conversion functions with
the thought of nested containers in mind.

            regards, tom lane

PS: I'm not volunteering to do the work here, but it seems like a good
change to make.