Re: json function question

On Tue, Feb 23, 2016 at 12:12 PM, Dan S <strd911(at)gmail(dot)com> wrote:

> Hi !
>
> I'm running "PostgreSQL 9.5.1 on i686-pc-linux-gnu, compiled by
> gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit"
>
> I'm trying out json functions and stumbled on a problem with
> json_populate_record.
> To try out the function I decided to take records from table test convert
> them to json and immediately repopulate them back into records of type
> test, but it fails with this message:
>
> ERROR: malformed array literal: "["abc","def","fgh"]"
> DETAIL: "[" must introduce explicitly-specified array dimensions.
> ********** Error **********
>
> ERROR: malformed array literal: "["abc","def","fgh"]"
> SQL state: 22P02
> Detail: "[" must introduce explicitly-specified array dimensions.
>
> Is it a bug or how am I supposed to use the populate function ?
>

​Personally, I'd call it a bug - whether design or implementation doesn't
matter to me.

What json_populate_record seems to be looking for when faced with a
"text[]" typed field is something of the form:

{"key":"{\"abc\",\"def\",\"ghi\"}"}

IOW, this works:

select j, json_populate_record(null::test, j)
from
(
select
'{"id":1,"txt":"jkl","txt_arr":"{\"abc\",\"def\",\"fgh\"}","f":3.14159}'::json
AS j
) r

Namely a scalar literal that looks like a PostgreSQL array - as opposed to
an actual JSON array.

The literal text is sent through the input function for text[] and gets
parsed into a PostgreSQL text array.​

​Given that this presently errors I would suggest we fix this case so that
both forms are acceptable to the parser. In other words, try harder in our
effort to coerce between the two formats.

David J.