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

Commitb9a45aa

Browse files
author
Nikita Glukhov
committed
Add implicit FORMAT JSON for composite JSON_TABLE columns
1 parent489b0ab commitb9a45aa

File tree

6 files changed

+148
-39
lines changed

6 files changed

+148
-39
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 24 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -18604,15 +18604,16 @@ JSON_TABLE (
1860418604
where <replaceable class="parameter">json_table_column</replaceable> is:
1860518605
</phrase>
1860618606
<replaceable>name</replaceable> <replaceable>type</replaceable> [ PATH <replaceable>json_path_specification</replaceable> ]
18607+
[ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ]
18608+
[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
1860718609
[ { ERROR | NULL | DEFAULT <replaceable>expression</replaceable> } ON EMPTY ]
1860818610
[ { ERROR | NULL | DEFAULT <replaceable>expression</replaceable> } ON ERROR ]
1860918611
| <replaceable>name</replaceable> <replaceable>type</replaceable> FORMAT <replaceable>json_representation</replaceable>
1861018612
[ PATH <replaceable>json_path_specification</replaceable> ]
18611-
[ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } }
18612-
[ ARRAY ] WRAPPER ]
18613+
[ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ]
1861318614
[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
18614-
[ { ERROR | NULL | EMPTY { ARRAY | OBJECT } } ON EMPTY ]
18615-
[ { ERROR | NULL | EMPTY { ARRAY | OBJECT } } ON ERROR ]
18615+
[ { ERROR | NULL | EMPTY { ARRAY | OBJECT }| DEFAULT expression} ON EMPTY ]
18616+
[ { ERROR | NULL | EMPTY { ARRAY | OBJECT }| DEFAULT expression} ON ERROR ]
1861618617
| <replaceable>name</replaceable> <replaceable>type</replaceable> EXISTS [ PATH <replaceable>json_path_specification</replaceable> ]
1861718618
[ { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR ]
1861818619
| NESTED PATH <replaceable>json_path_specification</replaceable> [ AS <replaceable>path_name</replaceable> ]
@@ -18718,7 +18719,7 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
1871818719
<para>
1871918720
The <literal>COLUMNS</literal> clause defining the schema of the
1872018721
constructed view. In this clause, you must specify all the columns
18721-
to be filled with SQL/JSON items. Only scalar column types are supported.
18722+
to be filled with SQL/JSON items.
1872218723
The <replaceable class="parameter">json_table_column</replaceable>
1872318724
expression has the following syntax variants:
1872418725
</para>
@@ -18746,11 +18747,22 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
1874618747
In this case, the column name must correspond to one of the
1874718748
keys within the SQL/JSON item produced by the row pattern.
1874818749
</para>
18750+
<para>
18751+
Internally, <xref linkend="functions-jsonvalue"/> and
18752+
<xref linkend="functions-jsonquery"/> are used to produce resulting values.
18753+
<xref linkend="functions-jsonquery"/> is used for JSON, array, and
18754+
composite column types, <xref linkend="functions-jsonvalue"/> is used for
18755+
other types.
18756+
</para>
1874918757
<para>
1875018758
Optionally, you can add <literal>ON EMPTY</literal> and
18751-
<literal>ON ERROR</literal> clauses to define how to handle
18752-
missing values or structural errors. These clauses have the same syntax
18753-
and semantics as in <xref linkend="functions-jsonvalue"/>.
18759+
<literal>ON ERROR</literal> clauses to define how to handle missing values
18760+
or structural errors.
18761+
<literal>WRAPPER</literal> and <literal>QUOTES</literal> clauses can only
18762+
be used with JSON, array, and composite types.
18763+
These clauses have the same syntax and semantics as in
18764+
<xref linkend="functions-jsonvalue"/> and
18765+
<xref linkend="functions-jsonquery"/>.
1875418766
</para>
1875518767
</listitem>
1875618768
</varlistentry>
@@ -18777,6 +18789,10 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
1877718789
In this case, the column name must correspond to one of the
1877818790
keys within the SQL/JSON item produced by the row pattern.
1877918791
</para>
18792+
<para>
18793+
Internally, <xref linkend="functions-jsonquery"/> is used to produce
18794+
resulting values.
18795+
</para>
1878018796
<para>
1878118797
Optionally, you can add <literal>WRAPPER</literal>, <literal>QUOTES</literal>,
1878218798
<literal>ON EMPTY</literal> and <literal>ON ERROR</literal> clauses

‎src/backend/parser/gram.y

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -15175,18 +15175,20 @@ json_table_ordinality_column_definition:
1517515175
json_table_regular_column_definition:
1517615176
ColId Typename
1517715177
json_table_column_path_specification_clause_opt
15178+
json_wrapper_clause_opt
15179+
json_quotes_clause_opt
1517815180
json_value_on_behavior_clause_opt
1517915181
{
1518015182
JsonTableColumn *n =makeNode(JsonTableColumn);
1518115183
n->coltype = JTC_REGULAR;
1518215184
n->name = $1;
1518315185
n->typeName = $2;
1518415186
n->format =makeJsonFormat(JS_FORMAT_DEFAULT, JS_ENC_DEFAULT, -1);
15185-
n->wrapper =JSW_NONE;
15186-
n->omit_quotes =false;
15187+
n->wrapper =$4;/* JSW_NONE*/
15188+
n->omit_quotes =$5;/* false*/
1518715189
n->pathspec = $3;
15188-
n->on_empty = $4.on_empty;
15189-
n->on_error = $4.on_error;
15190+
n->on_empty = $6.on_empty;
15191+
n->on_error = $6.on_error;
1519015192
n->location = @1;
1519115193
$$ = (Node *) n;
1519215194
}

‎src/backend/parser/parse_jsontable.c

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@
2626
#include"parser/parse_collate.h"
2727
#include"parser/parse_expr.h"
2828
#include"parser/parse_relation.h"
29+
#include"parser/parse_type.h"
2930
#include"utils/builtins.h"
3031
#include"utils/json.h"
3132
#include"utils/lsyscache.h"
@@ -212,6 +213,28 @@ transformJsonTableChildColumns(JsonTableContext *cxt, List *columns)
212213
returnres;
213214
}
214215

216+
/* Check whether type is json/jsonb, array, or record. */
217+
staticbool
218+
typeIsComposite(Oidtypid)
219+
{
220+
if (typid==JSONOID||
221+
typid==JSONBOID||
222+
typid==RECORDOID||
223+
type_is_array(typid))
224+
return true;
225+
226+
switch (get_typtype(typid))
227+
{
228+
caseTYPTYPE_COMPOSITE:
229+
return true;
230+
231+
caseTYPTYPE_DOMAIN:
232+
returntypeIsComposite(getBaseType(typid));
233+
}
234+
235+
return false;
236+
}
237+
215238
/* Append transformed non-nested JSON_TABLE columns to the TableFunc node */
216239
staticvoid
217240
appendJsonTableColumns(JsonTableContext*cxt,List*columns)
@@ -261,6 +284,26 @@ appendJsonTableColumns(JsonTableContext *cxt, List *columns)
261284
break;
262285

263286
caseJTC_REGULAR:
287+
typenameTypeIdAndMod(pstate,rawc->typeName,&typid,&typmod);
288+
289+
/*
290+
* Use implicit FORMAT JSON for composite types (arrays and
291+
* records)
292+
*/
293+
if (typeIsComposite(typid))
294+
rawc->coltype=JTC_FORMATTED;
295+
elseif (rawc->wrapper!=JSW_NONE)
296+
ereport(ERROR,
297+
(errcode(ERRCODE_SYNTAX_ERROR),
298+
errmsg("cannot use WITH WRAPPER clause with scalar columns"),
299+
parser_errposition(pstate,rawc->location)));
300+
elseif (rawc->omit_quotes)
301+
ereport(ERROR,
302+
(errcode(ERRCODE_SYNTAX_ERROR),
303+
errmsg("cannot use OMIT QUOTES clause with scalar columns"),
304+
parser_errposition(pstate,rawc->location)));
305+
306+
/* FALLTHROUGH */
264307
caseJTC_EXISTS:
265308
caseJTC_FORMATTED:
266309
{

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

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10503,9 +10503,18 @@ get_json_table_columns(TableFunc *tf, JsonTableParentNode *node,
1050310503
else
1050410504
{
1050510505
if (colexpr->op==IS_JSON_QUERY)
10506-
appendStringInfoString(buf,
10507-
colexpr->format->format==JS_FORMAT_JSONB ?
10508-
" FORMAT JSONB" :" FORMAT JSON");
10506+
{
10507+
chartypcategory;
10508+
booltypispreferred;
10509+
10510+
get_type_category_preferred(typid,&typcategory,&typispreferred);
10511+
10512+
if (typcategory==TYPCATEGORY_STRING)
10513+
appendStringInfoString(buf,
10514+
colexpr->format->format==JS_FORMAT_JSONB ?
10515+
" FORMAT JSONB" :" FORMAT JSON");
10516+
}
10517+
1050910518
default_behavior=JSON_BEHAVIOR_NULL;
1051010519
}
1051110520

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp