From 4523dd06b1849f72eba0f326935c1a41ea2b6241 Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Wed, 21 Jun 2023 16:12:39 +0900 Subject: [PATCH v5 4/7] SQL JSON 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. 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 Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org --- doc/src/sgml/func.sgml | 71 +++++ src/backend/executor/execExpr.c | 30 ++ src/backend/executor/execExprInterp.c | 43 ++- src/backend/nodes/nodeFuncs.c | 30 ++ src/backend/parser/gram.y | 69 ++++- src/backend/parser/parse_expr.c | 234 +++++++++++++++- src/backend/parser/parse_target.c | 9 + src/backend/utils/adt/format_type.c | 4 + src/backend/utils/adt/json.c | 21 +- src/backend/utils/adt/jsonb.c | 48 +++- src/backend/utils/adt/ruleutils.c | 14 +- src/include/nodes/parsenodes.h | 48 ++++ src/include/nodes/primnodes.h | 5 +- src/include/parser/kwlist.h | 4 +- src/include/utils/jsonb.h | 2 +- src/include/utils/jsonfuncs.h | 4 + src/test/regress/expected/sqljson.out | 384 ++++++++++++++++++++++++++ src/test/regress/sql/sqljson.sql | 95 +++++++ src/tools/pgindent/typedefs.list | 1 + 19 files changed, 1067 insertions(+), 49 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 0b62e0c828..9cece06c18 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -16001,6 +16001,77 @@ table2-mapping {"a": "1", "b": "2"} + + + + json constructor + json ( + expression + FORMAT JSON ENCODING UTF8 + { WITH | WITHOUT } UNIQUE KEYS + RETURNING json_data_type ) + + + The expression can be any text type or a + bytea in UTF8 encoding. If the + expression is NULL, an + SQL null value is returned. + If WITH UNIQUE is specified, the + expression must not contain any duplicate + object keys. + + + json('{"a":123, "b":[true,"foo"], "a":"bar"}') + {"a":123, "b":[true,"foo"], "a":"bar"} + + + json('{"a":123,"b":[true,"foo"],"a":"bar"}' returning jsonb) + {"a": "bar", "b": [true, "foo"]} + + + + + + + json_scalar + json_scalar (expression) + RETURNING json_data_type ) + + + Returns a JSON scalar value representing + expression. + If the input is NULL, an SQL NULL is returned. If the input is a number + or a boolean value, a corresponding JSON number or boolean value is + returned. For any other value a JSON string is returned. + + + json_scalar(123.45) + 123.45 + + + json_scalar(CURRENT_TIMESTAMP) + "2022-05-10T10:51:04.62128-04:00" + + + + + + json_serialize ( + expression FORMAT JSON ENCODING UTF8 + RETURNING data_type FORMAT JSON ENCODING UTF8 ) + + + Transforms an SQL/JSON value into a character or binary string. The + expression can be of any JSON type, any + character string type, or bytea in UTF8 encoding. + The returned type can be any character string type or + bytea. The default is text. + + + json_serialize('{ "a" : 1 } ' RETURNING bytea) + \x7b20226122203a2031207d20 + + diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index bf3a08c5f0..6ca4098bef 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -48,6 +48,7 @@ #include "utils/array.h" #include "utils/builtins.h" #include "utils/datum.h" +#include "utils/jsonfuncs.h" #include "utils/lsyscache.h" #include "utils/typcache.h" @@ -2311,6 +2312,12 @@ ExecInitExprRec(Expr *node, ExprState *state, { ExecInitExprRec(ctor->func, state, resv, resnull); } + else if ((ctor->type == JSCTOR_JSON_PARSE && !ctor->unique) || + ctor->type == JSCTOR_JSON_SERIALIZE) + { + /* Use the value of the first argument as a result */ + ExecInitExprRec(linitial(args), state, resv, resnull); + } else { JsonConstructorExprState *jcstate; @@ -2349,6 +2356,29 @@ ExecInitExprRec(Expr *node, ExprState *state, argno++; } + /* prepare type cache for datum_to_json[b]() */ + if (ctor->type == JSCTOR_JSON_SCALAR) + { + bool is_jsonb = + ctor->returning->format->format_type == JS_FORMAT_JSONB; + + jcstate->arg_type_cache = + palloc(sizeof(*jcstate->arg_type_cache) * nargs); + + for (int i = 0; i < nargs; i++) + { + JsonTypeCategory category; + Oid outfuncid; + Oid typid = jcstate->arg_types[i]; + + json_categorize_type(typid, is_jsonb, + &category, &outfuncid); + + jcstate->arg_type_cache[i].outfuncid = outfuncid; + jcstate->arg_type_cache[i].category = (int) category; + } + } + ExprEvalPushStep(state, &scratch); } diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 851946a927..76e59691e5 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -3992,7 +3992,7 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op, jcstate->arg_values, jcstate->arg_nulls, jcstate->arg_types, - jcstate->constructor->absent_on_null); + ctor->absent_on_null); else if (ctor->type == JSCTOR_JSON_OBJECT) res = (is_jsonb ? jsonb_build_object_worker : @@ -4002,6 +4002,47 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op, jcstate->arg_types, jcstate->constructor->absent_on_null, jcstate->constructor->unique); + else if (ctor->type == JSCTOR_JSON_SCALAR) + { + if (jcstate->arg_nulls[0]) + { + res = (Datum) 0; + isnull = true; + } + else + { + Datum value = jcstate->arg_values[0]; + Oid outfuncid = jcstate->arg_type_cache[0].outfuncid; + JsonTypeCategory category = (JsonTypeCategory) + jcstate->arg_type_cache[0].category; + + if (is_jsonb) + res = to_jsonb_worker(value, category, outfuncid); + else + res = to_json_worker(value, category, outfuncid); + } + } + else if (ctor->type == JSCTOR_JSON_PARSE) + { + if (jcstate->arg_nulls[0]) + { + res = (Datum) 0; + isnull = true; + } + else + { + Datum value = jcstate->arg_values[0]; + text *js = DatumGetTextP(value); + + if (is_jsonb) + res = jsonb_from_text(js, true); + else + { + (void) json_validate(js, true, true); + res = value; + } + } + } else elog(ERROR, "invalid JsonConstructorExpr type %d", ctor->type); diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index c41e6bb984..dda964bd19 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -3901,6 +3901,36 @@ raw_expression_tree_walker_impl(Node *node, return true; } break; + case T_JsonParseExpr: + { + JsonParseExpr *jpe = (JsonParseExpr *) node; + + if (WALK(jpe->expr)) + return true; + if (WALK(jpe->output)) + return true; + } + break; + case T_JsonScalarExpr: + { + JsonScalarExpr *jse = (JsonScalarExpr *) node; + + if (WALK(jse->expr)) + return true; + if (WALK(jse->output)) + return true; + } + break; + case T_JsonSerializeExpr: + { + JsonSerializeExpr *jse = (JsonSerializeExpr *) node; + + if (WALK(jse->expr)) + return true; + if (WALK(jse->output)) + return true; + } + break; case T_JsonConstructorExpr: { JsonConstructorExpr *ctor = (JsonConstructorExpr *) node; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index edb6c00ece..341b002dc7 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -566,7 +566,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type copy_options %type Typename SimpleTypename ConstTypename - GenericType Numeric opt_float + GenericType Numeric opt_float JsonType Character ConstCharacter CharacterWithLength CharacterWithoutLength ConstDatetime ConstInterval @@ -647,7 +647,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type json_format_clause_opt json_value_expr - json_output_clause_opt + json_returning_clause_opt json_name_and_value json_aggregate_func %type json_name_and_value_list @@ -659,7 +659,6 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); json_object_constructor_null_clause_opt json_array_constructor_null_clause_opt - /* * Non-keyword token types. These are hard-wired into the "flex" lexer. * They must be listed first so that their numeric codes do not depend on @@ -723,6 +722,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_OBJECT JSON_OBJECTAGG + JSON_SCALAR JSON_SERIALIZE KEY KEYS @@ -13981,6 +13981,7 @@ SimpleTypename: $$->typmods = list_make2(makeIntConst(INTERVAL_FULL_RANGE, -1), makeIntConst($3, @3)); } + | JsonType { $$ = $1; } ; /* We have a separate ConstTypename to allow defaulting fixed-length @@ -13999,6 +14000,7 @@ ConstTypename: | ConstBit { $$ = $1; } | ConstCharacter { $$ = $1; } | ConstDatetime { $$ = $1; } + | JsonType { $$ = $1; } ; /* @@ -14367,6 +14369,13 @@ interval_second: } ; +JsonType: + JSON + { + $$ = SystemTypeName("json"); + $$->location = @1; + } + ; /***************************************************************************** * @@ -15561,7 +15570,7 @@ func_expr_common_subexpr: | JSON_OBJECT '(' json_name_and_value_list json_object_constructor_null_clause_opt json_key_uniqueness_constraint_opt - json_output_clause_opt ')' + json_returning_clause_opt ')' { JsonObjectConstructor *n = makeNode(JsonObjectConstructor); @@ -15572,7 +15581,7 @@ func_expr_common_subexpr: n->location = @1; $$ = (Node *) n; } - | JSON_OBJECT '(' json_output_clause_opt ')' + | JSON_OBJECT '(' json_returning_clause_opt ')' { JsonObjectConstructor *n = makeNode(JsonObjectConstructor); @@ -15586,7 +15595,7 @@ func_expr_common_subexpr: | JSON_ARRAY '(' json_value_expr_list json_array_constructor_null_clause_opt - json_output_clause_opt + json_returning_clause_opt ')' { JsonArrayConstructor *n = makeNode(JsonArrayConstructor); @@ -15601,7 +15610,7 @@ func_expr_common_subexpr: select_no_parens json_format_clause_opt /* json_array_constructor_null_clause_opt */ - json_output_clause_opt + json_returning_clause_opt ')' { JsonArrayQueryConstructor *n = makeNode(JsonArrayQueryConstructor); @@ -15614,7 +15623,7 @@ func_expr_common_subexpr: $$ = (Node *) n; } | JSON_ARRAY '(' - json_output_clause_opt + json_returning_clause_opt ')' { JsonArrayConstructor *n = makeNode(JsonArrayConstructor); @@ -15625,7 +15634,37 @@ func_expr_common_subexpr: n->location = @1; $$ = (Node *) n; } - ; + | JSON '(' json_value_expr json_key_uniqueness_constraint_opt + json_returning_clause_opt ')' + { + JsonParseExpr *n = makeNode(JsonParseExpr); + + n->expr = (JsonValueExpr *) $3; + n->unique_keys = $4; + n->output = (JsonOutput *) $5; + n->location = @1; + $$ = (Node *) n; + } + | JSON_SCALAR '(' a_expr json_returning_clause_opt ')' + { + JsonScalarExpr *n = makeNode(JsonScalarExpr); + + n->expr = (Expr *) $3; + n->output = (JsonOutput *) $4; + n->location = @1; + $$ = (Node *) n; + } + | JSON_SERIALIZE '(' json_value_expr json_returning_clause_opt ')' + { + JsonSerializeExpr *n = makeNode(JsonSerializeExpr); + + n->expr = (JsonValueExpr *) $3; + n->output = (JsonOutput *) $4; + n->location = @1; + $$ = (Node *) n; + } + ; + /* * SQL/XML support @@ -16373,7 +16412,7 @@ json_encoding_clause_opt: | /* EMPTY */ { $$ = JS_ENC_DEFAULT; } ; -json_output_clause_opt: +json_returning_clause_opt: RETURNING Typename json_format_clause_opt { JsonOutput *n = makeNode(JsonOutput); @@ -16446,7 +16485,7 @@ json_aggregate_func: json_name_and_value json_object_constructor_null_clause_opt json_key_uniqueness_constraint_opt - json_output_clause_opt + json_returning_clause_opt ')' { JsonObjectAgg *n = makeNode(JsonObjectAgg); @@ -16464,7 +16503,7 @@ json_aggregate_func: json_value_expr json_array_aggregate_order_by_clause_opt json_array_constructor_null_clause_opt - json_output_clause_opt + json_returning_clause_opt ')' { JsonArrayAgg *n = makeNode(JsonArrayAgg); @@ -17064,7 +17103,6 @@ unreserved_keyword: | INSTEAD | INVOKER | ISOLATION - | JSON | KEY | KEYS | LABEL @@ -17279,10 +17317,13 @@ col_name_keyword: | INT_P | INTEGER | INTERVAL + | JSON | JSON_ARRAY | JSON_ARRAYAGG | JSON_OBJECT | JSON_OBJECTAGG + | JSON_SCALAR + | JSON_SERIALIZE | LEAST | NATIONAL | NCHAR @@ -17643,6 +17684,8 @@ bare_label_keyword: | JSON_ARRAYAGG | JSON_OBJECT | JSON_OBJECTAGG + | JSON_SCALAR + | JSON_SERIALIZE | KEY | KEYS | LABEL diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 8d7a44408c..198975cb8c 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -86,6 +86,10 @@ static Node *transformJsonArrayQueryConstructor(ParseState *pstate, static Node *transformJsonObjectAgg(ParseState *pstate, JsonObjectAgg *agg); static Node *transformJsonArrayAgg(ParseState *pstate, JsonArrayAgg *agg); static Node *transformJsonIsPredicate(ParseState *pstate, JsonIsPredicate *pred); +static Node *transformJsonParseExpr(ParseState *pstate, JsonParseExpr *expr); +static Node *transformJsonScalarExpr(ParseState *pstate, JsonScalarExpr *expr); +static Node *transformJsonSerializeExpr(ParseState *pstate, + JsonSerializeExpr * expr); static Node *make_row_comparison_op(ParseState *pstate, List *opname, List *largs, List *rargs, int location); static Node *make_row_distinct_op(ParseState *pstate, List *opname, @@ -337,6 +341,18 @@ transformExprRecurse(ParseState *pstate, Node *expr) result = transformJsonIsPredicate(pstate, (JsonIsPredicate *) expr); break; + case T_JsonParseExpr: + result = transformJsonParseExpr(pstate, (JsonParseExpr *) expr); + break; + + case T_JsonScalarExpr: + result = transformJsonScalarExpr(pstate, (JsonScalarExpr *) expr); + break; + + case T_JsonSerializeExpr: + result = transformJsonSerializeExpr(pstate, (JsonSerializeExpr *) expr); + break; + default: /* should not reach here */ elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr)); @@ -3208,7 +3224,8 @@ makeJsonByteaToTextConversion(Node *expr, JsonFormat *format, int location) */ static Node * transformJsonValueExpr(ParseState *pstate, char *constructName, - JsonValueExpr *ve, JsonFormatType default_format) + JsonValueExpr *ve, JsonFormatType default_format, + Oid targettype) { Node *expr = transformExprRecurse(pstate, (Node *) ve->raw_expr); Node *rawexpr; @@ -3250,12 +3267,14 @@ transformJsonValueExpr(ParseState *pstate, char *constructName, else format = default_format; - if (format != JS_FORMAT_DEFAULT) + if (format != JS_FORMAT_DEFAULT || + (OidIsValid(targettype) && exprtype != targettype)) { - Oid targettype = format == JS_FORMAT_JSONB ? JSONBOID : JSONOID; Node *coerced; + bool cast_is_needed = OidIsValid(targettype); - if (exprtype != BYTEAOID && typcategory != TYPCATEGORY_STRING) + if (!cast_is_needed && + exprtype != BYTEAOID && typcategory != TYPCATEGORY_STRING) ereport(ERROR, errcode(ERRCODE_DATATYPE_MISMATCH), errmsg(ve->format->format_type == JS_FORMAT_DEFAULT ? @@ -3271,6 +3290,9 @@ transformJsonValueExpr(ParseState *pstate, char *constructName, exprtype = TEXTOID; } + if (!OidIsValid(targettype)) + targettype = format == JS_FORMAT_JSONB ? JSONBOID : JSONOID; + /* Try to coerce to the target type. */ coerced = coerce_to_target_type(pstate, expr, exprtype, targettype, -1, @@ -3281,11 +3303,20 @@ transformJsonValueExpr(ParseState *pstate, char *constructName, if (!coerced) { /* If coercion failed, use to_json()/to_jsonb() functions. */ - Oid fnoid = targettype == JSONOID ? F_TO_JSON : F_TO_JSONB; - FuncExpr *fexpr = makeFuncExpr(fnoid, targettype, - list_make1(expr), - InvalidOid, InvalidOid, - COERCE_EXPLICIT_CALL); + FuncExpr *fexpr; + Oid fnoid; + + if (cast_is_needed) /* only CAST is allowed */ + ereport(ERROR, + (errcode(ERRCODE_CANNOT_COERCE), + errmsg("cannot cast type %s to %s", + format_type_be(exprtype), + format_type_be(targettype)), + parser_errposition(pstate, location))); + + fnoid = targettype == JSONOID ? F_TO_JSON : F_TO_JSONB; + fexpr = makeFuncExpr(fnoid, targettype, list_make1(expr), + InvalidOid, InvalidOid, COERCE_EXPLICIT_CALL); fexpr->location = location; @@ -3582,7 +3613,8 @@ transformJsonObjectConstructor(ParseState *pstate, JsonObjectConstructor *ctor) Node *key = transformExprRecurse(pstate, (Node *) kv->key); Node *val = transformJsonValueExpr(pstate, "JSON_OBJECT()", kv->value, - JS_FORMAT_DEFAULT); + JS_FORMAT_DEFAULT, + InvalidOid); args = lappend(args, key); args = lappend(args, val); @@ -3766,9 +3798,8 @@ transformJsonObjectAgg(ParseState *pstate, JsonObjectAgg *agg) Oid aggtype; key = transformExprRecurse(pstate, (Node *) agg->arg->key); - val = transformJsonValueExpr(pstate, "JSON_OBJECTAGG()", - agg->arg->value, - JS_FORMAT_DEFAULT); + val = transformJsonValueExpr(pstate, "JSON_OBJECTAGG()", agg->arg->value, + JS_FORMAT_DEFAULT, InvalidOid); args = list_make2(key, val); returning = transformJsonConstructorOutput(pstate, agg->constructor->output, @@ -3826,7 +3857,7 @@ transformJsonArrayAgg(ParseState *pstate, JsonArrayAgg *agg) arg = transformJsonValueExpr(pstate, "JSON_ARRAYAGG()", agg->arg, - JS_FORMAT_DEFAULT); + JS_FORMAT_DEFAULT, InvalidOid); returning = transformJsonConstructorOutput(pstate, agg->constructor->output, list_make1(arg)); @@ -3874,7 +3905,8 @@ transformJsonArrayConstructor(ParseState *pstate, JsonArrayConstructor *ctor) JsonValueExpr *jsval = castNode(JsonValueExpr, lfirst(lc)); Node *val = transformJsonValueExpr(pstate, "JSON_ARRAY()", jsval, - JS_FORMAT_DEFAULT); + JS_FORMAT_DEFAULT, + InvalidOid); args = lappend(args, val); } @@ -3957,3 +3989,175 @@ transformJsonIsPredicate(ParseState *pstate, JsonIsPredicate *pred) return makeJsonIsPredicate(expr, NULL, pred->item_type, pred->unique_keys, pred->location); } + +/* + * Transform the output clause of a JSON_*() expression if there is one and + * create one if not. + */ +static JsonReturning * +transformJsonReturning(ParseState *pstate, JsonOutput *output, const char *fname) +{ + JsonReturning *returning; + + if (output) + { + returning = transformJsonOutput(pstate, output, false); + + Assert(OidIsValid(returning->typid)); + + if (returning->typid != JSONOID && returning->typid != JSONBOID) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("cannot use RETURNING type %s in %s", + format_type_be(returning->typid), fname), + parser_errposition(pstate, output->typeName->location))); + } + else + { + /* Output type is JSON by default. */ + Oid targettype = JSONOID; + JsonFormatType format = JS_FORMAT_JSON; + + returning = makeNode(JsonReturning); + returning->format = makeJsonFormat(format, JS_ENC_DEFAULT, -1); + returning->typid = targettype; + returning->typmod = -1; + } + + return returning; +} + +/* + * Transform a JSON() expression. + */ +static Node * +transformJsonParseExpr(ParseState *pstate, JsonParseExpr *jsexpr) +{ + JsonOutput *output = jsexpr->output; + JsonReturning *returning; + Node *arg; + + /* Disallow FORMAT specification in the RETURNING clause. */ + if (output) + { + JsonFormat *format = output->returning->format; + + if(format->format_type != JS_FORMAT_DEFAULT || + format->encoding != JS_ENC_DEFAULT) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("cannot specify FORMAT in RETURNING clause of JSON()"), + parser_errposition(pstate, format->location))); + } + + returning = transformJsonReturning(pstate, output, "JSON()"); + + if (jsexpr->unique_keys) + { + /* + * Coerce string argument to text and then to json[b] in the executor + * node with key uniqueness check. + */ + JsonValueExpr *jve = jsexpr->expr; + Oid arg_type; + + arg = transformJsonParseArg(pstate, (Node *) jve->raw_expr, jve->format, + &arg_type); + + if (arg_type != TEXTOID) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("cannot use non-string types with WITH UNIQUE KEYS clause"), + parser_errposition(pstate, jsexpr->location))); + } + else + { + /* + * Coerce argument to target type using CAST for compatibility with PG + * function-like CASTs. + */ + arg = transformJsonValueExpr(pstate, "JSON()", jsexpr->expr, + JS_FORMAT_JSON, returning->typid); + } + + return makeJsonConstructorExpr(pstate, JSCTOR_JSON_PARSE, list_make1(arg), NULL, + returning, jsexpr->unique_keys, false, + jsexpr->location); +} + +/* + * Transform a JSON_SCALAR() expression. + */ +static Node * +transformJsonScalarExpr(ParseState *pstate, JsonScalarExpr *jsexpr) +{ + Node *arg = transformExprRecurse(pstate, (Node *) jsexpr->expr); + JsonOutput *output = jsexpr->output; + JsonReturning *returning; + + /* Disallow FORMAT specification in the RETURNING clause. */ + if (output) + { + JsonFormat *format = output->returning->format; + + if(format->format_type != JS_FORMAT_DEFAULT || + format->encoding != JS_ENC_DEFAULT) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("cannot specify FORMAT in RETURNING clause of JSON_SCALAR()"), + parser_errposition(pstate, format->location))); + } + + returning = transformJsonReturning(pstate, output, "JSON_SCALAR()"); + + if (exprType(arg) == UNKNOWNOID) + arg = coerce_to_specific_type(pstate, arg, TEXTOID, "JSON_SCALAR"); + + return makeJsonConstructorExpr(pstate, JSCTOR_JSON_SCALAR, list_make1(arg), NULL, + returning, false, false, jsexpr->location); +} + +/* + * Transform a JSON_SERIALIZE() expression. + */ +static Node * +transformJsonSerializeExpr(ParseState *pstate, JsonSerializeExpr * expr) +{ + Node *arg = transformJsonValueExpr(pstate, "JSON_SERIALIZE()", + expr->expr, + JS_FORMAT_JSON, + InvalidOid); + JsonReturning *returning; + + if (expr->output) + { + returning = transformJsonOutput(pstate, expr->output, true); + + if (returning->typid != BYTEAOID) + { + char typcategory; + bool typispreferred; + + get_type_category_preferred(returning->typid, &typcategory, + &typispreferred); + if (typcategory != TYPCATEGORY_STRING) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("cannot use RETURNING type %s in %s", + format_type_be(returning->typid), + "JSON_SERIALIZE()"), + errhint("Try returning a string type or bytea."))); + } + } + else + { + /* RETURNING TEXT FORMAT JSON is by default */ + returning = makeNode(JsonReturning); + returning->format = makeJsonFormat(JS_FORMAT_JSON, JS_ENC_DEFAULT, -1); + returning->typid = TEXTOID; + returning->typmod = -1; + } + + return makeJsonConstructorExpr(pstate, JSCTOR_JSON_SERIALIZE, list_make1(arg), + NULL, returning, false, false, expr->location); +} diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 4cca97ff9c..520d4f2a23 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -1953,6 +1953,15 @@ FigureColnameInternal(Node *node, char **name) /* make XMLSERIALIZE act like a regular function */ *name = "xmlserialize"; return 2; + case T_JsonParseExpr: + *name = "json"; + return 2; + case T_JsonScalarExpr: + *name = "json_scalar"; + return 2; + case T_JsonSerializeExpr: + *name = "json_serialize"; + return 2; case T_JsonObjectConstructor: /* make JSON_OBJECT act like a regular function */ *name = "json_object"; diff --git a/src/backend/utils/adt/format_type.c b/src/backend/utils/adt/format_type.c index 12402a0637..36c45a3978 100644 --- a/src/backend/utils/adt/format_type.c +++ b/src/backend/utils/adt/format_type.c @@ -294,6 +294,10 @@ format_type_extended(Oid type_oid, int32 typemod, bits16 flags) else buf = pstrdup("character varying"); break; + + case JSONOID: + buf = pstrdup("json"); + break; } if (buf == NULL) diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c index f6bef9c148..c316f848e1 100644 --- a/src/backend/utils/adt/json.c +++ b/src/backend/utils/adt/json.c @@ -653,6 +653,20 @@ row_to_json_pretty(PG_FUNCTION_ARGS) PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data, result->len)); } +/* + * Note: exported for use in SQL/JSON executor functions, which cache the + * value of tcategory and outfuncoid. + */ +Datum +to_json_worker(Datum val, JsonTypeCategory tcategory, Oid outfuncoid) +{ + StringInfo result = makeStringInfo(); + + datum_to_json(val, false, result, tcategory, outfuncoid, false); + + return PointerGetDatum(cstring_to_text_with_len(result->data, result->len)); +} + /* * Is the given type immutable when coming out of a JSON context? * @@ -704,7 +718,6 @@ to_json(PG_FUNCTION_ARGS) { Datum val = PG_GETARG_DATUM(0); Oid val_type = get_fn_expr_argtype(fcinfo->flinfo, 0); - StringInfo result; JsonTypeCategory tcategory; Oid outfuncoid; @@ -716,11 +729,7 @@ to_json(PG_FUNCTION_ARGS) json_categorize_type(val_type, false, &tcategory, &outfuncoid); - result = makeStringInfo(); - - datum_to_json(val, false, result, tcategory, outfuncoid, false); - - PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data, result->len)); + PG_RETURN_DATUM(to_json_worker(val, tcategory, outfuncoid)); } /* diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index fc64f56868..06ba409e64 100644 --- a/src/backend/utils/adt/jsonb.c +++ b/src/backend/utils/adt/jsonb.c @@ -33,6 +33,7 @@ typedef struct JsonbInState { JsonbParseState *parseState; JsonbValue *res; + bool unique_keys; Node *escontext; } JsonbInState; @@ -45,7 +46,8 @@ typedef struct JsonbAggState Oid val_output_func; } JsonbAggState; -static inline Datum jsonb_from_cstring(char *json, int len, Node *escontext); +static inline Datum jsonb_from_cstring(char *json, int len, bool unique_keys, + Node *escontext); static bool checkStringLen(size_t len, Node *escontext); static JsonParseErrorType jsonb_in_object_start(void *pstate); static JsonParseErrorType jsonb_in_object_end(void *pstate); @@ -76,7 +78,7 @@ jsonb_in(PG_FUNCTION_ARGS) { char *json = PG_GETARG_CSTRING(0); - return jsonb_from_cstring(json, strlen(json), fcinfo->context); + return jsonb_from_cstring(json, strlen(json), false, fcinfo->context); } /* @@ -100,7 +102,7 @@ jsonb_recv(PG_FUNCTION_ARGS) else elog(ERROR, "unsupported jsonb version number %d", version); - return jsonb_from_cstring(str, nbytes, NULL); + return jsonb_from_cstring(str, nbytes, false, NULL); } /* @@ -141,6 +143,18 @@ jsonb_send(PG_FUNCTION_ARGS) PG_RETURN_BYTEA_P(pq_endtypsend(&buf)); } +/* + * Note: exported for use in SQL/JSON executor functions. + */ +Datum +jsonb_from_text(text *js, bool unique_keys) +{ + return jsonb_from_cstring(VARDATA_ANY(js), + VARSIZE_ANY_EXHDR(js), + unique_keys, + NULL); +} + /* * Get the type name of a jsonb container. */ @@ -234,7 +248,7 @@ jsonb_typeof(PG_FUNCTION_ARGS) * instead of being thrown. */ static inline Datum -jsonb_from_cstring(char *json, int len, Node *escontext) +jsonb_from_cstring(char *json, int len, bool unique_keys, Node *escontext) { JsonLexContext *lex; JsonbInState state; @@ -244,6 +258,7 @@ jsonb_from_cstring(char *json, int len, Node *escontext) memset(&sem, 0, sizeof(sem)); lex = makeJsonLexContextCstringLen(json, len, GetDatabaseEncoding(), true); + state.unique_keys = unique_keys; state.escontext = escontext; sem.semstate = (void *) &state; @@ -280,6 +295,7 @@ jsonb_in_object_start(void *pstate) JsonbInState *_state = (JsonbInState *) pstate; _state->res = pushJsonbValue(&_state->parseState, WJB_BEGIN_OBJECT, NULL); + _state->parseState->unique_keys = _state->unique_keys; return JSON_SUCCESS; } @@ -1021,6 +1037,23 @@ add_jsonb(Datum val, bool is_null, JsonbInState *result, datum_to_jsonb(val, is_null, result, tcategory, outfuncoid, key_scalar); } + +/* + * Note: exported for use in SQL/JSON executor functions, which cache the + * value of tcategory and outfuncoid. + */ +Datum +to_jsonb_worker(Datum val, JsonTypeCategory tcategory, Oid outfuncoid) +{ + JsonbInState result; + + memset(&result, 0, sizeof(JsonbInState)); + + datum_to_jsonb(val, false, &result, tcategory, outfuncoid, false); + + return JsonbPGetDatum(JsonbValueToJsonb(result.res)); +} + /* * Is the given type immutable when coming out of a JSONB context? * @@ -1072,7 +1105,6 @@ to_jsonb(PG_FUNCTION_ARGS) { Datum val = PG_GETARG_DATUM(0); Oid val_type = get_fn_expr_argtype(fcinfo->flinfo, 0); - JsonbInState result; JsonTypeCategory tcategory; Oid outfuncoid; @@ -1084,11 +1116,7 @@ to_jsonb(PG_FUNCTION_ARGS) json_categorize_type(val_type, true, &tcategory, &outfuncoid); - memset(&result, 0, sizeof(JsonbInState)); - - datum_to_jsonb(val, false, &result, tcategory, outfuncoid, false); - - PG_RETURN_POINTER(JsonbValueToJsonb(result.res)); + PG_RETURN_DATUM(to_jsonb_worker(val, tcategory, outfuncoid)); } Datum diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index d3a973d86b..d1b03d6cb2 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -10832,6 +10832,15 @@ get_json_constructor(JsonConstructorExpr *ctor, deparse_context *context, case JSCTOR_JSON_ARRAY: funcname = "JSON_ARRAY"; break; + case JSCTOR_JSON_PARSE: + funcname = "JSON"; + break; + case JSCTOR_JSON_SCALAR: + funcname = "JSON_SCALAR"; + break; + case JSCTOR_JSON_SERIALIZE: + funcname = "JSON_SERIALIZE"; + break; default: elog(ERROR, "invalid JsonConstructorType %d", ctor->type); } @@ -10879,7 +10888,10 @@ get_json_constructor_options(JsonConstructorExpr *ctor, StringInfo buf) if (ctor->unique) appendStringInfoString(buf, " WITH UNIQUE KEYS"); - get_json_returning(ctor->returning, buf, true); + if (!((ctor->type == JSCTOR_JSON_PARSE || + ctor->type == JSCTOR_JSON_SCALAR) && + ctor->returning->typid == JSONOID)) + get_json_returning(ctor->returning, buf, true); } /* diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index efb5c3e098..d926713bd9 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1716,6 +1716,17 @@ typedef struct TriggerTransition /* Nodes for SQL/JSON support */ +/* + * JsonQuotes - + * representation of [KEEP|OMIT] QUOTES clause for JSON_QUERY() + */ +typedef enum JsonQuotes +{ + JS_QUOTES_UNSPEC, /* unspecified */ + JS_QUOTES_KEEP, /* KEEP QUOTES */ + JS_QUOTES_OMIT /* OMIT QUOTES */ +} JsonQuotes; + /* * JsonOutput - * representation of JSON output clause (RETURNING type [FORMAT format]) @@ -1739,6 +1750,43 @@ typedef struct JsonKeyValue JsonValueExpr *value; /* JSON value expression */ } JsonKeyValue; +/* + * JsonParseExpr - + * untransformed representation of JSON() + */ +typedef struct JsonParseExpr +{ + NodeTag type; + JsonValueExpr *expr; /* string expression */ + JsonOutput *output; /* RETURNING clause, if specified */ + bool unique_keys; /* WITH UNIQUE KEYS? */ + int location; /* token location, or -1 if unknown */ +} JsonParseExpr; + +/* + * JsonScalarExpr - + * untransformed representation of JSON_SCALAR() + */ +typedef struct JsonScalarExpr +{ + NodeTag type; + Expr *expr; /* scalar expression */ + JsonOutput *output; /* RETURNING clause, if specified */ + int location; /* token location, or -1 if unknown */ +} JsonScalarExpr; + +/* + * JsonSerializeExpr - + * untransformed representation of JSON_SERIALIZE() function + */ +typedef struct JsonSerializeExpr +{ + NodeTag type; + JsonValueExpr *expr; /* json value expression */ + JsonOutput *output; /* RETURNING clause, if specified */ + int location; /* token location, or -1 if unknown */ +} JsonSerializeExpr; + /* * JsonObjectConstructor - * untransformed representation of JSON_OBJECT() constructor diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 0d2df069b3..85e484bf43 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1612,7 +1612,10 @@ typedef enum JsonConstructorType JSCTOR_JSON_OBJECT = 1, JSCTOR_JSON_ARRAY = 2, JSCTOR_JSON_OBJECTAGG = 3, - JSCTOR_JSON_ARRAYAGG = 4 + JSCTOR_JSON_ARRAYAGG = 4, + JSCTOR_JSON_SCALAR = 5, + JSCTOR_JSON_SERIALIZE = 6, + JSCTOR_JSON_PARSE = 7 } JsonConstructorType; /* diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f5b2e61ca5..5984dcfa4b 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -230,11 +230,13 @@ PG_KEYWORD("is", IS, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("isnull", ISNULL, TYPE_FUNC_NAME_KEYWORD, AS_LABEL) PG_KEYWORD("isolation", ISOLATION, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("join", JOIN, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL) -PG_KEYWORD("json", JSON, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("json", JSON, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("json_array", JSON_ARRAY, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("json_arrayagg", JSON_ARRAYAGG, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("json_object", JSON_OBJECT, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("json_objectagg", JSON_OBJECTAGG, COL_NAME_KEYWORD, BARE_LABEL) +PG_KEYWORD("json_scalar", JSON_SCALAR, COL_NAME_KEYWORD, BARE_LABEL) +PG_KEYWORD("json_serialize", JSON_SERIALIZE, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("key", KEY, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("keys", KEYS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("label", LABEL, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index 649a1644f2..f928e6142a 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -368,7 +368,6 @@ typedef struct JsonbIterator struct JsonbIterator *parent; } JsonbIterator; - /* Convenience macros */ static inline Jsonb * DatumGetJsonbP(Datum d) @@ -418,6 +417,7 @@ extern void JsonbHashScalarValueExtended(const JsonbValue *scalarVal, uint64 *hash, uint64 seed); /* jsonb.c support functions */ +extern Datum jsonb_from_text(text *js, bool unique_keys); extern char *JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len); extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in, diff --git a/src/include/utils/jsonfuncs.h b/src/include/utils/jsonfuncs.h index 27c2d20610..586d948c94 100644 --- a/src/include/utils/jsonfuncs.h +++ b/src/include/utils/jsonfuncs.h @@ -82,5 +82,9 @@ typedef enum void json_categorize_type(Oid typoid, bool is_jsonb, JsonTypeCategory *tcategory, Oid *outfuncoid); +extern Datum to_json_worker(Datum val, JsonTypeCategory tcategory, + Oid outfuncoid); +extern Datum to_jsonb_worker(Datum val, JsonTypeCategory tcategory, + Oid outfuncoid); #endif diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index d73c7e2c6c..ddea8a072f 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -1,3 +1,345 @@ +-- JSON() +SELECT JSON(); +ERROR: syntax error at or near ")" +LINE 1: SELECT JSON(); + ^ +SELECT JSON(NULL); + json +------ + +(1 row) + +SELECT JSON('{ "a" : 1 } '); + json +-------------- + { "a" : 1 } +(1 row) + +SELECT JSON('{ "a" : 1 } ' FORMAT JSON); + json +-------------- + { "a" : 1 } +(1 row) + +SELECT JSON('{ "a" : 1 } ' FORMAT JSON ENCODING UTF8); +ERROR: JSON ENCODING clause is only allowed for bytea input type +LINE 1: SELECT JSON('{ "a" : 1 } ' FORMAT JSON ENCODING UTF8); + ^ +SELECT JSON('{ "a" : 1 } '::bytea FORMAT JSON ENCODING UTF8); + json +-------------- + { "a" : 1 } +(1 row) + +SELECT pg_typeof(JSON('{ "a" : 1 } ')); + pg_typeof +----------- + json +(1 row) + +SELECT JSON(' 1 '::json); + json +--------- + 1 +(1 row) + +SELECT JSON(' 1 '::jsonb); + json +------ + 1 +(1 row) + +SELECT JSON(' 1 '::json WITH UNIQUE KEYS); +ERROR: cannot use non-string types with WITH UNIQUE KEYS clause +LINE 1: SELECT JSON(' 1 '::json WITH UNIQUE KEYS); + ^ +SELECT JSON(123); +ERROR: cannot cast type integer to json +LINE 1: SELECT JSON(123); + ^ +SELECT JSON('{"a": 1, "a": 2}'); + json +------------------ + {"a": 1, "a": 2} +(1 row) + +SELECT JSON('{"a": 1, "a": 2}' WITH UNIQUE KEYS); +ERROR: duplicate JSON object key value +SELECT JSON('{"a": 1, "a": 2}' WITHOUT UNIQUE KEYS); + json +------------------ + {"a": 1, "a": 2} +(1 row) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'); + QUERY PLAN +----------------------------- + Result + Output: JSON('123'::json) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' FORMAT JSON); + QUERY PLAN +----------------------------- + Result + Output: JSON('123'::json) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON); + QUERY PLAN +----------------------------------------------- + Result + Output: JSON('\x313233'::bytea FORMAT JSON) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON ENCODING UTF8); + QUERY PLAN +------------------------------------------------------------- + Result + Output: JSON('\x313233'::bytea FORMAT JSON ENCODING UTF8) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITH UNIQUE KEYS); + QUERY PLAN +---------------------------------------------- + Result + Output: JSON('123'::text WITH UNIQUE KEYS) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITHOUT UNIQUE KEYS); + QUERY PLAN +----------------------------- + Result + Output: JSON('123'::json) +(2 rows) + +SELECT JSON('123' RETURNING text); +ERROR: cannot use RETURNING type text in JSON() +LINE 1: SELECT JSON('123' RETURNING text); + ^ +SELECT JSON('123' RETURNING text FORMAT JSON); -- RETURNING FORMAT not allowed +ERROR: cannot specify FORMAT in RETURNING clause of JSON() +LINE 1: SELECT JSON('123' RETURNING text FORMAT JSON); + ^ +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'); + QUERY PLAN +----------------------------- + Result + Output: JSON('123'::json) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING json); + QUERY PLAN +----------------------------- + Result + Output: JSON('123'::json) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING jsonb); + QUERY PLAN +---------------------------------------------- + Result + Output: JSON('123'::jsonb RETURNING jsonb) +(2 rows) + +SELECT pg_typeof(JSON('123')); + pg_typeof +----------- + json +(1 row) + +SELECT pg_typeof(JSON('123' RETURNING json)); + pg_typeof +----------- + json +(1 row) + +SELECT pg_typeof(JSON('123' RETURNING jsonb)); + pg_typeof +----------- + jsonb +(1 row) + +-- JSON_SCALAR() +SELECT JSON_SCALAR(); +ERROR: syntax error at or near ")" +LINE 1: SELECT JSON_SCALAR(); + ^ +SELECT JSON_SCALAR(NULL); + json_scalar +------------- + +(1 row) + +SELECT JSON_SCALAR(NULL::int); + json_scalar +------------- + +(1 row) + +SELECT JSON_SCALAR(123); + json_scalar +------------- + 123 +(1 row) + +SELECT JSON_SCALAR(123.45); + json_scalar +------------- + 123.45 +(1 row) + +SELECT JSON_SCALAR(123.45::numeric); + json_scalar +------------- + 123.45 +(1 row) + +SELECT JSON_SCALAR(true); + json_scalar +------------- + true +(1 row) + +SELECT JSON_SCALAR(false); + json_scalar +------------- + false +(1 row) + +SELECT JSON_SCALAR(' 123.45'); + json_scalar +------------- + " 123.45" +(1 row) + +SELECT JSON_SCALAR('2020-06-07'::date); + json_scalar +-------------- + "2020-06-07" +(1 row) + +SELECT JSON_SCALAR('2020-06-07 01:02:03'::timestamp); + json_scalar +----------------------- + "2020-06-07T01:02:03" +(1 row) + +SELECT JSON_SCALAR('{}'::json); + json_scalar +------------- + {} +(1 row) + +SELECT JSON_SCALAR('{}'::jsonb); + json_scalar +------------- + {} +(1 row) + +SELECT JSON_SCALAR(123 RETURNING jsonb FORMAT JSON ENCODING UTF8); -- RETURNING FORMAT not allowed +ERROR: cannot specify FORMAT in RETURNING clause of JSON_SCALAR() +LINE 1: SELECT JSON_SCALAR(123 RETURNING jsonb FORMAT JSON ENCODING ... + ^ +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123); + QUERY PLAN +---------------------------- + Result + Output: JSON_SCALAR(123) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR('123'); + QUERY PLAN +------------------------------------ + Result + Output: JSON_SCALAR('123'::text) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING json); + QUERY PLAN +---------------------------- + Result + Output: JSON_SCALAR(123) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING jsonb); + QUERY PLAN +-------------------------------------------- + Result + Output: JSON_SCALAR(123 RETURNING jsonb) +(2 rows) + +-- JSON_SERIALIZE() +SELECT JSON_SERIALIZE(); +ERROR: syntax error at or near ")" +LINE 1: SELECT JSON_SERIALIZE(); + ^ +SELECT JSON_SERIALIZE(NULL); + json_serialize +---------------- + +(1 row) + +SELECT JSON_SERIALIZE(JSON('{ "a" : 1 } ')); + json_serialize +---------------- + { "a" : 1 } +(1 row) + +SELECT JSON_SERIALIZE('{ "a" : 1 } '); + json_serialize +---------------- + { "a" : 1 } +(1 row) + +SELECT JSON_SERIALIZE('1'); + json_serialize +---------------- + 1 +(1 row) + +SELECT JSON_SERIALIZE('1' FORMAT JSON); + json_serialize +---------------- + 1 +(1 row) + +SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING bytea); + json_serialize +---------------------------- + \x7b20226122203a2031207d20 +(1 row) + +SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar); + json_serialize +---------------- + { "a" : 1 } +(1 row) + +SELECT pg_typeof(JSON_SERIALIZE(NULL)); + pg_typeof +----------- + text +(1 row) + +-- only string types or bytea allowed +SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING jsonb); +ERROR: cannot use RETURNING type jsonb in JSON_SERIALIZE() +HINT: Try returning a string type or bytea. +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}'); + QUERY PLAN +----------------------------------------------------- + Result + Output: JSON_SERIALIZE('{}'::json RETURNING text) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}' RETURNING bytea); + QUERY PLAN +------------------------------------------------------ + Result + Output: JSON_SERIALIZE('{}'::json RETURNING bytea) +(2 rows) + -- JSON_OBJECT() SELECT JSON_OBJECT(); json_object @@ -630,6 +972,13 @@ ERROR: duplicate JSON object key SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb) FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v); ERROR: duplicate JSON object key +SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb) +FROM (VALUES (1, 1), (0, NULL),(4, null), (5, null),(6, null),(2, 2)) foo(k, v); + json_objectagg +------------------ + {"1": 1, "2": 2} +(1 row) + -- Test JSON_OBJECT deparsing EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json); @@ -645,6 +994,41 @@ SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json); CREATE OR REPLACE VIEW public.json_object_view AS SELECT JSON_OBJECT('foo' : '1'::text FORMAT JSON, 'bar' : 'baz'::text RETURNING json) AS "json_object" DROP VIEW json_object_view; +SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k) +FROM (VALUES (1,1), (2,2)) a(k,v); + a | json_objectagg +---------------+---------------------- + {"k":1,"v":1} | { "1" : 1 } + {"k":2,"v":2} | { "1" : 1, "2" : 2 } +(2 rows) + +SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k) +FROM (VALUES (1,1), (1,2), (2,2)) a(k,v); +ERROR: duplicate JSON key "1" +SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL WITH UNIQUE KEYS) + OVER (ORDER BY k) +FROM (VALUES (1,1), (1,null), (2,2)) a(k,v); +ERROR: duplicate JSON key "1" +SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL) +OVER (ORDER BY k) +FROM (VALUES (1,1), (1,null), (2,2)) a(k,v); + a | json_objectagg +------------------+---------------------- + {"k":1,"v":1} | { "1" : 1 } + {"k":1,"v":null} | { "1" : 1 } + {"k":2,"v":2} | { "1" : 1, "2" : 2 } +(3 rows) + +SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL) +OVER (ORDER BY k RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +FROM (VALUES (1,1), (1,null), (2,2)) a(k,v); + a | json_objectagg +------------------+---------------------- + {"k":1,"v":1} | { "1" : 1, "2" : 2 } + {"k":1,"v":null} | { "1" : 1, "2" : 2 } + {"k":2,"v":2} | { "1" : 1, "2" : 2 } +(3 rows) + -- Test JSON_ARRAY deparsing EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_ARRAY('1' FORMAT JSON, 2 RETURNING json); diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql index 4fd820fd51..f9afae0d42 100644 --- a/src/test/regress/sql/sqljson.sql +++ b/src/test/regress/sql/sqljson.sql @@ -1,3 +1,77 @@ +-- JSON() +SELECT JSON(); +SELECT JSON(NULL); +SELECT JSON('{ "a" : 1 } '); +SELECT JSON('{ "a" : 1 } ' FORMAT JSON); +SELECT JSON('{ "a" : 1 } ' FORMAT JSON ENCODING UTF8); +SELECT JSON('{ "a" : 1 } '::bytea FORMAT JSON ENCODING UTF8); +SELECT pg_typeof(JSON('{ "a" : 1 } ')); + +SELECT JSON(' 1 '::json); +SELECT JSON(' 1 '::jsonb); +SELECT JSON(' 1 '::json WITH UNIQUE KEYS); +SELECT JSON(123); + +SELECT JSON('{"a": 1, "a": 2}'); +SELECT JSON('{"a": 1, "a": 2}' WITH UNIQUE KEYS); +SELECT JSON('{"a": 1, "a": 2}' WITHOUT UNIQUE KEYS); + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' FORMAT JSON); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON ENCODING UTF8); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITH UNIQUE KEYS); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITHOUT UNIQUE KEYS); + +SELECT JSON('123' RETURNING text); +SELECT JSON('123' RETURNING text FORMAT JSON); -- RETURNING FORMAT not allowed + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING json); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING jsonb); +SELECT pg_typeof(JSON('123')); +SELECT pg_typeof(JSON('123' RETURNING json)); +SELECT pg_typeof(JSON('123' RETURNING jsonb)); + +-- JSON_SCALAR() +SELECT JSON_SCALAR(); +SELECT JSON_SCALAR(NULL); +SELECT JSON_SCALAR(NULL::int); +SELECT JSON_SCALAR(123); +SELECT JSON_SCALAR(123.45); +SELECT JSON_SCALAR(123.45::numeric); +SELECT JSON_SCALAR(true); +SELECT JSON_SCALAR(false); +SELECT JSON_SCALAR(' 123.45'); +SELECT JSON_SCALAR('2020-06-07'::date); +SELECT JSON_SCALAR('2020-06-07 01:02:03'::timestamp); +SELECT JSON_SCALAR('{}'::json); +SELECT JSON_SCALAR('{}'::jsonb); +SELECT JSON_SCALAR(123 RETURNING jsonb FORMAT JSON ENCODING UTF8); -- RETURNING FORMAT not allowed + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR('123'); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING json); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING jsonb); + +-- JSON_SERIALIZE() +SELECT JSON_SERIALIZE(); +SELECT JSON_SERIALIZE(NULL); +SELECT JSON_SERIALIZE(JSON('{ "a" : 1 } ')); +SELECT JSON_SERIALIZE('{ "a" : 1 } '); +SELECT JSON_SERIALIZE('1'); +SELECT JSON_SERIALIZE('1' FORMAT JSON); +SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING bytea); +SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar); +SELECT pg_typeof(JSON_SERIALIZE(NULL)); + +-- only string types or bytea allowed +SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING jsonb); + + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}'); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}' RETURNING bytea); + -- JSON_OBJECT() SELECT JSON_OBJECT(); SELECT JSON_OBJECT(RETURNING json); @@ -216,6 +290,9 @@ FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v); SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb) FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v); +SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb) +FROM (VALUES (1, 1), (0, NULL),(4, null), (5, null),(6, null),(2, 2)) foo(k, v); + -- Test JSON_OBJECT deparsing EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json); @@ -227,6 +304,24 @@ SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json); DROP VIEW json_object_view; +SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k) +FROM (VALUES (1,1), (2,2)) a(k,v); + +SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k) +FROM (VALUES (1,1), (1,2), (2,2)) a(k,v); + +SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL WITH UNIQUE KEYS) + OVER (ORDER BY k) +FROM (VALUES (1,1), (1,null), (2,2)) a(k,v); + +SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL) +OVER (ORDER BY k) +FROM (VALUES (1,1), (1,null), (2,2)) a(k,v); + +SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL) +OVER (ORDER BY k RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +FROM (VALUES (1,1), (1,null), (2,2)) a(k,v); + -- Test JSON_ARRAY deparsing EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_ARRAY('1' FORMAT JSON, 2 RETURNING json); diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index b10590a252..d251fb9a91 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1296,6 +1296,7 @@ JsonPathPredicateCallback JsonPathString JsonReturning JsonSemAction +JsonSerializeExpr JsonTokenType JsonTransformStringValuesAction JsonTypeCategory -- 2.35.3