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

Commit489b0ab

Browse files
author
Nikita Glukhov
committed
Add EXISTS PATH columns to JSON_TABLE
1 parentd763dcb commit489b0ab

File tree

7 files changed

+163
-30
lines changed

7 files changed

+163
-30
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18613,6 +18613,8 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
1861318613
[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
1861418614
[ { ERROR | NULL | EMPTY { ARRAY | OBJECT } } ON EMPTY ]
1861518615
[ { ERROR | NULL | EMPTY { ARRAY | OBJECT } } ON ERROR ]
18616+
| <replaceable>name</replaceable> <replaceable>type</replaceable> EXISTS [ PATH <replaceable>json_path_specification</replaceable> ]
18617+
[ { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR ]
1861618618
| NESTED PATH <replaceable>json_path_specification</replaceable> [ AS <replaceable>path_name</replaceable> ]
1861718619
COLUMNS ( <replaceable>json_table_column</replaceable> [, ...] )
1861818620
| <replaceable>name</replaceable> FOR ORDINALITY
@@ -18785,6 +18787,38 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
1878518787
</listitem>
1878618788
</varlistentry>
1878718789

18790+
<varlistentry>
18791+
<term>
18792+
<literal>
18793+
<replaceable>name</replaceable> <replaceable>type</replaceable>
18794+
EXISTS [ PATH <replaceable>json_path_specification</replaceable> ]
18795+
</literal>
18796+
</term>
18797+
<listitem>
18798+
18799+
<para>
18800+
Gerenates a column and inserts a boolean item into each row of this column.
18801+
</para>
18802+
<para>
18803+
The provided <literal>PATH</literal> expression parses the
18804+
row pattern defined by <replaceable>json_api_common_syntax</replaceable>,
18805+
checks whether any SQL/JSON items were returned, and fills the column with
18806+
resulting boolean value, one for each row.
18807+
The specified <replaceable>type</replaceable> should have cast from
18808+
<type>boolean</type>.
18809+
If the <literal>PATH</literal> expression is omitted,
18810+
<function>JSON_TABLE</function> uses the
18811+
<literal>$.<replaceable>name</replaceable></literal> path expression,
18812+
where <replaceable>name</replaceable> is the provided column name.
18813+
</para>
18814+
<para>
18815+
Optionally, you can add <literal>ON ERROR</literal> clause to define
18816+
error behavior. This clause have the same syntax and semantics as in
18817+
<xref linkend="functions-jsonexists"/>.
18818+
</para>
18819+
</listitem>
18820+
</varlistentry>
18821+
1878818822
<varlistentry>
1878918823
<term>
1879018824
<literal>NESTED PATH <replaceable>json_path_specification</replaceable> [ AS <replaceable>json_path_name</replaceable> ]

‎src/backend/parser/gram.y

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -639,6 +639,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
639639
json_table_ordinality_column_definition
640640
json_table_regular_column_definition
641641
json_table_formatted_column_definition
642+
json_table_exists_column_definition
642643
json_table_nested_columns
643644

644645
%type<list>json_name_and_value_list
@@ -15156,6 +15157,7 @@ json_table_column_definition:
1515615157
json_table_ordinality_column_definition%prec json_table_column
1515715158
| json_table_regular_column_definition %prec json_table_column
1515815159
| json_table_formatted_column_definition%prec json_table_column
15160+
| json_table_exists_column_definition%prec json_table_column
1515915161
| json_table_nested_columns
1516015162
;
1516115163

@@ -15190,6 +15192,26 @@ json_table_regular_column_definition:
1519015192
}
1519115193
;
1519215194

15195+
json_table_exists_column_definition:
15196+
ColId Typename
15197+
EXISTS json_table_column_path_specification_clause_opt
15198+
json_exists_error_clause_opt
15199+
{
15200+
JsonTableColumn *n =makeNode(JsonTableColumn);
15201+
n->coltype = JTC_EXISTS;
15202+
n->name = $1;
15203+
n->typeName = $2;
15204+
n->format =makeJsonFormat(JS_FORMAT_DEFAULT, JS_ENC_DEFAULT, -1);
15205+
n->wrapper = JSW_NONE;
15206+
n->omit_quotes =false;
15207+
n->pathspec = $4;
15208+
n->on_empty =NULL;
15209+
n->on_error = $5;
15210+
n->location = @1;
15211+
$$ = (Node *) n;
15212+
}
15213+
;
15214+
1519315215
json_table_error_behavior:
1519415216
json_behavior_error
1519515217
| json_behavior_empty

‎src/backend/parser/parse_jsontable.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -72,7 +72,9 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
7272
JsonPathSpecpathspec;
7373
JsonFormat*default_format;
7474

75-
jfexpr->op=jtc->coltype==JTC_REGULAR ?IS_JSON_VALUE :IS_JSON_QUERY;
75+
jfexpr->op=
76+
jtc->coltype==JTC_REGULAR ?IS_JSON_VALUE :
77+
jtc->coltype==JTC_EXISTS ?IS_JSON_EXISTS :IS_JSON_QUERY;
7678
jfexpr->common=common;
7779
jfexpr->output=output;
7880
jfexpr->on_empty=jtc->on_empty;
@@ -259,6 +261,7 @@ appendJsonTableColumns(JsonTableContext *cxt, List *columns)
259261
break;
260262

261263
caseJTC_REGULAR:
264+
caseJTC_EXISTS:
262265
caseJTC_FORMATTED:
263266
{
264267
Node*je;

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

Lines changed: 18 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -10462,6 +10462,7 @@ get_json_table_columns(TableFunc *tf, JsonTableParentNode *node,
1046210462
Oidtypid;
1046310463
int32typmod;
1046410464
boolordinality;
10465+
JsonBehaviorTypedefault_behavior;
1046510466

1046610467
typid=lfirst_oid(l2);
1046710468
l2=lnext(tf->coltypes,l2);
@@ -10494,18 +10495,28 @@ get_json_table_columns(TableFunc *tf, JsonTableParentNode *node,
1049410495
if (ordinality)
1049510496
continue;
1049610497

10497-
if (colexpr->op==IS_JSON_QUERY)
10498-
appendStringInfoString(buf,
10499-
colexpr->format->format==JS_FORMAT_JSONB ?
10500-
" FORMAT JSONB" :" FORMAT JSON");
10498+
if (colexpr->op==IS_JSON_EXISTS)
10499+
{
10500+
appendStringInfoString(buf," EXISTS");
10501+
default_behavior=JSON_BEHAVIOR_FALSE;
10502+
}
10503+
else
10504+
{
10505+
if (colexpr->op==IS_JSON_QUERY)
10506+
appendStringInfoString(buf,
10507+
colexpr->format->format==JS_FORMAT_JSONB ?
10508+
" FORMAT JSONB" :" FORMAT JSON");
10509+
default_behavior=JSON_BEHAVIOR_NULL;
10510+
}
10511+
10512+
if (jexpr->on_error->btype==JSON_BEHAVIOR_ERROR)
10513+
default_behavior=JSON_BEHAVIOR_ERROR;
1050110514

1050210515
appendStringInfoString(buf," PATH ");
1050310516

1050410517
get_json_path_spec(colexpr->path_spec,context,showimplicit);
1050510518

10506-
get_json_expr_options(colexpr,context,
10507-
jexpr->on_error->btype==JSON_BEHAVIOR_ERROR ?
10508-
JSON_BEHAVIOR_ERROR :JSON_BEHAVIOR_NULL);
10519+
get_json_expr_options(colexpr,context,default_behavior);
1050910520
}
1051010521

1051110522
if (node->child)

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1507,6 +1507,7 @@ typedef enum
15071507
{
15081508
JTC_FOR_ORDINALITY,
15091509
JTC_REGULAR,
1510+
JTC_EXISTS,
15101511
JTC_FORMATTED,
15111512
JTC_NESTED,
15121513
}JsonTableColumnType;

0 commit comments

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