Re: json function question

On Tue, Feb 23, 2016 at 1: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.
>
> 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 hope so. When we debated these interfaces the current behavior
accepted on the principle that nested structures could be deserialized
at some point in the future. I think the endgame here is to be able
to do, foo::json[b]::foo for just about any postgres type.

merlin