Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6f34fcb

Browse files
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 gotthis slightly wrong when the field is just a string literal: it failed toescape the contents of the string. That typically resulted in syntaxerrors if the string contained any double quotes or backslashes.jsonb_to_record() handles such cases correctly, but I added correspondingtest cases for it too, to prevent future backsliding.Improve the documentation, as it provided only a very hand-wavydescription of the conversion rules used by these functions.Per bug report from Robert Vollmert. Back-patch to v10 where theerror was introduced (by commitcf35346).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 onin the field, so I won't risk trying to make the older branches behavelike the newer ones.Discussion:https://postgr.es/m/D6921B37-BD8E-4664-8D5F-DB3525765DCD@vllmrt.net
1 parent9f05c44 commit6f34fcb

File tree

6 files changed

+167
-44
lines changed

6 files changed

+167
-44
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 65 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -13050,30 +13050,72 @@ table2-mapping
1305013050
</note>
1305113051

1305213052
<note>
13053-
<para>
13054-
While the examples for the functions
13055-
<function>json_populate_record</function>,
13056-
<function>json_populate_recordset</function>,
13057-
<function>json_to_record</function> and
13058-
<function>json_to_recordset</function> use constants, the typical use
13059-
would be to reference a table in the <literal>FROM</literal> clause
13060-
and use one of its <type>json</type> or <type>jsonb</type> columns
13061-
as an argument to the function. Extracted key values can then be
13062-
referenced in other parts of the query, like <literal>WHERE</literal>
13063-
clauses and target lists. Extracting multiple values in this
13064-
way can improve performance over extracting them separately with
13065-
per-key operators.
13066-
</para>
13067-
13068-
<para>
13069-
JSON keys are matched to identical column names in the target
13070-
row type. JSON type coercion for these functions is <quote>best
13071-
effort</quote> and may not result in desired values for some types.
13072-
JSON fields that do not appear in the target row type will be
13073-
omitted from the output, and target columns that do not match any
13074-
JSON field will simply be NULL.
13053+
<para>
13054+
The functions
13055+
<function>json[b]_populate_record</function>,
13056+
<function>json[b]_populate_recordset</function>,
13057+
<function>json[b]_to_record</function> and
13058+
<function>json[b]_to_recordset</function>
13059+
operate on a JSON object, or array of objects, and extract the values
13060+
associated with keys whose names match column names of the output row
13061+
type.
13062+
Object fields that do not correspond to any output column name are
13063+
ignored, and output columns that do not match any object field will be
13064+
filled with nulls.
13065+
To convert a JSON value to the SQL type of an output column, the
13066+
following rules are applied in sequence:
13067+
<itemizedlist spacing="compact">
13068+
<listitem>
13069+
<para>
13070+
A JSON null value is converted to a SQL null in all cases.
13071+
</para>
13072+
</listitem>
13073+
<listitem>
13074+
<para>
13075+
If the output column is of type <type>json</type>
13076+
or <type>jsonb</type>, the JSON value is just reproduced exactly.
13077+
</para>
13078+
</listitem>
13079+
<listitem>
13080+
<para>
13081+
If the output column is a composite (row) type, and the JSON value is
13082+
a JSON object, the fields of the object are converted to columns of
13083+
the output row type by recursive application of these rules.
13084+
</para>
13085+
</listitem>
13086+
<listitem>
13087+
<para>
13088+
Likewise, if the output column is an array type and the JSON value is
13089+
a JSON array, the elements of the JSON array are converted to elements
13090+
of the output array by recursive application of these rules.
13091+
</para>
13092+
</listitem>
13093+
<listitem>
13094+
<para>
13095+
Otherwise, if the JSON value is a string literal, the contents of the
13096+
string are fed to the input conversion function for the column's data
13097+
type.
13098+
</para>
13099+
</listitem>
13100+
<listitem>
13101+
<para>
13102+
Otherwise, the ordinary text representation of the JSON value is fed
13103+
to the input conversion function for the column's data type.
13104+
</para>
13105+
</listitem>
13106+
</itemizedlist>
13107+
</para>
1307513108

13076-
</para>
13109+
<para>
13110+
While the examples for these functions use constants, the typical use
13111+
would be to reference a table in the <literal>FROM</literal> clause
13112+
and use one of its <type>json</type> or <type>jsonb</type> columns
13113+
as an argument to the function. Extracted key values can then be
13114+
referenced in other parts of the query, like <literal>WHERE</literal>
13115+
clauses and target lists. Extracting multiple values in this
13116+
way can improve performance over extracting them separately with
13117+
per-key operators.
13118+
</para>
1307713119
</note>
1307813120

1307913121
<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-
elseif (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+
StringInfoDatabuf;
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)
[8]ページ先頭
©2009-2025 Movatter.jp