Skip to content

Commit 606948b

Browse files
adunstanadunstan
committed
SQL JSON functions
This Patch introduces three SQL standard JSON functions: JSON() (incorrectly mentioned in my commit message for f4fb45d) 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. Nikita Glukhov 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. Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
1 parent 8e053dc commit 606948b

File tree

22 files changed

+880
-82
lines changed

22 files changed

+880
-82
lines changed

‎doc/src/sgml/keywords/sql2016-02-reserved.txt

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -156,12 +156,15 @@ INTERVAL
156156
INTO
157157
IS
158158
JOIN
159+
JSON
159160
JSON_ARRAY
160161
JSON_ARRAYAGG
161162
JSON_EXISTS
162163
JSON_OBJECT
163164
JSON_OBJECTAGG
164165
JSON_QUERY
166+
JSON_SCALAR
167+
JSON_SERIALIZE
165168
JSON_TABLE
166169
JSON_TABLE_PRIMITIVE
167170
JSON_VALUE

‎src/backend/executor/execExpr.c

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,8 @@
4747
#include "utils/array.h"
4848
#include "utils/builtins.h"
4949
#include "utils/datum.h"
50+
#include "utils/json.h"
51+
#include "utils/jsonb.h"
5052
#include "utils/jsonpath.h"
5153
#include "utils/lsyscache.h"
5254
#include "utils/typcache.h"
@@ -2460,6 +2462,12 @@ ExecInitExprRec(Expr *node, ExprState *state,
24602462
{
24612463
ExecInitExprRec(ctor->func, state, resv, resnull);
24622464
}
2465+
else if ((ctor->type == JSCTOR_JSON_PARSE && !ctor->unique) ||
2466+
ctor->type == JSCTOR_JSON_SERIALIZE)
2467+
{
2468+
/* Use the value of the first argument as a result */
2469+
ExecInitExprRec(linitial(args), state, resv, resnull);
2470+
}
24632471
else
24642472
{
24652473
scratch.opcode = EEOP_JSON_CONSTRUCTOR;
@@ -2492,6 +2500,43 @@ ExecInitExprRec(Expr *node, ExprState *state,
24922500
argno++;
24932501
}
24942502

2503+
/* prepare type cache for datum_to_json[b]() */
2504+
if (ctor->type == JSCTOR_JSON_SCALAR)
2505+
{
2506+
bool is_jsonb =
2507+
ctor->returning->format->format_type == JS_FORMAT_JSONB;
2508+
2509+
scratch.d.json_constructor.arg_type_cache =
2510+
palloc(sizeof(*scratch.d.json_constructor.arg_type_cache) * nargs);
2511+
2512+
for (int i = 0; i < nargs; i++)
2513+
{
2514+
int category;
2515+
Oid outfuncid;
2516+
Oid typid = scratch.d.json_constructor.arg_types[i];
2517+
2518+
if (is_jsonb)
2519+
{
2520+
JsonbTypeCategory jbcat;
2521+
2522+
jsonb_categorize_type(typid, &jbcat, &outfuncid);
2523+
2524+
category = (int) jbcat;
2525+
}
2526+
else
2527+
{
2528+
JsonTypeCategory jscat;
2529+
2530+
json_categorize_type(typid, &jscat, &outfuncid);
2531+
2532+
category = (int) jscat;
2533+
}
2534+
2535+
scratch.d.json_constructor.arg_type_cache[i].outfuncid = outfuncid;
2536+
scratch.d.json_constructor.arg_type_cache[i].category = category;
2537+
}
2538+
}
2539+
24952540
ExprEvalPushStep(state, &scratch);
24962541
}
24972542

‎src/backend/executor/execExprInterp.c

Lines changed: 41 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3982,7 +3982,7 @@ ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op)
39823982
* JSON text validation.
39833983
*/
39843984
if (res && (pred->unique_keys || exprtype == TEXTOID))
3985-
res = json_validate(json, pred->unique_keys);
3985+
res = json_validate(json, pred->unique_keys, false);
39863986
}
39873987
else if (exprtype == JSONBOID)
39883988
{
@@ -4527,6 +4527,46 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
45274527
op->d.json_constructor.arg_types,
45284528
op->d.json_constructor.constructor->absent_on_null,
45294529
op->d.json_constructor.constructor->unique);
4530+
else if (ctor->type == JSCTOR_JSON_SCALAR)
4531+
{
4532+
if (op->d.json_constructor.arg_nulls[0])
4533+
{
4534+
res = (Datum) 0;
4535+
isnull = true;
4536+
}
4537+
else
4538+
{
4539+
Datum value = op->d.json_constructor.arg_values[0];
4540+
int category = op->d.json_constructor.arg_type_cache[0].category;
4541+
Oid outfuncid = op->d.json_constructor.arg_type_cache[0].outfuncid;
4542+
4543+
if (is_jsonb)
4544+
res = to_jsonb_worker(value, category, outfuncid);
4545+
else
4546+
res = to_json_worker(value, category, outfuncid);
4547+
}
4548+
}
4549+
else if (ctor->type == JSCTOR_JSON_PARSE)
4550+
{
4551+
if (op->d.json_constructor.arg_nulls[0])
4552+
{
4553+
res = (Datum) 0;
4554+
isnull = true;
4555+
}
4556+
else
4557+
{
4558+
Datum value = op->d.json_constructor.arg_values[0];
4559+
text *js = DatumGetTextP(value);
4560+
4561+
if (is_jsonb)
4562+
res = jsonb_from_text(js, true);
4563+
else
4564+
{
4565+
(void) json_validate(js, true, true);
4566+
res = value;
4567+
}
4568+
}
4569+
}
45304570
else
45314571
{
45324572
res = (Datum) 0;

‎src/backend/nodes/copyfuncs.c

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2345,6 +2345,50 @@ _copyJsonValueExpr(const JsonValueExpr *from)
23452345
return newnode;
23462346
}
23472347

2348+
/*
2349+
* _copyJsonParseExpr
2350+
*/
2351+
static JsonParseExpr *
2352+
_copyJsonParseExpr(const JsonParseExpr *from)
2353+
{
2354+
JsonParseExpr *newnode = makeNode(JsonParseExpr);
2355+
2356+
COPY_NODE_FIELD(expr);
2357+
COPY_SCALAR_FIELD(unique_keys);
2358+
COPY_LOCATION_FIELD(location);
2359+
2360+
return newnode;
2361+
}
2362+
2363+
/*
2364+
* _copyJsonScalarExpr
2365+
*/
2366+
static JsonScalarExpr *
2367+
_copyJsonScalarExpr(const JsonScalarExpr *from)
2368+
{
2369+
JsonScalarExpr *newnode = makeNode(JsonScalarExpr);
2370+
2371+
COPY_NODE_FIELD(expr);
2372+
COPY_LOCATION_FIELD(location);
2373+
2374+
return newnode;
2375+
}
2376+
2377+
/*
2378+
* _copyJsonSerializeExpr
2379+
*/
2380+
static JsonSerializeExpr *
2381+
_copyJsonSerializeExpr(const JsonSerializeExpr *from)
2382+
{
2383+
JsonSerializeExpr *newnode = makeNode(JsonSerializeExpr);
2384+
2385+
COPY_NODE_FIELD(expr);
2386+
COPY_NODE_FIELD(output);
2387+
COPY_LOCATION_FIELD(location);
2388+
2389+
return newnode;
2390+
}
2391+
23482392
/*
23492393
* _copyJsonConstructorExpr
23502394
*/
@@ -5744,6 +5788,15 @@ copyObjectImpl(const void *from)
57445788
case T_JsonValueExpr:
57455789
retval = _copyJsonValueExpr(from);
57465790
break;
5791+
case T_JsonParseExpr:
5792+
retval = _copyJsonParseExpr(from);
5793+
break;
5794+
case T_JsonScalarExpr:
5795+
retval = _copyJsonScalarExpr(from);
5796+
break;
5797+
case T_JsonSerializeExpr:
5798+
retval = _copyJsonSerializeExpr(from);
5799+
break;
57475800
case T_JsonKeyValue:
57485801
retval = _copyJsonKeyValue(from);
57495802
break;

‎src/backend/nodes/equalfuncs.c

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -871,6 +871,35 @@ _equalJsonValueExpr(const JsonValueExpr *a, const JsonValueExpr *b)
871871
return true;
872872
}
873873

874+
static bool
875+
_equalJsonParseExpr(const JsonParseExpr *a, const JsonParseExpr *b)
876+
{
877+
COMPARE_NODE_FIELD(expr);
878+
COMPARE_SCALAR_FIELD(unique_keys);
879+
COMPARE_LOCATION_FIELD(location);
880+
881+
return true;
882+
}
883+
884+
static bool
885+
_equalJsonScalarExpr(const JsonScalarExpr *a, const JsonScalarExpr *b)
886+
{
887+
COMPARE_NODE_FIELD(expr);
888+
COMPARE_LOCATION_FIELD(location);
889+
890+
return true;
891+
}
892+
893+
static bool
894+
_equalJsonSerializeExpr(const JsonSerializeExpr *a, const JsonSerializeExpr *b)
895+
{
896+
COMPARE_NODE_FIELD(expr);
897+
COMPARE_NODE_FIELD(output);
898+
COMPARE_LOCATION_FIELD(location);
899+
900+
return true;
901+
}
902+
874903
static bool
875904
_equalJsonConstructorExpr(const JsonConstructorExpr *a, const JsonConstructorExpr *b)
876905
{
@@ -3661,6 +3690,15 @@ equal(const void *a, const void *b)
36613690
case T_JsonValueExpr:
36623691
retval = _equalJsonValueExpr(a, b);
36633692
break;
3693+
case T_JsonParseExpr:
3694+
retval = _equalJsonParseExpr(a, b);
3695+
break;
3696+
case T_JsonScalarExpr:
3697+
retval = _equalJsonScalarExpr(a, b);
3698+
break;
3699+
case T_JsonSerializeExpr:
3700+
retval = _equalJsonSerializeExpr(a, b);
3701+
break;
36643702
case T_JsonConstructorExpr:
36653703
retval = _equalJsonConstructorExpr(a, b);
36663704
break;

‎src/backend/nodes/nodeFuncs.c

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4363,6 +4363,20 @@ raw_expression_tree_walker(Node *node,
43634363
return true;
43644364
}
43654365
break;
4366+
case T_JsonParseExpr:
4367+
return walker(((JsonParseExpr *) node)->expr, context);
4368+
case T_JsonScalarExpr:
4369+
return walker(((JsonScalarExpr *) node)->expr, context);
4370+
case T_JsonSerializeExpr:
4371+
{
4372+
JsonSerializeExpr *jse = (JsonSerializeExpr *) node;
4373+
4374+
if (walker(jse->expr, context))
4375+
return true;
4376+
if (walker(jse->output, context))
4377+
return true;
4378+
}
4379+
break;
43664380
case T_JsonConstructorExpr:
43674381
{
43684382
JsonConstructorExpr *ctor = (JsonConstructorExpr *) node;

0 commit comments

Comments
 (0)