Re: json function question

On Tue, Feb 23, 2016 at 12:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Dan S <strd911(at)gmail(dot)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.
>

​Equivalent in the "ability to round-trip" sense. Since hstore doesn't
have nested containers internal serialization of a record to hstore is
forced to "stringify" the array which can then be fed back in as-is. But
the [row_]to_json​

​logic converts the PostgreSQL arrays to JSON arrays and then we fail to
handle them on the return portion of the trip.

Arrays are likely to be a much for common scenario but I agree that dealing
with arbitrary depths and objects would make the feature complete.

And yes, back-patching should only occur (and ideally behavior changing)
for situations that today raise errors - as the example does.

​David J.
​
​