0013-sqljson-json-v12.patch
text/x-patch
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
Series: patch v12-0013
| File | + | − |
|---|---|---|
| src/backend/executor/execExprInterp.c | 65 | 29 |
| src/backend/parser/parse_expr.c | 0 | 13 |
| src/include/executor/execExpr.h | 1 | 1 |
| src/include/utils/jsonpath.h | 6 | 0 |
| src/test/regress/expected/json_sqljson.out | 1073 | 10 |
| src/test/regress/parallel_schedule | 1 | 1 |
| src/test/regress/sql/json_sqljson.sql | 302 | 1 |
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 2d42608..d842670 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4158,17 +4158,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);
@@ -4195,17 +4199,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,
@@ -4215,7 +4222,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,
@@ -4249,7 +4256,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)
@@ -4258,8 +4265,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)
@@ -4316,7 +4329,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:
@@ -4331,7 +4355,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;
@@ -4345,7 +4370,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;
}
@@ -4354,15 +4380,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)
@@ -4370,7 +4396,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);
@@ -4383,8 +4409,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)
{
@@ -4398,7 +4427,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;
@@ -4417,14 +4447,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;
}
@@ -4441,6 +4472,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;
@@ -4448,7 +4483,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;
@@ -4471,7 +4506,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
@@ -4510,7 +4545,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)
{
@@ -4563,12 +4598,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 67ee55c..2ea7cff 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4679,13 +4679,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;
@@ -4706,8 +4703,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
break;
case IS_JSON_QUERY:
- func_name = "JSON_QUERY";
-
transformJsonFuncExprOutput(pstate, func, jsexpr);
jsexpr->wrapper = func->wrapper;
@@ -4716,8 +4711,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;
@@ -4727,11 +4720,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 1e0ee9b..f30bf1f 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -782,7 +782,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 b0e96d2..2c466f6 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -315,6 +315,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..f7d1568 100644
--- a/src/test/regress/expected/json_sqljson.out
+++ b/src/test/regress/expected/json_sqljson.out
@@ -1,15 +1,1078 @@
-- 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, '$');
- ^
+ json_exists
+-------------
+
+(1 row)
+
+SELECT JSON_EXISTS(NULL::text FORMAT JSON, '$');
+ json_exists
+-------------
+
+(1 row)
+
+SELECT JSON_EXISTS(NULL::bytea FORMAT JSON, '$');
+ json_exists
+-------------
+
+(1 row)
+
+SELECT JSON_EXISTS(NULL::json FORMAT JSON, '$');
+WARNING: FORMAT JSON has no effect for json and jsonb types
+ json_exists
+-------------
+
+(1 row)
+
+SELECT JSON_EXISTS(NULL::jsonb FORMAT JSON, '$');
+WARNING: FORMAT JSON has no effect for json and jsonb types
+ json_exists
+-------------
+
+(1 row)
+
+SELECT JSON_EXISTS(NULL::json, '$');
+ json_exists
+-------------
+
+(1 row)
+
+SELECT JSON_EXISTS('' FORMAT JSON, '$');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS('' FORMAT JSON, '$' TRUE ON ERROR);
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS('' FORMAT JSON, '$' FALSE ON ERROR);
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS('' FORMAT JSON, '$' UNKNOWN ON ERROR);
+ json_exists
+-------------
+
+(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 '[]', '$');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS('[]' FORMAT JSON, '$');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(JSON_OBJECT(RETURNING bytea FORMAT JSON) FORMAT JSON, '$');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(json '1', '$');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(json 'null', '$');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(json '[]', '$');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(json '1', '$.a');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(json '1', 'strict $.a');
+ json_exists
+-------------
+ 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');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(json '[]', '$.a');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(json '[1, "aaa", {"a": 1}]', 'strict $.a');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(json '[1, "aaa", {"a": 1}]', 'lax $.a');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(json '{}', '$.a');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(json '{"b": 1, "a": 2}', '$.a');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(json '1', '$.a.b');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(json '{"a": {"b": 1}}', '$.a.b');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.a.b');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING 1 AS x);
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING '1' AS x);
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 2 AS y);
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(json '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 1 AS y);
+ json_exists
+-------------
+ f
+(1 row)
+
+-- extension: boolean expressions
+SELECT JSON_EXISTS(json '1', '$ > 2');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(json '1', '$.a > 2' ERROR ON ERROR);
+ json_exists
+-------------
+ t
+(1 row)
+
-- JSON_VALUE
+SELECT JSON_VALUE(NULL, '$');
+ json_value
+------------
+
+(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, '$');
- ^
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(NULL::text, '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(NULL::bytea, '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(NULL::json, '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(NULL::jsonb FORMAT JSON, '$');
+WARNING: FORMAT JSON has no effect for json and jsonb types
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE('' FORMAT JSON, '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE('' FORMAT JSON, '$' NULL ON ERROR);
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE('' FORMAT JSON, '$' DEFAULT '"default value"' ON ERROR);
+ json_value
+-----------------
+ "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', '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(json 'null', '$' RETURNING int);
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(json 'true', '$');
+ json_value
+------------
+ true
+(1 row)
+
+SELECT JSON_VALUE(json 'true', '$' RETURNING bool);
+ json_value
+------------
+ t
+(1 row)
+
+SELECT JSON_VALUE(json '123', '$');
+ json_value
+------------
+ 123
+(1 row)
+
+SELECT JSON_VALUE(json '123', '$' RETURNING int) + 234;
+ ?column?
+----------
+ 357
+(1 row)
+
+SELECT JSON_VALUE(json '123', '$' RETURNING text);
+ json_value
+------------
+ 123
+(1 row)
+
+/* jsonb bytea ??? */
+SELECT JSON_VALUE(json '123', '$' RETURNING bytea);
+ json_value
+------------
+ \x313233
+(1 row)
+
+SELECT JSON_VALUE(json '1.23', '$');
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(json '1.23', '$' RETURNING int);
+ json_value
+------------
+ 1
+(1 row)
+
+SELECT JSON_VALUE(json '"1.23"', '$' RETURNING numeric);
+ json_value
+------------
+ 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"', '$');
+ json_value
+------------
+ aaa
+(1 row)
+
+SELECT JSON_VALUE(json '"aaa"', '$' RETURNING text);
+ json_value
+------------
+ aaa
+(1 row)
+
+SELECT JSON_VALUE(json '"aaa"', '$' RETURNING char(5));
+ json_value
+------------
+ aaa
+(1 row)
+
+SELECT JSON_VALUE(json '"aaa"', '$' RETURNING char(2));
+ json_value
+------------
+ aa
+(1 row)
+
+SELECT JSON_VALUE(json '"aaa"', '$' RETURNING json);
+ json_value
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_VALUE(json '"aaa"', '$' RETURNING jsonb);
+ json_value
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_VALUE(json '"aaa"', '$' RETURNING json ERROR ON ERROR);
+ json_value
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_VALUE(json '"aaa"', '$' RETURNING jsonb ERROR ON ERROR);
+ json_value
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_VALUE(json '"\"aaa\""', '$' RETURNING json);
+ json_value
+------------
+ "\"aaa\""
+(1 row)
+
+SELECT JSON_VALUE(json '"\"aaa\""', '$' RETURNING jsonb);
+ json_value
+------------
+ "\"aaa\""
+(1 row)
+
+SELECT JSON_VALUE(json '"aaa"', '$' RETURNING int);
+ json_value
+------------
+
+(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);
+ json_value
+------------
+ 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 '[]', '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(json '[]', '$' ERROR ON ERROR);
+ERROR: SQL/JSON scalar required
+SELECT JSON_VALUE(json '{}', '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(json '{}', '$' ERROR ON ERROR);
+ERROR: SQL/JSON scalar required
+SELECT JSON_VALUE(json '1', '$.a');
+ json_value
+------------
+
+(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);
+ json_value
+------------
+ error
+(1 row)
+
+SELECT JSON_VALUE(json '1', 'lax $.a' ERROR ON ERROR);
+ json_value
+------------
+
+(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);
+ json_value
+------------
+ 2
+(1 row)
+
+SELECT JSON_VALUE(json '1', 'lax $.a' DEFAULT 2 ON ERROR);
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(json '1', 'lax $.a' DEFAULT '2' ON ERROR);
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(json '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR);
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(json '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR);
+ json_value
+------------
+ 2
+(1 row)
+
+SELECT JSON_VALUE(json '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
+ json_value
+------------
+ 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);
+ json_value
+------------
+ 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);
+ json_value
+------------
+ 5
+(1 row)
+
+SELECT JSON_VALUE(json '["1"]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
+ json_value
+------------
+ 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);
+ json_value
+------------
+ (1,2)
+(1 row)
+
+SELECT JSON_VALUE(json 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
+ json_value
+------------
+ (1,2)
+(1 row)
+
+-- Test timestamptz passing and output
+SELECT JSON_VALUE(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
+ json_value
+------------------------------
+ Tue Feb 20 18:34:56 2018 PST
+(1 row)
+
+SELECT JSON_VALUE(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamptz);
+ json_value
+------------------------------
+ Tue Feb 20 18:34:56 2018 PST
+(1 row)
+
+SELECT JSON_VALUE(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamp);
+ json_value
+--------------------------
+ Tue Feb 20 18:34:56 2018
+(1 row)
+
+SELECT JSON_VALUE(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
+ json_value
+-----------------------------
+ "2018-02-21T02:34:56+00:00"
+(1 row)
+
+SELECT JSON_VALUE(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
+ json_value
+-----------------------------
+ "2018-02-21T02:34:56+00:00"
+(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);
+ json_query | json_query | json_query | json_query | json_query
+--------------------+--------------------+--------------------+----------------------+----------------------
+ 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);
+ json_query
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text KEEP QUOTES);
+ json_query
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text KEEP QUOTES ON SCALAR STRING);
+ json_query
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text OMIT QUOTES);
+ json_query
+------------
+ aaa
+(1 row)
+
+SELECT JSON_QUERY('"aaa"' FORMAT JSON, '$' RETURNING text OMIT QUOTES ON SCALAR STRING);
+ json_query
+------------
+ 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);
+ json_query
+------------
+ \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);
+ json_query
+------------
+ [1]
+(1 row)
+
+SELECT JSON_QUERY(json '[1]', '$' WITHOUT WRAPPER KEEP QUOTES);
+ json_query
+------------
+ [1]
+(1 row)
+
+SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]');
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' NULL ON EMPTY);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' EMPTY ARRAY ON EMPTY);
+ json_query
+------------
+ []
+(1 row)
+
+SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' EMPTY OBJECT ON EMPTY);
+ json_query
+------------
+ {}
+(1 row)
+
+SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY NULL ON ERROR);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
+ json_query
+------------
+ []
+(1 row)
+
+SELECT JSON_QUERY('[]' FORMAT JSON, '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
+ json_query
+------------
+ {}
+(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);
+ json_query
+------------
+
+(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);
+ json_query
+------------
+ [1,2]
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$' RETURNING json FORMAT JSON);
+ json_query
+------------
+ [1,2]
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$' RETURNING jsonb);
+ json_query
+------------
+ [1, 2]
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$' RETURNING jsonb FORMAT JSON);
+ json_query
+------------
+ [1, 2]
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$' RETURNING text);
+ json_query
+------------
+ [1,2]
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$' RETURNING char(10));
+ json_query
+------------
+ [1,2]
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$' RETURNING char(3));
+ json_query
+------------
+ [1,
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$' RETURNING text FORMAT JSON);
+ json_query
+------------
+ [1,2]
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$' RETURNING bytea);
+ json_query
+--------------
+ \x5b312c325d
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$' RETURNING bytea FORMAT JSON);
+ json_query
+--------------
+ \x5b312c325d
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING bytea EMPTY OBJECT ON ERROR);
+ json_query
+------------
+ \x7b7d
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJECT ON ERROR);
+ json_query
+------------
+ \x7b7d
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING json EMPTY OBJECT ON ERROR);
+ json_query
+------------
+ {}
+(1 row)
+
+SELECT JSON_QUERY(json '[1,2]', '$[*]' RETURNING jsonb EMPTY OBJECT ON ERROR);
+ json_query
+------------
+ {}
+(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);
+ json_query
+-----------------------------------------------------
+ (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);
+ json_query
+--------------
+ {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);
+ json_query
+------------
+ 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 timestamptz passing and output
+SELECT JSON_QUERY(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
+ json_query
+-----------------------------
+ "2018-02-21T02:34:56+00:00"
+(1 row)
+
+SELECT JSON_QUERY(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
+ json_query
+-----------------------------
+ "2018-02-21T02:34:56+00:00"
+(1 row)
+
+SELECT JSON_QUERY(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
+ json_query
+-----------------------------
+ "2018-02-21T02:34:56+00:00"
+(1 row)
+
+-- 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');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_VALUE(json '{"a": 123}', '$' || '.' || 'a');
+ json_value
+------------
+ 123
+(1 row)
+
+SELECT JSON_VALUE(json '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY);
+ json_value
+------------
+ foo
+(1 row)
+
+SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a');
+ json_query
+------------
+ 123
+(1 row)
+
+SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
+ json_query
+------------
+ [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 7065d54..cd0c7d7 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..6146c45 100644
--- a/src/test/regress/sql/json_sqljson.sql
+++ b/src/test/regress/sql/json_sqljson.sql
@@ -1,11 +1,312 @@
-- 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);
+
+-- Test timestamptz passing and output
+SELECT JSON_VALUE(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
+SELECT JSON_VALUE(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamptz);
+SELECT JSON_VALUE(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamp);
+SELECT JSON_VALUE(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
+SELECT JSON_VALUE(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
-- 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 timestamptz passing and output
+SELECT JSON_QUERY(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
+SELECT JSON_QUERY(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
+SELECT JSON_QUERY(json 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
+
+-- 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');