Re: inconsistent behaviour of json_to_record and friends with embedded json

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Thu, May 30, 2019 at 10:41 AM Robert Vollmert <rob(at)vllmrt(dot)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(at)dunslane(dot)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