Skip to content

Commit 03734a7

Browse files
amitlanamitlan
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 containis or represents json or jsonb; For the most part these functions don't add any significant new capabilities, but they will be of use to users wanting standard compliant 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, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby, Álvaro Herrera, Peter Eisentraut Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com
1 parent 254ac5a commit 03734a7

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+
else if ((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+
bool is_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 (int i = 0; i < nargs; i++)
2369+
{
2370+
JsonTypeCategory category;
2371+
Oid outfuncid;
2372+
Oid typid = 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+
else if (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+
Datum value = jcstate->arg_values[0];
4015+
Oid outfuncid = jcstate->arg_type_cache[0].outfuncid;
4016+
JsonTypeCategory category = (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+
else if (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+
Datum value = 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+
case T_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+
case T_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+
case T_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
case T_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> Typename SimpleTypename ConstTypename
569-
GenericType Numeric opt_float
569+
GenericType Numeric opt_float JsonType
570570
Character ConstCharacter
571571
CharacterWithLength CharacterWithoutLength
572572
ConstDatetime ConstInterval
@@ -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_expr json_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_expr json_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)