From 03734a7fed7d924679770adb78a7db8a37d14188 Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Thu, 20 Jul 2023 22:21:43 +0900 Subject: [PATCH] Add more SQL/JSON constructor functions MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit 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 Author: Teodor Sigaev Author: Oleg Bartunov Author: Alexander Korotkov Author: Andrew Dunstan Author: Amit Langote 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 --- doc/src/sgml/func.sgml | 66 ++++ src/backend/executor/execExpr.c | 30 ++ src/backend/executor/execExprInterp.c | 41 +++ src/backend/nodes/nodeFuncs.c | 30 ++ src/backend/parser/gram.y | 49 ++- src/backend/parser/parse_expr.c | 225 +++++++++++- src/backend/parser/parse_target.c | 12 + src/backend/utils/adt/format_type.c | 4 + src/backend/utils/adt/jsonb.c | 15 +- src/backend/utils/adt/ruleutils.c | 16 +- src/include/catalog/catversion.h | 2 +- src/include/nodes/parsenodes.h | 37 ++ src/include/nodes/primnodes.h | 5 +- src/include/parser/kwlist.h | 4 +- src/include/utils/jsonfuncs.h | 2 +- .../ecpg/test/expected/sql-sqljson.c | 204 ++++++++++- .../ecpg/test/expected/sql-sqljson.stderr | 201 ++++++++++- .../ecpg/test/expected/sql-sqljson.stdout | 16 + src/interfaces/ecpg/test/sql/sqljson.pgc | 60 ++++ src/test/regress/expected/sqljson.out | 332 ++++++++++++++++++ src/test/regress/sql/sqljson.sql | 85 +++++ src/tools/pgindent/typedefs.list | 3 + 22 files changed, 1397 insertions(+), 42 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index b94827674c..dcc9d6f59d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -16001,6 +16001,72 @@ table2-mapping {"a": "1", "b": "2"} + + + + json constructor + json ( + expression + FORMAT JSON ENCODING UTF8 + { WITH | WITHOUT } UNIQUE KEYS + + + Converts a given expression specified as text or + bytea string (in UTF8 encoding) into a JSON + value. If 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_scalar + json_scalar (expression) + + + Converts a given SQL scalar value into a JSON scalar value. + If the input is NULL, an SQL null is returned. If + the input is 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 ) + + + Converts an SQL/JSON expression 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 used in RETURNING 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..2c62b0c9c8 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 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..24c2b60c62 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -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 = datum_to_jsonb(value, category, outfuncid); + else + res = datum_to_json(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 503d76aae0..c03f4f23e2 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -3899,6 +3899,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 e7134add11..856d5dee0e 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 @@ -723,6 +723,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 @@ -13990,6 +13991,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 @@ -14008,6 +14010,7 @@ ConstTypename: | ConstBit { $$ = $1; } | ConstCharacter { $$ = $1; } | ConstDatetime { $$ = $1; } + | JsonType { $$ = $1; } ; /* @@ -14376,6 +14379,13 @@ interval_second: } ; +JsonType: + JSON + { + $$ = SystemTypeName("json"); + $$->location = @1; + } + ; /***************************************************************************** * @@ -15634,7 +15644,36 @@ func_expr_common_subexpr: n->location = @1; $$ = (Node *) n; } - ; + | JSON '(' json_value_expr json_key_uniqueness_constraint_opt ')' + { + JsonParseExpr *n = makeNode(JsonParseExpr); + + n->expr = (JsonValueExpr *) $3; + n->unique_keys = $4; + n->output = NULL; + n->location = @1; + $$ = (Node *) n; + } + | JSON_SCALAR '(' a_expr ')' + { + JsonScalarExpr *n = makeNode(JsonScalarExpr); + + n->expr = (Expr *) $3; + n->output = NULL; + 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 @@ -17075,7 +17114,6 @@ unreserved_keyword: | INSTEAD | INVOKER | ISOLATION - | JSON | KEY | KEYS | LABEL @@ -17290,10 +17328,13 @@ col_name_keyword: | INT_P | INTEGER | INTERVAL + | JSON | JSON_ARRAY | JSON_ARRAYAGG | JSON_OBJECT | JSON_OBJECTAGG + | JSON_SCALAR + | JSON_SERIALIZE | LEAST | NATIONAL | NCHAR @@ -17654,6 +17695,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 c08c06373a..fed8e4d089 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)); @@ -3204,15 +3220,16 @@ makeJsonByteaToTextConversion(Node *expr, JsonFormat *format, int location) /* * Transform JSON value expression using specified input JSON format or - * default format otherwise. + * default format otherwise, coercing to the targettype if needed. * * Returned expression is either ve->raw_expr coerced to text (if needed) or * a JsonValueExpr with formatted_expr set to the coerced copy of raw_expr - * if the specified format requires it. + * if the specified format and the targettype requires it. */ static Node * transformJsonValueExpr(ParseState *pstate, const char *constructName, - JsonValueExpr *ve, JsonFormatType default_format) + JsonValueExpr *ve, JsonFormatType default_format, + Oid targettype) { Node *expr = transformExprRecurse(pstate, (Node *) ve->raw_expr); Node *rawexpr; @@ -3254,12 +3271,14 @@ transformJsonValueExpr(ParseState *pstate, const 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 only_allow_cast = OidIsValid(targettype); - if (exprtype != BYTEAOID && typcategory != TYPCATEGORY_STRING) + if (!only_allow_cast && + exprtype != BYTEAOID && typcategory != TYPCATEGORY_STRING) ereport(ERROR, errcode(ERRCODE_DATATYPE_MISMATCH), errmsg(ve->format->format_type == JS_FORMAT_DEFAULT ? @@ -3275,6 +3294,9 @@ transformJsonValueExpr(ParseState *pstate, const 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, @@ -3285,11 +3307,24 @@ transformJsonValueExpr(ParseState *pstate, const 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; + + /* + * Though only allow a cast when the target type is specified by + * the caller. + */ + if (only_allow_cast) + 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; @@ -3590,7 +3625,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); @@ -3776,7 +3812,8 @@ transformJsonObjectAgg(ParseState *pstate, JsonObjectAgg *agg) key = transformExprRecurse(pstate, (Node *) agg->arg->key); val = transformJsonValueExpr(pstate, "JSON_OBJECTAGG()", agg->arg->value, - JS_FORMAT_DEFAULT); + JS_FORMAT_DEFAULT, + InvalidOid); args = list_make2(key, val); returning = transformJsonConstructorOutput(pstate, agg->constructor->output, @@ -3834,7 +3871,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)); @@ -3882,7 +3919,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); } @@ -3963,3 +4001,160 @@ transformJsonIsPredicate(ParseState *pstate, JsonIsPredicate *pred) return makeJsonIsPredicate(expr, NULL, pred->item_type, pred->unique_keys, pred->location); } + +/* + * Transform the RETURNING 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. + * + * JSON() is transformed into a JsonConstructorExpr of type JSCTOR_JSON_PARSE, + * which validates the input expression value as JSON. + */ +static Node * +transformJsonParseExpr(ParseState *pstate, JsonParseExpr *jsexpr) +{ + JsonOutput *output = jsexpr->output; + JsonReturning *returning; + Node *arg; + + 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. + * + * JSON_SCALAR() is transformed into a JsonConstructorExpr of type + * JSCTOR_JSON_SCALAR, which converts the input SQL scalar value into + * a json[b] value. + */ +static Node * +transformJsonScalarExpr(ParseState *pstate, JsonScalarExpr *jsexpr) +{ + Node *arg = transformExprRecurse(pstate, (Node *) jsexpr->expr); + JsonOutput *output = jsexpr->output; + JsonReturning *returning; + + 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. + * + * JSON_SERIALIZE() is transformed into a JsonConstructorExpr of type + * JSCTOR_JSON_SERIALIZE which converts the input JSON value into a character + * or bytea string. + */ +static Node * +transformJsonSerializeExpr(ParseState *pstate, JsonSerializeExpr *expr) +{ + JsonReturning *returning; + Node *arg = transformJsonValueExpr(pstate, "JSON_SERIALIZE()", + expr->expr, + JS_FORMAT_JSON, + InvalidOid); + + 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..57247de363 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -1953,6 +1953,18 @@ FigureColnameInternal(Node *node, char **name) /* make XMLSERIALIZE act like a regular function */ *name = "xmlserialize"; return 2; + case T_JsonParseExpr: + /* make JSON act like a regular function */ + *name = "json"; + return 2; + case T_JsonScalarExpr: + /* make JSON_SCALAR act like a regular function */ + *name = "json_scalar"; + return 2; + case T_JsonSerializeExpr: + /* make JSON_SERIALIZE act like a regular function */ + *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/jsonb.c b/src/backend/utils/adt/jsonb.c index 5ea582a888..9781852b0c 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); } /* @@ -147,10 +149,11 @@ jsonb_send(PG_FUNCTION_ARGS) * Turns json text string into a jsonb Datum. */ Datum -jsonb_from_text(text *js) +jsonb_from_text(text *js, bool unique_keys) { return jsonb_from_cstring(VARDATA_ANY(js), VARSIZE_ANY_EXHDR(js), + unique_keys, NULL); } @@ -247,7 +250,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; @@ -257,6 +260,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; @@ -293,6 +297,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; } diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index fcb2f45f62..03f2835c3f 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,12 @@ get_json_constructor_options(JsonConstructorExpr *ctor, StringInfo buf) if (ctor->unique) appendStringInfoString(buf, " WITH UNIQUE KEYS"); - get_json_returning(ctor->returning, buf, true); + /* + * Append RETURNING clause if needed; JSON() and JSON_SCALAR() don't + * support one. + */ + if (ctor->type != JSCTOR_JSON_PARSE && ctor->type != JSCTOR_JSON_SCALAR) + get_json_returning(ctor->returning, buf, true); } /* diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index d5969e6aea..f507b49bb2 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202307111 +#define CATALOG_VERSION_NO 202307261 #endif diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index efb5c3e098..228cdca0f1 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1739,6 +1739,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 e1aadc39cf..60d72a876b 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1613,7 +1613,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_PARSE = 5, + JSCTOR_JSON_SCALAR = 6, + JSCTOR_JSON_SERIALIZE = 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/jsonfuncs.h b/src/include/utils/jsonfuncs.h index 2ad648d1b8..c677ac8ff7 100644 --- a/src/include/utils/jsonfuncs.h +++ b/src/include/utils/jsonfuncs.h @@ -86,6 +86,6 @@ extern Datum datum_to_json(Datum val, JsonTypeCategory tcategory, Oid outfuncoid); extern Datum datum_to_jsonb(Datum val, JsonTypeCategory tcategory, Oid outfuncoid); -extern Datum jsonb_from_text(text *js); +extern Datum jsonb_from_text(text *js, bool unique_keys); #endif diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.c b/src/interfaces/ecpg/test/expected/sql-sqljson.c index a2c49b54f9..39221f9ea5 100644 --- a/src/interfaces/ecpg/test/expected/sql-sqljson.c +++ b/src/interfaces/ecpg/test/expected/sql-sqljson.c @@ -196,6 +196,202 @@ if (sqlca.sqlcode < 0) sqlprint();} printf("Found json=%s\n", json); + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( null )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 42 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 42 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( '{ \"a\" : 1 } ' format json )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 45 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 45 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( '{ \"a\" : 1 } ' format json encoding UTF8 )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 48 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 48 "sqljson.pgc" + + // error + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ' 1 ' :: jsonb )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 51 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 51 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ' 1 ' :: json with unique keys ) into json", ECPGt_EOIT, ECPGt_EORT); +#line 54 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 54 "sqljson.pgc" + + // error + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( '{\"a\": 1, \"a\": 2}' )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 57 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 57 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( '{\"a\": 1, \"a\": 2}' with unique keys )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 60 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 60 "sqljson.pgc" + + // error + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_scalar ( null )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 63 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 63 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_scalar ( null :: int )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 66 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 66 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_scalar ( 123.45 )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 69 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 69 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_scalar ( true )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 72 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 72 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_scalar ( ' 123.45' )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 75 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 75 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_scalar ( '2020-06-07 01:02:03' :: timestamp )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 78 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 78 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_scalar ( '{}' :: jsonb )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 81 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 81 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_serialize ( null )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 84 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 84 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_serialize ( json ( '{ \"a\" : 1 } ' ) )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 87 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 87 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_serialize ( '{ \"a\" : 1 } ' )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 90 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 90 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_serialize ( '1' format json )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 93 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 93 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_serialize ( '{ \"a\" : 1 } ' returning varchar )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 96 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 96 "sqljson.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_serialize ( '{ \"a\" : 1 } ' returning jsonb )", ECPGt_EOIT, ECPGt_EORT); +#line 99 "sqljson.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 99 "sqljson.pgc" + + // error + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "with val ( js ) as ( values ( '{ \"a\": 1, \"b\": [{ \"a\": 1, \"b\": 0, \"a\": 2 }] }' ) ) select js is json \"IS JSON\" , js is not json \"IS NOT JSON\" , js is json value \"IS VALUE\" , js is json object \"IS OBJECT\" , js is json array \"IS ARRAY\" , js is json scalar \"IS SCALAR\" , js is json without unique keys \"WITHOUT UNIQUE\" , js is json with unique keys \"WITH UNIQUE\" from val", ECPGt_EOIT, ECPGt_bool,&(is_json[0]),(long)1,(long)1,sizeof(bool), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, @@ -213,19 +409,19 @@ if (sqlca.sqlcode < 0) sqlprint();} ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_bool,&(is_json[7]),(long)1,(long)1,sizeof(bool), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); -#line 54 "sqljson.pgc" +#line 114 "sqljson.pgc" if (sqlca.sqlcode < 0) sqlprint();} -#line 54 "sqljson.pgc" +#line 114 "sqljson.pgc" for (int i = 0; i < sizeof(is_json); i++) printf("Found is_json[%d]: %s\n", i, is_json[i] ? "true" : "false"); { ECPGdisconnect(__LINE__, "CURRENT"); -#line 58 "sqljson.pgc" +#line 118 "sqljson.pgc" if (sqlca.sqlcode < 0) sqlprint();} -#line 58 "sqljson.pgc" +#line 118 "sqljson.pgc" return 0; diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr index 1252cb3b66..37a361156f 100644 --- a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr +++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr @@ -65,27 +65,208 @@ SQL error: duplicate JSON key "1" on line 33 [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ecpg_get_data on line 39: RESULT: {"1": 1} offset: -1; array: no [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ecpg_execute on line 42: query: with val ( js ) as ( values ( '{ "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] }' ) ) select js is json "IS JSON" , js is not json "IS NOT JSON" , js is json value "IS VALUE" , js is json object "IS OBJECT" , js is json array "IS ARRAY" , js is json scalar "IS SCALAR" , js is json without unique keys "WITHOUT UNIQUE" , js is json with unique keys "WITH UNIQUE" from val; with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: ecpg_execute on line 42: query: select json ( null ); with 0 parameter(s) on connection ecpg1_regression [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ecpg_execute on line 42: using PQexec [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ecpg_process_output on line 42: correctly got 1 tuples with 8 fields +[NO_PID]: ecpg_process_output on line 42: correctly got 1 tuples with 1 fields [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ecpg_get_data on line 42: RESULT: t offset: -1; array: no +[NO_PID]: ecpg_get_data on line 42: RESULT: offset: -1; array: no [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ecpg_get_data on line 42: RESULT: f offset: -1; array: no +[NO_PID]: raising sqlcode -213 on line 42: null value without indicator on line 42 +[NO_PID]: sqlca: code: -213, state: 22002 +SQL error: null value without indicator on line 42 +[NO_PID]: ecpg_execute on line 45: query: select json ( '{ "a" : 1 } ' format json ); with 0 parameter(s) on connection ecpg1_regression [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ecpg_get_data on line 42: RESULT: t offset: -1; array: no +[NO_PID]: ecpg_execute on line 45: using PQexec [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ecpg_get_data on line 42: RESULT: t offset: -1; array: no +[NO_PID]: ecpg_process_output on line 45: correctly got 1 tuples with 1 fields [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ecpg_get_data on line 42: RESULT: f offset: -1; array: no +[NO_PID]: ecpg_get_data on line 45: RESULT: { "a" : 1 } offset: -1; array: no [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ecpg_get_data on line 42: RESULT: f offset: -1; array: no +[NO_PID]: ecpg_execute on line 48: query: select json ( '{ "a" : 1 } ' format json encoding UTF8 ); with 0 parameter(s) on connection ecpg1_regression [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ecpg_get_data on line 42: RESULT: t offset: -1; array: no +[NO_PID]: ecpg_execute on line 48: using PQexec [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ecpg_get_data on line 42: RESULT: f offset: -1; array: no +[NO_PID]: ecpg_check_PQresult on line 48: bad response - ERROR: JSON ENCODING clause is only allowed for bytea input type +LINE 1: select json ( '{ "a" : 1 } ' format json encoding UTF8 ) + ^ +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: raising sqlstate 42804 (sqlcode -400): JSON ENCODING clause is only allowed for bytea input type on line 48 +[NO_PID]: sqlca: code: -400, state: 42804 +SQL error: JSON ENCODING clause is only allowed for bytea input type on line 48 +[NO_PID]: ecpg_execute on line 51: query: select json ( ' 1 ' :: jsonb ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 51: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 51: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 51: RESULT: 1 offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 54: query: select json ( ' 1 ' :: json with unique keys ) into json; with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 54: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_check_PQresult on line 54: bad response - ERROR: cannot use non-string types with WITH UNIQUE KEYS clause +LINE 1: select json ( ' 1 ' :: json with unique keys ) into json + ^ +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: raising sqlstate 42804 (sqlcode -400): cannot use non-string types with WITH UNIQUE KEYS clause on line 54 +[NO_PID]: sqlca: code: -400, state: 42804 +SQL error: cannot use non-string types with WITH UNIQUE KEYS clause on line 54 +[NO_PID]: ecpg_execute on line 57: query: select json ( '{"a": 1, "a": 2}' ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 57: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 57: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 57: RESULT: {"a": 1, "a": 2} offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 60: query: select json ( '{"a": 1, "a": 2}' with unique keys ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 60: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_check_PQresult on line 60: bad response - ERROR: duplicate JSON object key value +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: raising sqlstate 22030 (sqlcode -400): duplicate JSON object key value on line 60 +[NO_PID]: sqlca: code: -400, state: 22030 +SQL error: duplicate JSON object key value on line 60 +[NO_PID]: ecpg_execute on line 63: query: select json_scalar ( null ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 63: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 63: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 63: RESULT: offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: raising sqlcode -213 on line 63: null value without indicator on line 63 +[NO_PID]: sqlca: code: -213, state: 22002 +SQL error: null value without indicator on line 63 +[NO_PID]: ecpg_execute on line 66: query: select json_scalar ( null :: int ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 66: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 66: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 66: RESULT: offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: raising sqlcode -213 on line 66: null value without indicator on line 66 +[NO_PID]: sqlca: code: -213, state: 22002 +SQL error: null value without indicator on line 66 +[NO_PID]: ecpg_execute on line 69: query: select json_scalar ( 123.45 ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 69: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 69: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 69: RESULT: 123.45 offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 72: query: select json_scalar ( true ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 72: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 72: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 72: RESULT: true offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 75: query: select json_scalar ( ' 123.45' ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 75: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 75: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 75: RESULT: " 123.45" offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 78: query: select json_scalar ( '2020-06-07 01:02:03' :: timestamp ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 78: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 78: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 78: RESULT: "2020-06-07T01:02:03" offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 81: query: select json_scalar ( '{}' :: jsonb ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 81: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 81: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 81: RESULT: {} offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 84: query: select json_serialize ( null ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 84: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 84: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 84: RESULT: offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: raising sqlcode -213 on line 84: null value without indicator on line 84 +[NO_PID]: sqlca: code: -213, state: 22002 +SQL error: null value without indicator on line 84 +[NO_PID]: ecpg_execute on line 87: query: select json_serialize ( json ( '{ "a" : 1 } ' ) ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 87: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 87: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 87: RESULT: { "a" : 1 } offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 90: query: select json_serialize ( '{ "a" : 1 } ' ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 90: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 90: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 90: RESULT: { "a" : 1 } offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 93: query: select json_serialize ( '1' format json ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 93: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 93: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 93: RESULT: 1 offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 96: query: select json_serialize ( '{ "a" : 1 } ' returning varchar ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 96: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 96: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 96: RESULT: { "a" : 1 } offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 99: query: select json_serialize ( '{ "a" : 1 } ' returning jsonb ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 99: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_check_PQresult on line 99: bad response - ERROR: cannot use RETURNING type jsonb in JSON_SERIALIZE() +HINT: Try returning a string type or bytea. +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: raising sqlstate 42804 (sqlcode -400): cannot use RETURNING type jsonb in JSON_SERIALIZE() on line 99 +[NO_PID]: sqlca: code: -400, state: 42804 +SQL error: cannot use RETURNING type jsonb in JSON_SERIALIZE() on line 99 +[NO_PID]: ecpg_execute on line 102: query: with val ( js ) as ( values ( '{ "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] }' ) ) select js is json "IS JSON" , js is not json "IS NOT JSON" , js is json value "IS VALUE" , js is json object "IS OBJECT" , js is json array "IS ARRAY" , js is json scalar "IS SCALAR" , js is json without unique keys "WITHOUT UNIQUE" , js is json with unique keys "WITH UNIQUE" from val; with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 102: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 102: correctly got 1 tuples with 8 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 102: RESULT: t offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 102: RESULT: f offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 102: RESULT: t offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 102: RESULT: t offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 102: RESULT: f offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 102: RESULT: f offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 102: RESULT: t offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 102: RESULT: f offset: -1; array: no [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ecpg_finish: connection ecpg1_regression closed [NO_PID]: sqlca: code: 0, state: 00000 diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout index 558901c406..83f8df13e5 100644 --- a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout +++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout @@ -4,6 +4,22 @@ Found json=[] Found json=[] Found json={"1" : 1, "1" : "2"} Found json={"1": 1} +Found json={"1": 1} +Found json={ "a" : 1 } +Found json=1 +Found json={"a": 1, "a": 2} +Found json={"a": 1, "a": 2} +Found json={"a": 1, "a": 2} +Found json=123.45 +Found json=true +Found json=" 123.45" +Found json="2020-06-07T01:02:03" +Found json={} +Found json={} +Found json={ "a" : 1 } +Found json={ "a" : 1 } +Found json=1 +Found json={ "a" : 1 } Found is_json[0]: true Found is_json[1]: false Found is_json[2]: true diff --git a/src/interfaces/ecpg/test/sql/sqljson.pgc b/src/interfaces/ecpg/test/sql/sqljson.pgc index a005503834..ddcbcc3b3c 100644 --- a/src/interfaces/ecpg/test/sql/sqljson.pgc +++ b/src/interfaces/ecpg/test/sql/sqljson.pgc @@ -39,6 +39,66 @@ EXEC SQL END DECLARE SECTION; EXEC SQL SELECT JSON_OBJECT(1: 1, '2': NULL ABSENT ON NULL WITHOUT UNIQUE RETURNING jsonb) INTO :json; printf("Found json=%s\n", json); + EXEC SQL SELECT JSON(NULL) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON('{ "a" : 1 } ' FORMAT JSON) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON('{ "a" : 1 } ' FORMAT JSON ENCODING UTF8) INTO :json; + // error + + EXEC SQL SELECT JSON(' 1 '::jsonb) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON(' 1 '::json WITH UNIQUE KEYS) INTO json; + // error + + EXEC SQL SELECT JSON('{"a": 1, "a": 2}') INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON('{"a": 1, "a": 2}' WITH UNIQUE KEYS) INTO :json; + // error + + EXEC SQL SELECT JSON_SCALAR(NULL) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SCALAR(NULL::int) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SCALAR(123.45) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SCALAR(true) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SCALAR(' 123.45') INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SCALAR('2020-06-07 01:02:03'::timestamp) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SCALAR('{}'::jsonb) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SERIALIZE(NULL) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SERIALIZE(JSON('{ "a" : 1 } ')) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SERIALIZE('{ "a" : 1 } ') INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SERIALIZE('1' FORMAT JSON) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING jsonb); + // error + EXEC SQL WITH val (js) AS (VALUES ('{ "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] }')) SELECT js IS JSON "IS JSON", diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index d73c7e2c6c..d5074d73a2 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -1,3 +1,293 @@ +-- 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) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'); + QUERY PLAN +----------------------------- + Result + Output: JSON('123'::json) +(2 rows) + +SELECT pg_typeof(JSON('123')); + pg_typeof +----------- + json +(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) + +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) + +-- 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 +920,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 +942,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..e6e20175b0 100644 --- a/src/test/regress/sql/sqljson.sql +++ b/src/test/regress/sql/sqljson.sql @@ -1,3 +1,67 @@ +-- 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); + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'); +SELECT pg_typeof(JSON('123')); + +-- 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); + +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123); +EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR('123'); + +-- 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 +280,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 +294,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 05814136c6..11d47294cf 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1274,6 +1274,7 @@ JsonManifestWALRangeField JsonObjectAgg JsonObjectConstructor JsonOutput +JsonParseExpr JsonParseContext JsonParseErrorType JsonPath @@ -1295,7 +1296,9 @@ JsonPathParseResult JsonPathPredicateCallback JsonPathString JsonReturning +JsonScalarExpr JsonSemAction +JsonSerializeExpr JsonTokenType JsonTransformStringValuesAction JsonTypeCategory -- 2.39.5