Re: inconsistent behaviour of json_to_record and friends withembedded json

> On 30. May 2019, at 16:24, 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…

By now, I’m inclined to believe that this part is a bug in Postgres >= 10.

Compare:

# select ('{"a":"{\"b\":1}"}'::json)->'a' as a;
      a
-------------
 "{\"b\":1}"

# select * from jsonb_to_record('{"a":"{\"b\":1}"}') as (a json);
      a
-------------
 "{\"b\":1}"


# select * from json_to_record('{"a":"{\"b\":1}"}') as (a json);
ERROR:  invalid input syntax for type json
DETAIL:  Token "b" is invalid.
CONTEXT:  JSON data, line 1: "{"b...

# select ('{"a":"{"b":1}"}'::json)->'a' as a;
ERROR:  invalid input syntax for type json
LINE 1: select ('{"a":"{"b":1}"}'::json)->'a' as a;
                ^
DETAIL:  Token "b" is invalid.
CONTEXT:  JSON data, line 1: {"a":"{"b…

It seems that json_to_record is messing up the escaping of quotes, and that
json_to_record should behave like jsonb_to_record here (interpreting the
quoted field as a JSON string, and not parsing it as a JSON object as it did
in version 9.6). Thoughts?