diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 894cea8..c562447 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -4080,17 +4080,21 @@ ExecEvalExprPassingCaseValue(ExprState *estate, ExprContext *econtext, */ static Datum ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior, - ExprState *default_estate, bool *is_null) + ExprState *default_estate, bool is_jsonb, bool *is_null) { *is_null = false; switch (behavior->btype) { case JSON_BEHAVIOR_EMPTY_ARRAY: - return JsonbPGetDatum(JsonbMakeEmptyArray()); + return is_jsonb + ? JsonbPGetDatum(JsonbMakeEmptyArray()) + : PointerGetDatum(cstring_to_text("[]")); case JSON_BEHAVIOR_EMPTY_OBJECT: - return JsonbPGetDatum(JsonbMakeEmptyObject()); + return is_jsonb + ? JsonbPGetDatum(JsonbMakeEmptyObject()) + : PointerGetDatum(cstring_to_text("{}")); case JSON_BEHAVIOR_TRUE: return BoolGetDatum(true); @@ -4117,17 +4121,20 @@ ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior, */ static Datum ExecEvalJsonExprCoercion(ExprEvalStep *op, ExprContext *econtext, - Datum res, bool *isNull) + Datum res, bool *isNull, bool isJsonb) { JsonExpr *jexpr = op->d.jsonexpr.jsexpr; JsonCoercion *coercion = jexpr->result_coercion; - Jsonb *jb = *isNull ? NULL : DatumGetJsonbP(res); + Jsonb *jb = *isNull || !isJsonb ? NULL : DatumGetJsonbP(res); + Json *js = *isNull || isJsonb ? NULL : DatumGetJsonP(res); if ((coercion && coercion->via_io) || - (jexpr->omit_quotes && !*isNull && JB_ROOT_IS_SCALAR(jb))) + (jexpr->omit_quotes && !*isNull && + (isJsonb ? JB_ROOT_IS_SCALAR(jb) : JsonContainerIsScalar(&js->root)))) { /* strip quotes and call typinput function */ - char *str = *isNull ? NULL : JsonbUnquote(jb); + char *str = *isNull ? NULL : + (isJsonb ? JsonbUnquote(jb) : JsonUnquote(js)); res = InputFunctionCall(&op->d.jsonexpr.input.func, str, op->d.jsonexpr.input.typioparam, @@ -4137,7 +4144,7 @@ ExecEvalJsonExprCoercion(ExprEvalStep *op, ExprContext *econtext, res = ExecEvalExprPassingCaseValue(op->d.jsonexpr.result_expr, econtext, isNull, res, *isNull); else if (coercion && coercion->via_populate) - res = json_populate_type(res, JSONBOID, + res = json_populate_type(res, isJsonb ? JSONBOID : JSONOID, jexpr->returning.typid, jexpr->returning.typmod, &op->d.jsonexpr.cache, @@ -4171,7 +4178,7 @@ EvalJsonPathVar(void *cxt, bool *isnull) * corresponding SQL type and a pointer to the coercion state. */ Datum -ExecPrepareJsonItemCoercion(JsonbValue *item, +ExecPrepareJsonItemCoercion(JsonbValue *item, bool is_jsonb, JsonReturning *returning, struct JsonCoercionsState *coercions, struct JsonCoercionState **pcoercion) @@ -4180,8 +4187,14 @@ ExecPrepareJsonItemCoercion(JsonbValue *item, Datum res; JsonbValue jbvbuf; - if (item->type == jbvBinary && JsonContainerIsScalar(item->val.binary.data)) - item = JsonbExtractScalar(item->val.binary.data, &jbvbuf); + if (item->type == jbvBinary) + { + if (JsonContainerIsScalar(item->val.binary.data)) + item = is_jsonb + ? JsonbExtractScalar(item->val.binary.data, &jbvbuf) + : JsonExtractScalar((JsonContainer *) item->val.binary.data, + &jbvbuf); + } /* get coercion state reference and datum of the corresponding SQL type */ switch (item->type) @@ -4238,7 +4251,18 @@ ExecPrepareJsonItemCoercion(JsonbValue *item, case jbvObject: case jbvBinary: coercion = &coercions->composite; - res = JsonbPGetDatum(JsonbValueToJsonb(item)); + if (is_jsonb) + { + Jsonb *jb = JsonbValueToJsonb(item); + + res = JsonbPGetDatum(jb); + } + else + { + Json *js = JsonbValueToJson(item); + + res = JsonPGetDatum(js); + } break; default: @@ -4253,7 +4277,8 @@ ExecPrepareJsonItemCoercion(JsonbValue *item, static Datum ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext, - JsonExpr *jexpr, JsonPath *path, Datum item, bool *resnull) + JsonExpr *jexpr, JsonPath *path, Datum item, bool isjsonb, + bool *resnull) { bool empty = false; Datum res = (Datum) 0; @@ -4267,7 +4292,8 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext, if (isnull) { /* execute domain checks for NULLs */ - (void) ExecEvalJsonExprCoercion(op, econtext, res, resnull); + (void) ExecEvalJsonExprCoercion(op, econtext, res, resnull, + isjsonb); *resnull = true; return (Datum) 0; } @@ -4276,15 +4302,15 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext, switch (jexpr->op) { case IS_JSON_QUERY: - res = JsonbPathQuery(item, path, jexpr->wrapper, &empty, - op->d.jsonexpr.args); + res = (isjsonb ? JsonbPathQuery : JsonPathQuery) + (item, path, jexpr->wrapper, &empty, op->d.jsonexpr.args); *resnull = !DatumGetPointer(res); break; case IS_JSON_VALUE: { - JsonbValue *jbv = JsonbPathValue(item, path, &empty, - op->d.jsonexpr.args); + JsonbValue *jbv = (isjsonb ? JsonbPathValue : JsonPathValue) + (item, path, &empty, op->d.jsonexpr.args); struct JsonCoercionState *jcstate; if (!jbv) @@ -4292,7 +4318,7 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext, *resnull = false; - res = ExecPrepareJsonItemCoercion(jbv, + res = ExecPrepareJsonItemCoercion(jbv, isjsonb, &op->d.jsonexpr.jsexpr->returning, &op->d.jsonexpr.coercions, &jcstate); @@ -4305,8 +4331,11 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext, jexpr->returning.typid == JSONBOID) { /* use coercion via I/O from json[b] to the output type */ - res = JsonbPGetDatum(JsonbValueToJsonb(jbv)); - res = ExecEvalJsonExprCoercion(op, econtext, res, resnull); + res = isjsonb + ? JsonbPGetDatum(JsonbValueToJsonb(jbv)) + : JsonPGetDatum(JsonbValueToJson(jbv)); + res = ExecEvalJsonExprCoercion(op, econtext, res, + resnull, isjsonb); } else if (jcstate->estate) { @@ -4320,7 +4349,8 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext, break; case IS_JSON_EXISTS: - res = BoolGetDatum(JsonbPathExists(item, path, op->d.jsonexpr.args)); + res = BoolGetDatum((isjsonb ? JsonbPathExists : JsonPathExists) + (item, path, op->d.jsonexpr.args)); *resnull = false; break; @@ -4339,14 +4369,15 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext, /* execute ON EMPTY behavior */ res = ExecEvalJsonBehavior(econtext, &jexpr->on_empty, - op->d.jsonexpr.default_on_empty, resnull); + op->d.jsonexpr.default_on_empty, + isjsonb, resnull); } if (jexpr->op != IS_JSON_EXISTS && (!empty ? jexpr->op != IS_JSON_VALUE : /* result is already coerced in DEFAULT behavior case */ jexpr->on_empty.btype != JSON_BEHAVIOR_DEFAULT)) - res = ExecEvalJsonExprCoercion(op, econtext, res, resnull); + res = ExecEvalJsonExprCoercion(op, econtext, res, resnull, isjsonb); return res; } @@ -4363,6 +4394,10 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext) Datum res = (Datum) 0; JsonPath *path; ListCell *lc; + Oid formattedType = exprType(jexpr->formatted_expr ? + jexpr->formatted_expr : + jexpr->raw_expr); + bool isjsonb = formattedType == JSONBOID; *op->resnull = true; /* until we get a result */ *op->resvalue = (Datum) 0; @@ -4370,7 +4405,7 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext) if (op->d.jsonexpr.raw_expr->isnull || op->d.jsonexpr.pathspec->isnull) { /* execute domain checks for NULLs */ - (void) ExecEvalJsonExprCoercion(op, econtext, res, op->resnull); + (void) ExecEvalJsonExprCoercion(op, econtext, res, op->resnull, isjsonb); Assert(*op->resnull); *op->resnull = true; @@ -4393,7 +4428,7 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext) if (jexpr->on_error.btype == JSON_BEHAVIOR_ERROR) { /* No need to use PG_TRY/PG_CATCH with subtransactions. */ - res = ExecEvalJsonExpr(state, op, econtext, jexpr, path, item, + res = ExecEvalJsonExpr(state, op, econtext, jexpr, path, item, isjsonb, op->resnull); } else @@ -4432,7 +4467,7 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext) PG_TRY(); { res = ExecEvalJsonExpr(state, op, newecontext, jexpr, path, item, - op->resnull); + isjsonb, op->resnull); if (useSubTransaction) { @@ -4485,12 +4520,13 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext) /* Execute ON ERROR behavior. */ res = ExecEvalJsonBehavior(econtext, &jexpr->on_error, op->d.jsonexpr.default_on_error, - op->resnull); + isjsonb, op->resnull); if (jexpr->op != IS_JSON_EXISTS && /* result is already coerced in DEFAULT behavior case */ jexpr->on_error.btype != JSON_BEHAVIOR_DEFAULT) - res = ExecEvalJsonExprCoercion(op, econtext, res, op->resnull); + res = ExecEvalJsonExprCoercion(op, econtext, res, op->resnull, + isjsonb); } PG_END_TRY(); } diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index e15dab2..9d54ca4 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4674,13 +4674,10 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) JsonExpr *jsexpr = transformJsonExprCommon(pstate, func); Node *contextItemExpr = jsexpr->formatted_expr ? jsexpr->formatted_expr : jsexpr->raw_expr; - const char *func_name = NULL; switch (func->op) { case IS_JSON_VALUE: - func_name = "JSON_VALUE"; - transformJsonFuncExprOutput(pstate, func, jsexpr); jsexpr->returning.format.type = JS_FORMAT_DEFAULT; @@ -4701,8 +4698,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) break; case IS_JSON_QUERY: - func_name = "JSON_QUERY"; - transformJsonFuncExprOutput(pstate, func, jsexpr); jsexpr->wrapper = func->wrapper; @@ -4711,8 +4706,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) break; case IS_JSON_EXISTS: - func_name = "JSON_EXISTS"; - jsexpr->returning.format.type = JS_FORMAT_DEFAULT; jsexpr->returning.format.encoding = JS_ENC_DEFAULT; jsexpr->returning.format.location = -1; @@ -4722,11 +4715,5 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) break; } - if (exprType(contextItemExpr) != JSONBOID) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("%s() is not yet implemented for json type", func_name), - parser_errposition(pstate, func->location))); - return (Node *) jsexpr; } diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index 0ce2e40..a5e0853 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -781,7 +781,7 @@ extern void ExecEvalWholeRowVar(ExprState *state, ExprEvalStep *op, ExprContext *econtext); extern void ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext); -extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item, +extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item, bool is_jsonb, JsonReturning *returning, struct JsonCoercionsState *coercions, struct JsonCoercionState **pjcstate); diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h index 61cf2b7..4e24bd1 100644 --- a/src/include/utils/jsonpath.h +++ b/src/include/utils/jsonpath.h @@ -284,6 +284,12 @@ extern Datum JsonbPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, extern JsonbValue *JsonbPathValue(Datum jb, JsonPath *jp, bool *empty, List *vars); +extern bool JsonPathExists(Datum json, JsonPath *path, List *vars); +extern JsonbValue *JsonPathValue(Datum json, JsonPath *jp, bool *empty, + List *vars); +extern Datum JsonPathQuery(Datum json, JsonPath *jp, JsonWrapper wrapper, + bool *empty, List *vars); + extern Datum EvalJsonPathVar(void *cxt, bool *isnull); #endif diff --git a/src/test/regress/expected/json_sqljson.out b/src/test/regress/expected/json_sqljson.out index bb62634..0deee40 100644 --- a/src/test/regress/expected/json_sqljson.out +++ b/src/test/regress/expected/json_sqljson.out @@ -1,15 +1,1028 @@ -- JSON_EXISTS SELECT JSON_EXISTS(NULL FORMAT JSON, '$'); -ERROR: JSON_EXISTS() is not yet implemented for json type -LINE 1: SELECT JSON_EXISTS(NULL FORMAT JSON, '$'); - ^ + ?column? +---------- + +(1 row) + +SELECT JSON_EXISTS(NULL::text FORMAT JSON, '$'); + ?column? +---------- + +(1 row) + +SELECT JSON_EXISTS(NULL::bytea FORMAT JSON, '$'); + ?column? +---------- + +(1 row) + +SELECT JSON_EXISTS(NULL::json FORMAT JSON, '$'); +WARNING: FORMAT JSON has no effect for json and jsonb types + ?column? +---------- + +(1 row) + +SELECT JSON_EXISTS(NULL::jsonb FORMAT JSON, '$'); +WARNING: FORMAT JSON has no effect for json and jsonb types + ?column? +---------- + +(1 row) + +SELECT JSON_EXISTS(NULL::json, '$'); + ?column? +---------- + +(1 row) + +SELECT JSON_EXISTS('' FORMAT JSON, '$'); + ?column? +---------- + f +(1 row) + +SELECT JSON_EXISTS('' FORMAT JSON, '$' TRUE ON ERROR); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS('' FORMAT JSON, '$' FALSE ON ERROR); + ?column? +---------- + f +(1 row) + +SELECT JSON_EXISTS('' FORMAT JSON, '$' UNKNOWN ON ERROR); + ?column? +---------- + +(1 row) + +SELECT JSON_EXISTS('' FORMAT JSON, '$' ERROR ON ERROR); +ERROR: invalid input syntax for type json +DETAIL: The input string ended unexpectedly. +CONTEXT: JSON data, line 1: +SELECT JSON_EXISTS(bytea '' FORMAT JSON, '$' ERROR ON ERROR); +ERROR: invalid input syntax for type json +DETAIL: The input string ended unexpectedly. +CONTEXT: JSON data, line 1: +SELECT JSON_EXISTS(json '[]', '$'); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS('[]' FORMAT JSON, '$'); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS(JSON_OBJECT(RETURNING bytea FORMAT JSON) FORMAT JSON, '$'); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS(json '1', '$'); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS(json 'null', '$'); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS(json '[]', '$'); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS(json '1', '$.a'); + ?column? +---------- + f +(1 row) + +SELECT JSON_EXISTS(json '1', 'strict $.a'); + ?column? +---------- + f +(1 row) + +SELECT JSON_EXISTS(json '1', 'strict $.a' ERROR ON ERROR); +ERROR: SQL/JSON member not found +SELECT JSON_EXISTS(json 'null', '$.a'); + ?column? +---------- + f +(1 row) + +SELECT JSON_EXISTS(json '[]', '$.a'); + ?column? +---------- + f +(1 row) + +SELECT JSON_EXISTS(json '[1, "aaa", {"a": 1}]', 'strict $.a'); + ?column? +---------- + f +(1 row) + +SELECT JSON_EXISTS(json '[1, "aaa", {"a": 1}]', 'lax $.a'); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS(json '{}', '$.a'); + ?column? +---------- + f +(1 row) + +SELECT JSON_EXISTS(json '{"b": 1, "a": 2}', '$.a'); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS(json '1', '$.a.b'); + ?column? +---------- + f +(1 row) + +SELECT JSON_EXISTS(json '{"a": {"b": 1}}', '$.a.b'); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.a.b'); + ?column? +---------- + f +(1 row) + +SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING 1 AS x); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING '1' AS x); + ?column? +---------- + f +(1 row) + +SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 2 AS y); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 1 AS y); + ?column? +---------- + f +(1 row) + +-- extension: boolean expressions +SELECT JSON_EXISTS(json '1', '$ > 2'); + ?column? +---------- + t +(1 row) + +SELECT JSON_EXISTS(json '1', '$.a > 2' ERROR ON ERROR); + ?column? +---------- + t +(1 row) + -- JSON_VALUE +SELECT JSON_VALUE(NULL, '$'); + ?column? +---------- + +(1 row) + SELECT JSON_VALUE(NULL FORMAT JSON, '$'); -ERROR: JSON_VALUE() is not yet implemented for json type -LINE 1: SELECT JSON_VALUE(NULL FORMAT JSON, '$'); - ^ + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(NULL::text, '$'); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(NULL::bytea, '$'); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(NULL::json, '$'); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(NULL::jsonb FORMAT JSON, '$'); +WARNING: FORMAT JSON has no effect for json and jsonb types + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE('' FORMAT JSON, '$'); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE('' FORMAT JSON, '$' NULL ON ERROR); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE('' FORMAT JSON, '$' DEFAULT '"default value"' ON ERROR); + ?column? +----------------- + "default value" +(1 row) + +SELECT JSON_VALUE('' FORMAT JSON, '$' ERROR ON ERROR); +ERROR: invalid input syntax for type json +DETAIL: The input string ended unexpectedly. +CONTEXT: JSON data, line 1: +SELECT JSON_VALUE(json 'null', '$'); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(json 'null', '$' RETURNING int); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(json 'true', '$'); + ?column? +---------- + true +(1 row) + +SELECT JSON_VALUE(json 'true', '$' RETURNING bool); + ?column? +---------- + t +(1 row) + +SELECT JSON_VALUE(json '123', '$'); + ?column? +---------- + 123 +(1 row) + +SELECT JSON_VALUE(json '123', '$' RETURNING int) + 234; + ?column? +---------- + 357 +(1 row) + +SELECT JSON_VALUE(json '123', '$' RETURNING text); + ?column? +---------- + 123 +(1 row) + +/* jsonb bytea ??? */ +SELECT JSON_VALUE(json '123', '$' RETURNING bytea); + ?column? +---------- + \x313233 +(1 row) + +SELECT JSON_VALUE(json '1.23', '$'); + ?column? +---------- + 1.23 +(1 row) + +SELECT JSON_VALUE(json '1.23', '$' RETURNING int); + ?column? +---------- + 1 +(1 row) + +SELECT JSON_VALUE(json '"1.23"', '$' RETURNING numeric); + ?column? +---------- + 1.23 +(1 row) + +SELECT JSON_VALUE(json '"1.23"', '$' RETURNING int ERROR ON ERROR); +ERROR: invalid input syntax for integer: "1.23" +SELECT JSON_VALUE(json '"aaa"', '$'); + ?column? +---------- + aaa +(1 row) + +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING text); + ?column? +---------- + aaa +(1 row) + +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING char(5)); + ?column? +---------- + aaa +(1 row) + +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING char(2)); + ?column? +---------- + aa +(1 row) + +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING json); + ?column? +---------- + "aaa" +(1 row) + +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING jsonb); + ?column? +---------- + "aaa" +(1 row) + +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING json ERROR ON ERROR); + ?column? +---------- + "aaa" +(1 row) + +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING jsonb ERROR ON ERROR); + ?column? +---------- + "aaa" +(1 row) + +SELECT JSON_VALUE(json '"\"aaa\""', '$' RETURNING json); + ?column? +----------- + "\"aaa\"" +(1 row) + +SELECT JSON_VALUE(json '"\"aaa\""', '$' RETURNING jsonb); + ?column? +----------- + "\"aaa\"" +(1 row) + +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING int); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING int ERROR ON ERROR); +ERROR: invalid input syntax for integer: "aaa" +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING int DEFAULT 111 ON ERROR); + ?column? +---------- + 111 +(1 row) + +SELECT JSON_VALUE(json '"123"', '$' RETURNING int) + 234; + ?column? +---------- + 357 +(1 row) + +SELECT JSON_VALUE(json '"2017-02-20"', '$' RETURNING date) + 9; + ?column? +------------ + 03-01-2017 +(1 row) + +-- Test NULL checks execution in domain types +CREATE DOMAIN sqljson_int_not_null AS int NOT NULL; +SELECT JSON_VALUE(json '1', '$.a' RETURNING sqljson_int_not_null); +ERROR: domain sqljson_int_not_null does not allow null values +SELECT JSON_VALUE(json '1', '$.a' RETURNING sqljson_int_not_null NULL ON ERROR); +ERROR: domain sqljson_int_not_null does not allow null values +SELECT JSON_VALUE(json '1', '$.a' RETURNING sqljson_int_not_null DEFAULT NULL ON ERROR); +ERROR: domain sqljson_int_not_null does not allow null values +SELECT JSON_VALUE(json '[]', '$'); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(json '[]', '$' ERROR ON ERROR); +ERROR: SQL/JSON scalar required +SELECT JSON_VALUE(json '{}', '$'); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(json '{}', '$' ERROR ON ERROR); +ERROR: SQL/JSON scalar required +SELECT JSON_VALUE(json '1', '$.a'); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(json '1', 'strict $.a' ERROR ON ERROR); +ERROR: SQL/JSON member not found +SELECT JSON_VALUE(json '1', 'strict $.a' DEFAULT 'error' ON ERROR); + ?column? +---------- + error +(1 row) + +SELECT JSON_VALUE(json '1', 'lax $.a' ERROR ON ERROR); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(json '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR); +ERROR: no SQL/JSON item +SELECT JSON_VALUE(json '1', 'strict $.a' DEFAULT 2 ON ERROR); + ?column? +---------- + 2 +(1 row) + +SELECT JSON_VALUE(json '1', 'lax $.a' DEFAULT 2 ON ERROR); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(json '1', 'lax $.a' DEFAULT '2' ON ERROR); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(json '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR); + ?column? +---------- + +(1 row) + +SELECT JSON_VALUE(json '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR); + ?column? +---------- + 2 +(1 row) + +SELECT JSON_VALUE(json '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR); + ?column? +---------- + 3 +(1 row) + +SELECT JSON_VALUE(json '[1,2]', '$[*]' ERROR ON ERROR); +ERROR: more than one SQL/JSON item +SELECT JSON_VALUE(json '[1,2]', '$[*]' DEFAULT '0' ON ERROR); + ?column? +---------- + 0 +(1 row) + +SELECT JSON_VALUE(json '[" "]', '$[*]' RETURNING int ERROR ON ERROR); +ERROR: invalid input syntax for integer: " " +SELECT JSON_VALUE(json '[" "]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR); + ?column? +---------- + 5 +(1 row) + +SELECT JSON_VALUE(json '["1"]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR); + ?column? +---------- + 1 +(1 row) + +SELECT + x, + JSON_VALUE( + json '{"a": 1, "b": 2}', + '$.* ? (@ > $x)' PASSING x AS x + RETURNING int + DEFAULT -1 ON EMPTY + DEFAULT -2 ON ERROR + ) y +FROM + generate_series(0, 2) x; + x | y +---+---- + 0 | -2 + 1 | 2 + 2 | -1 +(3 rows) + +SELECT JSON_VALUE(json 'null', '$a' PASSING point ' (1, 2 )' AS a); + ?column? +---------- + (1,2) +(1 row) + +SELECT JSON_VALUE(json 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point); + ?column? +---------- + (1,2) +(1 row) + -- JSON_QUERY -SELECT JSON_QUERY(NULL FORMAT JSON, '$'); -ERROR: JSON_QUERY() is not yet implemented for json type -LINE 1: SELECT JSON_QUERY(NULL FORMAT JSON, '$'); - ^ +SELECT + JSON_QUERY(js FORMAT JSON, '$'), + JSON_QUERY(js FORMAT JSON, '$' WITHOUT WRAPPER), + JSON_QUERY(js FORMAT JSON, '$' WITH CONDITIONAL WRAPPER), + JSON_QUERY(js FORMAT JSON, '$' WITH UNCONDITIONAL ARRAY WRAPPER), + JSON_QUERY(js FORMAT JSON, '$' WITH ARRAY WRAPPER) +FROM + (VALUES + ('null'), + ('12.3'), + ('true'), + ('"aaa"'), + ('[1, null, "2"]'), + ('{"a": 1, "b": [2]}') + ) foo(js); + ?column? | ?column? | ?column? | ?column? | ?column? +--------------------+--------------------+--------------------+----------------------+---------------------- + null | null | [null] | [null] | [null] + 12.3 | 12.3 | [12.3] | [12.3] | [12.3] + true | true | [true] | [true] | [true] + "aaa" | "aaa" | ["aaa"] | ["aaa"] | ["aaa"] + [1, null, "2"] | [1, null, "2"] | [1, null, "2"] | [[1, null, "2"]] | [[1, null, "2"]] + {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | [{"a": 1, "b": [2]}] | [{"a": 1, "b": [2]}] +(6 rows) + +SELECT + JSON_QUERY(js FORMAT JSON, 'strict $[*]') AS "unspec", + JSON_QUERY(js FORMAT JSON, 'strict $[*]' WITHOUT WRAPPER) AS "without", + JSON_QUERY(js FORMAT JSON, 'strict $[*]' WITH CONDITIONAL WRAPPER) AS "with cond", + JSON_QUERY(js FORMAT JSON, 'strict $[*]' WITH UNCONDITIONAL ARRAY WRAPPER) AS "with uncond", + JSON_QUERY(js FORMAT JSON, 'strict $[*]' WITH ARRAY WRAPPER) AS "with" +FROM + (VALUES + ('1'), + ('[]'), + ('[null]'), + ('[12.3]'), + ('[true]'), + ('["aaa"]'), + ('[[1, 2, 3]]'), + ('[{"a": 1, "b": [2]}]'), + ('[1, "2", null, [3]]') + ) foo(js); + unspec | without | with cond | with uncond | with +--------------------+--------------------+---------------------+----------------------+---------------------- + | | | | + | | | | + null | null | [null] | [null] | [null] + 12.3 | 12.3 | [12.3] | [12.3] | [12.3] + true | true | [true] | [true] | [true] + "aaa" | "aaa" | ["aaa"] | ["aaa"] | ["aaa"] + [1, 2, 3] | [1, 2, 3] | [1, 2, 3] | [[1, 2, 3]] | [[1, 2, 3]] + {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | [{"a": 1, "b": [2]}] | [{"a": 1, "b": [2]}] + | | [1, "2", null, [3]] | [1, "2", null, [3]] | [1, "2", null, [3]] +(9 rows) + +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text); + ?column? +---------- + "aaa" +(1 row) + +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text KEEP QUOTES); + ?column? +---------- + "aaa" +(1 row) + +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text KEEP QUOTES ON SCALAR STRING); + ?column? +---------- + "aaa" +(1 row) + +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text OMIT QUOTES); + ?column? +---------- + aaa +(1 row) + +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text OMIT QUOTES ON SCALAR STRING); + ?column? +---------- + aaa +(1 row) + +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' OMIT QUOTES ERROR ON ERROR); +ERROR: invalid input syntax for type json +DETAIL: Token "aaa" is invalid. +CONTEXT: JSON data, line 1: aaa +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING json OMIT QUOTES ERROR ON ERROR); +ERROR: invalid input syntax for type json +DETAIL: Token "aaa" is invalid. +CONTEXT: JSON data, line 1: aaa +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING bytea FORMAT JSON OMIT QUOTES ERROR ON ERROR); + ?column? +---------- + \x616161 +(1 row) + +-- QUOTES behavior should not be specified when WITH WRAPPER used: +-- Should fail +SELECT JSON_QUERY(json '[1]', '$' WITH WRAPPER OMIT QUOTES); +ERROR: SQL/JSON QUOTES behavior shall not be specified when WITH WRAPPER is used +LINE 1: SELECT JSON_QUERY(json '[1]', '$' WITH WRAPPER OMIT QUOTES); + ^ +SELECT JSON_QUERY(json '[1]', '$' WITH WRAPPER KEEP QUOTES); +ERROR: SQL/JSON QUOTES behavior shall not be specified when WITH WRAPPER is used +LINE 1: SELECT JSON_QUERY(json '[1]', '$' WITH WRAPPER KEEP QUOTES); + ^ +SELECT JSON_QUERY(json '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTES); +ERROR: SQL/JSON QUOTES behavior shall not be specified when WITH WRAPPER is used +LINE 1: ...ON_QUERY(json '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTE... + ^ +SELECT JSON_QUERY(json '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTES); +ERROR: SQL/JSON QUOTES behavior shall not be specified when WITH WRAPPER is used +LINE 1: ...ON_QUERY(json '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTE... + ^ +-- Should succeed +SELECT JSON_QUERY(json '[1]', '$' WITHOUT WRAPPER OMIT QUOTES); + ?column? +---------- + [1] +(1 row) + +SELECT JSON_QUERY(json '[1]', '$' WITHOUT WRAPPER KEEP QUOTES); + ?column? +---------- + [1] +(1 row) + +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]'); + ?column? +---------- + +(1 row) + +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' NULL ON EMPTY); + ?column? +---------- + +(1 row) + +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' EMPTY ARRAY ON EMPTY); + ?column? +---------- + [] +(1 row) + +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' EMPTY OBJECT ON EMPTY); + ?column? +---------- + {} +(1 row) + +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY); + ?column? +---------- + +(1 row) + +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY NULL ON ERROR); + ?column? +---------- + +(1 row) + +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR); + ?column? +---------- + [] +(1 row) + +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR); + ?column? +---------- + {} +(1 row) + +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY ERROR ON ERROR); +ERROR: no SQL/JSON item +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON ERROR); + ?column? +---------- + +(1 row) + +SELECT JSON_QUERY('[1,2]' FORMAT JSON, '$[*]' ERROR ON ERROR); +ERROR: more than one SQL/JSON item +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING json); + ?column? +---------- + [1,2] +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING json FORMAT JSON); + ?column? +---------- + [1,2] +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING jsonb); + ?column? +---------- + [1, 2] +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING jsonb FORMAT JSON); + ?column? +---------- + [1, 2] +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING text); + ?column? +---------- + [1,2] +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING char(10)); + ?column? +------------ + [1,2] +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING char(3)); + ?column? +---------- + [1, +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING text FORMAT JSON); + ?column? +---------- + [1,2] +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING bytea); + ?column? +-------------- + \x5b312c325d +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING bytea FORMAT JSON); + ?column? +-------------- + \x5b312c325d +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING bytea EMPTY OBJECT ON ERROR); + ?column? +---------- + \x7b7d +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJECT ON ERROR); + ?column? +---------- + \x7b7d +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING json EMPTY OBJECT ON ERROR); + ?column? +---------- + {} +(1 row) + +SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING jsonb EMPTY OBJECT ON ERROR); + ?column? +---------- + {} +(1 row) + +SELECT + x, y, + JSON_QUERY( + json '[1,2,3,4,5,null]', + '$[*] ? (@ >= $x && @ <= $y)' + PASSING x AS x, y AS y + WITH CONDITIONAL WRAPPER + EMPTY ARRAY ON EMPTY + ) list +FROM + generate_series(0, 4) x, + generate_series(0, 4) y; + x | y | list +---+---+-------------- + 0 | 0 | [] + 0 | 1 | [1] + 0 | 2 | [1, 2] + 0 | 3 | [1, 2, 3] + 0 | 4 | [1, 2, 3, 4] + 1 | 0 | [] + 1 | 1 | [1] + 1 | 2 | [1, 2] + 1 | 3 | [1, 2, 3] + 1 | 4 | [1, 2, 3, 4] + 2 | 0 | [] + 2 | 1 | [] + 2 | 2 | [2] + 2 | 3 | [2, 3] + 2 | 4 | [2, 3, 4] + 3 | 0 | [] + 3 | 1 | [] + 3 | 2 | [] + 3 | 3 | [3] + 3 | 4 | [3, 4] + 4 | 0 | [] + 4 | 1 | [] + 4 | 2 | [] + 4 | 3 | [] + 4 | 4 | [4] +(25 rows) + +-- Extension: record types returning +CREATE TYPE sqljson_rec AS (a int, t text, js json, jb jsonb, jsa json[]); +CREATE TYPE sqljson_reca AS (reca sqljson_rec[]); +SELECT JSON_QUERY(json '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljson_rec); + ?column? +----------------------------------------------------- + (1,aaa,"[1, ""2"", {}]","{""x"": [1, ""2"", {}]}",) +(1 row) + +SELECT * FROM unnest((JSON_QUERY(json '{"jsa": [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljson_rec)).jsa); + unnest +------------------------ + {"a": 1, "b": ["foo"]} + {"a": 2, "c": {}} + 123 +(3 rows) + +SELECT * FROM unnest((JSON_QUERY(json '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljson_reca)).reca); + a | t | js | jb | jsa +---+-------------+----+------------+----- + 1 | ["foo", []] | | | + 2 | | | [{}, true] | +(2 rows) + +-- Extension: array types returning +SELECT JSON_QUERY(json '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER); + ?column? +-------------- + {1,2,NULL,3} +(1 row) + +SELECT * FROM unnest(JSON_QUERY(json '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljson_rec[])); + a | t | js | jb | jsa +---+-------------+----+------------+----- + 1 | ["foo", []] | | | + 2 | | | [{}, true] | +(2 rows) + +-- Extension: domain types returning +SELECT JSON_QUERY(json '{"a": 1}', '$.a' RETURNING sqljson_int_not_null); + ?column? +---------- + 1 +(1 row) + +SELECT JSON_QUERY(json '{"a": 1}', '$.b' RETURNING sqljson_int_not_null); +ERROR: domain sqljson_int_not_null does not allow null values +-- Test constraints +CREATE TABLE test_json_constraints ( + js text, + i int, + x jsonb DEFAULT JSON_QUERY(json '[1,2]', '$[*]' WITH WRAPPER) + CONSTRAINT test_json_constraint1 + CHECK (js IS JSON) + CONSTRAINT test_json_constraint2 + CHECK (JSON_EXISTS(js FORMAT JSON, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr)) + CONSTRAINT test_json_constraint3 + CHECK (JSON_VALUE(js::json, '$.a' RETURNING int DEFAULT ('12' || i)::int ON EMPTY ERROR ON ERROR) > i) + CONSTRAINT test_json_constraint4 + CHECK (JSON_QUERY(js FORMAT JSON, '$.a' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]') + CONSTRAINT test_json_constraint5 + CHECK (JSON_QUERY(js FORMAT JSON, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > 'a') +); +\d test_json_constraints + Table "public.test_json_constraints" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------------------------------------------------------------------------------------------------------- + js | text | | | + i | integer | | | + x | jsonb | | | JSON_QUERY('[1,2]'::json, '$[*]' RETURNING json WITH UNCONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) +Check constraints: + "test_json_constraint1" CHECK (js IS JSON) + "test_json_constraint2" CHECK (JSON_EXISTS(js FORMAT JSON, '$."a"' PASSING i + 5 AS int, i::text AS txt, ARRAY[1, 2, 3] AS arr FALSE ON ERROR)) + "test_json_constraint3" CHECK (JSON_VALUE(js::json, '$."a"' RETURNING integer DEFAULT ('12'::text || i)::integer ON EMPTY ERROR ON ERROR) > i) + "test_json_constraint4" CHECK (JSON_QUERY(js FORMAT JSON, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER NULL ON EMPTY EMPTY OBJECT ON ERROR) < '[10]'::jsonb) + "test_json_constraint5" CHECK (JSON_QUERY(js FORMAT JSON, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY NULL ON ERROR) > 'a'::bpchar) + +SELECT check_clause +FROM information_schema.check_constraints +WHERE constraint_name LIKE 'test_json_constraint%'; + check_clause +-------------------------------------------------------------------------------------------------------------------------------------- + ((js IS JSON)) + (JSON_EXISTS(js FORMAT JSON, '$."a"' PASSING (i + 5) AS int, (i)::text AS txt, ARRAY[1, 2, 3] AS arr FALSE ON ERROR)) + ((JSON_VALUE((js)::json, '$."a"' RETURNING integer DEFAULT (('12'::text || i))::integer ON EMPTY ERROR ON ERROR) > i)) + ((JSON_QUERY(js FORMAT JSON, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER NULL ON EMPTY EMPTY OBJECT ON ERROR) < '[10]'::jsonb)) + ((JSON_QUERY(js FORMAT JSON, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY NULL ON ERROR) > 'a'::bpchar)) +(5 rows) + +SELECT adsrc FROM pg_attrdef WHERE adrelid = 'test_json_constraints'::regclass; + adsrc +--------------------------------------------------------------------------------------------------------- + JSON_QUERY('[1,2]'::json, '$[*]' RETURNING json WITH UNCONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) +(1 row) + +INSERT INTO test_json_constraints VALUES ('', 1); +ERROR: new row for relation "test_json_constraints" violates check constraint "test_json_constraint1" +DETAIL: Failing row contains (, 1, [1, 2]). +INSERT INTO test_json_constraints VALUES ('1', 1); +ERROR: new row for relation "test_json_constraints" violates check constraint "test_json_constraint2" +DETAIL: Failing row contains (1, 1, [1, 2]). +INSERT INTO test_json_constraints VALUES ('[]'); +ERROR: new row for relation "test_json_constraints" violates check constraint "test_json_constraint2" +DETAIL: Failing row contains ([], null, [1, 2]). +INSERT INTO test_json_constraints VALUES ('{"b": 1}', 1); +ERROR: new row for relation "test_json_constraints" violates check constraint "test_json_constraint2" +DETAIL: Failing row contains ({"b": 1}, 1, [1, 2]). +INSERT INTO test_json_constraints VALUES ('{"a": 1}', 1); +ERROR: new row for relation "test_json_constraints" violates check constraint "test_json_constraint3" +DETAIL: Failing row contains ({"a": 1}, 1, [1, 2]). +INSERT INTO test_json_constraints VALUES ('{"a": 7}', 1); +ERROR: new row for relation "test_json_constraints" violates check constraint "test_json_constraint5" +DETAIL: Failing row contains ({"a": 7}, 1, [1, 2]). +INSERT INTO test_json_constraints VALUES ('{"a": 10}', 1); +ERROR: new row for relation "test_json_constraints" violates check constraint "test_json_constraint4" +DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]). +DROP TABLE test_json_constraints; +-- Extension: non-constant JSON path +SELECT JSON_EXISTS(json '{"a": 123}', '$' || '.' || 'a'); + ?column? +---------- + t +(1 row) + +SELECT JSON_VALUE(json '{"a": 123}', '$' || '.' || 'a'); + ?column? +---------- + 123 +(1 row) + +SELECT JSON_VALUE(json '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY); + ?column? +---------- + foo +(1 row) + +SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a'); + ?column? +---------- + 123 +(1 row) + +SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER); + ?column? +---------- + [123] +(1 row) + +-- Should fail (invalid path) +SELECT JSON_QUERY(json '{"a": 123}', 'error' || ' ' || 'error'); +ERROR: bad jsonpath representation +DETAIL: syntax error, unexpected IDENT_P at or near " " diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 408c711..94a2d1d 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -109,7 +109,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo # ---------- # Another group of parallel tests (JSON related) # ---------- -test: json jsonb json_encoding jsonpath json_jsonpath jsonb_jsonpath jsonb_sqljson sqljson +test: json jsonb json_encoding jsonpath json_jsonpath jsonb_jsonpath json_sqljson jsonb_sqljson sqljson # ---------- # Another group of parallel tests diff --git a/src/test/regress/sql/json_sqljson.sql b/src/test/regress/sql/json_sqljson.sql index 4f30fa4..084c7b1 100644 --- a/src/test/regress/sql/json_sqljson.sql +++ b/src/test/regress/sql/json_sqljson.sql @@ -1,11 +1,300 @@ -- JSON_EXISTS SELECT JSON_EXISTS(NULL FORMAT JSON, '$'); +SELECT JSON_EXISTS(NULL::text FORMAT JSON, '$'); +SELECT JSON_EXISTS(NULL::bytea FORMAT JSON, '$'); +SELECT JSON_EXISTS(NULL::json FORMAT JSON, '$'); +SELECT JSON_EXISTS(NULL::jsonb FORMAT JSON, '$'); +SELECT JSON_EXISTS(NULL::json, '$'); + +SELECT JSON_EXISTS('' FORMAT JSON, '$'); +SELECT JSON_EXISTS('' FORMAT JSON, '$' TRUE ON ERROR); +SELECT JSON_EXISTS('' FORMAT JSON, '$' FALSE ON ERROR); +SELECT JSON_EXISTS('' FORMAT JSON, '$' UNKNOWN ON ERROR); +SELECT JSON_EXISTS('' FORMAT JSON, '$' ERROR ON ERROR); + + +SELECT JSON_EXISTS(bytea '' FORMAT JSON, '$' ERROR ON ERROR); + +SELECT JSON_EXISTS(json '[]', '$'); +SELECT JSON_EXISTS('[]' FORMAT JSON, '$'); +SELECT JSON_EXISTS(JSON_OBJECT(RETURNING bytea FORMAT JSON) FORMAT JSON, '$'); + +SELECT JSON_EXISTS(json '1', '$'); +SELECT JSON_EXISTS(json 'null', '$'); +SELECT JSON_EXISTS(json '[]', '$'); + +SELECT JSON_EXISTS(json '1', '$.a'); +SELECT JSON_EXISTS(json '1', 'strict $.a'); +SELECT JSON_EXISTS(json '1', 'strict $.a' ERROR ON ERROR); +SELECT JSON_EXISTS(json 'null', '$.a'); +SELECT JSON_EXISTS(json '[]', '$.a'); +SELECT JSON_EXISTS(json '[1, "aaa", {"a": 1}]', 'strict $.a'); +SELECT JSON_EXISTS(json '[1, "aaa", {"a": 1}]', 'lax $.a'); +SELECT JSON_EXISTS(json '{}', '$.a'); +SELECT JSON_EXISTS(json '{"b": 1, "a": 2}', '$.a'); + +SELECT JSON_EXISTS(json '1', '$.a.b'); +SELECT JSON_EXISTS(json '{"a": {"b": 1}}', '$.a.b'); +SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.a.b'); + +SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING 1 AS x); +SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING '1' AS x); +SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 2 AS y); +SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 1 AS y); + +-- extension: boolean expressions +SELECT JSON_EXISTS(json '1', '$ > 2'); +SELECT JSON_EXISTS(json '1', '$.a > 2' ERROR ON ERROR); -- JSON_VALUE +SELECT JSON_VALUE(NULL, '$'); SELECT JSON_VALUE(NULL FORMAT JSON, '$'); +SELECT JSON_VALUE(NULL::text, '$'); +SELECT JSON_VALUE(NULL::bytea, '$'); +SELECT JSON_VALUE(NULL::json, '$'); +SELECT JSON_VALUE(NULL::jsonb FORMAT JSON, '$'); + +SELECT JSON_VALUE('' FORMAT JSON, '$'); +SELECT JSON_VALUE('' FORMAT JSON, '$' NULL ON ERROR); +SELECT JSON_VALUE('' FORMAT JSON, '$' DEFAULT '"default value"' ON ERROR); +SELECT JSON_VALUE('' FORMAT JSON, '$' ERROR ON ERROR); + +SELECT JSON_VALUE(json 'null', '$'); +SELECT JSON_VALUE(json 'null', '$' RETURNING int); + +SELECT JSON_VALUE(json 'true', '$'); +SELECT JSON_VALUE(json 'true', '$' RETURNING bool); + +SELECT JSON_VALUE(json '123', '$'); +SELECT JSON_VALUE(json '123', '$' RETURNING int) + 234; +SELECT JSON_VALUE(json '123', '$' RETURNING text); +/* jsonb bytea ??? */ +SELECT JSON_VALUE(json '123', '$' RETURNING bytea); + +SELECT JSON_VALUE(json '1.23', '$'); +SELECT JSON_VALUE(json '1.23', '$' RETURNING int); +SELECT JSON_VALUE(json '"1.23"', '$' RETURNING numeric); +SELECT JSON_VALUE(json '"1.23"', '$' RETURNING int ERROR ON ERROR); + +SELECT JSON_VALUE(json '"aaa"', '$'); +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING text); +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING char(5)); +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING char(2)); +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING json); +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING jsonb); +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING json ERROR ON ERROR); +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING jsonb ERROR ON ERROR); +SELECT JSON_VALUE(json '"\"aaa\""', '$' RETURNING json); +SELECT JSON_VALUE(json '"\"aaa\""', '$' RETURNING jsonb); +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING int); +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING int ERROR ON ERROR); +SELECT JSON_VALUE(json '"aaa"', '$' RETURNING int DEFAULT 111 ON ERROR); +SELECT JSON_VALUE(json '"123"', '$' RETURNING int) + 234; + +SELECT JSON_VALUE(json '"2017-02-20"', '$' RETURNING date) + 9; + +-- Test NULL checks execution in domain types +CREATE DOMAIN sqljson_int_not_null AS int NOT NULL; +SELECT JSON_VALUE(json '1', '$.a' RETURNING sqljson_int_not_null); +SELECT JSON_VALUE(json '1', '$.a' RETURNING sqljson_int_not_null NULL ON ERROR); +SELECT JSON_VALUE(json '1', '$.a' RETURNING sqljson_int_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(json '[]', '$'); +SELECT JSON_VALUE(json '[]', '$' ERROR ON ERROR); +SELECT JSON_VALUE(json '{}', '$'); +SELECT JSON_VALUE(json '{}', '$' ERROR ON ERROR); + +SELECT JSON_VALUE(json '1', '$.a'); +SELECT JSON_VALUE(json '1', 'strict $.a' ERROR ON ERROR); +SELECT JSON_VALUE(json '1', 'strict $.a' DEFAULT 'error' ON ERROR); +SELECT JSON_VALUE(json '1', 'lax $.a' ERROR ON ERROR); +SELECT JSON_VALUE(json '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR); +SELECT JSON_VALUE(json '1', 'strict $.a' DEFAULT 2 ON ERROR); +SELECT JSON_VALUE(json '1', 'lax $.a' DEFAULT 2 ON ERROR); +SELECT JSON_VALUE(json '1', 'lax $.a' DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(json '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(json '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR); +SELECT JSON_VALUE(json '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR); + +SELECT JSON_VALUE(json '[1,2]', '$[*]' ERROR ON ERROR); +SELECT JSON_VALUE(json '[1,2]', '$[*]' DEFAULT '0' ON ERROR); +SELECT JSON_VALUE(json '[" "]', '$[*]' RETURNING int ERROR ON ERROR); +SELECT JSON_VALUE(json '[" "]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR); +SELECT JSON_VALUE(json '["1"]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR); + +SELECT + x, + JSON_VALUE( + json '{"a": 1, "b": 2}', + '$.* ? (@ > $x)' PASSING x AS x + RETURNING int + DEFAULT -1 ON EMPTY + DEFAULT -2 ON ERROR + ) y +FROM + generate_series(0, 2) x; + +SELECT JSON_VALUE(json 'null', '$a' PASSING point ' (1, 2 )' AS a); +SELECT JSON_VALUE(json 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point); -- JSON_QUERY -SELECT JSON_QUERY(NULL FORMAT JSON, '$'); +SELECT + JSON_QUERY(js FORMAT JSON, '$'), + JSON_QUERY(js FORMAT JSON, '$' WITHOUT WRAPPER), + JSON_QUERY(js FORMAT JSON, '$' WITH CONDITIONAL WRAPPER), + JSON_QUERY(js FORMAT JSON, '$' WITH UNCONDITIONAL ARRAY WRAPPER), + JSON_QUERY(js FORMAT JSON, '$' WITH ARRAY WRAPPER) +FROM + (VALUES + ('null'), + ('12.3'), + ('true'), + ('"aaa"'), + ('[1, null, "2"]'), + ('{"a": 1, "b": [2]}') + ) foo(js); + +SELECT + JSON_QUERY(js FORMAT JSON, 'strict $[*]') AS "unspec", + JSON_QUERY(js FORMAT JSON, 'strict $[*]' WITHOUT WRAPPER) AS "without", + JSON_QUERY(js FORMAT JSON, 'strict $[*]' WITH CONDITIONAL WRAPPER) AS "with cond", + JSON_QUERY(js FORMAT JSON, 'strict $[*]' WITH UNCONDITIONAL ARRAY WRAPPER) AS "with uncond", + JSON_QUERY(js FORMAT JSON, 'strict $[*]' WITH ARRAY WRAPPER) AS "with" +FROM + (VALUES + ('1'), + ('[]'), + ('[null]'), + ('[12.3]'), + ('[true]'), + ('["aaa"]'), + ('[[1, 2, 3]]'), + ('[{"a": 1, "b": [2]}]'), + ('[1, "2", null, [3]]') + ) foo(js); + +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text); +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text KEEP QUOTES); +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text KEEP QUOTES ON SCALAR STRING); +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text OMIT QUOTES); +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text OMIT QUOTES ON SCALAR STRING); +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' OMIT QUOTES ERROR ON ERROR); +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING json OMIT QUOTES ERROR ON ERROR); +SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING bytea FORMAT JSON OMIT QUOTES ERROR ON ERROR); + +-- QUOTES behavior should not be specified when WITH WRAPPER used: +-- Should fail +SELECT JSON_QUERY(json '[1]', '$' WITH WRAPPER OMIT QUOTES); +SELECT JSON_QUERY(json '[1]', '$' WITH WRAPPER KEEP QUOTES); +SELECT JSON_QUERY(json '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTES); +SELECT JSON_QUERY(json '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTES); +-- Should succeed +SELECT JSON_QUERY(json '[1]', '$' WITHOUT WRAPPER OMIT QUOTES); +SELECT JSON_QUERY(json '[1]', '$' WITHOUT WRAPPER KEEP QUOTES); + +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]'); +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' NULL ON EMPTY); +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' EMPTY ARRAY ON EMPTY); +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' EMPTY OBJECT ON EMPTY); +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY); + +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY NULL ON ERROR); +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR); +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR); +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY ERROR ON ERROR); +SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON ERROR); + +SELECT JSON_QUERY('[1,2]' FORMAT JSON, '$[*]' ERROR ON ERROR); + +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING json); +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING json FORMAT JSON); +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING jsonb); +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING jsonb FORMAT JSON); +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING text); +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING char(10)); +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING char(3)); +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING text FORMAT JSON); +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING bytea); +SELECT JSON_QUERY(json '[1,2]', '$' RETURNING bytea FORMAT JSON); + +SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING bytea EMPTY OBJECT ON ERROR); +SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJECT ON ERROR); +SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING json EMPTY OBJECT ON ERROR); +SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING jsonb EMPTY OBJECT ON ERROR); + +SELECT + x, y, + JSON_QUERY( + json '[1,2,3,4,5,null]', + '$[*] ? (@ >= $x && @ <= $y)' + PASSING x AS x, y AS y + WITH CONDITIONAL WRAPPER + EMPTY ARRAY ON EMPTY + ) list +FROM + generate_series(0, 4) x, + generate_series(0, 4) y; + +-- Extension: record types returning +CREATE TYPE sqljson_rec AS (a int, t text, js json, jb jsonb, jsa json[]); +CREATE TYPE sqljson_reca AS (reca sqljson_rec[]); + +SELECT JSON_QUERY(json '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljson_rec); +SELECT * FROM unnest((JSON_QUERY(json '{"jsa": [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljson_rec)).jsa); +SELECT * FROM unnest((JSON_QUERY(json '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljson_reca)).reca); + +-- Extension: array types returning +SELECT JSON_QUERY(json '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER); +SELECT * FROM unnest(JSON_QUERY(json '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljson_rec[])); + +-- Extension: domain types returning +SELECT JSON_QUERY(json '{"a": 1}', '$.a' RETURNING sqljson_int_not_null); +SELECT JSON_QUERY(json '{"a": 1}', '$.b' RETURNING sqljson_int_not_null); + +-- Test constraints + +CREATE TABLE test_json_constraints ( + js text, + i int, + x jsonb DEFAULT JSON_QUERY(json '[1,2]', '$[*]' WITH WRAPPER) + CONSTRAINT test_json_constraint1 + CHECK (js IS JSON) + CONSTRAINT test_json_constraint2 + CHECK (JSON_EXISTS(js FORMAT JSON, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr)) + CONSTRAINT test_json_constraint3 + CHECK (JSON_VALUE(js::json, '$.a' RETURNING int DEFAULT ('12' || i)::int ON EMPTY ERROR ON ERROR) > i) + CONSTRAINT test_json_constraint4 + CHECK (JSON_QUERY(js FORMAT JSON, '$.a' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]') + CONSTRAINT test_json_constraint5 + CHECK (JSON_QUERY(js FORMAT JSON, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > 'a') +); + +\d test_json_constraints + +SELECT check_clause +FROM information_schema.check_constraints +WHERE constraint_name LIKE 'test_json_constraint%'; + +SELECT adsrc FROM pg_attrdef WHERE adrelid = 'test_json_constraints'::regclass; + +INSERT INTO test_json_constraints VALUES ('', 1); +INSERT INTO test_json_constraints VALUES ('1', 1); +INSERT INTO test_json_constraints VALUES ('[]'); +INSERT INTO test_json_constraints VALUES ('{"b": 1}', 1); +INSERT INTO test_json_constraints VALUES ('{"a": 1}', 1); +INSERT INTO test_json_constraints VALUES ('{"a": 7}', 1); +INSERT INTO test_json_constraints VALUES ('{"a": 10}', 1); + +DROP TABLE test_json_constraints; + +-- Extension: non-constant JSON path +SELECT JSON_EXISTS(json '{"a": 123}', '$' || '.' || 'a'); +SELECT JSON_VALUE(json '{"a": 123}', '$' || '.' || 'a'); +SELECT JSON_VALUE(json '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY); +SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a'); +SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER); +-- Should fail (invalid path) +SELECT JSON_QUERY(json '{"a": 123}', 'error' || ' ' || 'error');