> 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?