inconsistent behaviour of json_to_record and friends with embedded json

From: Robert Vollmert <rob(at)vllmrt(dot)net>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: inconsistent behaviour of json_to_record and friends with embedded json
Date: 2019-05-30 14:24:06
Message-ID: D6921B37-BD8E-4664-8D5F-DB3525765DCD@vllmrt.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

First off, I’m not sure what the bug is here; it might even be that there’s
just missing documentation. That said, I see internally inconsistent behaviour
in Postgres 10 and above, and a change of behaviour between 9.6 and 10 that’s
not really documented as intentional in the changelog.

In short, the differing statements are:

Postgres 9.6:

each of

1. select * from json_to_record('{"out": "{\"key\": 1}"}') as (out json);
2. select * from json_to_record('{"out": {"key": 1}}') as (out json);
3. select * from jsonb_to_record('{"out": "{\"key\": 1}"}') as (out json);
4. select * from jsonb_to_record('{"out": {"key": 1}}') as (out json);

gives

out
------------
{"key": 1}

Postgres 10/11:

1. gives

ERROR: invalid input syntax for type json
DETAIL: Token "key" is invalid.
CONTEXT: JSON data, line 1: "{“key…

2. and 4. give

out
------------
{"key": 1}

3. gives

out
----------------
"{\"key\": 1}”

I get similar results whith `… as (out jsonb)` or with json_populate_record in the form

create type j as (out json);
select out from json_populate_record(null::j, '{"out": "{\"key\": 1}"}’);

These tests were run on macos with postgresql versions 9.6, 10 and 11 installed
via nix, but I’ve confirmed that the issue also exists in other environments.

Apologies if this has been discussed before; the closest bug report I found is

BUG #10728: json_to_recordset with nested json objects NULLs columns
https://www.postgresql.org/message-id/flat/CAB7nPqTP1m5H%3DkNsm6mmv-5f7A99O7AP2X6E9ubb4ShZWq-COQ%40mail.gmail.com#1978f3e80110804859829f0f5cb9392e

The potentially buggy things:

1. Why do cases 1 and 3 behave differently in Postgres 10 and later? Should they?

2. Could the change in behaviour from 9.6 to 10 be documented more clearly if this part
is intentional? The release notes list:

> Make json_populate_record() and related functions process JSON arrays
> and objects recursively (Nikita Glukhov)

> With this change, array-type fields in the destination SQL type are
> properly converted from JSON arrays, and composite-type fields are
> properly converted from JSON objects. Previously, such cases would
> fail because the text representation of the JSON value would be fed
> to array_in() or record_in(), and its syntax would not match what
> those input functions expect.

It seems likely these changes are involved, but it’s not clear this concrete effect was
intended.

3. Could the documentation of the family of functions be extended to describe how
embedded quoted and unquoted json fields are treated?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-05-31 10:55:01 BUG #15826: BUG: Where-Clause referring to unknown column in CTE is ignored in Update-statement
Previous Message PG Bug reporting form 2019-05-30 02:02:35 BUG #15825: Setup does not complete