From b48457e7aa984ba44c9daab3ef29599445528d13 Mon Sep 17 00:00:00 2001 From: jian he Date: Wed, 3 Jan 2024 16:11:39 +0800 Subject: [PATCH v1 3/4] refactor the QUOTE behavior for json_value. By default, json_value, the QUOTES behavior is not allowed. However, in many cases, json_table will transform to json_value. json_table can specify quotes behavior freely. To coordinate with json_table quotes behavior, for json_value, we set the default to omit_quotes, if specified keep QUOTE via json_table , we can set keep_quotes to true. --- src/backend/executor/execExprInterp.c | 16 ++++++++++--- src/backend/parser/parse_expr.c | 26 ++++++++++++++++++--- src/test/regress/expected/jsonb_sqljson.out | 12 ++++++++-- 3 files changed, 46 insertions(+), 8 deletions(-) diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 5d075043..a667057a 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -4408,6 +4408,7 @@ ExecPrepareJsonItemCoercion(JsonbValue *item, JsonExprState *jsestate, bool via_expr; int jump_to; JsonbValue buf; + char *str; if (item->type == jbvBinary && JsonContainerIsScalar(item->val.binary.data)) { @@ -4433,9 +4434,18 @@ ExecPrepareJsonItemCoercion(JsonbValue *item, JsonExprState *jsestate, case jbvString: via_expr = item_coercion_via_expr[JsonItemTypeString]; jump_to = eval_item_coercion_jumps[JsonItemTypeString]; - *resvalue = - PointerGetDatum(cstring_to_text_with_len(item->val.string.val, - item->val.string.len)); + /* if we keep_quotes, then we need double-quote it. */ + if (!jsestate->jsexpr->omit_quotes) + { + str = psprintf("\"%s\"", item->val.string.val); + *resvalue = + PointerGetDatum(cstring_to_text_with_len(str, + item->val.string.len+2)); + } + else + *resvalue = + PointerGetDatum(cstring_to_text_with_len(item->val.string.val, + item->val.string.len)); break; case jbvNumeric: diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 6027b34e..e7abc457 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -103,7 +103,7 @@ static Node *coerceJsonExpr(ParseState *pstate, Node *expr, const JsonReturning *returning); static JsonCoercion *makeJsonCoercion(const JsonReturning *returning); static List *InitJsonItemCoercions(ParseState *pstate, const JsonReturning *returning, - Oid contextItemTypeId); + Oid contextItemTypeId, bool keep_quotes); static JsonBehavior *transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior, JsonBehaviorType default_behavior, JsonReturning *returning); @@ -4351,6 +4351,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) break; case JSON_VALUE_OP: + jsexpr->omit_quotes = (func->quotes != JS_QUOTES_KEEP); if (!OidIsValid(jsexpr->returning->typid)) { /* Make JSON_VALUE return text by default */ @@ -4359,6 +4360,19 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) } jsexpr->result_coercion = coerceJsonFuncExprOutput(pstate, jsexpr); + /* + * JSON_VALUE_OP not allowed to specify quotes behavior. + * By default it omit quotes. + * However JSON_TABLE_OP will be transformed to JSON_VALUE_OP in some cases, + * JSON_TABLE_OP can specify keep/omit quotes. + * So here we need adjust it. + */ + if (jsexpr->result_coercion) + { + JsonCoercion *coercion = (JsonCoercion *) jsexpr->result_coercion; + if (!jsexpr->omit_quotes) + coercion->keep_quotes = true; + } /* * Initialize expressions to coerce the scalar value returned * by JsonPathValue() to the "returning" type. @@ -4366,7 +4380,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) if (jsexpr->result_coercion) jsexpr->item_coercions = InitJsonItemCoercions(pstate, jsexpr->returning, - exprType(jsexpr->formatted_expr)); + exprType(jsexpr->formatted_expr), !jsexpr->omit_quotes); if (func->on_empty) jsexpr->on_empty = transformJsonBehavior(pstate, @@ -4627,7 +4641,7 @@ coerceJsonExpr(ParseState *pstate, Node *expr, const JsonReturning *returning) */ static List * InitJsonItemCoercions(ParseState *pstate, const JsonReturning *returning, - Oid contextItemTypeId) + Oid contextItemTypeId, bool keep_quotes) { List *item_coercions = NIL; int i; @@ -4677,6 +4691,12 @@ InitJsonItemCoercions(ParseState *pstate, const JsonReturning *returning, item_coercion->item_type = item_types[i].item_type; item_coercion->coercion = coerceJsonExpr(pstate, expr, returning); + if (item_coercion->coercion) + { + JsonCoercion *coercion = (JsonCoercion *) item_coercion->coercion; + if (keep_quotes) + coercion->keep_quotes = true; + } item_coercions = lappend(item_coercions, item_coercion); } diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out index e0613cc2..f6a2c1fc 100644 --- a/src/test/regress/expected/jsonb_sqljson.out +++ b/src/test/regress/expected/jsonb_sqljson.out @@ -301,7 +301,11 @@ SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9; -- Test NULL checks execution in domain types CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL; SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null); -ERROR: domain sqljsonb_int_not_null does not allow null values + json_value +------------ + +(1 row) + SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null ERROR ON ERROR); ERROR: domain sqljsonb_int_not_null does not allow null values SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY ERROR ON ERROR); @@ -313,7 +317,11 @@ SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON (1 row) SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY ERROR ON ERROR); -ERROR: domain sqljsonb_int_not_null does not allow null values + json_value +------------ + +(1 row) + CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); CREATE DOMAIN rgb AS rainbow CHECK (VALUE IN ('red', 'green', 'blue')); SELECT JSON_VALUE('"purple"'::jsonb, 'lax $[*]' RETURNING rgb); -- 2.34.1