v36-0005-SQL-JSON-query-functions.patch
application/octet-stream
Filename: v36-0005-SQL-JSON-query-functions.patch
Type: application/octet-stream
Part: 4
Message:
Re: remaining sql/json patches
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: format-patch
Series: patch v36-0005
Subject: SQL/JSON query functions
| File | + | − |
|---|---|---|
| doc/src/sgml/func.sgml | 173 | 0 |
| src/backend/catalog/sql_features.txt | 6 | 6 |
| src/backend/executor/execExpr.c | 344 | 0 |
| src/backend/executor/execExprInterp.c | 369 | 1 |
| src/backend/jit/llvm/llvmjit_expr.c | 140 | 0 |
| src/backend/jit/llvm/llvmjit_types.c | 3 | 0 |
| src/backend/nodes/makefuncs.c | 18 | 0 |
| src/backend/nodes/nodeFuncs.c | 231 | 2 |
| src/backend/optimizer/path/costsize.c | 2 | 1 |
| src/backend/optimizer/util/clauses.c | 19 | 0 |
| src/backend/parser/gram.y | 177 | 11 |
| src/backend/parser/parse_expr.c | 599 | 12 |
| src/backend/parser/parse_target.c | 15 | 0 |
| src/backend/utils/adt/formatting.c | 44 | 0 |
| src/backend/utils/adt/jsonb.c | 31 | 0 |
| src/backend/utils/adt/jsonfuncs.c | 56 | 0 |
| src/backend/utils/adt/jsonpath.c | 259 | 0 |
| src/backend/utils/adt/jsonpath_exec.c | 1 | 1 |
| src/backend/utils/adt/ruleutils.c | 136 | 0 |
| src/include/executor/execExpr.h | 23 | 1 |
| src/include/nodes/execnodes.h | 86 | 0 |
| src/include/nodes/makefuncs.h | 2 | 0 |
| src/include/nodes/parsenodes.h | 47 | 0 |
| src/include/nodes/primnodes.h | 164 | 0 |
| src/include/parser/kwlist.h | 11 | 0 |
| src/include/utils/formatting.h | 1 | 0 |
| src/include/utils/jsonb.h | 1 | 0 |
| src/include/utils/jsonfuncs.h | 6 | 0 |
| src/include/utils/jsonpath.h | 1 | 0 |
| src/interfaces/ecpg/preproc/ecpg.trailer | 28 | 0 |
| src/test/regress/expected/sqljson_queryfuncs.out | 1145 | 0 |
| src/test/regress/parallel_schedule | 1 | 1 |
| src/test/regress/sql/sqljson_queryfuncs.sql | 371 | 0 |
| src/tools/pgindent/typedefs.list | 17 | 0 |
From b0efafe204c93a7c7c2cd88613a5f3388c5233ff Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Thu, 18 Jan 2024 17:59:56 +0900
Subject: [PATCH v36 5/6] SQL/JSON query functions
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This introduces the following SQL/JSON functions for querying JSON
data using jsonpath expressions:
JSON_EXISTS()
JSON_QUERY()
JSON_VALUE()
JSON_EXISTS() tests if the jsonpath expression applied to the jsonb
value yields any values.
JSON_VALUE() must return a single value, and an error occurs if it
tries to return multiple values.
JSON_QUERY() must return a json object or array, and there are
various WRAPPER options for handling scalar or multi-value results.
Both these functions have options for handling EMPTY and ERROR
conditions.
All of these functions only operate on jsonb. The workaround for now
is to cast the argument to jsonb.
Author: Nikita Glukhov <n.gluhov@postgrespro.ru>
Author: Teodor Sigaev <teodor@sigaev.ru>
Author: Oleg Bartunov <obartunov@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Author: Andrew Dunstan <andrew@dunslane.net>
Author: Amit Langote <amitlangote09@gmail.com>
Author: Peter Eisentraut <peter@eisentraut.org>
Author: Jian He <jian.universality@gmail.com>
Reviewers have included (in no particular order) Andres Freund, Alexander
Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu,
Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby, Álvaro Herrera,
jian he, Anton A. Melnikov, Nikita Malakhov, Peter Eisentraut
Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de
Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org
Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com
---
doc/src/sgml/func.sgml | 173 +++
src/backend/catalog/sql_features.txt | 12 +-
src/backend/executor/execExpr.c | 344 +++++
src/backend/executor/execExprInterp.c | 370 +++++-
src/backend/jit/llvm/llvmjit_expr.c | 140 ++
src/backend/jit/llvm/llvmjit_types.c | 3 +
src/backend/nodes/makefuncs.c | 18 +
src/backend/nodes/nodeFuncs.c | 233 +++-
src/backend/optimizer/path/costsize.c | 3 +-
src/backend/optimizer/util/clauses.c | 19 +
src/backend/parser/gram.y | 188 ++-
src/backend/parser/parse_expr.c | 611 ++++++++-
src/backend/parser/parse_target.c | 15 +
src/backend/utils/adt/formatting.c | 44 +
src/backend/utils/adt/jsonb.c | 31 +
src/backend/utils/adt/jsonfuncs.c | 56 +
src/backend/utils/adt/jsonpath.c | 259 ++++
src/backend/utils/adt/jsonpath_exec.c | 2 +-
src/backend/utils/adt/ruleutils.c | 136 ++
src/include/executor/execExpr.h | 24 +-
src/include/nodes/execnodes.h | 86 ++
src/include/nodes/makefuncs.h | 2 +
src/include/nodes/parsenodes.h | 47 +
src/include/nodes/primnodes.h | 164 +++
src/include/parser/kwlist.h | 11 +
src/include/utils/formatting.h | 1 +
src/include/utils/jsonb.h | 1 +
src/include/utils/jsonfuncs.h | 6 +
src/include/utils/jsonpath.h | 1 +
src/interfaces/ecpg/preproc/ecpg.trailer | 28 +
.../regress/expected/sqljson_queryfuncs.out | 1145 +++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/sqljson_queryfuncs.sql | 371 ++++++
src/tools/pgindent/typedefs.list | 17 +
34 files changed, 4527 insertions(+), 36 deletions(-)
create mode 100644 src/test/regress/expected/sqljson_queryfuncs.out
create mode 100644 src/test/regress/sql/sqljson_queryfuncs.sql
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 210c7c0b02..a05e539077 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15461,6 +15461,11 @@ table2-mapping
the SQL/JSON path language
</para>
</listitem>
+ <listitem>
+ <para>
+ the SQL/JSON query functions
+ </para>
+ </listitem>
</itemizedlist>
</para>
@@ -18163,6 +18168,174 @@ $.* ? (@ like_regex "^\\d+$")
</para>
</sect3>
</sect2>
+
+ <sect2 id="sqljson-query-functions">
+ <title>SQL/JSON Query Functions</title>
+ <para>
+ SQL/JSON functions <literal>JSON_EXISTS()</literal>,
+ <literal>JSON_QUERY()</literal>, and <literal>JSON_VALUE()</literal>
+ described in <xref linkend="functions-sqljson-querying"/> can be used
+ to query JSON document. Each of these functions apply a
+ <replaceable>path_expression</replaceable> (the query) to a
+ <replaceable>context_item</replaceable> (the document); seen
+ <xref linkend="functions-sqljson-path"/> for more details on what
+ <replaceable>path_expression</replaceable> can contain.
+ </para>
+
+ <note>
+ <para>
+ SQL/JSON query functions currently only accept values of the
+ <type>jsonb</type> type, because the SQL/JSON path language only
+ supports those, so it might be necessary to cast the
+ <replaceable>context_item</replaceable> argument of these functions to
+ <type>jsonb</type>.
+ </para>
+ </note>
+
+ <table id="functions-sqljson-querying">
+ <title>SQL/JSON Query Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function signature
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para></entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json_exists</primary></indexterm>
+ <function>json_exists</function> (
+ <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
+ <optional> { <literal>TRUE</literal> | <literal>FALSE</literal> |<literal> UNKNOWN</literal> | <literal>ERROR</literal> } <literal>ON ERROR</literal> </optional>)
+ </para>
+ <para>
+ Returns true if the SQL/JSON <replaceable>path_expression</replaceable>
+ applied to the <replaceable>context_item</replaceable> using the
+ <replaceable>value</replaceable>s yields any items.
+ The <literal>ON ERROR</literal> clause specifies the behavior if
+ an error occurs; the default is to return the <type>boolean</type>
+ <literal>FALSE</literal> value.
+ Note that if the <replaceable>path_expression</replaceable>
+ is <literal>strict</literal> and <literal>ON ERROR</literal> behavior
+ is <literal>ON ERROR</literal>, an error is generated if it yields no
+ items.
+ </para>
+ <para>
+ <literal>json_exists(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)')</literal>
+ <returnvalue>t</returnvalue>
+ </para>
+ <para>
+ <literal>json_exists(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR)</literal>
+ <returnvalue>f</returnvalue>
+ </para>
+ <para>
+ <literal>json_exists(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR)</literal>
+ <returnvalue>ERROR: jsonpath array subscript is out of bounds</returnvalue>
+ </para></entry>
+ </row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json_query</primary></indexterm>
+ <function>json_query</function> (
+ <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>
+ <optional> { <literal>WITHOUT</literal> | <literal>WITH</literal> { <literal>CONDITIONAL</literal> | <optional><literal>UNCONDITIONAL</literal></optional> } } <optional> <literal>ARRAY</literal> </optional> <literal>WRAPPER</literal> </optional>
+ <optional> { <literal>KEEP</literal> | <literal>OMIT</literal> } <literal>QUOTES</literal> <optional> <literal>ON SCALAR STRING</literal> </optional> </optional>
+ <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
+ <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
+ </para>
+ <para>
+ Returns the result of applying the
+ <replaceable>path_expression</replaceable> to the
+ <replaceable>context_item</replaceable> using the
+ <replaceable>value</replaceable>s.
+ This function must return a JSON string, so if the path expression
+ returns multiple SQL/JSON items, you must wrap the result using the
+ <literal>WITH WRAPPER</literal> clause. If the wrapper is
+ <literal>UNCONDITIONAL</literal>, an array wrapper will always
+ be applied, even if the returned value is already a single JSON object
+ or an array, but if it is <literal>CONDITIONAL</literal>, it will not be
+ applied to a single array or object. <literal>UNCONDITIONAL</literal>
+ is the default. If the result is a scalar string, by default the value
+ returned will have surrounding quotes making it a valid JSON value,
+ which can be made explicit by specifying <literal>KEEP QUOTES</literal>.
+ Conversely, quotes can be omitted by specifying <literal>OMIT QUOTES</literal>.
+ The <literal>RETURNING</literal> clause can be used to specify the
+ <replaceable>data_type</replaceable> of the result value. It must
+ be a type for which there is a cast from <type>text</type> to that type.
+ If no <literal>RETURNING</literal> is spcified, the returned value will
+ be of type <type>jsonb</type>.
+ The <literal>ON EMPTY</literal> clause specifies the behavior if the
+ <replaceable>path_expression</replaceable> yields no value at all; the
+ default when <literal>ON EMPTY</literal> is not specified is to return a
+ null value. The <literal>ON ERROR</literal> clause specifies the behavior
+ if an error occurs as a result of <type>jsonpath</type> evaluation
+ (including cast to the output type) or during the execution of
+ <literal>ON EMPTY</literal> behavior (that was caused by empty result of
+ <type>jsonpath</type> evaluation); the default when
+ <literal>ON ERROR</literal> is not specified is to return a null value.
+ </para>
+ <para>
+ <literal>json_query(jsonb '[1,[2,3],null]', 'lax $[*][1]' WITH CONDITIONAL WRAPPER)</literal>
+ <returnvalue>[3]</returnvalue>
+ </para></entry>
+ </row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json_value</primary></indexterm>
+ <function>json_value</function> (
+ <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable>
+ <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> </optional>
+ <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
+ <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
+ </para>
+ <para>
+ Returns the result of applying the
+ <replaceable>path_expression</replaceable> to the
+ <replaceable>context_item</replaceable> using the
+ <literal>PASSING</literal> <replaceable>value</replaceable>s. The
+ extracted value must be a single <acronym>SQL/JSON</acronym> scalar
+ item. For results that are objects or arrays, use the
+ <function>json_query</function> function instead.
+ The <literal>RETURNING</literal> clause can be used to specify the
+ <replaceable>data_type</replaceable> of the result value. It must
+ be a type for which there are casts from all possible JSON scalar
+ value types (<type>text</type>, <type>boolean</type>, <type>numeric</type>,
+ and various datetime types) to that type. If no <literal>RETURNING</literal>
+ is spcified, the returned value will be of type <type>text</type>.
+ The <literal>ON ERROR</literal> and <literal>ON EMPTY</literal>
+ clauses have similar semantics as mentioned in the description of
+ <function>json_query</function>. Note that scalar strings returned
+ by <function>json_value</function> always have their quotes removed,
+ equivalent to what one would get with <literal>OMIT QUOTES</literal>
+ when using <function>json_query</function>.
+ </para>
+ <para>
+ <literal>json_value(jsonb '"123.45"', '$' RETURNING float)</literal>
+ <returnvalue>123.45</returnvalue>
+ </para>
+ <para>
+ <literal>json_value(jsonb '"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date)</literal>
+ <returnvalue>2015-02-01</returnvalue>
+ </para>
+ <para>
+ <literal>json_value(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR)</literal>
+ <returnvalue>9</returnvalue>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect2>
</sect1>
<sect1 id="functions-sequence">
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 80c40eaf57..7598bd8f22 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -548,15 +548,15 @@ T811 Basic SQL/JSON constructor functions YES
T812 SQL/JSON: JSON_OBJECTAGG YES
T813 SQL/JSON: JSON_ARRAYAGG with ORDER BY YES
T814 Colon in JSON_OBJECT or JSON_OBJECTAGG YES
-T821 Basic SQL/JSON query operators NO
+T821 Basic SQL/JSON query operators YES
T822 SQL/JSON: IS JSON WITH UNIQUE KEYS predicate YES
-T823 SQL/JSON: PASSING clause NO
+T823 SQL/JSON: PASSING clause YES
T824 JSON_TABLE: specific PLAN clause NO
-T825 SQL/JSON: ON EMPTY and ON ERROR clauses NO
-T826 General value expression in ON ERROR or ON EMPTY clauses NO
+T825 SQL/JSON: ON EMPTY and ON ERROR clauses YES
+T826 General value expression in ON ERROR or ON EMPTY clauses YES
T827 JSON_TABLE: sibling NESTED COLUMNS clauses NO
-T828 JSON_QUERY NO
-T829 JSON_QUERY: array wrapper options NO
+T828 JSON_QUERY YES
+T829 JSON_QUERY: array wrapper options YES
T830 Enforcing unique keys in SQL/JSON constructor functions YES
T831 SQL/JSON path language: strict mode YES
T832 SQL/JSON path language: item method YES
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 3181b1136a..37d7df9640 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -49,6 +49,7 @@
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/jsonfuncs.h"
+#include "utils/jsonpath.h"
#include "utils/lsyscache.h"
#include "utils/typcache.h"
@@ -88,6 +89,12 @@ static void ExecBuildAggTransCall(ExprState *state, AggState *aggstate,
FunctionCallInfo fcinfo, AggStatePerTrans pertrans,
int transno, int setno, int setoff, bool ishash,
bool nullcheck);
+static void ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state,
+ Datum *resv, bool *resnull,
+ ExprEvalStep *scratch);
+static int ExecInitJsonExprCoercion(ExprState *state, Node *coercion,
+ ErrorSaveContext *escontext,
+ Datum *resv, bool *resnull);
/*
@@ -2413,6 +2420,14 @@ ExecInitExprRec(Expr *node, ExprState *state,
break;
}
+ case T_JsonExpr:
+ {
+ JsonExpr *jexpr = castNode(JsonExpr, node);
+
+ ExecInitJsonExpr(jexpr, state, resv, resnull, &scratch);
+ break;
+ }
+
case T_NullTest:
{
NullTest *ntest = (NullTest *) node;
@@ -4181,3 +4196,332 @@ ExecBuildParamSetEqual(TupleDesc desc,
return state;
}
+
+/*
+ * Push steps to evaluate a JsonExpr and its various subsidiary expressions.
+ */
+static void
+ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state,
+ Datum *resv, bool *resnull,
+ ExprEvalStep *scratch)
+{
+ JsonExprState *jsestate = palloc0(sizeof(JsonExprState));
+ JsonExprPostEvalState *post_eval = &jsestate->post_eval;
+ ListCell *argexprlc;
+ ListCell *argnamelc;
+ List *jumps_if_skip = NIL;
+ List *jumps_to_coerce_finish = NIL;
+ List *jumps_to_end = NIL;
+ ListCell *lc;
+ ExprEvalStep *as;
+
+ jsestate->jsexpr = jexpr;
+
+ /*
+ * Evaluate formatted_expr storing the result into
+ * jsestate->formatted_expr.
+ */
+ ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
+ &jsestate->formatted_expr.value,
+ &jsestate->formatted_expr.isnull);
+
+ /* Steps to jump to end if formatted_expr evaluates to NULL */
+ scratch->opcode = EEOP_JUMP_IF_NULL;
+ scratch->resnull = &jsestate->formatted_expr.isnull;
+ scratch->d.jump.jumpdone = -1; /* set below */
+ jumps_if_skip = lappend_int(jumps_if_skip, state->steps_len);
+ ExprEvalPushStep(state, scratch);
+
+ /*
+ * Evaluate pathspec expression storing the result into
+ * jsestate->pathspec.
+ */
+ ExecInitExprRec((Expr *) jexpr->path_spec, state,
+ &jsestate->pathspec.value,
+ &jsestate->pathspec.isnull);
+
+ /* Steps to JUMP to end if pathspec evaluates to NULL */
+ scratch->opcode = EEOP_JUMP_IF_NULL;
+ scratch->resnull = &jsestate->pathspec.isnull;
+ scratch->d.jump.jumpdone = -1; /* set below */
+ jumps_if_skip = lappend_int(jumps_if_skip, state->steps_len);
+ ExprEvalPushStep(state, scratch);
+
+ /* Steps to compute PASSING args. */
+ jsestate->args = NIL;
+ forboth(argexprlc, jexpr->passing_values,
+ argnamelc, jexpr->passing_names)
+ {
+ Expr *argexpr = (Expr *) lfirst(argexprlc);
+ String *argname = lfirst_node(String, argnamelc);
+ JsonPathVariable *var = palloc(sizeof(*var));
+
+ var->name = argname->sval;
+ var->typid = exprType((Node *) argexpr);
+ var->typmod = exprTypmod((Node *) argexpr);
+
+ ExecInitExprRec((Expr *) argexpr, state, &var->value, &var->isnull);
+
+ jsestate->args = lappend(jsestate->args, var);
+ }
+
+ /* Step for JsonPath* evaluation; see ExecEvalJsonExprPath(). */
+ scratch->opcode = EEOP_JSONEXPR_PATH;
+ scratch->resvalue = resv;
+ scratch->resnull = resnull;
+ scratch->d.jsonexpr.jsestate = jsestate;
+ ExprEvalPushStep(state, scratch);
+
+ /*
+ * Step to jump to end when there's neither an error when evaluating
+ * JsonPath* nor any need to coerce the result because it's already
+ * of the specified type.
+ */
+ scratch->opcode = EEOP_JUMP;
+ scratch->d.jump.jumpdone = -1; /* set below */
+ jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
+ ExprEvalPushStep(state, scratch);
+
+ /*
+ * Steps to coerce the result value computed by EEOP_JSONEXPR_PATH.
+ * To handle coercion errors softly, use the following ErrorSaveContext
+ * when initializing the coercion expressions, including any JsonCoercion
+ * nodes.
+ */
+ jsestate->escontext.type = T_ErrorSaveContext;
+ if (jexpr->result_coercion || jexpr->omit_quotes)
+ {
+ jsestate->jump_eval_result_coercion =
+ ExecInitJsonExprCoercion(state, jexpr->result_coercion,
+ jexpr->on_error->btype != JSON_BEHAVIOR_ERROR ?
+ &jsestate->escontext : NULL,
+ resv, resnull);
+ /* Jump to COERCION_FINISH. */
+ scratch->opcode = EEOP_JUMP;
+ scratch->d.jump.jumpdone = -1; /* set below */
+ jumps_to_coerce_finish = lappend_int(jumps_to_coerce_finish,
+ state->steps_len);
+ ExprEvalPushStep(state, scratch);
+ }
+ else
+ jsestate->jump_eval_result_coercion = -1;
+
+ /* Steps for coercing JsonItemType values returned by JsonPathValue(). */
+ if (jexpr->item_coercions)
+ {
+ /*
+ * Here we create the steps for each JsonItemType type's coercion
+ * expression and also store a flag whether the expression is
+ * a JsonCoercion node. ExecPrepareJsonItemCoercion() called by
+ * ExecEvalJsonExprPath() will map a given JsonbValue returned by
+ * JsonPathValue() to its JsonItemType's expression's step address
+ * and the flag by indexing the following arrays with JsonItemType
+ * enum value.
+ */
+ jsestate->num_item_coercions = list_length(jexpr->item_coercions);
+ jsestate->eval_item_coercion_jumps = (int *)
+ palloc(jsestate->num_item_coercions * sizeof(int));
+ jsestate->item_coercion_via_expr = (bool *)
+ palloc0(jsestate->num_item_coercions * sizeof(bool));
+ foreach(lc, jexpr->item_coercions)
+ {
+ JsonItemCoercion *item_coercion = lfirst(lc);
+ Node *coercion = item_coercion->coercion;
+
+ jsestate->item_coercion_via_expr[item_coercion->item_type] =
+ (coercion != NULL && !IsA(coercion, JsonCoercion));
+ jsestate->eval_item_coercion_jumps[item_coercion->item_type] =
+ ExecInitJsonExprCoercion(state, coercion,
+ jexpr->on_error->btype != JSON_BEHAVIOR_ERROR ?
+ &jsestate->escontext : NULL,
+ resv, resnull);
+
+ /* Jump to COERCION_FINISH. */
+ scratch->opcode = EEOP_JUMP;
+ scratch->d.jump.jumpdone = -1; /* set below */
+ jumps_to_coerce_finish = lappend_int(jumps_to_coerce_finish,
+ state->steps_len);
+ ExprEvalPushStep(state, scratch);
+ }
+ }
+
+ /*
+ * Add step to reset the ErrorSaveContext and set error flag if the
+ * coercion steps encountered an error but was not thrown because of the
+ * ON ERROR behavior.
+ */
+ if (jexpr->result_coercion || jexpr->item_coercions)
+ {
+ foreach(lc, jumps_to_coerce_finish)
+ {
+ as = &state->steps[lfirst_int(lc)];
+ as->d.jump.jumpdone = state->steps_len;
+ }
+
+ scratch->opcode = EEOP_JSONEXPR_COERCION_FINISH;
+ scratch->d.jsonexpr.jsestate = jsestate;
+ ExprEvalPushStep(state, scratch);
+ }
+
+ jsestate->jump_empty = jsestate->jump_error = -1;
+
+ /*
+ * Step to handle ON ERROR behaviors. This handles both the errors
+ * that occur during EEOP_JSONEXPR_PATH evaluation and subsequent coercion
+ * evaluation.
+ */
+ if (jexpr->on_error &&
+ jexpr->on_error->btype != JSON_BEHAVIOR_ERROR)
+ {
+ jsestate->jump_error = state->steps_len;
+ scratch->opcode = EEOP_JUMP_IF_NOT_TRUE;
+
+ /*
+ * post_eval.error is set by ExecEvalJsonExprPath() and
+ * ExecEvalJsonCoercionFinish().
+ */
+ scratch->resvalue = &post_eval->error.value;
+ scratch->resnull = &post_eval->error.isnull;
+ scratch->d.jump.jumpdone = -1; /* set below */
+ jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
+ ExprEvalPushStep(state, scratch);
+
+ /* Steps to evaluate the ON ERROR expression */
+ ExecInitExprRec((Expr *) jexpr->on_error->expr,
+ state, resv, resnull);
+
+ /* Steps to coerce the ON ERROR expression if needed */
+ (void) ExecInitJsonExprCoercion(state,
+ (Node *) jexpr->on_error->coercion,
+ &jsestate->escontext,
+ resv, resnull);
+
+ jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
+ scratch->opcode = EEOP_JUMP;
+ scratch->d.jump.jumpdone = -1;
+ ExprEvalPushStep(state, scratch);
+ }
+
+ /* Step to handle ON EMPTY behaviors. */
+ if (jexpr->on_empty != NULL &&
+ jexpr->on_empty->btype != JSON_BEHAVIOR_ERROR)
+ {
+ jsestate->jump_empty = state->steps_len;
+
+ scratch->opcode = EEOP_JUMP_IF_NOT_TRUE;
+ scratch->resvalue = &post_eval->empty.value;
+ scratch->resnull = &post_eval->empty.isnull;
+ scratch->d.jump.jumpdone = -1; /* set below */
+ jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
+ ExprEvalPushStep(state, scratch);
+
+ /* Steps to evaluate the ON EMPTY expression */
+ ExecInitExprRec((Expr *) jexpr->on_empty->expr,
+ state, resv, resnull);
+
+ /* Steps to coerce the ON EMPTY expression if needed */
+ (void) ExecInitJsonExprCoercion(state,
+ (Node *) jexpr->on_empty->coercion,
+ &jsestate->escontext,
+ resv, resnull);
+
+ scratch->opcode = EEOP_JUMP;
+ scratch->d.jump.jumpdone = -1; /* set below */
+ jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
+ ExprEvalPushStep(state, scratch);
+ }
+
+ if (jsestate->jump_error < 0 && jsestate->jump_empty < 0)
+ {
+ scratch->opcode = EEOP_JUMP;
+ scratch->d.jump.jumpdone = -1; /* set below */
+ jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
+ ExprEvalPushStep(state, scratch);
+ }
+
+ /* Return NULL when either formatted_expr or pathspec is NULL. */
+ foreach(lc, jumps_if_skip)
+ {
+ as = &state->steps[lfirst_int(lc)];
+ as->d.jump.jumpdone = state->steps_len;
+ }
+ scratch->opcode = EEOP_CONST;
+ scratch->resvalue = resv;
+ scratch->resnull = resnull;
+ scratch->d.constval.value = (Datum) 0;
+ scratch->d.constval.isnull = true;
+ ExprEvalPushStep(state, scratch);
+
+ /* Jump to coerce the NULL using result_coercion is present. */
+ if (jsestate->jump_eval_result_coercion >= 0)
+ {
+ scratch->opcode = EEOP_JUMP;
+ scratch->d.jump.jumpdone = jsestate->jump_eval_result_coercion;
+ ExprEvalPushStep(state, scratch);
+ }
+
+ foreach(lc, jumps_to_end)
+ {
+ as = &state->steps[lfirst_int(lc)];
+ as->d.jump.jumpdone = state->steps_len;
+ }
+
+ jsestate->jump_end = state->steps_len;
+}
+
+/* Initialize one JsonCoercion for execution. */
+static int
+ExecInitJsonExprCoercion(ExprState *state, Node *coercion,
+ ErrorSaveContext *escontext,
+ Datum *resv, bool *resnull)
+{
+ int jump_eval_coercion;
+ Datum *save_innermost_caseval;
+ bool *save_innermost_casenull;
+ ErrorSaveContext *save_escontext;
+
+ if (coercion == NULL)
+ return -1;
+
+ jump_eval_coercion = state->steps_len;
+ if (IsA(coercion, JsonCoercion))
+ {
+ ExprEvalStep scratch = {0};
+ Oid typinput;
+ FmgrInfo *finfo;
+ Oid typioparam;
+
+ getTypeInputInfo(((JsonCoercion *) coercion)->targettype,
+ &typinput, &typioparam);
+ finfo = palloc0(sizeof(FmgrInfo));
+ fmgr_info(typinput, finfo);
+
+ scratch.opcode = EEOP_JSONEXPR_COERCION;
+ scratch.resvalue = resv;
+ scratch.resnull = resnull;
+ scratch.d.jsonexpr_coercion.coercion = (JsonCoercion *) coercion;
+ scratch.d.jsonexpr_coercion.input_finfo = finfo;
+ scratch.d.jsonexpr_coercion.typioparam = typioparam;
+ scratch.d.jsonexpr_coercion.json_populate_type_cache = NULL;
+ scratch.d.jsonexpr_coercion.escontext = escontext;
+ ExprEvalPushStep(state, &scratch);
+ return jump_eval_coercion;
+ }
+
+ /* Push step(s) to compute cstate->coercion. */
+ save_innermost_caseval = state->innermost_caseval;
+ save_innermost_casenull = state->innermost_casenull;
+ save_escontext = state->escontext;
+
+ state->innermost_caseval = resv;
+ state->innermost_casenull = resnull;
+ state->escontext = escontext;
+
+ ExecInitExprRec((Expr *) coercion, state, resv, resnull);
+
+ state->innermost_caseval = save_innermost_caseval;
+ state->innermost_casenull = save_innermost_casenull;
+ state->escontext = save_escontext;
+
+ return jump_eval_coercion;
+}
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index b17cab06b6..964433a0e7 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -73,8 +73,8 @@
#include "utils/datum.h"
#include "utils/expandedrecord.h"
#include "utils/json.h"
-#include "utils/jsonb.h"
#include "utils/jsonfuncs.h"
+#include "utils/jsonpath.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/timestamp.h"
@@ -181,6 +181,10 @@ static pg_attribute_always_inline void ExecAggPlainTransByRef(AggState *aggstate
AggStatePerGroup pergroup,
ExprContext *aggcontext,
int setno);
+static void ExecPrepareJsonItemCoercion(JsonbValue *item, JsonExprState *jsestate,
+ bool throw_error,
+ int *jump_eval_item_coercion,
+ Datum *resvalue, bool *resnull);
/*
* ScalarArrayOpExprHashEntry
@@ -482,6 +486,9 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_XMLEXPR,
&&CASE_EEOP_JSON_CONSTRUCTOR,
&&CASE_EEOP_IS_JSON,
+ &&CASE_EEOP_JSONEXPR_PATH,
+ &&CASE_EEOP_JSONEXPR_COERCION,
+ &&CASE_EEOP_JSONEXPR_COERCION_FINISH,
&&CASE_EEOP_AGGREF,
&&CASE_EEOP_GROUPING_FUNC,
&&CASE_EEOP_WINDOW_FUNC,
@@ -1551,6 +1558,28 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
+ EEO_CASE(EEOP_JSONEXPR_PATH)
+ {
+ /* too complex for an inline implementation */
+ EEO_JUMP(ExecEvalJsonExprPath(state, op, econtext));
+ }
+
+ EEO_CASE(EEOP_JSONEXPR_COERCION)
+ {
+ /* too complex for an inline implementation */
+ ExecEvalJsonCoercion(state, op, econtext);
+
+ EEO_NEXT();
+ }
+
+ EEO_CASE(EEOP_JSONEXPR_COERCION_FINISH)
+ {
+ /* too complex for an inline implementation */
+ ExecEvalJsonCoercionFinish(state, op);
+
+ EEO_NEXT();
+ }
+
EEO_CASE(EEOP_AGGREF)
{
/*
@@ -4208,6 +4237,345 @@ ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op)
*op->resvalue = BoolGetDatum(res);
}
+/*
+ * Performs JsonPath{Exists|Query|Value}() for given context item and JSON
+ * path.
+ *
+ * Result is set in *op->resvalue and *op->resnull. Return value is the
+ * step address to be performed next.
+ *
+ * On return, JsonExprPostEvalState is populated with the following details:
+ * - error.value: true if an error occurred during JsonPath evaluation
+ * - empty.value: true if JsonPath{Query|Value}() found no matching item
+ *
+ * No return if the ON ERROR/EMPTY behavior is ERROR.
+ */
+int
+ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext)
+{
+ JsonExprState *jsestate = op->d.jsonexpr.jsestate;
+ JsonExprPostEvalState *post_eval = &jsestate->post_eval;
+ JsonExpr *jexpr = jsestate->jsexpr;
+ Datum item;
+ JsonPath *path;
+ bool throw_error = jexpr->on_error->btype == JSON_BEHAVIOR_ERROR;
+ bool error = false,
+ empty = false;
+ /* Might get overridden for JSON_VALUE_OP by an per-item coercion. */
+ int jump_eval_coercion = jsestate->jump_eval_result_coercion;
+
+ item = jsestate->formatted_expr.value;
+ path = DatumGetJsonPathP(jsestate->pathspec.value);
+
+ /* Reset JsonExprPostEvalState for this evaluation. */
+ memset(post_eval, 0, sizeof(*post_eval));
+ switch (jexpr->op)
+ {
+ case JSON_EXISTS_OP:
+ {
+ bool exists = JsonPathExists(item, path,
+ !throw_error ? &error : NULL,
+ jsestate->args);
+
+ if (!error)
+ {
+ *op->resvalue = BoolGetDatum(exists);
+ *op->resnull = false;
+ }
+ }
+ break;
+
+ case JSON_QUERY_OP:
+ *op->resvalue = JsonPathQuery(item, path, jexpr->wrapper, &empty,
+ !throw_error ? &error : NULL,
+ jsestate->args);
+
+ if (!error && !empty)
+ *op->resnull = (DatumGetPointer(*op->resvalue) == NULL);
+ break;
+
+ case JSON_VALUE_OP:
+ {
+ JsonbValue *jbv = JsonPathValue(item, path, &empty,
+ !throw_error ? &error : NULL,
+ jsestate->args);
+
+ if (jbv == NULL)
+ {
+ /* Will be coerced with result_coercion. */
+ *op->resvalue = (Datum) 0;
+ *op->resnull = true;
+ }
+ else if (!error && !empty)
+ {
+ /*
+ * If the requested output type is json(b), use
+ * result_coercion to do the coercion.
+ */
+ if (jexpr->returning->typid == JSONOID ||
+ jexpr->returning->typid == JSONBOID)
+ {
+ *op->resvalue = JsonbPGetDatum(JsonbValueToJsonb(jbv));
+ *op->resnull = false;
+ }
+ else
+ {
+ /*
+ * Else, use one of the item_coercions.
+ *
+ * Error out if no cast expression exists.
+ */
+ ExecPrepareJsonItemCoercion(jbv, jsestate, throw_error,
+ &jump_eval_coercion,
+ op->resvalue, op->resnull);
+ }
+ }
+ break;
+ }
+
+ default:
+ elog(ERROR, "unrecognized SQL/JSON expression op %d", jexpr->op);
+ return false;
+ }
+
+ if (empty)
+ {
+ if (jexpr->on_empty)
+ {
+ if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
+ ereport(ERROR,
+ errcode(ERRCODE_NO_SQL_JSON_ITEM),
+ errmsg("no SQL/JSON item"));
+ else
+ post_eval->empty.value = BoolGetDatum(true);
+
+ Assert(jsestate->jump_empty >= 0);
+ return jsestate->jump_empty;
+ }
+ else if (jexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+ ereport(ERROR,
+ errcode(ERRCODE_NO_SQL_JSON_ITEM),
+ errmsg("no SQL/JSON item"));
+ else
+ post_eval->error.value = BoolGetDatum(true);
+
+ *op->resvalue = (Datum) 0;
+ *op->resnull = true;
+
+ Assert(jsestate->jump_error >= 0);
+ return jsestate->jump_error;
+ }
+
+ if (error)
+ {
+ Assert(!throw_error && jsestate->jump_error >= 0);
+ *op->resvalue = (Datum) 0;
+ *op->resnull = true;
+ post_eval->error.value = BoolGetDatum(true);
+ return jsestate->jump_error;
+ }
+
+ /* Else return the coercion step address or the address to skip to end. */
+ return jump_eval_coercion >= 0 ? jump_eval_coercion : jsestate->jump_end;
+}
+
+/*
+ * Selects a coercion for a given JsonbValue based on its type.
+ *
+ * On return, *resvalue and *resnull are set to the value extracted from the
+ * JsonbValue and *jump_eval_item_coercion is set to the step address of the
+ * coercion expression.
+ *
+ * If the found expression is a JsonCoercion node that means the parser
+ * didnt' find a cast to do the coercion, so throw an error if the
+ * ON ERROR behavior says to do so.
+ */
+static void
+ExecPrepareJsonItemCoercion(JsonbValue *item, JsonExprState *jsestate,
+ bool throw_error,
+ int *jump_eval_item_coercion,
+ Datum *resvalue, bool *resnull)
+{
+ int *eval_item_coercion_jumps = jsestate->eval_item_coercion_jumps;
+ bool *item_coercion_via_expr = jsestate->item_coercion_via_expr;
+ bool via_expr;
+ int jump_to;
+ JsonbValue buf;
+
+ if (item->type == jbvBinary && JsonContainerIsScalar(item->val.binary.data))
+ {
+ bool is_scalar PG_USED_FOR_ASSERTS_ONLY;
+
+ is_scalar = JsonbExtractScalar(item->val.binary.data, &buf);
+ item = &buf;
+ Assert(is_scalar);
+ }
+
+ *resnull = false;
+
+ /* get coercion state reference and datum of the corresponding SQL type */
+ switch (item->type)
+ {
+ case jbvNull:
+ via_expr = item_coercion_via_expr[JsonItemTypeNull];
+ jump_to = eval_item_coercion_jumps[JsonItemTypeNull];
+ *resvalue = (Datum) 0;
+ *resnull = true;
+ break;
+
+ 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));
+ break;
+
+ case jbvNumeric:
+ via_expr = item_coercion_via_expr[JsonItemTypeNumeric];
+ jump_to = eval_item_coercion_jumps[JsonItemTypeNumeric];
+ *resvalue = NumericGetDatum(item->val.numeric);
+ break;
+
+ case jbvBool:
+ via_expr = item_coercion_via_expr[JsonItemTypeBoolean];
+ jump_to = eval_item_coercion_jumps[JsonItemTypeBoolean];
+ *resvalue = BoolGetDatum(item->val.boolean);
+ break;
+
+ case jbvDatetime:
+ *resvalue = item->val.datetime.value;
+ switch (item->val.datetime.typid)
+ {
+ case DATEOID:
+ via_expr = item_coercion_via_expr[JsonItemTypeDate];
+ jump_to = eval_item_coercion_jumps[JsonItemTypeDate];
+ break;
+ case TIMEOID:
+ via_expr = item_coercion_via_expr[JsonItemTypeTime];
+ jump_to = eval_item_coercion_jumps[JsonItemTypeTime];
+ break;
+ case TIMETZOID:
+ via_expr = item_coercion_via_expr[JsonItemTypeTimetz];
+ jump_to = eval_item_coercion_jumps[JsonItemTypeTimetz];
+ break;
+ case TIMESTAMPOID:
+ via_expr = item_coercion_via_expr[JsonItemTypeTimestamp];
+ jump_to = eval_item_coercion_jumps[JsonItemTypeTimestamp];
+ break;
+ case TIMESTAMPTZOID:
+ via_expr = item_coercion_via_expr[JsonItemTypeTimestamptz];
+ jump_to = eval_item_coercion_jumps[JsonItemTypeTimestamptz];
+ break;
+ default:
+ elog(ERROR, "unexpected jsonb datetime type oid %u",
+ item->val.datetime.typid);
+ }
+ break;
+
+ case jbvArray:
+ case jbvObject:
+ case jbvBinary:
+ via_expr = item_coercion_via_expr[JsonItemTypeComposite];
+ jump_to = eval_item_coercion_jumps[JsonItemTypeComposite];
+ *resvalue = JsonbPGetDatum(JsonbValueToJsonb(item));
+ break;
+
+ default:
+ elog(ERROR, "unexpected jsonb value type %d", item->type);
+ }
+
+ /* If the expression is a JsonCoercion, throw an error. */
+ if (jump_to >= 0 && !via_expr)
+ {
+ if (throw_error)
+ ereport(ERROR,
+ errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
+ errmsg("SQL/JSON item cannot be cast to target type"));
+
+ *resvalue = (Datum) 0;
+ *resnull = true;
+ }
+
+ *jump_eval_item_coercion = jump_to;
+}
+
+/*
+ * Coerce a jsonb value produced by ExecEvalJsonExprPath() or an ON ERROR /
+ * EMPTY behavior expression to the target type by either calling
+ * json_populate_type() or by directly calling the type's input function in
+ * some cases.
+ *
+ * Any soft errors that occur will be checked by EEOP_JSONEXPR_COERCION_FINISH
+ * that will run right after this.
+ */
+void
+ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext)
+{
+ JsonCoercion *coercion = op->d.jsonexpr_coercion.coercion;
+ ErrorSaveContext *escontext = op->d.jsonexpr_coercion.escontext;
+ Datum res = *op->resvalue;
+ bool resnull = *op->resnull;
+ Jsonb *jb = !resnull ? DatumGetJsonbP(res) : NULL;
+
+ /*
+ * Can't go to json_populate_type() for scalars when OMIT QUOTES is
+ * specified, because it keeps the quotes by default. So let's do the
+ * deed ourselves by calling the input function, that is, after removing
+ * the quotes.
+ */
+ if (jb && JB_ROOT_IS_SCALAR(jb) && coercion->omit_quotes)
+ {
+ FmgrInfo *input_finfo = op->d.jsonexpr_coercion.input_finfo;
+ Oid typioparam = op->d.jsonexpr_coercion.typioparam;
+ char *val_string = JsonbUnquote(jb);
+
+ (void) InputFunctionCallSafe(input_finfo, val_string, typioparam,
+ coercion->targettypmod,
+ (Node *) escontext,
+ op->resvalue);
+ }
+ else
+ {
+ void *cache = op->d.jsonexpr_coercion.json_populate_type_cache;
+
+ *op->resvalue = json_populate_type(res, JSONBOID,
+ coercion->targettype,
+ coercion->targettypmod,
+ &cache,
+ econtext->ecxt_per_query_memory,
+ op->resnull, (Node *) escontext);
+ }
+}
+
+/*
+ * Checks if the coercion evaluation led to an error. If an error did occur,
+ * this sets post_eval->error to trigger the subsequent ON ERROR handling
+ * steps.
+ */
+void
+ExecEvalJsonCoercionFinish(ExprState *state, ExprEvalStep *op)
+{
+ JsonExprState *jsestate = op->d.jsonexpr.jsestate;
+
+ if (SOFT_ERROR_OCCURRED(&jsestate->escontext))
+ {
+ *op->resvalue = (Datum) 0;
+ *op->resnull = true;
+ jsestate->post_eval.error.value = BoolGetDatum(true);
+
+ /*
+ * Also make ErrorSaveContext ready for the next row. Since we never
+ * set details_wanted, we don't need to also reset error_data, which
+ * would be NULL anyway.
+ */
+ Assert(!jsestate->escontext.details_wanted &&
+ jsestate->escontext.error_data == NULL);
+ jsestate->escontext.error_occurred = false;
+ }
+}
/*
* ExecEvalGroupingFunc
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index 09994503b1..7a5ff2fedf 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -1930,6 +1930,146 @@ llvm_compile_expr(ExprState *state)
LLVMBuildBr(b, opblocks[opno + 1]);
break;
+ case EEOP_JSONEXPR_PATH:
+ {
+ JsonExprState *jsestate = op->d.jsonexpr.jsestate;
+ LLVMValueRef v_ret;
+
+ /*
+ * Call ExecEvalJsonExprPath(). It returns the address of
+ * the step to perform next.
+ */
+ v_ret = build_EvalXFunc(b, mod, "ExecEvalJsonExprPath",
+ v_state, op, v_econtext);
+
+ /*
+ * Build a switch to map the return value, which is a
+ * runtime value of the step address to perform next, to
+ * either jump_empty, jump_error, or the coercion
+ * expression.
+ */
+ if (jsestate->jump_empty >= 0 ||
+ jsestate->jump_error >= 0 ||
+ jsestate->jump_eval_result_coercion >= 0 ||
+ jsestate->num_item_coercions > 0)
+ {
+ int i;
+ LLVMValueRef v_jump_empty;
+ LLVMValueRef v_jump_error;
+ LLVMValueRef v_jump_coercion;
+ LLVMValueRef v_switch;
+ LLVMBasicBlockRef b_done,
+ b_empty,
+ b_error,
+ b_result_coercion,
+ *b_item_coercions = NULL;
+
+ b_empty =
+ l_bb_before_v(opblocks[opno + 1],
+ "op.%d.jsonexpr_empty", opno);
+ b_error =
+ l_bb_before_v(opblocks[opno + 1],
+ "op.%d.jsonexpr_error", opno);
+ b_result_coercion =
+ l_bb_before_v(opblocks[opno + 1],
+ "op.%d.jsonexpr_result_coercion", opno);
+ if (jsestate->num_item_coercions > 0)
+ {
+ b_item_coercions = palloc(sizeof(LLVMBasicBlockRef) *
+ jsestate->num_item_coercions);
+ for (i = 0; i < jsestate->num_item_coercions; i++)
+ {
+ b_item_coercions[i] =
+ l_bb_before_v(opblocks[opno + 1],
+ "op.%d.jsonexpr_item_coercion.%d",
+ opno, i);
+ }
+ }
+ b_done =
+ l_bb_before_v(opblocks[opno + 1],
+ "op.%d.jsonexpr_done", opno);
+
+ v_switch = LLVMBuildSwitch(b,
+ v_ret,
+ b_done,
+ jsestate->num_item_coercions + 3);
+ /* Returned jsestate->jump_empty? */
+ if (jsestate->jump_empty >= 0)
+ {
+ v_jump_empty = l_int32_const(lc, jsestate->jump_empty);
+ LLVMAddCase(v_switch, v_jump_empty, b_empty);
+ }
+ /* Returned jsestate->jump_error? */
+ if (jsestate->jump_error >= 0)
+ {
+ v_jump_error = l_int32_const(lc, jsestate->jump_error);
+ LLVMAddCase(v_switch, v_jump_error, b_error);
+ }
+ /* Returned jsestate->jump_eval_result_coercion? */
+ if (jsestate->jump_eval_result_coercion >= 0)
+ {
+ v_jump_coercion = l_int32_const(lc, jsestate->jump_eval_result_coercion);
+ LLVMAddCase(v_switch, v_jump_coercion, b_result_coercion);
+ }
+ /* Returned one of jsestate->eval_item_coercion_jumps[]? */
+ for (i = 0; i < jsestate->num_item_coercions; i++)
+ {
+ if (jsestate->eval_item_coercion_jumps[i] >= 0)
+ {
+ v_jump_coercion = l_int32_const(lc, jsestate->eval_item_coercion_jumps[i]);
+ LLVMAddCase(v_switch, v_jump_coercion, b_item_coercions[i]);
+ }
+ }
+
+ /* ON EMPTY code */
+ LLVMPositionBuilderAtEnd(b, b_empty);
+ if (jsestate->jump_empty >= 0)
+ LLVMBuildBr(b, opblocks[jsestate->jump_empty]);
+ else
+ LLVMBuildUnreachable(b);
+ /* ON ERROR code */
+ LLVMPositionBuilderAtEnd(b, b_error);
+ if (jsestate->jump_error >= 0)
+ LLVMBuildBr(b, opblocks[jsestate->jump_error]);
+ else
+ LLVMBuildUnreachable(b);
+ /* result_coercion code */
+ LLVMPositionBuilderAtEnd(b, b_result_coercion);
+ if (jsestate->jump_eval_result_coercion >= 0)
+ LLVMBuildBr(b, opblocks[jsestate->jump_eval_result_coercion]);
+ else
+ LLVMBuildUnreachable(b);
+ /* item coercion code blocks */
+ for (i = 0; i < jsestate->num_item_coercions; i++)
+ {
+ LLVMPositionBuilderAtEnd(b, b_item_coercions[i]);
+ if (jsestate->eval_item_coercion_jumps[i] >= 0)
+ LLVMBuildBr(b, opblocks[jsestate->eval_item_coercion_jumps[i]]);
+ else
+ LLVMBuildUnreachable(b);
+ }
+
+ LLVMPositionBuilderAtEnd(b, b_done);
+ }
+
+ LLVMBuildBr(b, opblocks[opno + 1]);
+ break;
+ }
+
+ case EEOP_JSONEXPR_COERCION:
+ build_EvalXFunc(b, mod, "ExecEvalJsonCoercion",
+ v_state, op, v_econtext);
+
+ LLVMBuildBr(b, opblocks[opno + 1]);
+ break;
+
+ case EEOP_JSONEXPR_COERCION_FINISH:
+ build_EvalXFunc(b, mod, "ExecEvalJsonCoercionFinish",
+ v_state, op);
+
+ LLVMBuildBr(b, opblocks[opno + 1]);
+ break;
+
case EEOP_AGGREF:
{
LLVMValueRef v_aggno;
diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c
index 47c9daf402..edd1e1679b 100644
--- a/src/backend/jit/llvm/llvmjit_types.c
+++ b/src/backend/jit/llvm/llvmjit_types.c
@@ -172,6 +172,9 @@ void *referenced_functions[] =
ExecEvalXmlExpr,
ExecEvalJsonConstructor,
ExecEvalJsonIsPredicate,
+ ExecEvalJsonCoercion,
+ ExecEvalJsonCoercionFinish,
+ ExecEvalJsonExprPath,
MakeExpandedObjectReadOnlyInternal,
slot_getmissingattrs,
slot_getsomeattrs_int,
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index a02332a1ec..09a05a0373 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -857,6 +857,24 @@ makeJsonValueExpr(Expr *raw_expr, Expr *formatted_expr,
return jve;
}
+/*
+ * makeJsonBehavior -
+ * creates a JsonBehavior node
+ */
+JsonBehavior *
+makeJsonBehavior(JsonBehaviorType type, Node *expr, JsonCoercion *coercion,
+ int location)
+{
+ JsonBehavior *behavior = makeNode(JsonBehavior);
+
+ behavior->btype = type;
+ behavior->expr = expr;
+ behavior->coercion = coercion;
+ behavior->location = location;
+
+ return behavior;
+}
+
/*
* makeJsonKeyValue -
* creates a JsonKeyValue node
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index e1a5bc7e95..166bddd4ec 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -234,6 +234,33 @@ exprType(const Node *expr)
case T_JsonIsPredicate:
type = BOOLOID;
break;
+ case T_JsonExpr:
+ {
+ const JsonExpr *jexpr = (const JsonExpr *) expr;
+
+ type = jexpr->returning->typid;
+ break;
+ }
+ case T_JsonCoercion:
+ {
+ const JsonCoercion *coercion = (const JsonCoercion *) expr;
+
+ type = coercion->targettype;
+ break;
+ }
+ case T_JsonItemCoercion:
+ type = exprType(((JsonItemCoercion *) expr)->coercion);
+ break;
+ case T_JsonBehavior:
+ {
+ const JsonBehavior *behavior = (const JsonBehavior *) expr;
+
+ if (behavior->coercion)
+ type = exprType((Node *) behavior->coercion);
+ else
+ type = exprType(behavior->expr);
+ break;
+ }
case T_NullTest:
type = BOOLOID;
break;
@@ -491,8 +518,32 @@ exprTypmod(const Node *expr)
return ((const SQLValueFunction *) expr)->typmod;
case T_JsonValueExpr:
return exprTypmod((Node *) ((const JsonValueExpr *) expr)->formatted_expr);
- case T_JsonConstructorExpr:
- return ((const JsonConstructorExpr *) expr)->returning->typmod;
+ case T_JsonExpr:
+ {
+ const JsonExpr *jexpr = (const JsonExpr *) expr;
+
+ return jexpr->returning->typmod;
+ }
+ break;
+ case T_JsonCoercion:
+ {
+ const JsonCoercion *coercion = (const JsonCoercion *) expr;
+
+ return coercion->targettypmod;
+ }
+ break;
+ case T_JsonItemCoercion:
+ return exprTypmod(((JsonItemCoercion *) expr)->coercion);
+ case T_JsonBehavior:
+ {
+ const JsonBehavior *behavior = (const JsonBehavior *) expr;
+
+ if (behavior->coercion)
+ return exprTypmod((Node *) behavior->coercion);
+ else
+ return exprTypmod(behavior->expr);
+ }
+ break;
case T_CoerceToDomain:
return ((const CoerceToDomain *) expr)->resulttypmod;
case T_CoerceToDomainValue:
@@ -969,6 +1020,27 @@ exprCollation(const Node *expr)
/* IS JSON's result is boolean ... */
coll = InvalidOid; /* ... so it has no collation */
break;
+ case T_JsonExpr:
+ coll = exprCollation(((JsonExpr *) expr)->result_coercion);
+ break;
+ case T_JsonCoercion:
+ coll = ((const JsonCoercion *) expr)->collation;
+ break;
+ case T_JsonItemCoercion:
+ coll = exprCollation(((JsonItemCoercion *) expr)->coercion);
+ break;
+ case T_JsonBehavior:
+ {
+ JsonBehavior *behavior = (JsonBehavior *) expr;
+
+ if (behavior->coercion)
+ coll = exprCollation((Node *) behavior->coercion);
+ else if (behavior->expr)
+ coll = exprCollation(behavior->expr);
+ else
+ coll = InvalidOid;
+ }
+ break;
case T_NullTest:
/* NullTest's result is boolean ... */
coll = InvalidOid; /* ... so it has no collation */
@@ -1205,6 +1277,42 @@ exprSetCollation(Node *expr, Oid collation)
case T_JsonIsPredicate:
Assert(!OidIsValid(collation)); /* result is always boolean */
break;
+ case T_JsonExpr:
+ {
+ JsonExpr *jexpr = (JsonExpr *) expr;
+
+ if (jexpr->result_coercion)
+ exprSetCollation((Node *) jexpr->result_coercion, collation);
+ else
+ Assert(!OidIsValid(collation)); /* result is always a
+ * json[b] type */
+ }
+ break;
+ case T_JsonItemCoercion:
+ {
+ JsonItemCoercion *item_coercion = (JsonItemCoercion *) expr;
+
+ if (item_coercion->coercion)
+ exprSetCollation(item_coercion->coercion, collation);
+ }
+ break;
+ case T_JsonCoercion:
+ {
+ JsonCoercion *coercion = (JsonCoercion *) expr;
+
+ coercion->collation = collation;
+ }
+ break;
+ case T_JsonBehavior:
+ {
+ JsonBehavior *behavior = (JsonBehavior *) expr;
+
+ if (behavior->expr)
+ exprSetCollation(behavior->expr, collation);
+ if (behavior->coercion)
+ exprSetCollation((Node *) behavior->coercion, collation);
+ }
+ break;
case T_NullTest:
/* NullTest's result is boolean ... */
Assert(!OidIsValid(collation)); /* ... so never set a collation */
@@ -1508,6 +1616,18 @@ exprLocation(const Node *expr)
case T_JsonIsPredicate:
loc = ((const JsonIsPredicate *) expr)->location;
break;
+ case T_JsonExpr:
+ {
+ const JsonExpr *jsexpr = (const JsonExpr *) expr;
+
+ /* consider both function name and leftmost arg */
+ loc = leftmostLoc(jsexpr->location,
+ exprLocation(jsexpr->formatted_expr));
+ }
+ break;
+ case T_JsonBehavior:
+ loc = exprLocation(((JsonBehavior *) expr)->expr);
+ break;
case T_NullTest:
{
const NullTest *nexpr = (const NullTest *) expr;
@@ -2260,6 +2380,45 @@ expression_tree_walker_impl(Node *node,
break;
case T_JsonIsPredicate:
return WALK(((JsonIsPredicate *) node)->expr);
+ case T_JsonExpr:
+ {
+ JsonExpr *jexpr = (JsonExpr *) node;
+
+ if (WALK(jexpr->formatted_expr))
+ return true;
+ if (WALK(jexpr->result_coercion))
+ return true;
+ if (WALK(jexpr->item_coercions))
+ return true;
+ if (WALK(jexpr->passing_values))
+ return true;
+ /* we assume walker doesn't care about passing_names */
+ if (WALK(jexpr->on_empty))
+ return true;
+ if (WALK(jexpr->on_error))
+ return true;
+ }
+ break;
+ case T_JsonCoercion:
+ break;
+ case T_JsonItemCoercion:
+ {
+ JsonItemCoercion *item_coercion = (JsonItemCoercion *) node;
+
+ if (WALK(item_coercion->coercion))
+ return true;
+ }
+ break;
+ case T_JsonBehavior:
+ {
+ JsonBehavior *behavior = (JsonBehavior *) node;
+
+ if (WALK(behavior->expr))
+ return true;
+ if (WALK(behavior->coercion))
+ return true;
+ }
+ break;
case T_NullTest:
return WALK(((NullTest *) node)->arg);
case T_BooleanTest:
@@ -3263,6 +3422,46 @@ expression_tree_mutator_impl(Node *node,
return (Node *) newnode;
}
+ case T_JsonExpr:
+ {
+ JsonExpr *jexpr = (JsonExpr *) node;
+ JsonExpr *newnode;
+
+ FLATCOPY(newnode, jexpr, JsonExpr);
+ MUTATE(newnode->path_spec, jexpr->path_spec, Node *);
+ MUTATE(newnode->formatted_expr, jexpr->formatted_expr, Node *);
+ MUTATE(newnode->result_coercion, jexpr->result_coercion, Node *);
+ MUTATE(newnode->item_coercions, jexpr->item_coercions, List *);
+ MUTATE(newnode->passing_values, jexpr->passing_values, List *);
+ /* assume mutator does not care about passing_names */
+ MUTATE(newnode->on_empty, jexpr->on_empty, JsonBehavior *);
+ MUTATE(newnode->on_error, jexpr->on_error, JsonBehavior *);
+ return (Node *) newnode;
+ }
+ break;
+ case T_JsonCoercion:
+ return (Node *) copyObject(node);
+ case T_JsonItemCoercion:
+ {
+ JsonItemCoercion *item_coercion = (JsonItemCoercion *) node;
+ JsonItemCoercion *newnode;
+
+ FLATCOPY(newnode, item_coercion, JsonItemCoercion);
+ MUTATE(newnode->coercion, item_coercion->coercion, Node *);
+ return (Node *) newnode;
+ }
+ break;
+ case T_JsonBehavior:
+ {
+ JsonBehavior *behavior = (JsonBehavior *) node;
+ JsonBehavior *newnode;
+
+ FLATCOPY(newnode, behavior, JsonBehavior);
+ MUTATE(newnode->expr, behavior->expr, Node *);
+ MUTATE(newnode->coercion, behavior->coercion, JsonCoercion *);
+ return (Node *) newnode;
+ }
+ break;
case T_NullTest:
{
NullTest *ntest = (NullTest *) node;
@@ -3951,6 +4150,36 @@ raw_expression_tree_walker_impl(Node *node,
break;
case T_JsonIsPredicate:
return WALK(((JsonIsPredicate *) node)->expr);
+ case T_JsonArgument:
+ return WALK(((JsonArgument *) node)->val);
+ case T_JsonFuncExpr:
+ {
+ JsonFuncExpr *jfe = (JsonFuncExpr *) node;
+
+ if (WALK(jfe->context_item))
+ return true;
+ if (WALK(jfe->pathspec))
+ return true;
+ if (WALK(jfe->passing))
+ return true;
+ if (jfe->output && WALK(jfe->output))
+ return true;
+ if (jfe->on_empty)
+ return true;
+ if (jfe->on_error)
+ return true;
+ }
+ break;
+ case T_JsonBehavior:
+ {
+ JsonBehavior *jb = (JsonBehavior *) node;
+
+ if (WALK(jb->expr))
+ return true;
+ if (WALK(jb->coercion))
+ return true;
+ }
+ break;
case T_NullTest:
return WALK(((NullTest *) node)->arg);
case T_BooleanTest:
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 8b76e98529..4cd606ca73 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -4879,7 +4879,8 @@ cost_qual_eval_walker(Node *node, cost_qual_eval_context *context)
IsA(node, SQLValueFunction) ||
IsA(node, XmlExpr) ||
IsA(node, CoerceToDomain) ||
- IsA(node, NextValueExpr))
+ IsA(node, NextValueExpr) ||
+ IsA(node, JsonExpr))
{
/* Treat all these as having cost 1 */
context->total.per_tuple += cpu_operator_cost;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 94eb56a1e7..8849864cad 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -53,6 +53,7 @@
#include "utils/fmgroids.h"
#include "utils/json.h"
#include "utils/jsonb.h"
+#include "utils/jsonpath.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/syscache.h"
@@ -417,6 +418,24 @@ contain_mutable_functions_walker(Node *node, void *context)
/* Check all subnodes */
}
+ if (IsA(node, JsonExpr))
+ {
+ JsonExpr *jexpr = castNode(JsonExpr, node);
+ Const *cnst;
+
+ if (!IsA(jexpr->path_spec, Const))
+ return true;
+
+ cnst = castNode(Const, jexpr->path_spec);
+
+ Assert(cnst->consttype == JSONPATHOID);
+ if (cnst->constisnull)
+ return false;
+
+ return jspIsMutable(DatumGetJsonPathP(cnst->constvalue),
+ jexpr->passing_names, jexpr->passing_values);
+ }
+
if (IsA(node, SQLValueFunction))
{
/* all variants of SQLValueFunction are stable */
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3460fea56b..272acc8856 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -652,10 +652,19 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_returning_clause_opt
json_name_and_value
json_aggregate_func
+ json_argument
+ json_behavior
+ json_on_error_clause_opt
%type <list> json_name_and_value_list
json_value_expr_list
json_array_aggregate_order_by_clause_opt
-%type <ival> json_predicate_type_constraint
+ json_arguments
+ json_behavior_clause_opt
+ json_passing_clause_opt
+%type <ival> json_behavior_type
+ json_predicate_type_constraint
+ json_quotes_clause_opt
+ json_wrapper_behavior
%type <boolean> json_key_uniqueness_constraint_opt
json_object_constructor_null_clause_opt
json_array_constructor_null_clause_opt
@@ -696,7 +705,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
CACHE CALL CALLED CASCADE CASCADED CASE CAST CATALOG_P CHAIN CHAR_P
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLLATION COLUMN COLUMNS COMMENT COMMENTS COMMIT
- COMMITTED COMPRESSION CONCURRENTLY CONFIGURATION CONFLICT
+ COMMITTED COMPRESSION CONCURRENTLY CONDITIONAL CONFIGURATION CONFLICT
CONNECTION CONSTRAINT CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY
COST CREATE CROSS CSV CUBE CURRENT_P
CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
@@ -707,8 +716,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P
DOUBLE_P DROP
- EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT
- EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
+ EACH ELSE EMPTY_P ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ERROR_P ESCAPE
+ EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
EXTENSION EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FILTER FINALIZE FIRST_P FLOAT_P FOLLOWING FOR
@@ -723,10 +732,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
- JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_OBJECT JSON_OBJECTAGG
- JSON_SCALAR JSON_SERIALIZE
+ JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_EXISTS JSON_OBJECT JSON_OBJECTAGG
+ JSON_QUERY JSON_SCALAR JSON_SERIALIZE JSON_VALUE
- KEY KEYS
+ KEEP KEY KEYS
LABEL LANGUAGE LARGE_P LAST_P LATERAL_P
LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
@@ -740,7 +749,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
NULLS_P NUMERIC
- OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR
+ OBJECT_P OF OFF OFFSET OIDS OLD OMIT ON ONLY OPERATOR OPTION OPTIONS OR
ORDER ORDINALITY OTHERS OUT_P OUTER_P
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
@@ -749,7 +758,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
- QUOTE
+ QUOTE QUOTES
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF_P REFERENCES REFERENCING
REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
@@ -760,7 +769,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
- START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P
+ START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRING_P STRIP_P
SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER
TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
@@ -768,7 +777,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P TYPES_P
- UESCAPE UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
+ UESCAPE UNBOUNDED UNCONDITIONAL UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
UNLISTEN UNLOGGED UNTIL UPDATE USER USING
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
@@ -15782,6 +15791,62 @@ func_expr_common_subexpr:
n->location = @1;
$$ = (Node *) n;
}
+ | JSON_QUERY '('
+ json_value_expr ',' a_expr json_passing_clause_opt
+ json_returning_clause_opt
+ json_wrapper_behavior
+ json_quotes_clause_opt
+ json_behavior_clause_opt
+ ')'
+ {
+ JsonFuncExpr *n = makeNode(JsonFuncExpr);
+
+ n->op = JSON_QUERY_OP;
+ n->context_item = (JsonValueExpr *) $3;
+ n->pathspec = $5;
+ n->passing = $6;
+ n->output = (JsonOutput *) $7;
+ n->wrapper = $8;
+ n->quotes = $9;
+ n->on_empty = (JsonBehavior *) linitial($10);
+ n->on_error = (JsonBehavior *) lsecond($10);
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | JSON_EXISTS '('
+ json_value_expr ',' a_expr json_passing_clause_opt
+ json_on_error_clause_opt
+ ')'
+ {
+ JsonFuncExpr *n = makeNode(JsonFuncExpr);
+
+ n->op = JSON_EXISTS_OP;
+ n->context_item = (JsonValueExpr *) $3;
+ n->pathspec = $5;
+ n->passing = $6;
+ n->output = NULL;
+ n->on_error = (JsonBehavior *) $7;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | JSON_VALUE '('
+ json_value_expr ',' a_expr json_passing_clause_opt
+ json_returning_clause_opt
+ json_behavior_clause_opt
+ ')'
+ {
+ JsonFuncExpr *n = makeNode(JsonFuncExpr);
+
+ n->op = JSON_VALUE_OP;
+ n->context_item = (JsonValueExpr *) $3;
+ n->pathspec = $5;
+ n->passing = $6;
+ n->output = (JsonOutput *) $7;
+ n->on_empty = (JsonBehavior *) linitial($8);
+ n->on_error = (JsonBehavior *) lsecond($8);
+ n->location = @1;
+ $$ = (Node *) n;
+ }
;
@@ -16508,6 +16573,77 @@ opt_asymmetric: ASYMMETRIC
;
/* SQL/JSON support */
+json_passing_clause_opt:
+ PASSING json_arguments { $$ = $2; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
+json_arguments:
+ json_argument { $$ = list_make1($1); }
+ | json_arguments ',' json_argument { $$ = lappend($1, $3); }
+ ;
+
+json_argument:
+ json_value_expr AS ColLabel
+ {
+ JsonArgument *n = makeNode(JsonArgument);
+
+ n->val = (JsonValueExpr *) $1;
+ n->name = $3;
+ $$ = (Node *) n;
+ }
+ ;
+
+/* ARRAY is a noise word */
+json_wrapper_behavior:
+ WITHOUT WRAPPER { $$ = JSW_NONE; }
+ | WITHOUT ARRAY WRAPPER { $$ = JSW_NONE; }
+ | WITH WRAPPER { $$ = JSW_UNCONDITIONAL; }
+ | WITH ARRAY WRAPPER { $$ = JSW_UNCONDITIONAL; }
+ | WITH CONDITIONAL ARRAY WRAPPER { $$ = JSW_CONDITIONAL; }
+ | WITH UNCONDITIONAL ARRAY WRAPPER { $$ = JSW_UNCONDITIONAL; }
+ | WITH CONDITIONAL WRAPPER { $$ = JSW_CONDITIONAL; }
+ | WITH UNCONDITIONAL WRAPPER { $$ = JSW_UNCONDITIONAL; }
+ | /* empty */ { $$ = JSW_UNSPEC; }
+ ;
+
+json_behavior:
+ DEFAULT a_expr
+ { $$ = (Node *) makeJsonBehavior(JSON_BEHAVIOR_DEFAULT, $2, NULL, @1); }
+ | json_behavior_type
+ { $$ = (Node *) makeJsonBehavior($1, NULL, NULL, @1); }
+ ;
+
+json_behavior_type:
+ ERROR_P { $$ = JSON_BEHAVIOR_ERROR; }
+ | NULL_P { $$ = JSON_BEHAVIOR_NULL; }
+ | TRUE_P { $$ = JSON_BEHAVIOR_TRUE; }
+ | FALSE_P { $$ = JSON_BEHAVIOR_FALSE; }
+ | UNKNOWN { $$ = JSON_BEHAVIOR_UNKNOWN; }
+ | EMPTY_P ARRAY { $$ = JSON_BEHAVIOR_EMPTY_ARRAY; }
+ | EMPTY_P OBJECT_P { $$ = JSON_BEHAVIOR_EMPTY_OBJECT; }
+ /* non-standard, for Oracle compatibility only */
+ | EMPTY_P { $$ = JSON_BEHAVIOR_EMPTY_ARRAY; }
+ ;
+
+json_behavior_clause_opt:
+ json_behavior ON EMPTY_P
+ { $$ = list_make2($1, NULL); }
+ | json_behavior ON ERROR_P
+ { $$ = list_make2(NULL, $1); }
+ | json_behavior ON EMPTY_P json_behavior ON ERROR_P
+ { $$ = list_make2($1, $4); }
+ | /* EMPTY */
+ { $$ = list_make2(NULL, NULL); }
+ ;
+
+json_on_error_clause_opt:
+ json_behavior ON ERROR_P
+ { $$ = $1; }
+ | /* EMPTY */
+ { $$ = NULL; }
+ ;
+
json_value_expr:
a_expr json_format_clause_opt
{
@@ -16552,6 +16688,14 @@ json_format_clause_opt:
}
;
+json_quotes_clause_opt:
+ KEEP QUOTES ON SCALAR STRING_P { $$ = JS_QUOTES_KEEP; }
+ | KEEP QUOTES { $$ = JS_QUOTES_KEEP; }
+ | OMIT QUOTES ON SCALAR STRING_P { $$ = JS_QUOTES_OMIT; }
+ | OMIT QUOTES { $$ = JS_QUOTES_OMIT; }
+ | /* EMPTY */ { $$ = JS_QUOTES_UNSPEC; }
+ ;
+
json_returning_clause_opt:
RETURNING Typename json_format_clause_opt
{
@@ -17168,6 +17312,7 @@ unreserved_keyword:
| COMMIT
| COMMITTED
| COMPRESSION
+ | CONDITIONAL
| CONFIGURATION
| CONFLICT
| CONNECTION
@@ -17204,10 +17349,12 @@ unreserved_keyword:
| DOUBLE_P
| DROP
| EACH
+ | EMPTY_P
| ENABLE_P
| ENCODING
| ENCRYPTED
| ENUM_P
+ | ERROR_P
| ESCAPE
| EVENT
| EXCLUDE
@@ -17257,6 +17404,7 @@ unreserved_keyword:
| INSTEAD
| INVOKER
| ISOLATION
+ | KEEP
| KEY
| KEYS
| LABEL
@@ -17303,6 +17451,7 @@ unreserved_keyword:
| OFF
| OIDS
| OLD
+ | OMIT
| OPERATOR
| OPTION
| OPTIONS
@@ -17333,6 +17482,7 @@ unreserved_keyword:
| PROGRAM
| PUBLICATION
| QUOTE
+ | QUOTES
| RANGE
| READ
| REASSIGN
@@ -17392,6 +17542,7 @@ unreserved_keyword:
| STORAGE
| STORED
| STRICT_P
+ | STRING_P
| STRIP_P
| SUBSCRIPTION
| SUPPORT
@@ -17414,6 +17565,7 @@ unreserved_keyword:
| UESCAPE
| UNBOUNDED
| UNCOMMITTED
+ | UNCONDITIONAL
| UNENCRYPTED
| UNKNOWN
| UNLISTEN
@@ -17474,10 +17626,13 @@ col_name_keyword:
| JSON
| JSON_ARRAY
| JSON_ARRAYAGG
+ | JSON_EXISTS
| JSON_OBJECT
| JSON_OBJECTAGG
+ | JSON_QUERY
| JSON_SCALAR
| JSON_SERIALIZE
+ | JSON_VALUE
| LEAST
| NATIONAL
| NCHAR
@@ -17710,6 +17865,7 @@ bare_label_keyword:
| COMMITTED
| COMPRESSION
| CONCURRENTLY
+ | CONDITIONAL
| CONFIGURATION
| CONFLICT
| CONNECTION
@@ -17762,11 +17918,13 @@ bare_label_keyword:
| DROP
| EACH
| ELSE
+ | EMPTY_P
| ENABLE_P
| ENCODING
| ENCRYPTED
| END_P
| ENUM_P
+ | ERROR_P
| ESCAPE
| EVENT
| EXCLUDE
@@ -17836,10 +17994,14 @@ bare_label_keyword:
| JSON
| JSON_ARRAY
| JSON_ARRAYAGG
+ | JSON_EXISTS
| JSON_OBJECT
| JSON_OBJECTAGG
+ | JSON_QUERY
| JSON_SCALAR
| JSON_SERIALIZE
+ | JSON_VALUE
+ | KEEP
| KEY
| KEYS
| LABEL
@@ -17900,6 +18062,7 @@ bare_label_keyword:
| OFF
| OIDS
| OLD
+ | OMIT
| ONLY
| OPERATOR
| OPTION
@@ -17937,6 +18100,7 @@ bare_label_keyword:
| PROGRAM
| PUBLICATION
| QUOTE
+ | QUOTES
| RANGE
| READ
| REAL
@@ -18005,6 +18169,7 @@ bare_label_keyword:
| STORAGE
| STORED
| STRICT_P
+ | STRING_P
| STRIP_P
| SUBSCRIPTION
| SUBSTRING
@@ -18039,6 +18204,7 @@ bare_label_keyword:
| UESCAPE
| UNBOUNDED
| UNCOMMITTED
+ | UNCONDITIONAL
| UNENCRYPTED
| UNIQUE
| UNKNOWN
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9300c7b9ab..5493b05ae8 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -37,6 +37,7 @@
#include "utils/builtins.h"
#include "utils/date.h"
#include "utils/fmgroids.h"
+#include "utils/jsonb.h"
#include "utils/lsyscache.h"
#include "utils/timestamp.h"
#include "utils/xml.h"
@@ -90,6 +91,22 @@ static Node *transformJsonParseExpr(ParseState *pstate, JsonParseExpr *expr);
static Node *transformJsonScalarExpr(ParseState *pstate, JsonScalarExpr *expr);
static Node *transformJsonSerializeExpr(ParseState *pstate,
JsonSerializeExpr *expr);
+static Node *transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *p);
+static JsonExpr *transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func,
+ const char *constructName);
+static void transformJsonPassingArgs(ParseState *pstate, const char *constructName,
+ JsonFormatType format, List *args,
+ List **passing_values, List **passing_names);
+static Node *coerceJsonFuncExprOutput(ParseState *pstate, JsonExpr *jsexpr);
+static Oid JsonFuncExprDefaultReturnType(JsonExpr *jsexpr);
+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);
+static JsonBehavior *transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
+ JsonBehaviorType default_behavior,
+ JsonReturning *returning);
static Node *make_row_comparison_op(ParseState *pstate, List *opname,
List *largs, List *rargs, int location);
static Node *make_row_distinct_op(ParseState *pstate, List *opname,
@@ -353,6 +370,10 @@ transformExprRecurse(ParseState *pstate, Node *expr)
result = transformJsonSerializeExpr(pstate, (JsonSerializeExpr *) expr);
break;
+ case T_JsonFuncExpr:
+ result = transformJsonFuncExpr(pstate, (JsonFuncExpr *) expr);
+ break;
+
default:
/* should not reach here */
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
@@ -3229,7 +3250,7 @@ makeJsonByteaToTextConversion(Node *expr, JsonFormat *format, int location)
static Node *
transformJsonValueExpr(ParseState *pstate, const char *constructName,
JsonValueExpr *ve, JsonFormatType default_format,
- Oid targettype)
+ Oid targettype, bool isarg)
{
Node *expr = transformExprRecurse(pstate, (Node *) ve->raw_expr);
Node *rawexpr;
@@ -3261,6 +3282,41 @@ transformJsonValueExpr(ParseState *pstate, const char *constructName,
else
format = ve->format->format_type;
}
+ else if (isarg)
+ {
+ /*
+ * Special treatment for PASSING arguments.
+ *
+ * Pass types supported by GetJsonPathVar() /
+ * JsonItemFromDatum() directly without converting to json[b].
+ */
+ switch (exprtype)
+ {
+ case BOOLOID:
+ case NUMERICOID:
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case TEXTOID:
+ case VARCHAROID:
+ case DATEOID:
+ case TIMEOID:
+ case TIMETZOID:
+ case TIMESTAMPOID:
+ case TIMESTAMPTZOID:
+ return expr;
+
+ default:
+ if (typcategory == TYPCATEGORY_STRING)
+ return expr;
+ /* else convert argument to json[b] type */
+ break;
+ }
+
+ format = default_format;
+ }
else if (exprtype == JSONOID || exprtype == JSONBOID)
format = JS_FORMAT_DEFAULT; /* do not format json[b] types */
else
@@ -3272,7 +3328,12 @@ transformJsonValueExpr(ParseState *pstate, const char *constructName,
Node *coerced;
bool only_allow_cast = OidIsValid(targettype);
- if (!only_allow_cast &&
+ /*
+ * PASSING args are handled appropriately by GetJsonPathVar() /
+ * JsonItemFromDatum().
+ */
+ if (!isarg &&
+ !only_allow_cast &&
exprtype != BYTEAOID && typcategory != TYPCATEGORY_STRING)
ereport(ERROR,
errcode(ERRCODE_DATATYPE_MISMATCH),
@@ -3425,6 +3486,11 @@ transformJsonOutput(ParseState *pstate, const JsonOutput *output,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("returning SETOF types is not supported in SQL/JSON functions"));
+ if (get_typtype(ret->typid) == TYPTYPE_PSEUDO)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("returning pseudo-types is not supported in SQL/JSON functions"));
+
if (ret->format->format_type == JS_FORMAT_DEFAULT)
/* assign JSONB format when returning jsonb, or JSON format otherwise */
ret->format->format_type =
@@ -3621,7 +3687,7 @@ transformJsonObjectConstructor(ParseState *pstate, JsonObjectConstructor *ctor)
Node *val = transformJsonValueExpr(pstate, "JSON_OBJECT()",
kv->value,
JS_FORMAT_DEFAULT,
- InvalidOid);
+ InvalidOid, false);
args = lappend(args, key);
args = lappend(args, val);
@@ -3808,7 +3874,7 @@ transformJsonObjectAgg(ParseState *pstate, JsonObjectAgg *agg)
val = transformJsonValueExpr(pstate, "JSON_OBJECTAGG()",
agg->arg->value,
JS_FORMAT_DEFAULT,
- InvalidOid);
+ InvalidOid, false);
args = list_make2(key, val);
returning = transformJsonConstructorOutput(pstate, agg->constructor->output,
@@ -3864,9 +3930,8 @@ transformJsonArrayAgg(ParseState *pstate, JsonArrayAgg *agg)
Oid aggfnoid;
Oid aggtype;
- arg = transformJsonValueExpr(pstate, "JSON_ARRAYAGG()",
- agg->arg,
- JS_FORMAT_DEFAULT, InvalidOid);
+ arg = transformJsonValueExpr(pstate, "JSON_ARRAYAGG()", agg->arg,
+ JS_FORMAT_DEFAULT, InvalidOid, false);
returning = transformJsonConstructorOutput(pstate, agg->constructor->output,
list_make1(arg));
@@ -3913,9 +3978,8 @@ transformJsonArrayConstructor(ParseState *pstate, JsonArrayConstructor *ctor)
{
JsonValueExpr *jsval = castNode(JsonValueExpr, lfirst(lc));
Node *val = transformJsonValueExpr(pstate, "JSON_ARRAY()",
- jsval,
- JS_FORMAT_DEFAULT,
- InvalidOid);
+ jsval, JS_FORMAT_DEFAULT,
+ InvalidOid, false);
args = lappend(args, val);
}
@@ -4074,7 +4138,7 @@ transformJsonParseExpr(ParseState *pstate, JsonParseExpr *jsexpr)
* function-like CASTs.
*/
arg = transformJsonValueExpr(pstate, "JSON()", jsexpr->expr,
- JS_FORMAT_JSON, returning->typid);
+ JS_FORMAT_JSON, returning->typid, false);
}
return makeJsonConstructorExpr(pstate, JSCTOR_JSON_PARSE, list_make1(arg), NULL,
@@ -4119,7 +4183,7 @@ transformJsonSerializeExpr(ParseState *pstate, JsonSerializeExpr *expr)
Node *arg = transformJsonValueExpr(pstate, "JSON_SERIALIZE()",
expr->expr,
JS_FORMAT_JSON,
- InvalidOid);
+ InvalidOid, false);
if (expr->output)
{
@@ -4153,3 +4217,526 @@ transformJsonSerializeExpr(ParseState *pstate, JsonSerializeExpr *expr)
return makeJsonConstructorExpr(pstate, JSCTOR_JSON_SERIALIZE, list_make1(arg),
NULL, returning, false, false, expr->location);
}
+
+/*
+ * Transform JSON_VALUE, JSON_QUERY, JSON_EXISTS functions into a JsonExpr node.
+ */
+static Node *
+transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
+{
+ JsonExpr *jsexpr = NULL;
+ const char *func_name = NULL;
+
+ switch (func->op)
+ {
+ case JSON_EXISTS_OP:
+ func_name = "JSON_EXISTS";
+ break;
+ case JSON_QUERY_OP:
+ func_name = "JSON_QUERY";
+ break;
+ case JSON_VALUE_OP:
+ func_name = "JSON_VALUE";
+ break;
+ default:
+ elog(ERROR, "invalid JsonFuncExpr op");
+ break;
+ }
+
+ /* Only allow FORMAT specification for JSON_QUERY(). */
+ if (func->output && func->op != JSON_QUERY_OP)
+ {
+ JsonFormat *format = func->output->returning->format;
+
+ if (format->format_type != JS_FORMAT_DEFAULT ||
+ format->encoding != JS_ENC_DEFAULT)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot specify FORMAT in RETURNING clause of %s()",
+ func_name),
+ parser_errposition(pstate, format->location));
+ }
+
+ if (func->op == JSON_QUERY_OP &&
+ func->quotes != JS_QUOTES_UNSPEC &&
+ (func->wrapper == JSW_CONDITIONAL ||
+ func->wrapper == JSW_UNCONDITIONAL))
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used"),
+ parser_errposition(pstate, func->location));
+
+
+ jsexpr = transformJsonExprCommon(pstate, func, func_name);
+
+ switch (func->op)
+ {
+ case JSON_EXISTS_OP:
+ if (!OidIsValid(jsexpr->returning->typid))
+ {
+ jsexpr->returning->typid = BOOLOID;
+ jsexpr->returning->typmod = -1;
+ }
+
+ jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
+ JSON_BEHAVIOR_FALSE,
+ jsexpr->returning);
+ break;
+
+ case JSON_QUERY_OP:
+ jsexpr->wrapper = func->wrapper;
+
+ /*
+ * Keep quotes by default, omitting them only if OMIT QUOTES is
+ * specified.
+ */
+ jsexpr->omit_quotes = (func->quotes == JS_QUOTES_OMIT);
+
+ if (!OidIsValid(jsexpr->returning->typid))
+ {
+ JsonReturning *ret = jsexpr->returning;
+
+ ret->typid = JsonFuncExprDefaultReturnType(jsexpr);
+ ret->typmod = -1;
+ }
+ jsexpr->result_coercion = coerceJsonFuncExprOutput(pstate, jsexpr);
+
+ if (func->on_empty)
+ jsexpr->on_empty = transformJsonBehavior(pstate,
+ func->on_empty,
+ JSON_BEHAVIOR_NULL,
+ jsexpr->returning);
+ jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
+ JSON_BEHAVIOR_NULL,
+ jsexpr->returning);
+ break;
+
+ case JSON_VALUE_OP:
+ if (!OidIsValid(jsexpr->returning->typid))
+ {
+ /* Make JSON_VALUE return text by default */
+ jsexpr->returning->typid = TEXTOID;
+ jsexpr->returning->typmod = -1;
+ }
+ jsexpr->result_coercion = coerceJsonFuncExprOutput(pstate, jsexpr);
+
+ /*
+ * Initialize expressions to coerce the scalar value returned
+ * by JsonPathValue() to the "returning" type.
+ */
+ if (jsexpr->result_coercion)
+ jsexpr->item_coercions =
+ InitJsonItemCoercions(pstate, jsexpr->returning,
+ exprType(jsexpr->formatted_expr));
+
+ if (func->on_empty)
+ jsexpr->on_empty = transformJsonBehavior(pstate,
+ func->on_empty,
+ JSON_BEHAVIOR_NULL,
+ jsexpr->returning);
+ jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
+ JSON_BEHAVIOR_NULL,
+ jsexpr->returning);
+ break;
+
+ default:
+ elog(ERROR, "invalid JsonFuncExpr op");
+ break;
+ }
+
+ return (Node *) jsexpr;
+}
+
+/*
+ * Common code for JSON_VALUE, JSON_QUERY, JSON_EXISTS transformation
+ * into a JsonExpr node.
+ */
+static JsonExpr *
+transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func,
+ const char *constructName)
+{
+ JsonExpr *jsexpr = makeNode(JsonExpr);
+ Node *pathspec;
+
+ jsexpr->location = func->location;
+ jsexpr->op = func->op;
+ jsexpr->formatted_expr = transformJsonValueExpr(pstate, constructName,
+ func->context_item,
+ JS_FORMAT_JSON,
+ InvalidOid, false);
+
+ if (exprType(jsexpr->formatted_expr) != JSONBOID)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s() is not yet implemented for the json type",
+ constructName),
+ errhint("Try casting the argument to jsonb"),
+ parser_errposition(pstate, exprLocation(jsexpr->formatted_expr)));
+
+ jsexpr->format = func->context_item->format;
+
+ /* Both set in the caller. */
+ jsexpr->result_coercion = NULL;
+ jsexpr->omit_quotes = false;
+
+ pathspec = transformExprRecurse(pstate, func->pathspec);
+
+ jsexpr->path_spec =
+ coerce_to_target_type(pstate, pathspec, exprType(pathspec),
+ JSONPATHOID, -1,
+ COERCION_EXPLICIT, COERCE_IMPLICIT_CAST,
+ exprLocation(pathspec));
+ if (!jsexpr->path_spec)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("JSON path expression must be of type %s, not of type %s",
+ "jsonpath", format_type_be(exprType(pathspec))),
+ parser_errposition(pstate, exprLocation(pathspec))));
+
+ /*
+ * Transform and coerce to json[b] passing arguments, whose format is
+ * determined by context item type.
+ */
+ transformJsonPassingArgs(pstate, constructName,
+ exprType(jsexpr->formatted_expr) == JSONBOID ?
+ JS_FORMAT_JSONB : JS_FORMAT_JSON,
+ func->passing,
+ &jsexpr->passing_values,
+ &jsexpr->passing_names);
+
+ jsexpr->returning = transformJsonOutput(pstate, func->output, false);
+
+ /* JSON_QUERY supports specifying FORMAT explicitly. */
+ if (func->op != JSON_QUERY_OP)
+ {
+ jsexpr->returning->format->format_type = JS_FORMAT_DEFAULT;
+ jsexpr->returning->format->encoding = JS_ENC_DEFAULT;
+ }
+
+ return jsexpr;
+}
+
+/*
+ * Transform a JSON PASSING clause.
+ */
+static void
+transformJsonPassingArgs(ParseState *pstate, const char *constructName,
+ JsonFormatType format, List *args,
+ List **passing_values, List **passing_names)
+{
+ ListCell *lc;
+
+ *passing_values = NIL;
+ *passing_names = NIL;
+
+ foreach(lc, args)
+ {
+ JsonArgument *arg = castNode(JsonArgument, lfirst(lc));
+ Node *expr = transformJsonValueExpr(pstate, constructName,
+ arg->val, format,
+ InvalidOid, true);
+
+ *passing_values = lappend(*passing_values, expr);
+ *passing_names = lappend(*passing_names, makeString(arg->name));
+ }
+}
+
+/*
+ * Create an expression to coerce the output of JSON_VALUE() / JSON_QUERY()
+ * to the output type, if needed.
+ */
+static Node *
+coerceJsonFuncExprOutput(ParseState *pstate, JsonExpr *jsexpr)
+{
+ JsonReturning *returning = jsexpr->returning;
+ Node *context_item = jsexpr->formatted_expr;
+ int default_typmod;
+ Oid default_typid;
+
+ Assert(returning);
+
+ /*
+ * Use a JsonCoercion node to implement a non-default QUOTES or WRAPPER
+ * behavior.
+ */
+ if (jsexpr->omit_quotes || jsexpr->wrapper != JSW_UNSPEC)
+ {
+ JsonCoercion *coercion = makeJsonCoercion(returning);
+
+ coercion->omit_quotes = jsexpr->omit_quotes;
+
+ return (Node *) coercion;
+ }
+
+ default_typid = JsonFuncExprDefaultReturnType(jsexpr);
+ default_typmod = -1;
+ if (returning->typid != default_typid ||
+ returning->typmod != default_typmod)
+ {
+ /*
+ * We abuse CaseTestExpr here as placeholder to pass the result of
+ * evaluating the JSON_VALUE/QUERY jsonpath expression as input to the
+ * coercion expression.
+ */
+ CaseTestExpr *placeholder = makeNode(CaseTestExpr);
+
+ placeholder->typeId = exprType(context_item);
+ placeholder->typeMod = exprTypmod(context_item);
+ placeholder->collation = exprCollation(context_item);
+
+ Assert(placeholder->typeId == default_typid);
+ Assert(placeholder->typeMod == default_typmod);
+
+ return coerceJsonExpr(pstate, (Node *) placeholder, returning);
+ }
+
+ return NULL;
+}
+
+/* Returns the default type for a given JsonExpr for a given JsonFormat. */
+static Oid
+JsonFuncExprDefaultReturnType(JsonExpr *jsexpr)
+{
+ JsonFormat *format = jsexpr->format;
+ Node *context_item = jsexpr->formatted_expr;
+
+ Assert(format);
+ if (format->format_type == JS_FORMAT_JSONB)
+ return JSONBOID;
+ else if (format->format_type == JS_FORMAT_DEFAULT &&
+ exprType(context_item) == JSONBOID)
+ return JSONBOID;
+
+ return JSONOID;
+}
+
+/*
+ * Returns a JsonCoercion node to coerce a jsonb-valued expression to the
+ * target type given by 'returning' using either json_populate_type() or
+ * by using the target type's input function.
+ */
+static JsonCoercion *
+makeJsonCoercion(const JsonReturning *returning)
+{
+ JsonCoercion *coercion = makeNode(JsonCoercion);
+
+ coercion->targettype = returning->typid;
+ coercion->targettypmod = returning->typmod;
+
+ return coercion;
+}
+
+/*
+ * Set up a JsonCoercion node to:
+ *
+ * - coerce expression to the output returning type, or
+ * - coerce using json_populate_type() if returning type requires it, or
+ * - coerce via I/O.
+ */
+static Node *
+coerceJsonExpr(ParseState *pstate, Node *expr, const JsonReturning *returning)
+{
+ Node *coerced_expr;
+
+ coerced_expr = coerceJsonFuncExpr(pstate, expr, returning, false);
+ if (coerced_expr)
+ {
+ if (coerced_expr == expr)
+ return NULL;
+ return coerced_expr;
+ }
+
+ return (Node *) makeJsonCoercion(returning);
+}
+
+/*
+ * Initialize JsonCoercion nodes for coercing a given JSON item value produced
+ * by JSON_VALUE to the target "returning" type; also see
+ * ExecPrepareJsonItemCoercion().
+ */
+static List *
+InitJsonItemCoercions(ParseState *pstate, const JsonReturning *returning,
+ Oid contextItemTypeId)
+{
+ List *item_coercions = NIL;
+ int i;
+ Oid typeoid;
+ struct
+ {
+ JsonItemType item_type;
+ Oid typeoid;
+ } item_types[] =
+ {
+ {JsonItemTypeNull, UNKNOWNOID},
+ {JsonItemTypeString, TEXTOID},
+ {JsonItemTypeNumeric, NUMERICOID},
+ {JsonItemTypeBoolean, BOOLOID},
+ {JsonItemTypeDate, DATEOID},
+ {JsonItemTypeTime, TIMEOID},
+ {JsonItemTypeTimetz, TIMETZOID},
+ {JsonItemTypeTimestamp, TIMESTAMPOID},
+ {JsonItemTypeTimestamptz, TIMESTAMPTZOID},
+ {JsonItemTypeComposite, contextItemTypeId},
+ {JsonItemTypeInvalid, InvalidOid}
+ };
+
+ for (i = 0; OidIsValid(typeoid = item_types[i].typeoid); i++)
+ {
+ Node *expr;
+ JsonItemCoercion *item_coercion = makeNode(JsonItemCoercion);
+
+ if (typeoid == UNKNOWNOID)
+ {
+ expr = (Node *) makeNullConst(UNKNOWNOID, -1, InvalidOid);
+ }
+ else
+ {
+ CaseTestExpr *placeholder = makeNode(CaseTestExpr);
+
+ /*
+ * We abuse CaseTestExpr here as placeholder to pass the result of
+ * JSON_VALUE jsonpath expression to the coercion function.
+ */
+ placeholder->typeId = item_types[i].typeoid;
+ placeholder->typeMod = -1;
+ placeholder->collation = InvalidOid;
+
+ expr = (Node *) placeholder;
+ }
+
+ item_coercion->item_type = item_types[i].item_type;
+ item_coercion->coercion = coerceJsonExpr(pstate, expr, returning);
+ item_coercions = lappend(item_coercions, item_coercion);
+ }
+
+ return item_coercions;
+}
+
+/*
+ * Returns constant values to be returned to the user for various
+ * non-ERROR ON ERROR/EMPTY behaviors.
+ *
+ * Note that JSON_BEHAVIOR_DEFAULT expression is handled by the
+ * caller separately.
+ */
+static Node *
+GetJsonBehaviorConstExpr(JsonBehaviorType btype, int location)
+{
+ Datum val = (Datum) 0;
+ Oid typid = JSONBOID;
+ int len = -1;
+ bool isbyval = false;
+ bool isnull = false;
+ Const *con;
+
+ switch (btype)
+ {
+ case JSON_BEHAVIOR_EMPTY_ARRAY:
+ val = DirectFunctionCall1(jsonb_in, CStringGetDatum("[]"));
+ break;
+
+ case JSON_BEHAVIOR_EMPTY_OBJECT:
+ val = DirectFunctionCall1(jsonb_in, CStringGetDatum("{}"));
+ break;
+
+ case JSON_BEHAVIOR_TRUE:
+ val = BoolGetDatum(true);
+ typid = BOOLOID;
+ len = sizeof(bool);
+ isbyval = true;
+ break;
+
+ case JSON_BEHAVIOR_FALSE:
+ val = BoolGetDatum(false);
+ typid = BOOLOID;
+ len = sizeof(bool);
+ isbyval = true;
+ break;
+
+ case JSON_BEHAVIOR_NULL:
+ case JSON_BEHAVIOR_UNKNOWN:
+ case JSON_BEHAVIOR_EMPTY:
+ val = (Datum) 0;
+ isnull = true;
+ typid = INT4OID;
+ len = sizeof(int32);
+ isbyval = true;
+ break;
+
+ case JSON_BEHAVIOR_DEFAULT:
+ case JSON_BEHAVIOR_ERROR:
+ /* Always handled in the caller. */
+ Assert(false);
+ break;
+
+ default:
+ elog(ERROR, "unrecognized SQL/JSON behavior %d", btype);
+ break;
+ }
+
+ con = makeConst(typid, -1, InvalidOid, len, val, isnull, isbyval);
+ con->location = location;
+
+ return (Node *) con;
+}
+
+/*
+ * Transform a JSON BEHAVIOR clause.
+ */
+static JsonBehavior *
+transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
+ JsonBehaviorType default_behavior,
+ JsonReturning *returning)
+{
+ JsonBehaviorType behavior_type = default_behavior;
+ Node *expr = NULL;
+ JsonCoercion *coercion = NULL;
+ int location = -1;
+
+ if (behavior)
+ {
+ behavior_type = behavior->btype;
+ location = behavior->location;
+ if (behavior_type == JSON_BEHAVIOR_DEFAULT)
+ expr = transformExprRecurse(pstate, behavior->expr);
+ }
+
+ if (expr == NULL && behavior_type != JSON_BEHAVIOR_ERROR)
+ expr = GetJsonBehaviorConstExpr(behavior_type, location);
+
+ if (expr)
+ {
+ Node *coerced_expr = expr;
+ bool isnull = (IsA(expr, Const) && ((Const *) expr)->constisnull);
+
+ /*
+ * Coerce NULLs and "default" (that is, not specified by the user)
+ * jsonb-valued expressions using a JsonCoercion node.
+ *
+ * For other (user-specified) non-NULL values, try to find a cast
+ * and error out if one is not found.
+ */
+ if (isnull ||
+ (exprType(expr) == JSONBOID &&
+ behavior_type == default_behavior))
+ coercion = makeJsonCoercion(returning);
+ else
+ coerced_expr =
+ coerce_to_target_type(pstate, expr, exprType(expr),
+ returning->typid, returning->typmod,
+ COERCION_EXPLICIT, COERCE_EXPLICIT_CAST,
+ exprLocation((Node *) behavior));
+
+ if (coerced_expr == NULL)
+ ereport(ERROR,
+ errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast behavior expression of type %s to %s",
+ format_type_be(exprType(expr)),
+ format_type_be(returning->typid)),
+ parser_errposition(pstate, exprLocation(expr)));
+ else
+ expr = coerced_expr;
+ }
+
+ return makeJsonBehavior(behavior_type, expr, coercion, location);
+}
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 0cd904f8da..ea5ac6bafe 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1989,6 +1989,21 @@ FigureColnameInternal(Node *node, char **name)
/* make JSON_ARRAYAGG act like a regular function */
*name = "json_arrayagg";
return 2;
+ case T_JsonFuncExpr:
+ /* make SQL/JSON functions act like a regular function */
+ switch (((JsonFuncExpr *) node)->op)
+ {
+ case JSON_EXISTS_OP:
+ *name = "json_exists";
+ return 2;
+ case JSON_QUERY_OP:
+ *name = "json_query";
+ return 2;
+ case JSON_VALUE_OP:
+ *name = "json_value";
+ return 2;
+ }
+ break;
default:
break;
}
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 83e1f1265c..41bb0e0546 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -4426,6 +4426,50 @@ parse_datetime(text *date_txt, text *fmt, Oid collid, bool strict,
}
}
+/*
+ * Parses the datetime format string in 'fmt_str' and returns true if it
+ * contains a timezone specifier, false if not.
+ */
+bool
+datetime_format_has_tz(const char *fmt_str)
+{
+ bool incache;
+ int fmt_len = strlen(fmt_str);
+ int result;
+ FormatNode *format;
+
+ if (fmt_len > DCH_CACHE_SIZE)
+ {
+ /*
+ * Allocate new memory if format picture is bigger than static cache
+ * and do not use cache (call parser always)
+ */
+ incache = false;
+
+ format = (FormatNode *) palloc((fmt_len + 1) * sizeof(FormatNode));
+
+ parse_format(format, fmt_str, DCH_keywords,
+ DCH_suff, DCH_index, DCH_FLAG, NULL);
+ }
+ else
+ {
+ /*
+ * Use cache buffers
+ */
+ DCHCacheEntry *ent = DCH_cache_fetch(fmt_str, false);
+
+ incache = true;
+ format = ent->format;
+ }
+
+ result = DCH_datetime_type(format);
+
+ if (!incache)
+ pfree(format);
+
+ return result & DCH_ZONED;
+}
+
/*
* do_to_timestamp: shared code for to_timestamp and to_date
*
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index c10b3fbedf..6d797c0953 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -2163,3 +2163,34 @@ jsonb_float8(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(retValue);
}
+
+/*
+ * Convert jsonb to a C-string stripping quotes from scalar strings.
+ */
+char *
+JsonbUnquote(Jsonb *jb)
+{
+ if (JB_ROOT_IS_SCALAR(jb))
+ {
+ JsonbValue v;
+
+ (void) JsonbExtractScalar(&jb->root, &v);
+
+ if (v.type == jbvString)
+ return pnstrdup(v.val.string.val, v.val.string.len);
+ else if (v.type == jbvBool)
+ return pstrdup(v.val.boolean ? "true" : "false");
+ else if (v.type == jbvNumeric)
+ return DatumGetCString(DirectFunctionCall1(numeric_out,
+ PointerGetDatum(v.val.numeric)));
+ else if (v.type == jbvNull)
+ return pstrdup("null");
+ else
+ {
+ elog(ERROR, "unrecognized jsonb value type %d", v.type);
+ return NULL;
+ }
+ }
+ else
+ return JsonbToCString(NULL, &jb->root, VARSIZE(jb));
+}
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 623289305a..5ebef026c8 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -3308,6 +3308,62 @@ prepare_column_cache(ColumnIOData *column,
ReleaseSysCache(tup);
}
+/*
+ * Populate and return the value of specified type from a given json/jsonb
+ * value 'json_val'. 'cache' is caller-specified pointer to save the
+ * ColumnIOData that will be initialized on the 1st call and then reused
+ * during any subsequent calls. 'mcxt' gives the memory context to allocate
+ * the ColumnIOData and any other subsidiary memory in. 'escontext',
+ * if not NULL, tells that any errors that occur should be handled softly.
+ */
+Datum
+json_populate_type(Datum json_val, Oid json_type,
+ Oid typid, int32 typmod,
+ void **cache, MemoryContext mcxt,
+ bool *isnull,
+ Node *escontext)
+{
+ JsValue jsv = {0};
+ JsonbValue jbv;
+
+ jsv.is_json = json_type == JSONOID;
+
+ if (*isnull)
+ {
+ if (jsv.is_json)
+ jsv.val.json.str = NULL;
+ else
+ jsv.val.jsonb = NULL;
+ }
+ else if (jsv.is_json)
+ {
+ text *json = DatumGetTextPP(json_val);
+
+ jsv.val.json.str = VARDATA_ANY(json);
+ jsv.val.json.len = VARSIZE_ANY_EXHDR(json);
+ jsv.val.json.type = JSON_TOKEN_INVALID; /* not used in
+ * populate_composite() */
+ }
+ else
+ {
+ Jsonb *jsonb = DatumGetJsonbP(json_val);
+
+ jsv.val.jsonb = &jbv;
+
+ /* fill binary jsonb value pointing to jb */
+ jbv.type = jbvBinary;
+ jbv.val.binary.data = &jsonb->root;
+ jbv.val.binary.len = VARSIZE(jsonb) - VARHDRSZ;
+ }
+
+ if (*cache == NULL)
+ *cache = MemoryContextAllocZero(mcxt, sizeof(ColumnIOData));
+
+ return populate_record_field(*cache, typid, typmod, NULL, mcxt,
+ PointerGetDatum(NULL), &jsv, isnull,
+ escontext);
+}
+
/* recursively populate a record field or an array element from a json/jsonb value */
static Datum
populate_record_field(ColumnIOData *col,
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index d02c03e014..7cea6ad45c 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -68,7 +68,9 @@
#include "libpq/pqformat.h"
#include "nodes/miscnodes.h"
#include "miscadmin.h"
+#include "nodes/nodeFuncs.h"
#include "utils/builtins.h"
+#include "utils/formatting.h"
#include "utils/json.h"
#include "utils/jsonpath.h"
@@ -1110,3 +1112,260 @@ jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from, JsonPathItem *to,
return true;
}
+
+/* SQL/JSON datatype status: */
+enum JsonPathDatatypeStatus
+{
+ jpdsNonDateTime, /* null, bool, numeric, string, array, object */
+ jpdsUnknownDateTime, /* unknown datetime type */
+ jpdsDateTimeZoned, /* timetz, timestamptz */
+ jpdsDateTimeNonZoned, /* time, timestamp, date */
+};
+
+/* Context for jspIsMutableWalker() */
+struct JsonPathMutableContext
+{
+ List *varnames; /* list of variable names */
+ List *varexprs; /* list of variable expressions */
+ enum JsonPathDatatypeStatus current; /* status of @ item */
+ bool lax; /* jsonpath is lax or strict */
+ bool mutable; /* resulting mutability status */
+};
+
+static enum JsonPathDatatypeStatus jspIsMutableWalker(JsonPathItem *jpi,
+ struct JsonPathMutableContext *cxt);
+
+/*
+ * Function to check whether jsonpath expression is mutable to be used in the
+ * planner function contain_mutable_functions().
+ */
+bool
+jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
+{
+ struct JsonPathMutableContext cxt;
+ JsonPathItem jpi;
+
+ cxt.varnames = varnames;
+ cxt.varexprs = varexprs;
+ cxt.current = jpdsNonDateTime;
+ cxt.lax = (path->header & JSONPATH_LAX) != 0;
+ cxt.mutable = false;
+
+ jspInit(&jpi, path);
+ (void) jspIsMutableWalker(&jpi, &cxt);
+
+ return cxt.mutable;
+}
+
+/*
+ * Recursive walker for jspIsMutable()
+ */
+static enum JsonPathDatatypeStatus
+jspIsMutableWalker(JsonPathItem *jpi, struct JsonPathMutableContext *cxt)
+{
+ JsonPathItem next;
+ enum JsonPathDatatypeStatus status = jpdsNonDateTime;
+
+ while (!cxt->mutable)
+ {
+ JsonPathItem arg;
+ enum JsonPathDatatypeStatus leftStatus;
+ enum JsonPathDatatypeStatus rightStatus;
+
+ switch (jpi->type)
+ {
+ case jpiRoot:
+ Assert(status == jpdsNonDateTime);
+ break;
+
+ case jpiCurrent:
+ Assert(status == jpdsNonDateTime);
+ status = cxt->current;
+ break;
+
+ case jpiFilter:
+ {
+ enum JsonPathDatatypeStatus prevStatus = cxt->current;
+
+ cxt->current = status;
+ jspGetArg(jpi, &arg);
+ jspIsMutableWalker(&arg, cxt);
+
+ cxt->current = prevStatus;
+ break;
+ }
+
+ case jpiVariable:
+ {
+ int32 len;
+ const char *name = jspGetString(jpi, &len);
+ ListCell *lc1;
+ ListCell *lc2;
+
+ Assert(status == jpdsNonDateTime);
+
+ forboth(lc1, cxt->varnames, lc2, cxt->varexprs)
+ {
+ String *varname = lfirst_node(String, lc1);
+ Node *varexpr = lfirst(lc2);
+
+ if (strncmp(varname->sval, name, len))
+ continue;
+
+ switch (exprType(varexpr))
+ {
+ case DATEOID:
+ case TIMEOID:
+ case TIMESTAMPOID:
+ status = jpdsDateTimeNonZoned;
+ break;
+
+ case TIMETZOID:
+ case TIMESTAMPTZOID:
+ status = jpdsDateTimeZoned;
+ break;
+
+ default:
+ status = jpdsNonDateTime;
+ break;
+ }
+
+ break;
+ }
+ break;
+ }
+
+ case jpiEqual:
+ case jpiNotEqual:
+ case jpiLess:
+ case jpiGreater:
+ case jpiLessOrEqual:
+ case jpiGreaterOrEqual:
+ Assert(status == jpdsNonDateTime);
+ jspGetLeftArg(jpi, &arg);
+ leftStatus = jspIsMutableWalker(&arg, cxt);
+
+ jspGetRightArg(jpi, &arg);
+ rightStatus = jspIsMutableWalker(&arg, cxt);
+
+ /*
+ * Comparison of datetime type with different timezone status
+ * is mutable.
+ */
+ if (leftStatus != jpdsNonDateTime &&
+ rightStatus != jpdsNonDateTime &&
+ (leftStatus == jpdsUnknownDateTime ||
+ rightStatus == jpdsUnknownDateTime ||
+ leftStatus != rightStatus))
+ cxt->mutable = true;
+ break;
+
+ case jpiNot:
+ case jpiIsUnknown:
+ case jpiExists:
+ case jpiPlus:
+ case jpiMinus:
+ Assert(status == jpdsNonDateTime);
+ jspGetArg(jpi, &arg);
+ jspIsMutableWalker(&arg, cxt);
+ break;
+
+ case jpiAnd:
+ case jpiOr:
+ case jpiAdd:
+ case jpiSub:
+ case jpiMul:
+ case jpiDiv:
+ case jpiMod:
+ case jpiStartsWith:
+ Assert(status == jpdsNonDateTime);
+ jspGetLeftArg(jpi, &arg);
+ jspIsMutableWalker(&arg, cxt);
+ jspGetRightArg(jpi, &arg);
+ jspIsMutableWalker(&arg, cxt);
+ break;
+
+ case jpiIndexArray:
+ for (int i = 0; i < jpi->content.array.nelems; i++)
+ {
+ JsonPathItem from;
+ JsonPathItem to;
+
+ if (jspGetArraySubscript(jpi, &from, &to, i))
+ jspIsMutableWalker(&to, cxt);
+
+ jspIsMutableWalker(&from, cxt);
+ }
+ /* FALLTHROUGH */
+
+ case jpiAnyArray:
+ if (!cxt->lax)
+ status = jpdsNonDateTime;
+ break;
+
+ case jpiAny:
+ if (jpi->content.anybounds.first > 0)
+ status = jpdsNonDateTime;
+ break;
+
+ case jpiDatetime:
+ if (jpi->content.arg)
+ {
+ char *template;
+
+ jspGetArg(jpi, &arg);
+ if (arg.type != jpiString)
+ {
+ status = jpdsNonDateTime;
+ break; /* there will be runtime error */
+ }
+
+ template = jspGetString(&arg, NULL);
+ if (datetime_format_has_tz(template))
+ status = jpdsDateTimeZoned;
+ else
+ status = jpdsDateTimeNonZoned;
+ }
+ else
+ {
+ status = jpdsUnknownDateTime;
+ }
+ break;
+
+ case jpiLikeRegex:
+ Assert(status == jpdsNonDateTime);
+ jspInitByBuffer(&arg, jpi->base, jpi->content.like_regex.expr);
+ jspIsMutableWalker(&arg, cxt);
+ break;
+
+ /* literals */
+ case jpiNull:
+ case jpiString:
+ case jpiNumeric:
+ case jpiBool:
+ /* accessors */
+ case jpiKey:
+ case jpiAnyKey:
+ /* special items */
+ case jpiSubscript:
+ case jpiLast:
+ /* item methods */
+ case jpiType:
+ case jpiSize:
+ case jpiAbs:
+ case jpiFloor:
+ case jpiCeiling:
+ case jpiDouble:
+ case jpiKeyValue:
+ status = jpdsNonDateTime;
+ break;
+ }
+
+ if (!jspGetNext(jpi, &next))
+ break;
+
+ jpi = &next;
+ }
+
+ return status;
+}
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 6da6e27ee6..6d61d87f01 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -3085,7 +3085,7 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
singleton = count > 0 ? JsonValueListHead(&found) : NULL;
if (singleton == NULL)
wrap = false;
- else if (wrapper == JSW_NONE)
+ else if (wrapper == JSW_NONE || wrapper == JSW_UNSPEC)
wrap = false;
else if (wrapper == JSW_UNCONDITIONAL)
wrap = true;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0b2a164057..2735348416 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -476,6 +476,8 @@ static void get_const_expr(Const *constval, deparse_context *context,
int showtype);
static void get_const_collation(Const *constval, deparse_context *context);
static void get_json_format(JsonFormat *format, StringInfo buf);
+static void get_json_returning(JsonReturning *returning, StringInfo buf,
+ bool json_format_by_default);
static void get_json_constructor(JsonConstructorExpr *ctor,
deparse_context *context, bool showimplicit);
static void get_json_constructor_options(JsonConstructorExpr *ctor,
@@ -518,6 +520,8 @@ static char *generate_qualified_type_name(Oid typid);
static text *string_to_text(char *str);
static char *flatten_reloptions(Oid relid);
static void get_reloptions(StringInfo buf, Datum reloptions);
+static void get_json_path_spec(Node *path_spec, deparse_context *context,
+ bool showimplicit);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -8300,6 +8304,7 @@ isSimpleNode(Node *node, Node *parentNode, int prettyFlags)
case T_WindowFunc:
case T_FuncExpr:
case T_JsonConstructorExpr:
+ case T_JsonExpr:
/* function-like: name(..) or name[..] */
return true;
@@ -8471,6 +8476,7 @@ isSimpleNode(Node *node, Node *parentNode, int prettyFlags)
case T_GroupingFunc: /* own parentheses */
case T_WindowFunc: /* own parentheses */
case T_CaseExpr: /* other separators */
+ case T_JsonExpr: /* own parentheses */
return true;
default:
return false;
@@ -8586,6 +8592,64 @@ get_rule_expr_paren(Node *node, deparse_context *context,
appendStringInfoChar(context->buf, ')');
}
+static void
+get_json_behavior(JsonBehavior *behavior, deparse_context *context,
+ const char *on)
+{
+ /*
+ * The order of array elements must correspond to the order of
+ * JsonBehaviorType members.
+ */
+ const char *behavior_names[] =
+ {
+ " NULL",
+ " ERROR",
+ " EMPTY",
+ " TRUE",
+ " FALSE",
+ " UNKNOWN",
+ " EMPTY ARRAY",
+ " EMPTY OBJECT",
+ " DEFAULT "
+ };
+
+ if ((int) behavior->btype < 0 || behavior->btype >= lengthof(behavior_names))
+ elog(ERROR, "invalid json behavior type: %d", behavior->btype);
+
+ appendStringInfoString(context->buf, behavior_names[behavior->btype]);
+
+ if (behavior->btype == JSON_BEHAVIOR_DEFAULT)
+ get_rule_expr(behavior->expr, context, false);
+
+ appendStringInfo(context->buf, " ON %s", on);
+}
+
+/*
+ * get_json_expr_options
+ *
+ * Parse back common options for JSON_QUERY, JSON_VALUE, JSON_EXISTS.
+ */
+static void
+get_json_expr_options(JsonExpr *jsexpr, deparse_context *context,
+ JsonBehaviorType default_behavior)
+{
+ if (jsexpr->op == JSON_QUERY_OP)
+ {
+ if (jsexpr->wrapper == JSW_CONDITIONAL)
+ appendStringInfo(context->buf, " WITH CONDITIONAL WRAPPER");
+ else if (jsexpr->wrapper == JSW_UNCONDITIONAL)
+ appendStringInfo(context->buf, " WITH UNCONDITIONAL WRAPPER");
+
+ if (jsexpr->omit_quotes)
+ appendStringInfo(context->buf, " OMIT QUOTES");
+ }
+
+ if (jsexpr->on_empty && jsexpr->on_empty->btype != default_behavior)
+ get_json_behavior(jsexpr->on_empty, context, "EMPTY");
+
+ if (jsexpr->on_error && jsexpr->on_error->btype != default_behavior)
+ get_json_behavior(jsexpr->on_error, context, "ERROR");
+}
/* ----------
* get_rule_expr - Parse back an expression
@@ -9745,6 +9809,7 @@ get_rule_expr(Node *node, deparse_context *context,
}
break;
+
case T_JsonValueExpr:
{
JsonValueExpr *jve = (JsonValueExpr *) node;
@@ -9794,6 +9859,64 @@ get_rule_expr(Node *node, deparse_context *context,
}
break;
+ case T_JsonExpr:
+ {
+ JsonExpr *jexpr = (JsonExpr *) node;
+
+ switch (jexpr->op)
+ {
+ case JSON_EXISTS_OP:
+ appendStringInfoString(buf, "JSON_EXISTS(");
+ break;
+ case JSON_QUERY_OP:
+ appendStringInfoString(buf, "JSON_QUERY(");
+ break;
+ case JSON_VALUE_OP:
+ appendStringInfoString(buf, "JSON_VALUE(");
+ break;
+ }
+
+ get_rule_expr(jexpr->formatted_expr, context, showimplicit);
+
+ appendStringInfoString(buf, ", ");
+
+ get_json_path_spec(jexpr->path_spec, context, showimplicit);
+
+ if (jexpr->passing_values)
+ {
+ ListCell *lc1,
+ *lc2;
+ bool needcomma = false;
+
+ appendStringInfoString(buf, " PASSING ");
+
+ forboth(lc1, jexpr->passing_names,
+ lc2, jexpr->passing_values)
+ {
+ if (needcomma)
+ appendStringInfoString(buf, ", ");
+ needcomma = true;
+
+ get_rule_expr((Node *) lfirst(lc2), context, showimplicit);
+ appendStringInfo(buf, " AS %s",
+ ((String *) lfirst_node(String, lc1))->sval);
+ }
+ }
+
+ if (jexpr->op != JSON_EXISTS_OP ||
+ jexpr->returning->typid != BOOLOID)
+ get_json_returning(jexpr->returning, context->buf,
+ jexpr->op == JSON_QUERY_OP);
+
+ get_json_expr_options(jexpr, context,
+ jexpr->op != JSON_EXISTS_OP ?
+ JSON_BEHAVIOR_NULL :
+ JSON_BEHAVIOR_FALSE);
+
+ appendStringInfoString(buf, ")");
+ }
+ break;
+
case T_List:
{
char *sep;
@@ -9917,6 +10040,7 @@ looks_like_function(Node *node)
case T_MinMaxExpr:
case T_SQLValueFunction:
case T_XmlExpr:
+ case T_JsonExpr:
/* these are all accepted by func_expr_common_subexpr */
return true;
default:
@@ -10786,6 +10910,18 @@ get_const_collation(Const *constval, deparse_context *context)
}
}
+/*
+ * get_json_path_spec - Parse back a JSON path specification
+ */
+static void
+get_json_path_spec(Node *path_spec, deparse_context *context, bool showimplicit)
+{
+ if (IsA(path_spec, Const))
+ get_const_expr((Const *) path_spec, context, -1);
+ else
+ get_rule_expr(path_spec, context, showimplicit);
+}
+
/*
* get_json_format - Parse back a JsonFormat node
*/
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index a28ddcdd77..5db354f220 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -240,6 +240,9 @@ typedef enum ExprEvalOp
EEOP_XMLEXPR,
EEOP_JSON_CONSTRUCTOR,
EEOP_IS_JSON,
+ EEOP_JSONEXPR_PATH,
+ EEOP_JSONEXPR_COERCION,
+ EEOP_JSONEXPR_COERCION_FINISH,
EEOP_AGGREF,
EEOP_GROUPING_FUNC,
EEOP_WINDOW_FUNC,
@@ -692,6 +695,21 @@ typedef struct ExprEvalStep
JsonIsPredicate *pred; /* original expression node */
} is_json;
+ /* for EEOP_JSONEXPR_PATH */
+ struct
+ {
+ struct JsonExprState *jsestate;
+ } jsonexpr;
+
+ /* for EEOP_JSONEXPR_COERCION */
+ struct
+ {
+ JsonCoercion *coercion;
+ FmgrInfo *input_finfo;
+ Oid typioparam;
+ void *json_populate_type_cache;
+ ErrorSaveContext *escontext;
+ } jsonexpr_coercion;
} d;
} ExprEvalStep;
@@ -755,7 +773,6 @@ typedef struct JsonConstructorExprState
int nargs;
} JsonConstructorExprState;
-
/* functions in execExpr.c */
extern void ExprEvalPushStep(ExprState *es, const ExprEvalStep *s);
@@ -809,6 +826,11 @@ extern void ExecEvalXmlExpr(ExprState *state, ExprEvalStep *op);
extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
extern void ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op);
+extern void ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext);
+extern void ExecEvalJsonCoercionFinish(ExprState *state, ExprEvalStep *op);
+extern int ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext);
extern void ExecEvalGroupingFunc(ExprState *state, ExprEvalStep *op);
extern void ExecEvalSubPlan(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 444a5f0fd5..2e8df2301f 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1008,6 +1008,92 @@ typedef struct DomainConstraintState
ExprState *check_exprstate; /* check_expr's eval state, or NULL */
} DomainConstraintState;
+/*
+ * Information about the state of JsonPath* evaluation.
+ */
+typedef struct JsonExprPostEvalState
+{
+ /* Did JsonPath* evaluation cause an error? */
+ NullableDatum error;
+
+ /* Is the result of JsonPath* evaluation empty? */
+ NullableDatum empty;
+
+ /*
+ * ExecEvalJsonExprPath() will set this to the address of the step to
+ * use to coerce the result of JsonPath* evaluation to the RETURNING type.
+ * Also see the description of possible step addresses that this could be
+ * set to in the definition of JsonExprState.
+ */
+ int jump_eval_coercion;
+} JsonExprPostEvalState;
+
+/* State for evaluating a JsonExpr, too big to inline */
+typedef struct JsonExprState
+{
+ /* original expression node */
+ JsonExpr *jsexpr;
+
+ /* value/isnull for formatted_expr */
+ NullableDatum formatted_expr;
+
+ /* value/isnull for pathspec */
+ NullableDatum pathspec;
+
+ /* JsonPathVariable entries for passing_values */
+ List *args;
+
+ /*
+ * Per-row result status info populated by ExecEvalJsonExprPath()
+ * and ExecEvalJsonCoercionFinish().
+ */
+ JsonExprPostEvalState post_eval;
+
+ /*
+ * Addresses of the steps that implements the non-ERROR variant of ON EMPTY
+ * and ON ERROR behaviors, respectively.
+ */
+ int jump_empty;
+ int jump_error;
+
+ /*
+ * Addresses of steps to perform the coercion of the JsonPath* result value
+ * to the RETURNING type. Each address points to either 1) a special
+ * EEOP_JSONEXPR_COERCION step that handles coercion using the RETURNING
+ * type's input function or by using json_via_populate(), or 2) an
+ * expression such as CoerceViaIO. It may be -1 if no coercion is
+ * necessary.
+ *
+ * jump_eval_result_coercion points to the step to evaluate the coercion
+ * given in JsonExpr.result_coercion.
+ */
+ int jump_eval_result_coercion;
+
+ /* Jump to end to skip all the steps after EEOP_JSONEXPR_PATH. */
+ int jump_end;
+
+ /* eval_item_coercion_jumps is an array of num_item_coercions elements
+ * each containing a step address to evaluate the coercion from a value of
+ * the given JsonItemType to the RETURNING type, or -1 if no coercion is
+ * necessary. item_coercion_via_expr is an array of boolean flags of the
+ * same length that indicates whether each valid step address in the
+ * eval_item_coercion_jumps array points to an expression or a
+ * EEOP_JSONEXPR_COERCION step. ExecEvalJsonExprPath() will cause an
+ * error if it's the latter, because that mode of coercion is not
+ * supported for all JsonItemTypes.
+ */
+ int num_item_coercions;
+ int *eval_item_coercion_jumps;
+ bool *item_coercion_via_expr;
+
+ /*
+ * For passing when initializing a EEOP_IOCOERCE_SAFE step for any
+ * CoerceViaIO nodes in the expression that must be evaluated in an
+ * error-safe manner.
+ */
+ ErrorSaveContext escontext;
+} JsonExprState;
+
/* ----------------------------------------------------------------
* Executor State Trees
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 2dc79648d2..a96fd62d7f 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -112,6 +112,8 @@ extern JsonFormat *makeJsonFormat(JsonFormatType type, JsonEncoding encoding,
int location);
extern JsonValueExpr *makeJsonValueExpr(Expr *raw_expr, Expr *formatted_expr,
JsonFormat *format);
+extern JsonBehavior *makeJsonBehavior(JsonBehaviorType type, Node *expr,
+ JsonCoercion *coercion, int location);
extern Node *makeJsonKeyValue(Node *key, Node *value);
extern Node *makeJsonIsPredicate(Node *expr, JsonFormat *format,
JsonValueType item_type, bool unique_keys,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b3181f34ae..0184c76ce6 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1692,6 +1692,23 @@ typedef struct TriggerTransition
/* Nodes for SQL/JSON support */
+/*
+ * JsonQuotes -
+ * representation of [KEEP|OMIT] QUOTES clause for JSON_QUERY()
+ */
+typedef enum JsonQuotes
+{
+ JS_QUOTES_UNSPEC, /* unspecified */
+ JS_QUOTES_KEEP, /* KEEP QUOTES */
+ JS_QUOTES_OMIT, /* OMIT QUOTES */
+} JsonQuotes;
+
+/*
+ * JsonPathSpec -
+ * representation of JSON path constant
+ */
+typedef char *JsonPathSpec;
+
/*
* JsonOutput -
* representation of JSON output clause (RETURNING type [FORMAT format])
@@ -1703,6 +1720,36 @@ typedef struct JsonOutput
JsonReturning *returning; /* RETURNING FORMAT clause and type Oids */
} JsonOutput;
+/*
+ * JsonArgument -
+ * representation of argument from JSON PASSING clause
+ */
+typedef struct JsonArgument
+{
+ NodeTag type;
+ JsonValueExpr *val; /* argument value expression */
+ char *name; /* argument name */
+} JsonArgument;
+
+/*
+ * JsonFuncExpr -
+ * untransformed representation of JSON function expressions
+ */
+typedef struct JsonFuncExpr
+{
+ NodeTag type;
+ JsonExprOp op; /* expression type */
+ JsonValueExpr *context_item; /* context item expression */
+ Node *pathspec; /* JSON path specification expression */
+ List *passing; /* list of PASSING clause arguments, if any */
+ JsonOutput *output; /* output clause, if specified */
+ JsonBehavior *on_empty; /* ON EMPTY behavior */
+ JsonBehavior *on_error; /* ON ERROR behavior */
+ JsonWrapper wrapper; /* array wrapper behavior (JSON_QUERY only) */
+ JsonQuotes quotes; /* omit or keep quotes? (JSON_QUERY only) */
+ int location; /* token location, or -1 if unknown */
+} JsonFuncExpr;
+
/*
* JsonKeyValue -
* untransformed representation of JSON object key-value pair for
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 61289d8124..fe9dfbb02a 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1552,6 +1552,17 @@ typedef struct XmlExpr
int location;
} XmlExpr;
+/*
+ * JsonExprOp -
+ * enumeration of JSON functions using JSON path
+ */
+typedef enum JsonExprOp
+{
+ JSON_VALUE_OP, /* JSON_VALUE() */
+ JSON_QUERY_OP, /* JSON_QUERY() */
+ JSON_EXISTS_OP, /* JSON_EXISTS() */
+} JsonExprOp;
+
/*
* JsonEncoding -
* representation of JSON ENCODING clause
@@ -1582,11 +1593,32 @@ typedef enum JsonFormatType
*/
typedef enum JsonWrapper
{
+ JSW_UNSPEC,
JSW_NONE,
JSW_CONDITIONAL,
JSW_UNCONDITIONAL,
} JsonWrapper;
+/*
+ * JsonBehaviorType -
+ * enumeration of behavior types used in JSON ON ... BEHAVIOR clause
+ *
+ * If enum members are reordered, get_json_behavior() from ruleutils.c
+ * must be updated accordingly.
+ */
+typedef enum JsonBehaviorType
+{
+ JSON_BEHAVIOR_NULL = 0,
+ JSON_BEHAVIOR_ERROR,
+ JSON_BEHAVIOR_EMPTY,
+ JSON_BEHAVIOR_TRUE,
+ JSON_BEHAVIOR_FALSE,
+ JSON_BEHAVIOR_UNKNOWN,
+ JSON_BEHAVIOR_EMPTY_ARRAY,
+ JSON_BEHAVIOR_EMPTY_OBJECT,
+ JSON_BEHAVIOR_DEFAULT,
+} JsonBehaviorType;
+
/*
* JsonFormat -
* representation of JSON FORMAT clause
@@ -1681,6 +1713,138 @@ typedef struct JsonIsPredicate
int location; /* token location, or -1 if unknown */
} JsonIsPredicate;
+/*
+ * JsonCoercion
+ * Information about coercing a SQL/JSON value to the specified
+ * type at runtime using json_populate_type() or by calling the type's
+ * input funtion.
+ *
+ * A node of this type is created if the parser cannot find a cast expression
+ * using coerce_type().
+ */
+typedef struct JsonCoercion
+{
+ NodeTag type;
+
+ Oid targettype;
+ int32 targettypmod;
+ bool omit_quotes; /* omit quotes from scalar output strings? */
+ Oid collation; /* collation for coercion via I/O or populate */
+} JsonCoercion;
+
+/*
+ * JsonItemType
+ * Possible types for scalar values returned by JSON_VALUE()
+ *
+ * The comment next to each item type mentions the corresponding
+ * JsonbValue.jbvType.
+ */
+typedef enum JsonItemType
+{
+ JsonItemTypeNull, /* jbvNull */
+ JsonItemTypeString, /* jbvString */
+ JsonItemTypeNumeric, /* jbvNumeric */
+ JsonItemTypeBoolean, /* jbvBool */
+ JsonItemTypeDate, /* jbvDatetime: DATEOID */
+ JsonItemTypeTime, /* jbvDatetime: TIMEOID */
+ JsonItemTypeTimetz, /* jbvDatetime: TIMETZOID */
+ JsonItemTypeTimestamp, /* jbvDatetime: TIMESTAMPOID */
+ JsonItemTypeTimestamptz,/* jbvDatetime: TIMESTAMPTZOID */
+ JsonItemTypeComposite, /* jbvArray, jbvObject, jbvBinary */
+ JsonItemTypeInvalid,
+} JsonItemType;
+
+/*
+ * JsonItemCoercion
+ * Coercion expression for the given JsonItemType
+ *
+ * If not NULL, 'coercion' given the expression node to convert a scalar value
+ * extracted from a JsonbValue of the given type to the target type given by
+ * JsonExpr.returning. NULL means the coercion is unnecessary.
+ */
+typedef struct JsonItemCoercion
+{
+ NodeTag type;
+
+ JsonItemType item_type;
+ Node *coercion;
+} JsonItemCoercion;
+
+/*
+ * JsonBehavior
+ * Information about ON ERROR / ON EMPTY behaviors of JSON_VALUE(),
+ * JSON_QUERY(), and JSON_EXISTS()
+ *
+ * 'expr' is the expression to emit when a given behavior (EMPTY or ERROR)
+ * occurs on evaluating the SQL/JSON query function. 'coercion' is set
+ * if 'expr' isn't already of the expected target type given by
+ * JsonExpr.returning.
+ */
+typedef struct JsonBehavior
+{
+ NodeTag type;
+
+ JsonBehaviorType btype;
+ Node *expr;
+ JsonCoercion *coercion; /* to coerce behavior expression when there is
+ * no cast to the target type */
+ int location; /* token location, or -1 if unknown */
+} JsonBehavior;
+
+/*
+ * JsonExpr -
+ * Transformed representation of JSON_VALUE(), JSON_QUERY(), and
+ * JSON_EXISTS()
+ */
+typedef struct JsonExpr
+{
+ Expr xpr;
+
+ /* JSON_* function identifier */
+ JsonExprOp op;
+
+ /* json(b)-valued expression to query */
+ Node *formatted_expr;
+
+ /* Format of the above expression needed by ruleutils.c */
+ JsonFormat *format;
+
+ /* jsopath-valued expression containing the query pattern */
+ Node *path_spec;
+
+ /* Expected type/format of the output. */
+ JsonReturning *returning;
+
+ /* Information about the PASSING argument expressions */
+ List *passing_names;
+ List *passing_values;
+
+ /* Use-specified or default ON EMPTY and ON ERROR behaviors */
+ JsonBehavior *on_empty;
+ JsonBehavior *on_error;
+
+ /*
+ * Expression to convert the result of JSON_* function to the
+ * RETURNING type
+ */
+ Node *result_coercion;
+
+ /*
+ * List of expressions for coercing JSON_VALUE() result values, containing
+ * one element for every JsonItemType.
+ */
+ List *item_coercions;
+
+ /* WRAPPER specification for JSON_QUERY */
+ JsonWrapper wrapper;
+
+ /* KEEP or OMIT QUOTES for singleton scalars returned by JSON_QUERY() */
+ bool omit_quotes;
+
+ /* Original JsonFuncExpr's location */
+ int location;
+} JsonExpr;
+
/* ----------------
* NullTest
*
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 2331acac09..94e1cb4dce 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -93,6 +93,7 @@ PG_KEYWORD("commit", COMMIT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("committed", COMMITTED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("compression", COMPRESSION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("concurrently", CONCURRENTLY, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("conditional", CONDITIONAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("configuration", CONFIGURATION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("conflict", CONFLICT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("connection", CONNECTION, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -147,11 +148,13 @@ PG_KEYWORD("double", DOUBLE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("drop", DROP, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("each", EACH, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("else", ELSE, RESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("empty", EMPTY_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("enable", ENABLE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("encoding", ENCODING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("encrypted", ENCRYPTED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("end", END_P, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("enum", ENUM_P, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("error", ERROR_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("escape", ESCAPE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("event", EVENT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("except", EXCEPT, RESERVED_KEYWORD, AS_LABEL)
@@ -233,10 +236,14 @@ PG_KEYWORD("join", JOIN, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("json", JSON, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("json_array", JSON_ARRAY, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("json_arrayagg", JSON_ARRAYAGG, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("json_exists", JSON_EXISTS, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("json_object", JSON_OBJECT, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("json_objectagg", JSON_OBJECTAGG, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("json_query", JSON_QUERY, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("json_scalar", JSON_SCALAR, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("json_serialize", JSON_SERIALIZE, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("json_value", JSON_VALUE, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("keep", KEEP, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("key", KEY, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("keys", KEYS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("label", LABEL, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -302,6 +309,7 @@ PG_KEYWORD("off", OFF, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("offset", OFFSET, RESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("oids", OIDS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("old", OLD, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("omit", OMIT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("on", ON, RESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("only", ONLY, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("operator", OPERATOR, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -344,6 +352,7 @@ PG_KEYWORD("procedures", PROCEDURES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("program", PROGRAM, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("publication", PUBLICATION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("quote", QUOTE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("quotes", QUOTES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("range", RANGE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("read", READ, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("real", REAL, COL_NAME_KEYWORD, BARE_LABEL)
@@ -414,6 +423,7 @@ PG_KEYWORD("stdout", STDOUT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("storage", STORAGE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("stored", STORED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("strict", STRICT_P, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("string", STRING_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("strip", STRIP_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("subscription", SUBSCRIPTION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("substring", SUBSTRING, COL_NAME_KEYWORD, BARE_LABEL)
@@ -449,6 +459,7 @@ PG_KEYWORD("types", TYPES_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("uescape", UESCAPE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("unbounded", UNBOUNDED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("uncommitted", UNCOMMITTED, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("unconditional", UNCONDITIONAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("unencrypted", UNENCRYPTED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("union", UNION, RESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("unique", UNIQUE, RESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/utils/formatting.h b/src/include/utils/formatting.h
index 7ea1a70f71..cde030414e 100644
--- a/src/include/utils/formatting.h
+++ b/src/include/utils/formatting.h
@@ -29,5 +29,6 @@ extern char *asc_initcap(const char *buff, size_t nbytes);
extern Datum parse_datetime(text *date_txt, text *fmt, Oid collid, bool strict,
Oid *typid, int32 *typmod, int *tz,
struct Node *escontext);
+extern bool datetime_format_has_tz(const char *fmt_str);
#endif
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index e38dfd4901..d589ace5a2 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -422,6 +422,7 @@ extern char *JsonbToCString(StringInfo out, JsonbContainer *in,
int estimated_len);
extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in,
int estimated_len);
+extern char *JsonbUnquote(Jsonb *jb);
extern bool JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res);
extern const char *JsonbTypeName(JsonbValue *val);
diff --git a/src/include/utils/jsonfuncs.h b/src/include/utils/jsonfuncs.h
index 94cb6edc8f..190e13284b 100644
--- a/src/include/utils/jsonfuncs.h
+++ b/src/include/utils/jsonfuncs.h
@@ -89,4 +89,10 @@ extern Datum datum_to_jsonb(Datum val, JsonTypeCategory tcategory,
Oid outfuncoid);
extern Datum jsonb_from_text(text *js, bool unique_keys);
+extern Datum json_populate_type(Datum json_val, Oid json_type,
+ Oid typid, int32 typmod,
+ void **cache, MemoryContext mcxt,
+ bool *isnull,
+ Node *escontext);
+
#endif
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 6eabdcfb75..897de21a51 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -192,6 +192,7 @@ extern bool jspGetBool(JsonPathItem *v);
extern char *jspGetString(JsonPathItem *v, int32 *len);
extern bool jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from,
JsonPathItem *to, int i);
+extern bool jspIsMutable(JsonPath *path, List *varnames, List *varexprs);
extern const char *jspOperationName(JsonPathItemType type);
diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer b/src/interfaces/ecpg/preproc/ecpg.trailer
index 435c139ec2..b2aa44f36d 100644
--- a/src/interfaces/ecpg/preproc/ecpg.trailer
+++ b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -651,6 +651,34 @@ var_type: simple_type
$$.type_index = mm_strdup("-1");
$$.type_sizeof = NULL;
}
+ | STRING_P
+ {
+ if (INFORMIX_MODE)
+ {
+ /* In Informix mode, "string" is automatically a typedef */
+ $$.type_enum = ECPGt_string;
+ $$.type_str = mm_strdup("char");
+ $$.type_dimension = mm_strdup("-1");
+ $$.type_index = mm_strdup("-1");
+ $$.type_sizeof = NULL;
+ }
+ else
+ {
+ /* Otherwise, legal only if user typedef'ed it */
+ struct typedefs *this = get_typedef("string", false);
+
+ $$.type_str = (this->type->type_enum == ECPGt_varchar || this->type->type_enum == ECPGt_bytea) ? EMPTY : mm_strdup(this->name);
+ $$.type_enum = this->type->type_enum;
+ $$.type_dimension = this->type->type_dimension;
+ $$.type_index = this->type->type_index;
+ if (this->type->type_sizeof && strlen(this->type->type_sizeof) != 0)
+ $$.type_sizeof = this->type->type_sizeof;
+ else
+ $$.type_sizeof = cat_str(3, mm_strdup("sizeof("), mm_strdup(this->name), mm_strdup(")"));
+
+ struct_member_list[struct_level] = ECPGstruct_member_dup(this->struct_member_list);
+ }
+ }
| INTERVAL ecpg_interval
{
$$.type_enum = ECPGt_interval;
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
new file mode 100644
index 0000000000..f02381de25
--- /dev/null
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -0,0 +1,1145 @@
+-- JSON_EXISTS
+-- json arguments currently not supported
+SELECT JSON_EXISTS(NULL FORMAT JSON, '$');
+ERROR: JSON_EXISTS() is not yet implemented for the json type
+LINE 1: SELECT JSON_EXISTS(NULL FORMAT JSON, '$');
+ ^
+HINT: Try casting the argument to jsonb
+SELECT JSON_EXISTS(NULL::jsonb, '$');
+ json_exists
+-------------
+
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '[]', '$');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(JSON_OBJECT(RETURNING jsonb), '$');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', '$');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(jsonb 'null', '$');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '[]', '$');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', '$.a');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', 'strict $.a'); -- FALSE on error
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', 'strict $.a' ERROR ON ERROR);
+ERROR: jsonpath member accessor can only be applied to an object
+SELECT JSON_EXISTS(jsonb 'null', '$.a');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '[]', '$.a');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '[1, "aaa", {"a": 1}]', 'strict $.a'); -- FALSE on error
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '[1, "aaa", {"a": 1}]', 'lax $.a');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '{}', '$.a');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '{"b": 1, "a": 2}', '$.a');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', '$.a.b');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '{"a": {"b": 1}}', '$.a.b');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.a.b');
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING 1 AS x);
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING '1' AS x);
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 2 AS y);
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 1 AS y);
+ json_exists
+-------------
+ f
+(1 row)
+
+-- extension: boolean expressions
+SELECT JSON_EXISTS(jsonb '1', '$ > 2');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', '$.a > 2' ERROR ON ERROR);
+ json_exists
+-------------
+ t
+(1 row)
+
+-- JSON_VALUE
+-- json arguments currently not supported
+SELECT JSON_VALUE(NULL FORMAT JSON, '$');
+ERROR: JSON_VALUE() is not yet implemented for the json type
+LINE 1: SELECT JSON_VALUE(NULL FORMAT JSON, '$');
+ ^
+HINT: Try casting the argument to jsonb
+SELECT JSON_VALUE(NULL::jsonb, '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(jsonb 'null', '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING int);
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(jsonb 'true', '$');
+ json_value
+------------
+ true
+(1 row)
+
+SELECT JSON_VALUE(jsonb 'true', '$' RETURNING bool);
+ json_value
+------------
+ t
+(1 row)
+
+SELECT JSON_VALUE(jsonb '123', '$');
+ json_value
+------------
+ 123
+(1 row)
+
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING int) + 234;
+ ?column?
+----------
+ 357
+(1 row)
+
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING text);
+ json_value
+------------
+ 123
+(1 row)
+
+/* jsonb bytea ??? */
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ERROR);
+ERROR: SQL/JSON item cannot be cast to target type
+SELECT JSON_VALUE(jsonb '1.23', '$');
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING int);
+ json_value
+------------
+ 1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING int ERROR ON ERROR);
+ERROR: invalid input syntax for type integer: "1.23"
+SELECT JSON_VALUE(jsonb '"aaa"', '$');
+ json_value
+------------
+ aaa
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING text);
+ json_value
+------------
+ aaa
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(5));
+ json_value
+------------
+ aaa
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2));
+ json_value
+------------
+ aa
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json);
+ json_value
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb);
+ json_value
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json ERROR ON ERROR);
+ json_value
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb ERROR ON ERROR);
+ json_value
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"\"aaa\""', '$' RETURNING json);
+ json_value
+------------
+ "\"aaa\""
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"\"aaa\""', '$' RETURNING jsonb);
+ json_value
+------------
+ "\"aaa\""
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int);
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int ERROR ON ERROR);
+ERROR: invalid input syntax for type integer: "aaa"
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int DEFAULT 111 ON ERROR);
+ json_value
+------------
+ 111
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234;
+ ?column?
+----------
+ 357
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
+ ?column?
+------------
+ 03-01-2017
+(1 row)
+
+-- 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);
+ 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);
+ERROR: domain sqljsonb_int_not_null does not allow null values
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY ERROR ON ERROR);
+ json_value
+------------
+ 2
+(1 row)
+
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY ERROR ON ERROR);
+ 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);
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE('"purple"'::jsonb, 'lax $[*]' RETURNING rgb ERROR ON ERROR);
+ERROR: value for domain rgb violates check constraint "rgb_check"
+SELECT JSON_VALUE(jsonb '[]', '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
+ERROR: JSON path expression in JSON_VALUE should return singleton scalar item
+SELECT JSON_VALUE(jsonb '{}', '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(jsonb '{}', '$' ERROR ON ERROR);
+ERROR: JSON path expression in JSON_VALUE should return singleton scalar item
+SELECT JSON_VALUE(jsonb '1', '$.a');
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(jsonb '1', 'strict $.a' ERROR ON ERROR);
+ERROR: jsonpath member accessor can only be applied to an object
+SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 'error' ON ERROR);
+ json_value
+------------
+ error
+(1 row)
+
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR);
+ERROR: no SQL/JSON item
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR);
+ERROR: no SQL/JSON item
+SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
+ json_value
+------------
+ 2
+(1 row)
+
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT 2 ON ERROR);
+ json_value
+------------
+ 2
+(1 row)
+
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON ERROR);
+ json_value
+------------
+ 2
+(1 row)
+
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR);
+ json_value
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR);
+ json_value
+------------
+ 2
+(1 row)
+
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
+ERROR: no SQL/JSON item
+SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
+ERROR: JSON path expression in JSON_VALUE should return singleton scalar item
+SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' DEFAULT '0' ON ERROR);
+ json_value
+------------
+ 0
+(1 row)
+
+SELECT JSON_VALUE(jsonb '[" "]', '$[*]' RETURNING int ERROR ON ERROR);
+ERROR: invalid input syntax for type integer: " "
+SELECT JSON_VALUE(jsonb '[" "]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
+ json_value
+------------
+ 5
+(1 row)
+
+SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
+ json_value
+------------
+ 1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int FORMAT JSON); -- RETURNING FORMAT not allowed
+ERROR: cannot specify FORMAT in RETURNING clause of JSON_VALUE()
+LINE 1: ...CT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int FORMAT JSO...
+ ^
+-- RETUGNING pseudo-types not allowed
+SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING record);
+ERROR: returning pseudo-types is not supported in SQL/JSON functions
+SELECT
+ x,
+ JSON_VALUE(
+ jsonb '{"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(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
+ json_value
+------------
+ (1,2)
+(1 row)
+
+SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
+ json_value
+------------
+ (1,2)
+(1 row)
+
+SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point ERROR ON ERROR);
+ json_value
+------------
+ (1,2)
+(1 row)
+
+-- Test PASSING and RETURNING date/time types
+SELECT JSON_VALUE(jsonb '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(jsonb '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(jsonb '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(jsonb 'null', '$ts' PASSING date '2018-02-21 12:34:56 +10' AS ts RETURNING date);
+ json_value
+------------
+ 02-21-2018
+(1 row)
+
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING time '2018-02-21 12:34:56 +10' AS ts RETURNING time);
+ json_value
+------------
+ 12:34:56
+(1 row)
+
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timetz '2018-02-21 12:34:56 +10' AS ts RETURNING timetz);
+ json_value
+-------------
+ 12:34:56+10
+(1 row)
+
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamp '2018-02-21 12:34:56 +10' AS ts RETURNING timestamp);
+ json_value
+--------------------------
+ Wed Feb 21 12:34:56 2018
+(1 row)
+
+-- Also test RETURNING json[b]
+SELECT JSON_VALUE(jsonb '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(jsonb '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
+-- json arguments currently not supported
+SELECT JSON_QUERY(NULL FORMAT JSON, '$');
+ERROR: JSON_QUERY() is not yet implemented for the json type
+LINE 1: SELECT JSON_QUERY(NULL FORMAT JSON, '$');
+ ^
+HINT: Try casting the argument to jsonb
+SELECT JSON_VALUE(NULL::jsonb, '$');
+ json_value
+------------
+
+(1 row)
+
+SELECT
+ JSON_QUERY(js, '$'),
+ JSON_QUERY(js, '$' WITHOUT WRAPPER),
+ JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER),
+ JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER),
+ JSON_QUERY(js, '$' WITH ARRAY WRAPPER)
+FROM
+ (VALUES
+ (jsonb '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, 'strict $[*]') AS "unspec",
+ JSON_QUERY(js, 'strict $[*]' WITHOUT WRAPPER) AS "without",
+ JSON_QUERY(js, 'strict $[*]' WITH CONDITIONAL WRAPPER) AS "with cond",
+ JSON_QUERY(js, 'strict $[*]' WITH UNCONDITIONAL ARRAY WRAPPER) AS "with uncond",
+ JSON_QUERY(js, 'strict $[*]' WITH ARRAY WRAPPER) AS "with"
+FROM
+ (VALUES
+ (jsonb '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(jsonb '"aaa"', '$' RETURNING text);
+ json_query
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES);
+ json_query
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES ON SCALAR STRING);
+ json_query
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES);
+ json_query
+------------
+ aaa
+(1 row)
+
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES ON SCALAR STRING);
+ json_query
+------------
+ aaa
+(1 row)
+
+SELECT JSON_QUERY(jsonb '"aaa"', '$' 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(jsonb '"aaa"', '$' 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(jsonb '"aaa"', '$' 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(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES);
+ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
+LINE 1: SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES)...
+ ^
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER KEEP QUOTES);
+ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
+LINE 1: SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER KEEP QUOTES)...
+ ^
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTES);
+ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
+LINE 1: SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER ...
+ ^
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTES);
+ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
+LINE 1: SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER ...
+ ^
+-- Should succeed
+SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER OMIT QUOTES);
+ json_query
+------------
+ [1]
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER KEEP QUOTES);
+ json_query
+------------
+ [1]
+(1 row)
+
+-- test QUOTES behavior.
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] omit quotes);
+ json_query
+------------
+ {1,2,3}
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes error on error);
+ERROR: expected JSON array
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range omit quotes);
+ json_query
+------------
+ [1,3)
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range keep quotes);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range keep quotes error on error);
+ERROR: malformed range literal: ""[1,2]""
+DETAIL: Missing left parenthesis or bracket.
+SELECT JSON_QUERY(jsonb '[]', '$[*]');
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' NULL ON EMPTY);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ON EMPTY);
+ json_query
+------------
+ []
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ARRAY ON EMPTY);
+ json_query
+------------
+ []
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY OBJECT ON EMPTY);
+ json_query
+------------
+ {}
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY);
+ERROR: no SQL/JSON item
+SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
+ json_query
+------------
+ "empty"
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
+ERROR: no SQL/JSON item
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
+ERROR: no SQL/JSON item
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
+ERROR: no SQL/JSON item
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
+ERROR: no SQL/JSON item
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
+ERROR: no SQL/JSON item
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
+ERROR: JSON path expression in JSON_QUERY should return singleton item without wrapper
+HINT: Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
+ json_query
+------------
+ "empty"
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING json);
+ json_query
+------------
+ [1, 2]
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING json FORMAT JSON);
+ json_query
+------------
+ [1, 2]
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb);
+ json_query
+------------
+ [1, 2]
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb FORMAT JSON);
+ json_query
+------------
+ [1, 2]
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text);
+ json_query
+------------
+ [1, 2]
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(10));
+ json_query
+------------
+ [1, 2]
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3));
+ json_query
+------------
+ [1,
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text FORMAT JSON);
+ json_query
+------------
+ [1, 2]
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea);
+ json_query
+----------------
+ \x5b312c20325d
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea FORMAT JSON);
+ json_query
+----------------
+ \x5b312c20325d
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea EMPTY OBJECT ON ERROR);
+ERROR: cannot cast behavior expression of type jsonb to bytea
+LINE 1: ... JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea EMPTY OBJE...
+ ^
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJECT ON ERROR);
+ERROR: cannot cast behavior expression of type jsonb to bytea
+LINE 1: ...jsonb '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJE...
+ ^
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING json EMPTY OBJECT ON ERROR);
+ json_query
+------------
+ {}
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING jsonb EMPTY OBJECT ON ERROR);
+ json_query
+------------
+ {}
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[3,4]', '$[*]' RETURNING bigint[] EMPTY OBJECT ON ERROR);
+ERROR: cannot cast behavior expression of type jsonb to bigint[]
+LINE 1: ...ON_QUERY(jsonb '[3,4]', '$[*]' RETURNING bigint[] EMPTY OBJE...
+ ^
+SELECT JSON_QUERY(jsonb '"[3,4]"', '$[*]' RETURNING bigint[] EMPTY OBJECT ON ERROR);
+ERROR: cannot cast behavior expression of type jsonb to bigint[]
+LINE 1: ..._QUERY(jsonb '"[3,4]"', '$[*]' RETURNING bigint[] EMPTY OBJE...
+ ^
+-- RETUGNING pseudo-types not allowed
+SELECT JSON_QUERY(jsonb '[3,4]', '$[*]' RETURNING anyarray EMPTY OBJECT ON ERROR);
+ERROR: returning pseudo-types is not supported in SQL/JSON functions
+SELECT
+ x, y,
+ JSON_QUERY(
+ jsonb '[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)
+
+-- record type returning with quotes behavior.
+CREATE TYPE comp_abc AS (a text, b int, c timestamp);
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc omit quotes);
+ json_query
+-------------------------------------
+ (abc,42,"Thu Jan 02 00:00:00 2003")
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc keep quotes);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc keep quotes error on error);
+ERROR: cannot call populate_composite on a scalar
+DROP TYPE comp_abc;
+-- Extension: record types returning
+CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]);
+CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]);
+SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljsonb_rec);
+ json_query
+-----------------------------------------------------
+ (1,aaa,"[1, ""2"", {}]","{""x"": [1, ""2"", {}]}",)
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[{"a": "a", "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljsonb_rec ERROR ON ERROR);
+ERROR: invalid input syntax for type integer: "a"
+SELECT JSON_QUERY(jsonb '[{"a": "a", "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljsonb_rec);
+ json_query
+------------
+
+(1 row)
+
+SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa": [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljsonb_rec)).jsa);
+ unnest
+------------------------
+ {"a": 1, "b": ["foo"]}
+ {"a": 2, "c": {}}
+ 123
+(3 rows)
+
+SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljsonb_reca)).reca);
+ a | t | js | jb | jsa
+---+-------------+----+------------+-----
+ 1 | ["foo", []] | | |
+ 2 | | | [{}, true] |
+(2 rows)
+
+SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING jsonpath);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING jsonpath ERROR ON ERROR);
+ERROR: syntax error at or near "{" of jsonpath input
+-- Extension: array types returning
+SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER);
+ json_query
+--------------
+ {1,2,NULL,3}
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2,null,"a"]', '$[*]' RETURNING int[] WITH WRAPPER ERROR ON ERROR);
+ERROR: invalid input syntax for type integer: "a"
+SELECT JSON_QUERY(jsonb '[1,2,null,"a"]', '$[*]' RETURNING int[] WITH WRAPPER);
+ json_query
+------------
+
+(1 row)
+
+SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljsonb_rec[]));
+ a | t | js | jb | jsa
+---+-------------+----+------------+-----
+ 1 | ["foo", []] | | |
+ 2 | | | [{}, true] |
+(2 rows)
+
+-- Extension: domain types returning
+SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
+ json_query
+------------
+ 1
+(1 row)
+
+SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON ERROR);
+ERROR: no SQL/JSON item
+-- Test timestamptz passing and output
+SELECT JSON_QUERY(jsonb '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(jsonb '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(jsonb '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_jsonb_constraints (
+ js text,
+ i int,
+ x jsonb DEFAULT JSON_QUERY(jsonb '[1,2]', '$[*]' WITH WRAPPER)
+ CONSTRAINT test_jsonb_constraint1
+ CHECK (js IS JSON)
+ CONSTRAINT test_jsonb_constraint2
+ CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr))
+ CONSTRAINT test_jsonb_constraint3
+ CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT '12' ON EMPTY ERROR ON ERROR) > i)
+ CONSTRAINT test_jsonb_constraint4
+ CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]')
+ CONSTRAINT test_jsonb_constraint5
+ CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > 'a' COLLATE "C")
+);
+\d test_jsonb_constraints
+ Table "public.test_jsonb_constraints"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+--------------------------------------------------------------------------------
+ js | text | | |
+ i | integer | | |
+ x | jsonb | | | JSON_QUERY('[1, 2]'::jsonb, '$[*]' RETURNING jsonb WITH UNCONDITIONAL WRAPPER)
+Check constraints:
+ "test_jsonb_constraint1" CHECK (js IS JSON)
+ "test_jsonb_constraint2" CHECK (JSON_EXISTS(js::jsonb, '$."a"' PASSING i + 5 AS int, i::text AS txt, ARRAY[1, 2, 3] AS arr))
+ "test_jsonb_constraint3" CHECK (JSON_VALUE(js::jsonb, '$."a"' RETURNING integer DEFAULT 12 ON EMPTY ERROR ON ERROR) > i)
+ "test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb)
+ "test_jsonb_constraint5" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
+
+SELECT check_clause
+FROM information_schema.check_constraints
+WHERE constraint_name LIKE 'test_jsonb_constraint%'
+ORDER BY 1;
+ check_clause
+------------------------------------------------------------------------------------------------------------------------
+ (JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
+ (JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb)
+ (JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT 12 ON EMPTY ERROR ON ERROR) > i)
+ (js IS JSON)
+ JSON_EXISTS((js)::jsonb, '$."a"' PASSING (i + 5) AS int, (i)::text AS txt, ARRAY[1, 2, 3] AS arr)
+(5 rows)
+
+SELECT pg_get_expr(adbin, adrelid)
+FROM pg_attrdef
+WHERE adrelid = 'test_jsonb_constraints'::regclass
+ORDER BY 1;
+ pg_get_expr
+--------------------------------------------------------------------------------
+ JSON_QUERY('[1, 2]'::jsonb, '$[*]' RETURNING jsonb WITH UNCONDITIONAL WRAPPER)
+(1 row)
+
+INSERT INTO test_jsonb_constraints VALUES ('', 1);
+ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint1"
+DETAIL: Failing row contains (, 1, [1, 2]).
+INSERT INTO test_jsonb_constraints VALUES ('1', 1);
+ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint2"
+DETAIL: Failing row contains (1, 1, [1, 2]).
+INSERT INTO test_jsonb_constraints VALUES ('[]');
+ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint2"
+DETAIL: Failing row contains ([], null, [1, 2]).
+INSERT INTO test_jsonb_constraints VALUES ('{"b": 1}', 1);
+ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint2"
+DETAIL: Failing row contains ({"b": 1}, 1, [1, 2]).
+INSERT INTO test_jsonb_constraints VALUES ('{"a": 1}', 1);
+ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint3"
+DETAIL: Failing row contains ({"a": 1}, 1, [1, 2]).
+INSERT INTO test_jsonb_constraints VALUES ('{"a": 7}', 1);
+ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint5"
+DETAIL: Failing row contains ({"a": 7}, 1, [1, 2]).
+INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
+ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint4"
+DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]).
+DROP TABLE test_jsonb_constraints;
+-- Test mutabilily of query functions
+CREATE TABLE test_jsonb_mutability(js jsonb);
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a[0]'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@ < $.datetime())'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime())'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime("HH:MI TZH"))'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI TZH") < $.datetime("HH:MI TZH"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI") < $.datetime("YY-MM-DD HH:MI"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI TZH") < $.datetime("YY-MM-DD HH:MI"))'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $x' PASSING '12:34'::timetz AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $y' PASSING '12:34'::timetz AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '12:34'::timetz AS x));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '1234'::int AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() ? (@ == $x)' PASSING '12:34'::time AS x));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("YY-MM-DD") ? (@ == $x)' PASSING '2020-07-14'::date AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
+DROP TABLE test_jsonb_mutability;
+-- Extension: non-constant JSON path
+SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a');
+ json_value
+------------
+ 123
+(1 row)
+
+SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY);
+ json_value
+------------
+ foo
+(1 row)
+
+SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a');
+ json_query
+------------
+ 123
+(1 row)
+
+SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
+ json_query
+------------
+ [123]
+(1 row)
+
+-- Should fail (invalid path)
+SELECT JSON_QUERY(jsonb '{"a": 123}', 'error' || ' ' || 'error');
+ERROR: syntax error at or near " " of jsonpath input
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index f0987ff537..f488afefa5 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -103,7 +103,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 jsonpath_encoding jsonb_jsonpath sqljson
+test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson sqljson_queryfuncs
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
new file mode 100644
index 0000000000..82ed87275d
--- /dev/null
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -0,0 +1,371 @@
+-- JSON_EXISTS
+
+-- json arguments currently not supported
+SELECT JSON_EXISTS(NULL FORMAT JSON, '$');
+
+SELECT JSON_EXISTS(NULL::jsonb, '$');
+SELECT JSON_EXISTS(jsonb '[]', '$');
+SELECT JSON_EXISTS(JSON_OBJECT(RETURNING jsonb), '$');
+
+SELECT JSON_EXISTS(jsonb '1', '$');
+SELECT JSON_EXISTS(jsonb 'null', '$');
+SELECT JSON_EXISTS(jsonb '[]', '$');
+
+SELECT JSON_EXISTS(jsonb '1', '$.a');
+SELECT JSON_EXISTS(jsonb '1', 'strict $.a'); -- FALSE on error
+SELECT JSON_EXISTS(jsonb '1', 'strict $.a' ERROR ON ERROR);
+SELECT JSON_EXISTS(jsonb 'null', '$.a');
+SELECT JSON_EXISTS(jsonb '[]', '$.a');
+SELECT JSON_EXISTS(jsonb '[1, "aaa", {"a": 1}]', 'strict $.a'); -- FALSE on error
+SELECT JSON_EXISTS(jsonb '[1, "aaa", {"a": 1}]', 'lax $.a');
+SELECT JSON_EXISTS(jsonb '{}', '$.a');
+SELECT JSON_EXISTS(jsonb '{"b": 1, "a": 2}', '$.a');
+
+SELECT JSON_EXISTS(jsonb '1', '$.a.b');
+SELECT JSON_EXISTS(jsonb '{"a": {"b": 1}}', '$.a.b');
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.a.b');
+
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING 1 AS x);
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING '1' AS x);
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 2 AS y);
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 1 AS y);
+
+-- extension: boolean expressions
+SELECT JSON_EXISTS(jsonb '1', '$ > 2');
+SELECT JSON_EXISTS(jsonb '1', '$.a > 2' ERROR ON ERROR);
+
+-- JSON_VALUE
+
+-- json arguments currently not supported
+SELECT JSON_VALUE(NULL FORMAT JSON, '$');
+
+SELECT JSON_VALUE(NULL::jsonb, '$');
+
+SELECT JSON_VALUE(jsonb 'null', '$');
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING int);
+
+SELECT JSON_VALUE(jsonb 'true', '$');
+SELECT JSON_VALUE(jsonb 'true', '$' RETURNING bool);
+
+SELECT JSON_VALUE(jsonb '123', '$');
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING int) + 234;
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING text);
+/* jsonb bytea ??? */
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb '1.23', '$');
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING int);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING int ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$');
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING text);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(5));
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2));
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '"\"aaa\""', '$' RETURNING json);
+SELECT JSON_VALUE(jsonb '"\"aaa\""', '$' RETURNING jsonb);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int DEFAULT 111 ON ERROR);
+SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234;
+
+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);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY ERROR ON ERROR);
+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);
+SELECT JSON_VALUE('"purple"'::jsonb, 'lax $[*]' RETURNING rgb ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb '[]', '$');
+SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '{}', '$');
+SELECT JSON_VALUE(jsonb '{}', '$' ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb '1', '$.a');
+SELECT JSON_VALUE(jsonb '1', 'strict $.a' ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 'error' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT 2 ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
+
+SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' DEFAULT '0' ON ERROR);
+SELECT JSON_VALUE(jsonb '[" "]', '$[*]' RETURNING int ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '[" "]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
+SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
+SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int FORMAT JSON); -- RETURNING FORMAT not allowed
+
+-- RETUGNING pseudo-types not allowed
+SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING record);
+
+SELECT
+ x,
+ JSON_VALUE(
+ jsonb '{"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(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
+SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
+SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point ERROR ON ERROR);
+
+-- Test PASSING and RETURNING date/time types
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamptz);
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamp);
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING date '2018-02-21 12:34:56 +10' AS ts RETURNING date);
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING time '2018-02-21 12:34:56 +10' AS ts RETURNING time);
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timetz '2018-02-21 12:34:56 +10' AS ts RETURNING timetz);
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamp '2018-02-21 12:34:56 +10' AS ts RETURNING timestamp);
+
+-- Also test RETURNING json[b]
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
+
+-- JSON_QUERY
+
+-- json arguments currently not supported
+SELECT JSON_QUERY(NULL FORMAT JSON, '$');
+
+SELECT JSON_VALUE(NULL::jsonb, '$');
+
+SELECT
+ JSON_QUERY(js, '$'),
+ JSON_QUERY(js, '$' WITHOUT WRAPPER),
+ JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER),
+ JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER),
+ JSON_QUERY(js, '$' WITH ARRAY WRAPPER)
+FROM
+ (VALUES
+ (jsonb 'null'),
+ ('12.3'),
+ ('true'),
+ ('"aaa"'),
+ ('[1, null, "2"]'),
+ ('{"a": 1, "b": [2]}')
+ ) foo(js);
+
+SELECT
+ JSON_QUERY(js, 'strict $[*]') AS "unspec",
+ JSON_QUERY(js, 'strict $[*]' WITHOUT WRAPPER) AS "without",
+ JSON_QUERY(js, 'strict $[*]' WITH CONDITIONAL WRAPPER) AS "with cond",
+ JSON_QUERY(js, 'strict $[*]' WITH UNCONDITIONAL ARRAY WRAPPER) AS "with uncond",
+ JSON_QUERY(js, 'strict $[*]' WITH ARRAY WRAPPER) AS "with"
+FROM
+ (VALUES
+ (jsonb '1'),
+ ('[]'),
+ ('[null]'),
+ ('[12.3]'),
+ ('[true]'),
+ ('["aaa"]'),
+ ('[[1, 2, 3]]'),
+ ('[{"a": 1, "b": [2]}]'),
+ ('[1, "2", null, [3]]')
+ ) foo(js);
+
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES ON SCALAR STRING);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES ON SCALAR STRING);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' OMIT QUOTES ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING json OMIT QUOTES ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING bytea FORMAT JSON OMIT QUOTES ERROR ON ERROR);
+
+-- QUOTES behavior should not be specified when WITH WRAPPER used:
+-- Should fail
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES);
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER KEEP QUOTES);
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTES);
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTES);
+-- Should succeed
+SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER OMIT QUOTES);
+SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER KEEP QUOTES);
+
+-- test QUOTES behavior.
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] omit quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes error on error);
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range omit quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range keep quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range keep quotes error on error);
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]');
+SELECT JSON_QUERY(jsonb '[]', '$[*]' NULL ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ARRAY ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY OBJECT ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING json);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING json FORMAT JSON);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb FORMAT JSON);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(10));
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3));
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text FORMAT JSON);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea FORMAT JSON);
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea EMPTY OBJECT ON ERROR);
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJECT ON ERROR);
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING json EMPTY OBJECT ON ERROR);
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING jsonb EMPTY OBJECT ON ERROR);
+SELECT JSON_QUERY(jsonb '[3,4]', '$[*]' RETURNING bigint[] EMPTY OBJECT ON ERROR);
+SELECT JSON_QUERY(jsonb '"[3,4]"', '$[*]' RETURNING bigint[] EMPTY OBJECT ON ERROR);
+
+-- RETUGNING pseudo-types not allowed
+SELECT JSON_QUERY(jsonb '[3,4]', '$[*]' RETURNING anyarray EMPTY OBJECT ON ERROR);
+
+SELECT
+ x, y,
+ JSON_QUERY(
+ jsonb '[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;
+
+-- record type returning with quotes behavior.
+CREATE TYPE comp_abc AS (a text, b int, c timestamp);
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc omit quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc keep quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc keep quotes error on error);
+DROP TYPE comp_abc;
+
+-- Extension: record types returning
+CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]);
+CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]);
+
+SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljsonb_rec);
+SELECT JSON_QUERY(jsonb '[{"a": "a", "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljsonb_rec ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '[{"a": "a", "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljsonb_rec);
+SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa": [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljsonb_rec)).jsa);
+SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljsonb_reca)).reca);
+
+SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING jsonpath);
+SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING jsonpath ERROR ON ERROR);
+
+-- Extension: array types returning
+SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER);
+SELECT JSON_QUERY(jsonb '[1,2,null,"a"]', '$[*]' RETURNING int[] WITH WRAPPER ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '[1,2,null,"a"]', '$[*]' RETURNING int[] WITH WRAPPER);
+SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljsonb_rec[]));
+
+-- Extension: domain types returning
+SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
+SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
+SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON ERROR);
+
+-- Test timestamptz passing and output
+SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
+SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
+SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
+
+-- Test constraints
+
+CREATE TABLE test_jsonb_constraints (
+ js text,
+ i int,
+ x jsonb DEFAULT JSON_QUERY(jsonb '[1,2]', '$[*]' WITH WRAPPER)
+ CONSTRAINT test_jsonb_constraint1
+ CHECK (js IS JSON)
+ CONSTRAINT test_jsonb_constraint2
+ CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr))
+ CONSTRAINT test_jsonb_constraint3
+ CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT '12' ON EMPTY ERROR ON ERROR) > i)
+ CONSTRAINT test_jsonb_constraint4
+ CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]')
+ CONSTRAINT test_jsonb_constraint5
+ CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > 'a' COLLATE "C")
+);
+
+\d test_jsonb_constraints
+
+SELECT check_clause
+FROM information_schema.check_constraints
+WHERE constraint_name LIKE 'test_jsonb_constraint%'
+ORDER BY 1;
+
+SELECT pg_get_expr(adbin, adrelid)
+FROM pg_attrdef
+WHERE adrelid = 'test_jsonb_constraints'::regclass
+ORDER BY 1;
+
+INSERT INTO test_jsonb_constraints VALUES ('', 1);
+INSERT INTO test_jsonb_constraints VALUES ('1', 1);
+INSERT INTO test_jsonb_constraints VALUES ('[]');
+INSERT INTO test_jsonb_constraints VALUES ('{"b": 1}', 1);
+INSERT INTO test_jsonb_constraints VALUES ('{"a": 1}', 1);
+INSERT INTO test_jsonb_constraints VALUES ('{"a": 7}', 1);
+INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
+
+DROP TABLE test_jsonb_constraints;
+
+-- Test mutabilily of query functions
+CREATE TABLE test_jsonb_mutability(js jsonb);
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a[0]'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@ < $.datetime())'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime())'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime("HH:MI TZH"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI TZH") < $.datetime("HH:MI TZH"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI") < $.datetime("YY-MM-DD HH:MI"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI TZH") < $.datetime("YY-MM-DD HH:MI"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $x' PASSING '12:34'::timetz AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $y' PASSING '12:34'::timetz AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '12:34'::timetz AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '1234'::int AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() ? (@ == $x)' PASSING '12:34'::time AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("YY-MM-DD") ? (@ == $x)' PASSING '2020-07-14'::date AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
+DROP TABLE test_jsonb_mutability;
+
+-- Extension: non-constant JSON path
+SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
+SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a');
+SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY);
+SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a');
+SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
+-- Should fail (invalid path)
+SELECT JSON_QUERY(jsonb '{"a": 123}', 'error' || ' ' || 'error');
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 7e866e3c3d..53e13fe729 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1256,6 +1256,7 @@ Join
JoinCostWorkspace
JoinDomain
JoinExpr
+JsonFuncExpr
JoinHashEntry
JoinPath
JoinPathExtraData
@@ -1266,18 +1267,27 @@ JsObject
JsValue
JsonAggConstructor
JsonAggState
+JsonArgument
JsonArrayAgg
JsonArrayConstructor
JsonArrayQueryConstructor
JsonBaseObjectInfo
+JsonBehavior
+JsonBehaviorType
+JsonCoercion
JsonConstructorExpr
JsonConstructorExprState
JsonConstructorType
JsonEncoding
+JsonExpr
+JsonExprOp
+JsonExprPostEvalState
+JsonExprState
JsonFormat
JsonFormatType
JsonHashEntry
JsonIsPredicate
+JsonItemType
JsonIterateStringValuesAction
JsonKeyValue
JsonLexContext
@@ -1295,6 +1305,7 @@ JsonParseContext
JsonParseErrorType
JsonPath
JsonPathBool
+JsonPathDatatypeStatus
JsonPathExecContext
JsonPathExecResult
JsonPathGinAddPathItemFunc
@@ -1307,10 +1318,15 @@ JsonPathGinPathItem
JsonPathItem
JsonPathItemType
JsonPathKeyword
+JsonPathMutableContext
JsonPathParseItem
JsonPathParseResult
JsonPathPredicateCallback
JsonPathString
+JsonPathVarCallback
+JsonPathVariable
+JsonPathVariableEvalContext
+JsonQuotes
JsonReturning
JsonScalarExpr
JsonSemAction
@@ -1327,6 +1343,7 @@ JsonValueExpr
JsonValueList
JsonValueListIterator
JsonValueType
+JsonWrapper
Jsonb
JsonbAggState
JsonbContainer
--
2.35.3