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

Commit2c5b48a

Browse files
author
Nikita Glukhov
committed
Enable DEFAULT behavior for JSON_QUERY
1 parent309839b commit2c5b48a

File tree

5 files changed

+33
-4
lines changed

5 files changed

+33
-4
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 10 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -18005,8 +18005,8 @@ SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]' DEFAULT 1 ON ERROR);
1800518005
[ RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]
1800618006
[ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ]
1800718007
[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
18008-
[ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } } ON EMPTY ]
18009-
[ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } } ON ERROR ]
18008+
[ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT }| DEFAULT <replaceable class="parameter">expression</replaceable>} ON EMPTY ]
18009+
[ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT }| DEFAULT <replaceable class="parameter">expression</replaceable>} ON ERROR ]
1801018010
)
1801118011
</synopsis>
1801218012
</refsynopsisdiv>
@@ -18120,13 +18120,16 @@ SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]' DEFAULT 1 ON ERROR);
1812018120

1812118121
<varlistentry>
1812218122
<term>
18123-
<literal>{ ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } } ON EMPTY</literal>
18123+
<literal>{ ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT }| DEFAULT <replaceable class="parameter">expression</replaceable>} ON EMPTY</literal>
1812418124
</term>
1812518125
<listitem>
1812618126
<para>
1812718127
Defines the return value if no JSON value is found. The default is <literal>NULL</literal>.
1812818128
If you use <literal>EMPTY [ARRAY]</literal> or <literal>EMPTY OBJECT</literal>,
1812918129
an empty JSON array [] or object {} is returned, respectively.
18130+
If you use <literal>DEFAULT <replaceable>expression</replaceable></literal>,
18131+
the provided <replaceable>expression</replaceable> is evaluated and cast
18132+
to the type specified in the <command>RETURNING</command> clause.
1813018133
</para>
1813118134
<para>
1813218135
You cannot use this clause together with the <literal>WRAPPER</literal> clause.
@@ -18136,13 +18139,16 @@ SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]' DEFAULT 1 ON ERROR);
1813618139

1813718140
<varlistentry>
1813818141
<term>
18139-
<literal>{ ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } } ON ERROR</literal>
18142+
<literal>{ ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT }| DEFAULT <replaceable class="parameter">expression</replaceable>} ON ERROR</literal>
1814018143
</term>
1814118144
<listitem>
1814218145
<para>
1814318146
Defines the return value if an unhandled error occurs. The default is <literal>NULL</literal>.
1814418147
If you use <literal>EMPTY [ARRAY]</literal> or <literal>EMPTY OBJECT</literal>,
1814518148
an empty JSON array [] or object {} are returned, respectively.
18149+
If you use <literal>DEFAULT <replaceable>expression</replaceable></literal>,
18150+
the provided <replaceable>expression</replaceable> is evaluated and cast
18151+
to the type specified in the <command>RETURNING</command> clause.
1814618152
</para>
1814718153
</listitem>
1814818154
</varlistentry>

‎src/backend/parser/gram.y

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15020,6 +15020,7 @@ json_query_behavior:
1502015020
| json_behavior_null
1502115021
| json_behavior_empty_array
1502215022
| json_behavior_empty_object
15023+
| json_behavior_default
1502315024
;
1502415025

1502515026
json_query_on_behavior_clause_opt:

‎src/backend/parser/parse_expr.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4820,6 +4820,14 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
48204820

48214821
transformJsonFuncExprOutput(pstate,func,jsexpr);
48224822

4823+
jsexpr->on_empty->default_expr=
4824+
coerceDefaultJsonExpr(pstate,jsexpr,
4825+
jsexpr->on_empty->default_expr);
4826+
4827+
jsexpr->on_error->default_expr=
4828+
coerceDefaultJsonExpr(pstate,jsexpr,
4829+
jsexpr->on_error->default_expr);
4830+
48234831
jsexpr->wrapper=func->wrapper;
48244832
jsexpr->omit_quotes=func->omit_quotes;
48254833

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -682,6 +682,12 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY);
682682

683683
(1 row)
684684

685+
SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
686+
json_query
687+
------------
688+
"empty"
689+
(1 row)
690+
685691
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
686692
json_query
687693
------------
@@ -711,6 +717,12 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
711717
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
712718
ERROR: JSON path expression in JSON_QUERY should return singleton item without wrapper
713719
HINT: use WITH WRAPPER clause to wrap SQL/JSON item sequence into array
720+
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
721+
json_query
722+
------------
723+
"empty"
724+
(1 row)
725+
714726
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING json);
715727
json_query
716728
------------

‎src/test/regress/sql/jsonb_sqljson.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -195,6 +195,7 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ON EMPTY);
195195
SELECT JSON_QUERY(jsonb'[]','$[*]' EMPTY ARRAYON EMPTY);
196196
SELECT JSON_QUERY(jsonb'[]','$[*]' EMPTY OBJECTON EMPTY);
197197
SELECT JSON_QUERY(jsonb'[]','$[*]' ERRORON EMPTY);
198+
SELECT JSON_QUERY(jsonb'[]','$[*]' DEFAULT'"empty"'ON EMPTY);
198199

199200
SELECT JSON_QUERY(jsonb'[]','$[*]' ERRORON EMPTYNULLON ERROR);
200201
SELECT JSON_QUERY(jsonb'[]','$[*]' ERRORON EMPTY EMPTY ARRAYON ERROR);
@@ -203,6 +204,7 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
203204
SELECT JSON_QUERY(jsonb'[]','$[*]' ERRORON ERROR);
204205

205206
SELECT JSON_QUERY(jsonb'[1,2]','$[*]' ERRORON ERROR);
207+
SELECT JSON_QUERY(jsonb'[1,2]','$[*]' DEFAULT'"empty"'ON ERROR);
206208

207209
SELECT JSON_QUERY(jsonb'[1,2]','$' RETURNING json);
208210
SELECT JSON_QUERY(jsonb'[1,2]','$' RETURNING json FORMAT JSON);

0 commit comments

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