Skip to content

Commit b6f5689

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 52ad5fc commit b6f5689

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
@@ -11864,29 +11864,72 @@ table2-mapping
1186411864
</note>
1186511865

1186611866
<note>
11867-
<para>
11868-
While the examples for the functions
11869-
<function>json_populate_record</function>,
11870-
<function>json_populate_recordset</function>,
11871-
<function>json_to_record</function> and
11872-
<function>json_to_recordset</function> use constants, the typical use
11873-
would be to reference a table in the <literal>FROM</literal> clause
11874-
and use one of its <type>json</type> or <type>jsonb</type> columns
11875-
as an argument to the function. Extracted key values can then be
11876-
referenced in other parts of the query, like <literal>WHERE</literal>
11877-
clauses and target lists. Extracting multiple values in this
11878-
way can improve performance over extracting them separately with
11879-
per-key operators.
11880-
</para>
11867+
<para>
11868+
The functions
11869+
<function>json[b]_populate_record</function>,
11870+
<function>json[b]_populate_recordset</function>,
11871+
<function>json[b]_to_record</function> and
11872+
<function>json[b]_to_recordset</function>
11873+
operate on a JSON object, or array of objects, and extract the values
11874+
associated with keys whose names match column names of the output row
11875+
type.
11876+
Object fields that do not correspond to any output column name are
11877+
ignored, and output columns that do not match any object field will be
11878+
filled with nulls.
11879+
To convert a JSON value to the SQL type of an output column, the
11880+
following rules are applied in sequence:
11881+
<itemizedlist spacing="compact">
11882+
<listitem>
11883+
<para>
11884+
A JSON null value is converted to a SQL null in all cases.
11885+
</para>
11886+
</listitem>
11887+
<listitem>
11888+
<para>
11889+
If the output column is of type <type>json</type>
11890+
or <type>jsonb</type>, the JSON value is just reproduced exactly.
11891+
</para>
11892+
</listitem>
11893+
<listitem>
11894+
<para>
11895+
If the output column is a composite (row) type, and the JSON value is
11896+
a JSON object, the fields of the object are converted to columns of
11897+
the output row type by recursive application of these rules.
11898+
</para>
11899+
</listitem>
11900+
<listitem>
11901+
<para>
11902+
Likewise, if the output column is an array type and the JSON value is
11903+
a JSON array, the elements of the JSON array are converted to elements
11904+
of the output array by recursive application of these rules.
11905+
</para>
11906+
</listitem>
11907+
<listitem>
11908+
<para>
11909+
Otherwise, if the JSON value is a string literal, the contents of the
11910+
string are fed to the input conversion function for the column's data
11911+
type.
11912+
</para>
11913+
</listitem>
11914+
<listitem>
11915+
<para>
11916+
Otherwise, the ordinary text representation of the JSON value is fed
11917+
to the input conversion function for the column's data type.
11918+
</para>
11919+
</listitem>
11920+
</itemizedlist>
11921+
</para>
1188111922

11882-
<para>
11883-
JSON keys are matched to identical column names in the target
11884-
row type. JSON type coercion for these functions is <quote>best
11885-
effort</quote> and may not result in desired values for some types.
11886-
JSON fields that do not appear in the target row type will be
11887-
omitted from the output, and target columns that do not match any
11888-
JSON field will simply be NULL.
11889-
</para>
11923+
<para>
11924+
While the examples for these functions use constants, the typical use
11925+
would be to reference a table in the <literal>FROM</literal> clause
11926+
and use one of its <type>json</type> or <type>jsonb</type> columns
11927+
as an argument to the function. Extracted key values can then be
11928+
referenced in other parts of the query, like <literal>WHERE</literal>
11929+
clauses and target lists. Extracting multiple values in this
11930+
way can improve performance over extracting them separately with
11931+
per-key operators.
11932+
</para>
1189011933
</note>
1189111934

1189211935
<note>

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

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

27632763
json = jsv->val.json.str;
27642764
Assert(json);
2765-
2766-
/* already done the hard work in the json case */
2767-
if ((typid == JSONOID || typid == JSONBOID) &&
2768-
jsv->val.json.type == JSON_TOKEN_STRING)
2769-
{
2770-
/*
2771-
* Add quotes around string value (should be already escaped) if
2772-
* converting to json/jsonb.
2773-
*/
2774-
2775-
if (len < 0)
2776-
len = strlen(json);
2777-
2778-
str = palloc(len + sizeof(char) * 3);
2779-
str[0] = '"';
2780-
memcpy(&str[1], json, len);
2781-
str[len + 1] = '"';
2782-
str[len + 2] = '\0';
2783-
}
2784-
else if (len >= 0)
2765+
if (len >= 0)
27852766
{
27862767
/* Need to copy non-null-terminated string */
27872768
str = palloc(len + 1 * sizeof(char));
27882769
memcpy(str, json, len);
27892770
str[len] = '\0';
27902771
}
27912772
else
2792-
str = json; /* null-terminated string */
2773+
str = json; /* string is already null-terminated */
2774+
2775+
/* If converting to json/jsonb, make string into valid JSON literal */
2776+
if ((typid == JSONOID || typid == JSONBOID) &&
2777+
jsv->val.json.type == JSON_TOKEN_STRING)
2778+
{
2779+
StringInfoData buf;
2780+
2781+
initStringInfo(&buf);
2782+
escape_json(&buf, str);
2783+
/* free temporary buffer */
2784+
if (str != json)
2785+
pfree(str);
2786+
str = buf.data;
2787+
}
27932788
}
27942789
else
27952790
{

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

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

2203+
select * from json_to_record('{"out": {"key": 1}}') as x(out json);
2204+
out
2205+
------------
2206+
{"key": 1}
2207+
(1 row)
2208+
2209+
select * from json_to_record('{"out": [{"key": 1}]}') as x(out json);
2210+
out
2211+
--------------
2212+
[{"key": 1}]
2213+
(1 row)
2214+
2215+
select * from json_to_record('{"out": "{\"key\": 1}"}') as x(out json);
2216+
out
2217+
----------------
2218+
"{\"key\": 1}"
2219+
(1 row)
2220+
2221+
select * from json_to_record('{"out": {"key": 1}}') as x(out jsonb);
2222+
out
2223+
------------
2224+
{"key": 1}
2225+
(1 row)
2226+
2227+
select * from json_to_record('{"out": [{"key": 1}]}') as x(out jsonb);
2228+
out
2229+
--------------
2230+
[{"key": 1}]
2231+
(1 row)
2232+
2233+
select * from json_to_record('{"out": "{\"key\": 1}"}') as x(out jsonb);
2234+
out
2235+
----------------
2236+
"{\"key\": 1}"
2237+
(1 row)
2238+
22032239
-- json_strip_nulls
22042240
select json_strip_nulls(null);
22052241
json_strip_nulls

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

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

2581+
select * from jsonb_to_record('{"out": {"key": 1}}') as x(out json);
2582+
out
2583+
------------
2584+
{"key": 1}
2585+
(1 row)
2586+
2587+
select * from jsonb_to_record('{"out": [{"key": 1}]}') as x(out json);
2588+
out
2589+
--------------
2590+
[{"key": 1}]
2591+
(1 row)
2592+
2593+
select * from jsonb_to_record('{"out": "{\"key\": 1}"}') as x(out json);
2594+
out
2595+
----------------
2596+
"{\"key\": 1}"
2597+
(1 row)
2598+
2599+
select * from jsonb_to_record('{"out": {"key": 1}}') as x(out jsonb);
2600+
out
2601+
------------
2602+
{"key": 1}
2603+
(1 row)
2604+
2605+
select * from jsonb_to_record('{"out": [{"key": 1}]}') as x(out jsonb);
2606+
out
2607+
--------------
2608+
[{"key": 1}]
2609+
(1 row)
2610+
2611+
select * from jsonb_to_record('{"out": "{\"key\": 1}"}') as x(out jsonb);
2612+
out
2613+
----------------
2614+
"{\"key\": 1}"
2615+
(1 row)
2616+
25812617
-- test type info caching in jsonb_populate_record()
25822618
CREATE TEMP TABLE jsbpoptest (js jsonb);
25832619
INSERT INTO jsbpoptest

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

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

715+
select * from json_to_record('{"out": {"key": 1}}') as x(out json);
716+
select * from json_to_record('{"out": [{"key": 1}]}') as x(out json);
717+
select * from json_to_record('{"out": "{\"key\": 1}"}') as x(out json);
718+
select * from json_to_record('{"out": {"key": 1}}') as x(out jsonb);
719+
select * from json_to_record('{"out": [{"key": 1}]}') as x(out jsonb);
720+
select * from json_to_record('{"out": "{\"key\": 1}"}') as x(out jsonb);
721+
715722
-- json_strip_nulls
716723

717724
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
@@ -681,6 +681,13 @@ select * from jsonb_to_record('{"ia2": [1, 2, 3]}') as x(ia2 int[][]);
681681
select * from jsonb_to_record('{"ia2": [[1, 2], [3, 4]]}') as x(ia2 int4[][]);
682682
select * from jsonb_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]);
683683

684+
select * from jsonb_to_record('{"out": {"key": 1}}') as x(out json);
685+
select * from jsonb_to_record('{"out": [{"key": 1}]}') as x(out json);
686+
select * from jsonb_to_record('{"out": "{\"key\": 1}"}') as x(out json);
687+
select * from jsonb_to_record('{"out": {"key": 1}}') as x(out jsonb);
688+
select * from jsonb_to_record('{"out": [{"key": 1}]}') as x(out jsonb);
689+
select * from jsonb_to_record('{"out": "{\"key\": 1}"}') as x(out jsonb);
690+
684691
-- test type info caching in jsonb_populate_record()
685692
CREATE TEMP TABLE jsbpoptest (js jsonb);
686693

0 commit comments

Comments
 (0)