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

Commit03734a7

Browse files
committed
Add more SQL/JSON constructor functions
This Patch introduces three SQL standard JSON functions:JSON()JSON_SCALAR()JSON_SERIALIZE()JSON() produces json values from text, bytea, json or jsonb values,and has facilitites for handling duplicate keys.JSON_SCALAR() produces a json value from any scalar sql value,including json and jsonb.JSON_SERIALIZE() produces text or bytea from input which containisor represents json or jsonb;For the most part these functions don't add any significant newcapabilities, but they will be of use to users wanting standardcompliant JSON handling.Catversion bumped as this changes ruleutils.c.Author: Nikita Glukhov <n.gluhov@postgrespro.ru>Author: Teodor Sigaev <teodor@sigaev.ru>Author: Oleg Bartunov <obartunov@gmail.com>Author: Alexander Korotkov <aekorotkov@gmail.com>Author: Andrew Dunstan <andrew@dunslane.net>Author: Amit Langote <amitlangote09@gmail.com>Reviewers have included (in no particular order) Andres Freund, AlexanderKorotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu,Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby, Álvaro Herrera,Peter EisentrautDiscussion:https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ruDiscussion:https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.deDiscussion:https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.orgDiscussion:https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com
1 parent254ac5a commit03734a7

File tree

22 files changed

+1397
-42
lines changed

22 files changed

+1397
-42
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16001,6 +16001,72 @@ table2-mapping
1600116001
<returnvalue>{"a": "1", "b": "2"}</returnvalue>
1600216002
</para></entry>
1600316003
</row>
16004+
<row>
16005+
<entry role="func_table_entry">
16006+
<para role="func_signature">
16007+
<indexterm><primary>json constructor</primary></indexterm>
16008+
<function>json</function> (
16009+
<replaceable>expression</replaceable>
16010+
<optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional></optional>
16011+
<optional> { <literal>WITH</literal> | <literal>WITHOUT</literal> } <literal>UNIQUE</literal> <optional> <literal>KEYS</literal> </optional></optional>
16012+
</para>
16013+
<para>
16014+
Converts a given expression specified as <type>text</type> or
16015+
<type>bytea</type> string (in UTF8 encoding) into a JSON
16016+
value. If <replaceable>expression</replaceable> is NULL, an
16017+
<acronym>SQL</acronym> null value is returned.
16018+
If <literal>WITH UNIQUE</literal> is specified, the
16019+
<replaceable>expression</replaceable> must not contain any duplicate
16020+
object keys.
16021+
</para>
16022+
<para>
16023+
<literal>json('{"a":123, "b":[true,"foo"], "a":"bar"}')</literal>
16024+
<returnvalue>{"a":123, "b":[true,"foo"], "a":"bar"}</returnvalue>
16025+
</para>
16026+
</entry>
16027+
</row>
16028+
<row>
16029+
<entry role="func_table_entry">
16030+
<para role="func_signature">
16031+
<indexterm><primary>json_scalar</primary></indexterm>
16032+
<function>json_scalar</function> (<replaceable>expression</replaceable>)
16033+
</para>
16034+
<para>
16035+
Converts a given SQL scalar value into a JSON scalar value.
16036+
If the input is NULL, an <acronym>SQL</acronym> null is returned. If
16037+
the input is number or a boolean value, a corresponding JSON number
16038+
or boolean value is returned. For any other value, a JSON string is
16039+
returned.
16040+
</para>
16041+
<para>
16042+
<literal>json_scalar(123.45)</literal>
16043+
<returnvalue>123.45</returnvalue>
16044+
</para>
16045+
<para>
16046+
<literal>json_scalar(CURRENT_TIMESTAMP)</literal>
16047+
<returnvalue>"2022-05-10T10:51:04.62128-04:00"</returnvalue>
16048+
</para></entry>
16049+
</row>
16050+
<row>
16051+
<entry role="func_table_entry">
16052+
<para role="func_signature">
16053+
<function>json_serialize</function> (
16054+
<replaceable>expression</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional>
16055+
<optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
16056+
</para>
16057+
<para>
16058+
Converts an SQL/JSON expression into a character or binary string. The
16059+
<replaceable>expression</replaceable> can be of any JSON type, any
16060+
character string type, or <type>bytea</type> in UTF8 encoding.
16061+
The returned type used in <literal> RETURNING</literal> can be any
16062+
character string type or <type>bytea</type>. The default is
16063+
<type>text</type>.
16064+
</para>
16065+
<para>
16066+
<literal>json_serialize('{ "a" : 1 } ' RETURNING bytea)</literal>
16067+
<returnvalue>\x7b20226122203a2031207d20</returnvalue>
16068+
</para></entry>
16069+
</row>
1600416070
</tbody>
1600516071
</tgroup>
1600616072
</table>

‎src/backend/executor/execExpr.c

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,7 @@
4848
#include"utils/array.h"
4949
#include"utils/builtins.h"
5050
#include"utils/datum.h"
51+
#include"utils/jsonfuncs.h"
5152
#include"utils/lsyscache.h"
5253
#include"utils/typcache.h"
5354

@@ -2311,6 +2312,12 @@ ExecInitExprRec(Expr *node, ExprState *state,
23112312
{
23122313
ExecInitExprRec(ctor->func,state,resv,resnull);
23132314
}
2315+
elseif ((ctor->type==JSCTOR_JSON_PARSE&& !ctor->unique)||
2316+
ctor->type==JSCTOR_JSON_SERIALIZE)
2317+
{
2318+
/* Use the value of the first argument as result */
2319+
ExecInitExprRec(linitial(args),state,resv,resnull);
2320+
}
23142321
else
23152322
{
23162323
JsonConstructorExprState*jcstate;
@@ -2349,6 +2356,29 @@ ExecInitExprRec(Expr *node, ExprState *state,
23492356
argno++;
23502357
}
23512358

2359+
/* prepare type cache for datum_to_json[b]() */
2360+
if (ctor->type==JSCTOR_JSON_SCALAR)
2361+
{
2362+
boolis_jsonb=
2363+
ctor->returning->format->format_type==JS_FORMAT_JSONB;
2364+
2365+
jcstate->arg_type_cache=
2366+
palloc(sizeof(*jcstate->arg_type_cache)*nargs);
2367+
2368+
for (inti=0;i<nargs;i++)
2369+
{
2370+
JsonTypeCategorycategory;
2371+
Oidoutfuncid;
2372+
Oidtypid=jcstate->arg_types[i];
2373+
2374+
json_categorize_type(typid,is_jsonb,
2375+
&category,&outfuncid);
2376+
2377+
jcstate->arg_type_cache[i].outfuncid=outfuncid;
2378+
jcstate->arg_type_cache[i].category= (int)category;
2379+
}
2380+
}
2381+
23522382
ExprEvalPushStep(state,&scratch);
23532383
}
23542384

‎src/backend/executor/execExprInterp.c

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4002,6 +4002,47 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
40024002
jcstate->arg_types,
40034003
jcstate->constructor->absent_on_null,
40044004
jcstate->constructor->unique);
4005+
elseif (ctor->type==JSCTOR_JSON_SCALAR)
4006+
{
4007+
if (jcstate->arg_nulls[0])
4008+
{
4009+
res= (Datum)0;
4010+
isnull= true;
4011+
}
4012+
else
4013+
{
4014+
Datumvalue=jcstate->arg_values[0];
4015+
Oidoutfuncid=jcstate->arg_type_cache[0].outfuncid;
4016+
JsonTypeCategorycategory= (JsonTypeCategory)
4017+
jcstate->arg_type_cache[0].category;
4018+
4019+
if (is_jsonb)
4020+
res=datum_to_jsonb(value,category,outfuncid);
4021+
else
4022+
res=datum_to_json(value,category,outfuncid);
4023+
}
4024+
}
4025+
elseif (ctor->type==JSCTOR_JSON_PARSE)
4026+
{
4027+
if (jcstate->arg_nulls[0])
4028+
{
4029+
res= (Datum)0;
4030+
isnull= true;
4031+
}
4032+
else
4033+
{
4034+
Datumvalue=jcstate->arg_values[0];
4035+
text*js=DatumGetTextP(value);
4036+
4037+
if (is_jsonb)
4038+
res=jsonb_from_text(js, true);
4039+
else
4040+
{
4041+
(void)json_validate(js, true, true);
4042+
res=value;
4043+
}
4044+
}
4045+
}
40054046
else
40064047
elog(ERROR,"invalid JsonConstructorExpr type %d",ctor->type);
40074048

‎src/backend/nodes/nodeFuncs.c

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3899,6 +3899,36 @@ raw_expression_tree_walker_impl(Node *node,
38993899
return true;
39003900
}
39013901
break;
3902+
caseT_JsonParseExpr:
3903+
{
3904+
JsonParseExpr*jpe= (JsonParseExpr*)node;
3905+
3906+
if (WALK(jpe->expr))
3907+
return true;
3908+
if (WALK(jpe->output))
3909+
return true;
3910+
}
3911+
break;
3912+
caseT_JsonScalarExpr:
3913+
{
3914+
JsonScalarExpr*jse= (JsonScalarExpr*)node;
3915+
3916+
if (WALK(jse->expr))
3917+
return true;
3918+
if (WALK(jse->output))
3919+
return true;
3920+
}
3921+
break;
3922+
caseT_JsonSerializeExpr:
3923+
{
3924+
JsonSerializeExpr*jse= (JsonSerializeExpr*)node;
3925+
3926+
if (WALK(jse->expr))
3927+
return true;
3928+
if (WALK(jse->output))
3929+
return true;
3930+
}
3931+
break;
39023932
caseT_JsonConstructorExpr:
39033933
{
39043934
JsonConstructorExpr*ctor= (JsonConstructorExpr*)node;

‎src/backend/parser/gram.y

Lines changed: 46 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -566,7 +566,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
566566
%type<list>copy_options
567567

568568
%type<typnam>TypenameSimpleTypenameConstTypename
569-
GenericTypeNumericopt_float
569+
GenericTypeNumericopt_floatJsonType
570570
CharacterConstCharacter
571571
CharacterWithLengthCharacterWithoutLength
572572
ConstDatetimeConstInterval
@@ -723,6 +723,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
723723
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
724724

725725
JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_OBJECT JSON_OBJECTAGG
726+
JSON_SCALAR JSON_SERIALIZE
726727

727728
KEY KEYS
728729

@@ -13990,6 +13991,7 @@ SimpleTypename:
1399013991
$$->typmods = list_make2(makeIntConst(INTERVAL_FULL_RANGE, -1),
1399113992
makeIntConst($3, @3));
1399213993
}
13994+
|JsonType{$$ =$1; }
1399313995
;
1399413996

1399513997
/* We have a separate ConstTypename to allow defaulting fixed-length
@@ -14008,6 +14010,7 @@ ConstTypename:
1400814010
|ConstBit{$$ =$1; }
1400914011
|ConstCharacter{$$ =$1; }
1401014012
|ConstDatetime{$$ =$1; }
14013+
|JsonType{$$ =$1; }
1401114014
;
1401214015

1401314016
/*
@@ -14376,6 +14379,13 @@ interval_second:
1437614379
}
1437714380
;
1437814381

14382+
JsonType:
14383+
JSON
14384+
{
14385+
$$ = SystemTypeName("json");
14386+
$$->location =@1;
14387+
}
14388+
;
1437914389

1438014390
/*****************************************************************************
1438114391
*
@@ -15634,7 +15644,36 @@ func_expr_common_subexpr:
1563415644
n->location =@1;
1563515645
$$ = (Node *) n;
1563615646
}
15637-
;
15647+
|JSON'('json_value_exprjson_key_uniqueness_constraint_opt')'
15648+
{
15649+
JsonParseExpr *n = makeNode(JsonParseExpr);
15650+
15651+
n->expr = (JsonValueExpr *)$3;
15652+
n->unique_keys =$4;
15653+
n->output =NULL;
15654+
n->location =@1;
15655+
$$ = (Node *) n;
15656+
}
15657+
|JSON_SCALAR'('a_expr')'
15658+
{
15659+
JsonScalarExpr *n = makeNode(JsonScalarExpr);
15660+
15661+
n->expr = (Expr *)$3;
15662+
n->output =NULL;
15663+
n->location =@1;
15664+
$$ = (Node *) n;
15665+
}
15666+
|JSON_SERIALIZE'('json_value_exprjson_returning_clause_opt')'
15667+
{
15668+
JsonSerializeExpr *n = makeNode(JsonSerializeExpr);
15669+
15670+
n->expr = (JsonValueExpr *)$3;
15671+
n->output = (JsonOutput *)$4;
15672+
n->location =@1;
15673+
$$ = (Node *) n;
15674+
}
15675+
;
15676+
1563815677

1563915678
/*
1564015679
* SQL/XML support
@@ -17075,7 +17114,6 @@ unreserved_keyword:
1707517114
| INSTEAD
1707617115
| INVOKER
1707717116
| ISOLATION
17078-
| JSON
1707917117
| KEY
1708017118
| KEYS
1708117119
| LABEL
@@ -17290,10 +17328,13 @@ col_name_keyword:
1729017328
| INT_P
1729117329
| INTEGER
1729217330
| INTERVAL
17331+
| JSON
1729317332
| JSON_ARRAY
1729417333
| JSON_ARRAYAGG
1729517334
| JSON_OBJECT
1729617335
| JSON_OBJECTAGG
17336+
| JSON_SCALAR
17337+
| JSON_SERIALIZE
1729717338
| LEAST
1729817339
| NATIONAL
1729917340
| NCHAR
@@ -17654,6 +17695,8 @@ bare_label_keyword:
1765417695
| JSON_ARRAYAGG
1765517696
| JSON_OBJECT
1765617697
| JSON_OBJECTAGG
17698+
| JSON_SCALAR
17699+
| JSON_SERIALIZE
1765717700
| KEY
1765817701
| KEYS
1765917702
| LABEL

0 commit comments

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