Skip to content

Commit 1c90345

Browse files
tglsfdctglsfdc
committed
Fix conversion of JSON strings to JSON output columns in json_to_record().
json_to_record(), when an output column is declared as type json or jsonb, should emit the corresponding field of the input JSON object. But it got this slightly wrong when the field is just a string literal: it failed to escape the contents of the string. That typically resulted in syntax errors if the string contained any double quotes or backslashes. jsonb_to_record() handles such cases correctly, but I added corresponding test cases for it too, to prevent future backsliding. Improve the documentation, as it provided only a very hand-wavy description of the conversion rules used by these functions. Per bug report from Robert Vollmert. Back-patch to v10 where the error was introduced (by commit cf35346). Note that PG 9.4 - 9.6 also get this case wrong, but differently so: they feed the de-escaped contents of the string literal to json[b]_in. That behavior is less obviously wrong, so possibly it's being depended on in the field, so I won't risk trying to make the older branches behave like the newer ones. Discussion: https://postgr.es/m/D6921B37-BD8E-4664-8D5F-DB3525765DCD@vllmrt.net
1 parent c015560 commit 1c90345

File tree

6 files changed

+167
-43
lines changed

6 files changed

+167
-43
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 65 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -12006,29 +12006,72 @@ table2-mapping
1200612006
</note>
1200712007

1200812008
<note>
12009-
<para>
12010-
While the examples for the functions
12011-
<function>json_populate_record</function>,
12012-
<function>json_populate_recordset</function>,
12013-
<function>json_to_record</function> and
12014-
<function>json_to_recordset</function> use constants, the typical use
12015-
would be to reference a table in the <literal>FROM</literal> clause
12016-
and use one of its <type>json</type> or <type>jsonb</type> columns
12017-
as an argument to the function. Extracted key values can then be
12018-
referenced in other parts of the query, like <literal>WHERE</literal>
12019-
clauses and target lists. Extracting multiple values in this
12020-
way can improve performance over extracting them separately with
12021-
per-key operators.
12022-
</para>
12009+
<para>
12010+
The functions
12011+
<function>json[b]_populate_record</function>,
12012+
<function>json[b]_populate_recordset</function>,
12013+
<function>json[b]_to_record</function> and
12014+
<function>json[b]_to_recordset</function>
12015+
operate on a JSON object, or array of objects, and extract the values
12016+
associated with keys whose names match column names of the output row
12017+
type.
12018+
Object fields that do not correspond to any output column name are
12019+
ignored, and output columns that do not match any object field will be
12020+
filled with nulls.
12021+
To convert a JSON value to the SQL type of an output column, the
12022+
following rules are applied in sequence:
12023+
<itemizedlist spacing="compact">
12024+
<listitem>
12025+
<para>
12026+
A JSON null value is converted to a SQL null in all cases.
12027+
</para>
12028+
</listitem>
12029+
<listitem>
12030+
<para>
12031+
If the output column is of type <type>json</type>
12032+
or <type>jsonb</type>, the JSON value is just reproduced exactly.
12033+
</para>
12034+
</listitem>
12035+
<listitem>
12036+
<para>
12037+
If the output column is a composite (row) type, and the JSON value is
12038+
a JSON object, the fields of the object are converted to columns of
12039+
the output row type by recursive application of these rules.
12040+
</para>
12041+
</listitem>
12042+
<listitem>
12043+
<para>
12044+
Likewise, if the output column is an array type and the JSON value is
12045+
a JSON array, the elements of the JSON array are converted to elements
12046+
of the output array by recursive application of these rules.
12047+
</para>
12048+
</listitem>
12049+
<listitem>
12050+
<para>
12051+
Otherwise, if the JSON value is a string literal, the contents of the
12052+
string are fed to the input conversion function for the column's data
12053+
type.
12054+
</para>
12055+
</listitem>
12056+
<listitem>
12057+
<para>
12058+
Otherwise, the ordinary text representation of the JSON value is fed
12059+
to the input conversion function for the column's data type.
12060+
</para>
12061+
</listitem>
12062+
</itemizedlist>
12063+
</para>
1202312064

12024-
<para>
12025-
JSON keys are matched to identical column names in the target
12026-
row type. JSON type coercion for these functions is <quote>best
12027-
effort</quote> and may not result in desired values for some types.
12028-
JSON fields that do not appear in the target row type will be
12029-
omitted from the output, and target columns that do not match any
12030-
JSON field will simply be NULL.
12031-
</para>
12065+
<para>
12066+
While the examples for these functions use constants, the typical use
12067+
would be to reference a table in the <literal>FROM</literal> clause
12068+
and use one of its <type>json</type> or <type>jsonb</type> columns
12069+
as an argument to the function. Extracted key values can then be
12070+
referenced in other parts of the query, like <literal>WHERE</literal>
12071+
clauses and target lists. Extracting multiple values in this
12072+
way can improve performance over extracting them separately with
12073+
per-key operators.
12074+
</para>
1203212075
</note>
1203312076

1203412077
<note>

‎src/backend/utils/adt/jsonfuncs.c

Lines changed: 16 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -2803,34 +2803,29 @@ populate_scalar(ScalarIOData *io, Oid typid, int32 typmod, JsValue *jsv)
28032803

28042804
json = jsv->val.json.str;
28052805
Assert(json);
2806-
2807-
/* already done the hard work in the json case */
2808-
if ((typid == JSONOID || typid == JSONBOID) &&
2809-
jsv->val.json.type == JSON_TOKEN_STRING)
2810-
{
2811-
/*
2812-
* Add quotes around string value (should be already escaped) if
2813-
* converting to json/jsonb.
2814-
*/
2815-
2816-
if (len < 0)
2817-
len = strlen(json);
2818-
2819-
str = palloc(len + sizeof(char) * 3);
2820-
str[0] = '"';
2821-
memcpy(&str[1], json, len);
2822-
str[len + 1] = '"';
2823-
str[len + 2] = '\0';
2824-
}
2825-
else if (len >= 0)
2806+
if (len >= 0)
28262807
{
28272808
/* Need to copy non-null-terminated string */
28282809
str = palloc(len + 1 * sizeof(char));
28292810
memcpy(str, json, len);
28302811
str[len] = '\0';
28312812
}
28322813
else
2833-
str = json; /* null-terminated string */
2814+
str = json; /* string is already null-terminated */
2815+
2816+
/* If converting to json/jsonb, make string into valid JSON literal */
2817+
if ((typid == JSONOID || typid == JSONBOID) &&
2818+
jsv->val.json.type == JSON_TOKEN_STRING)
2819+
{
2820+
StringInfoData buf;
2821+
2822+
initStringInfo(&buf);
2823+
escape_json(&buf, str);
2824+
/* free temporary buffer */
2825+
if (str != json)
2826+
pfree(str);
2827+
str = buf.data;
2828+
}
28342829
}
28352830
else
28362831
{

‎src/test/regress/expected/json.out

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2276,6 +2276,42 @@ select * from json_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]);
22762276
{{{1},{2},{3}}}
22772277
(1 row)
22782278

2279+
select * from json_to_record('{"out": {"key": 1}}') as x(out json);
2280+
out
2281+
------------
2282+
{"key": 1}
2283+
(1 row)
2284+
2285+
select * from json_to_record('{"out": [{"key": 1}]}') as x(out json);
2286+
out
2287+
--------------
2288+
[{"key": 1}]
2289+
(1 row)
2290+
2291+
select * from json_to_record('{"out": "{\"key\": 1}"}') as x(out json);
2292+
out
2293+
----------------
2294+
"{\"key\": 1}"
2295+
(1 row)
2296+
2297+
select * from json_to_record('{"out": {"key": 1}}') as x(out jsonb);
2298+
out
2299+
------------
2300+
{"key": 1}
2301+
(1 row)
2302+
2303+
select * from json_to_record('{"out": [{"key": 1}]}') as x(out jsonb);
2304+
out
2305+
--------------
2306+
[{"key": 1}]
2307+
(1 row)
2308+
2309+
select * from json_to_record('{"out": "{\"key\": 1}"}') as x(out jsonb);
2310+
out
2311+
----------------
2312+
"{\"key\": 1}"
2313+
(1 row)
2314+
22792315
-- json_strip_nulls
22802316
select json_strip_nulls(null);
22812317
json_strip_nulls

‎src/test/regress/expected/jsonb.out

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2652,6 +2652,42 @@ select * from jsonb_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]);
26522652
{{{1},{2},{3}}}
26532653
(1 row)
26542654

2655+
select * from jsonb_to_record('{"out": {"key": 1}}') as x(out json);
2656+
out
2657+
------------
2658+
{"key": 1}
2659+
(1 row)
2660+
2661+
select * from jsonb_to_record('{"out": [{"key": 1}]}') as x(out json);
2662+
out
2663+
--------------
2664+
[{"key": 1}]
2665+
(1 row)
2666+
2667+
select * from jsonb_to_record('{"out": "{\"key\": 1}"}') as x(out json);
2668+
out
2669+
----------------
2670+
"{\"key\": 1}"
2671+
(1 row)
2672+
2673+
select * from jsonb_to_record('{"out": {"key": 1}}') as x(out jsonb);
2674+
out
2675+
------------
2676+
{"key": 1}
2677+
(1 row)
2678+
2679+
select * from jsonb_to_record('{"out": [{"key": 1}]}') as x(out jsonb);
2680+
out
2681+
--------------
2682+
[{"key": 1}]
2683+
(1 row)
2684+
2685+
select * from jsonb_to_record('{"out": "{\"key\": 1}"}') as x(out jsonb);
2686+
out
2687+
----------------
2688+
"{\"key\": 1}"
2689+
(1 row)
2690+
26552691
-- test type info caching in jsonb_populate_record()
26562692
CREATE TEMP TABLE jsbpoptest (js jsonb);
26572693
INSERT INTO jsbpoptest

‎src/test/regress/sql/json.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -742,6 +742,13 @@ select * from json_to_record('{"ia2": [1, 2, 3]}') as x(ia2 int[][]);
742742
select * from json_to_record('{"ia2": [[1, 2], [3, 4]]}') as x(ia2 int4[][]);
743743
select * from json_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]);
744744

745+
select * from json_to_record('{"out": {"key": 1}}') as x(out json);
746+
select * from json_to_record('{"out": [{"key": 1}]}') as x(out json);
747+
select * from json_to_record('{"out": "{\"key\": 1}"}') as x(out json);
748+
select * from json_to_record('{"out": {"key": 1}}') as x(out jsonb);
749+
select * from json_to_record('{"out": [{"key": 1}]}') as x(out jsonb);
750+
select * from json_to_record('{"out": "{\"key\": 1}"}') as x(out jsonb);
751+
745752
-- json_strip_nulls
746753

747754
select json_strip_nulls(null);

‎src/test/regress/sql/jsonb.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -709,6 +709,13 @@ select * from jsonb_to_record('{"ia2": [1, 2, 3]}') as x(ia2 int[][]);
709709
select * from jsonb_to_record('{"ia2": [[1, 2], [3, 4]]}') as x(ia2 int4[][]);
710710
select * from jsonb_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]);
711711

712+
select * from jsonb_to_record('{"out": {"key": 1}}') as x(out json);
713+
select * from jsonb_to_record('{"out": [{"key": 1}]}') as x(out json);
714+
select * from jsonb_to_record('{"out": "{\"key\": 1}"}') as x(out json);
715+
select * from jsonb_to_record('{"out": {"key": 1}}') as x(out jsonb);
716+
select * from jsonb_to_record('{"out": [{"key": 1}]}') as x(out jsonb);
717+
select * from jsonb_to_record('{"out": "{\"key\": 1}"}') as x(out jsonb);
718+
712719
-- test type info caching in jsonb_populate_record()
713720
CREATE TEMP TABLE jsbpoptest (js jsonb);
714721

0 commit comments

Comments
 (0)