Re: inconsistent behaviour of json_to_record and friends with embedded json

Jeff Janes <jeff.janes@gmail.com> writes:
> On Thu, May 30, 2019 at 10:41 AM Robert Vollmert <rob@vllmrt.net> wrote:
>> 1. select * from json_to_record('{"out": "{\"key\": 1}"}') as (out json);
>> Postgres 10/11:
>> 1. gives
>> ERROR:  invalid input syntax for type json
>> DETAIL:  Token "key" is invalid.
>> CONTEXT:  JSON data, line 1: "{"key...

> This is caused by commit:

> commit cf35346e813e5a1373f308d397bb0a8f3f21d530
> Author: Andrew Dunstan <andrew@dunslane.net>
> Date:   Thu Apr 6 22:11:21 2017 -0400
> 
>     Make json_populate_record and friends operate recursively

> As far as I can tell, this was not an intended change, so I agree it is
> probably a bug.

I agree.  It looks to me like the problem is this over-optimistic
assumption:

            /*
             * Add quotes around string value (should be already escaped) if
             * converting to json/jsonb.
             */

No, it's *not* already escaped.  Fixing the code to use escape_json()
is a bit tedious, because for some reason that function wasn't designed
to support non-null-terminated input, but with the attached patch we get
what seems to me like sane behavior:

regression=# select * from json_to_record('{"out": "{\"key\": 1}"}') as (out json);
      out       
----------------
 "{\"key\": 1}"
(1 row)

regression=# select * from json_to_record('{"out": {"key": 1}}') as (out json);
    out     
------------
 {"key": 1}
(1 row)

i.e. what you get is either a string or an object, the same as it was in
the input (same for all combinations of json/jsonb in/out).

Not terribly surprisingly, this patch changes no existing regression test
cases.  We should add some, but I've not done so here.

Also, modulo this bug for the json-input case, this *is* an intentional
behavioral change from 9.6, but it seems fairly poorly documented to
me.  Should we try to improve that?

			regards, tom lane