agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrew Dunstan <[email protected]>
Subject: [PATCH 4/4] SQLJSON query functions v48
Date: Tue, 14 Jul 2020 11:25:45 -0400
---
.../pg_stat_statements/pg_stat_statements.c | 21 +
doc/src/sgml/func.sgml | 519 ++++++++
src/backend/executor/execExpr.c | 206 +++-
src/backend/executor/execExprInterp.c | 543 +++++++++
src/backend/jit/llvm/llvmjit_expr.c | 7 +
src/backend/nodes/copyfuncs.c | 151 +++
src/backend/nodes/equalfuncs.c | 82 ++
src/backend/nodes/makefuncs.c | 15 +
src/backend/nodes/nodeFuncs.c | 186 ++-
src/backend/nodes/outfuncs.c | 70 ++
src/backend/nodes/readfuncs.c | 86 ++
src/backend/optimizer/path/costsize.c | 3 +-
src/backend/optimizer/util/clauses.c | 13 +
src/backend/parser/gram.y | 325 ++++-
src/backend/parser/parse_collate.c | 4 +
src/backend/parser/parse_expr.c | 488 +++++++-
src/backend/parser/parse_target.c | 15 +
src/backend/utils/adt/jsonb.c | 62 +
src/backend/utils/adt/jsonfuncs.c | 50 +-
src/backend/utils/adt/jsonpath_exec.c | 350 +++++-
src/backend/utils/adt/ruleutils.c | 151 +++
src/include/executor/execExpr.h | 54 +
src/include/executor/executor.h | 2 +
src/include/nodes/makefuncs.h | 1 +
src/include/nodes/nodes.h | 8 +
src/include/nodes/parsenodes.h | 59 +
src/include/nodes/primnodes.h | 106 ++
src/include/parser/kwlist.h | 11 +
src/include/utils/jsonb.h | 3 +
src/include/utils/jsonfuncs.h | 4 +
src/include/utils/jsonpath.h | 32 +
src/test/regress/expected/json_sqljson.out | 15 +
src/test/regress/expected/jsonb_sqljson.out | 1061 +++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 2 +
src/test/regress/sql/json_sqljson.sql | 11 +
src/test/regress/sql/jsonb_sqljson.sql | 320 +++++
37 files changed, 4922 insertions(+), 116 deletions(-)
create mode 100644 src/test/regress/expected/json_sqljson.out
create mode 100644 src/test/regress/expected/jsonb_sqljson.out
create mode 100644 src/test/regress/sql/json_sqljson.sql
create mode 100644 src/test/regress/sql/jsonb_sqljson.sql
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 543433d98e..41d12481f9 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -3134,6 +3134,27 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
APP_JUMB(pred->value_type);
}
break;
+ case T_JsonExpr:
+ {
+ JsonExpr *jexpr = (JsonExpr *) node;
+
+ APP_JUMB(jexpr->op);
+ JumbleExpr(jstate, jexpr->formatted_expr);
+ JumbleExpr(jstate, jexpr->path_spec);
+ foreach(temp, jexpr->passing_names)
+ {
+ APP_JUMB_STRING(castNode(Value, temp)->val.str);
+ }
+ JumbleExpr(jstate, (Node *) jexpr->passing_values);
+ if (jexpr->on_empty)
+ {
+ APP_JUMB(jexpr->on_empty->btype);
+ JumbleExpr(jstate, jexpr->on_empty->default_expr);
+ }
+ APP_JUMB(jexpr->on_error->btype);
+ JumbleExpr(jstate, jexpr->on_error->default_expr);
+ }
+ break;
case T_List:
foreach(temp, (List *) node)
{
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 52c6788695..5b3a9df260 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17680,6 +17680,21 @@ FROM films AS f;
<xref linkend="functions-isjson-predicate"/>
</para>
</listitem>
+ <listitem>
+ <para>
+ <xref linkend="functions-jsonexists"/>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="functions-jsonvalue"/>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="functions-jsonquery"/>
+ </para>
+ </listitem>
</itemizedlist>
<para>
@@ -17709,6 +17724,510 @@ INSERT INTO my_films VALUES (
</programlisting>
</para>
+ <refentry id="functions-jsonexists">
+ <refnamediv>
+ <refname>JSON_EXISTS</refname>
+ <refpurpose>check whether a JSON path expression can return any SQL/JSON items</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>JSON_EXISTS (
+ <replaceable class="parameter">json_api_common_syntax</replaceable>
+[ RETURNING <replaceable class="parameter">data_type</replaceable> ]
+[ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]
+)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>JSON_EXISTS</function> function checks whether the provided
+ <acronym>JSON</acronym> path expression can return any <acronym>SQL/JSON</acronym> items.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">json_api_common_syntax</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The input data to query, the JSON path expression defining the query, and an optional <literal>PASSING</literal> clause.
+ See <xref linkend="sqljson-input-clause"/> for details.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>RETURNING <replaceable class="parameter">data_type</replaceable></literal>
+ </term>
+ <listitem>
+ <para>
+ The output clause that specifies the data type of the returned value.
+ The specified data type should have a cast from a <literal>boolean</literal>
+ type, which is returned by default.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ TRUE | FALSE | UNKNOWN | ERROR } ON ERROR</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines the return value if an error occurs. The default value is <literal>FALSE</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Check whether the provided <type>jsonb</type> data contains a
+ key/value pair with the <literal>key1</literal> key, and its value
+ contains an array with one or more elements bigger than 2:
+ </para>
+<screen>
+SELECT JSON_EXISTS(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)');
+ json_exists
+-------------
+ t
+(1 row)
+</screen>
+
+ <para>
+ Note the difference between strict and lax modes
+ if the required item does not exist:
+ </para>
+<screen>
+-- Strict mode with ERROR on ERROR clause
+SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR);
+ERROR: Invalid SQL/JSON subscript
+(1 row)
+</screen>
+
+<screen>
+-- Lax mode
+SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR);
+ json_exists
+-------------
+ f
+(1 row)
+</screen>
+
+<screen>
+-- Strict mode using the default value for the ON ERROR clause
+SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'strict $.a[5]');
+ json_exists
+-------------
+ f
+(1 row)
+</screen>
+
+ </refsect1>
+ </refentry>
+
+ <refentry id="functions-jsonvalue">
+ <refnamediv>
+ <refname>JSON_VALUE</refname>
+ <refpurpose>extract a value from JSON data and convert
+ it to an <acronym>SQL</acronym> scalar</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>JSON_VALUE (
+ <replaceable class="parameter">json_api_common_syntax</replaceable>
+[ RETURNING <replaceable class="parameter">data_type</replaceable> ]
+[ { ERROR | NULL | DEFAULT <replaceable class="parameter">expression</replaceable> } ON EMPTY ]
+[ { ERROR | NULL | DEFAULT <replaceable class="parameter">expression</replaceable> } ON ERROR ]
+)
+ </synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>JSON_VALUE</function> function extracts a value from the provided
+ <acronym>JSON</acronym> data and converts it to an <acronym>SQL</acronym> scalar.
+ If the specified JSON path expression returns more than one
+ <acronym>SQL/JSON</acronym> item, an error occurs. To extract
+ an <acronym>SQL/JSON</acronym> array or object, use <xref linkend="functions-jsonquery"/>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">json_api_common_syntax</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The input data to query, the JSON path expression defining the query, and an optional <literal>PASSING</literal> clause.
+ For details, see <xref linkend="functions-sqljson-path"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>RETURNING <replaceable class="parameter">data_type</replaceable></literal>
+ </term>
+ <listitem>
+ <para>
+ The output clause that specifies the data type of the returned value.
+ Out of the box, <productname>PostgreSQL</productname>
+ supports the following types: <literal>json</literal>, <literal>jsonb</literal>,
+ <literal>bytea</literal>, and character string types (<literal>text</literal>, <literal>char</literal>,
+ <literal>varchar</literal>, and <literal>nchar</literal>).
+ The extracted value must be a single <acronym>SQL/JSON</acronym> scalar item
+ and have a cast to the specified type. Otherwise, an error occurs.
+ By default, <function>JSON_VALUE</function> returns a string
+ of the <literal>text</literal> type.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ ERROR | NULL | DEFAULT <replaceable>expression</replaceable> } ON EMPTY</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines the return value if no JSON value is found. The default is <literal>NULL</literal>.
+ If you use <literal>DEFAULT <replaceable>expression</replaceable></literal>, the provided
+ <replaceable>expression</replaceable> is evaluated and cast to the type specified in the
+ <command>RETURNING</command> clause.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ ERROR | NULL | DEFAULT <replaceable>expression</replaceable> } ON ERROR</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines the return value if an unhandled error occurs. The default is <literal>NULL</literal>.
+ If you use <literal>DEFAULT <replaceable>expression</replaceable></literal>, the provided
+ <replaceable>expression</replaceable> is evaluated and cast to the type specified in the
+ <command>RETURNING</command> clause.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Extract an SQL/JSON value and return it as an SQL
+ scalar of the specified type. Note that
+ <command>JSON_VALUE</command> can only return a
+ single scalar, and the returned value must have a
+ cast to the specified return type:
+ </para>
+
+<screen>
+SELECT JSON_VALUE('"123.45"', '$' RETURNING float);
+ json_value
+------------
+ 123.45
+(1 row)
+
+SELECT JSON_VALUE('123.45', '$' RETURNING int ERROR ON ERROR);
+ json_value
+------------
+ 123
+(1 row)
+
+SELECT JSON_VALUE('"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date);
+ json_value
+------------
+ 2015-02-01
+(1 row)
+
+SELECT JSON_VALUE('"123.45"', '$' RETURNING int ERROR ON ERROR);
+ERROR: invalid input syntax for integer: "123.45"
+
+SELECT JSON_VALUE(jsonb '[1]', 'strict $' ERROR ON ERROR);
+ERROR: SQL/JSON scalar required
+
+SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]' ERROR ON ERROR);
+ERROR: more than one SQL/JSON item
+</screen>
+
+ <para>
+ If the path expression returns an array, an object, or
+ multiple SQL/JSON items, an error is returned, as specified
+ in the <command>ON ERROR</command> clause:
+ </para>
+<screen>
+SELECT JSON_VALUE(jsonb '[1]', 'strict $' ERROR ON ERROR);
+ERROR: SQL/JSON scalar required
+
+SELECT JSON_VALUE(jsonb '{"a": 1}', 'strict $' ERROR ON ERROR);
+ERROR: SQL/JSON scalar required
+
+SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]' ERROR ON ERROR);
+ERROR: more than one SQL/JSON item
+
+SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]' DEFAULT 1 ON ERROR);
+1
+</screen>
+
+ </refsect1>
+ </refentry>
+
+ <refentry id="functions-jsonquery">
+ <refnamediv>
+ <refname>JSON_QUERY</refname>
+ <refpurpose>extract an SQL/JSON array or object from JSON data
+ and return a JSON string</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>JSON_QUERY (
+ <replaceable class="parameter">json_api_common_syntax</replaceable>
+[ RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]
+[ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ]
+[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
+[ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT <replaceable class="parameter">expression</replaceable> } ON EMPTY ]
+[ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT <replaceable class="parameter">expression</replaceable> } ON ERROR ]
+)
+ </synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>JSON_QUERY</function> function extracts an <acronym>SQL/JSON</acronym>
+ array or object from <acronym>JSON</acronym> data. This function must return
+ a JSON string, so if the path expression returns a scalar or multiple SQL/JSON
+ items, you must wrap the result using the <literal>WITH WRAPPER</literal> clause.
+ To extract a single <acronym>SQL/JSON</acronym> value, you can use <xref linkend="functions-jsonvalue"/>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">json_api_common_syntax</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The input data to query, the JSON path expression defining the query, and an optional <literal>PASSING</literal> clause.
+ For details, see <xref linkend="functions-sqljson-path"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]</literal>
+ </term>
+ <listitem>
+ <para>
+ The output clause that specifies the data type of the returned value.
+ For details, see <xref linkend="sqljson-output-clause"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines whether to wrap a returned sequence of <acronym>SQL/JSON</acronym>
+ items into a <acronym>SQL/JSON</acronym> array.
+ </para>
+ <variablelist>
+ <varlistentry>
+ <term><literal>WITHOUT WRAPPER</literal></term>
+ <listitem>
+ <para>
+ Do not wrap the result.
+ This is the default behavior if the <literal>WRAPPER</literal>
+ clause is omitted.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>WITH [UNCONDITIONAL] WRAPPER</literal></term>
+ <listitem>
+ <para>
+ Always wrap the result.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>WITH CONDITIONAL WRAPPER</literal></term>
+ <listitem>
+ <para>
+ Wrap the result if the path
+ expression returns anything other than a single
+ <acronym>SQL/JSON</acronym> array or object.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ <para>
+ Optionally, you can add the <literal>ARRAY</literal> keyword for semantic clarity.
+ </para>
+ <important>
+ <para>You cannot use this clause together with the <literal>ON EMPTY</literal> clause.
+ </para>
+ </important>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ KEEP | OMIT } QUOTES [ ON SCALAR STRING ]</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines whether to keep or omit quotes if a scalar string is returned.
+ By default, scalar strings are returned with quotes. Using this
+ clause together with the <command>WITH WRAPPER</command> clause is not allowed.
+ </para>
+ <para>
+ Optionally, you can add the <literal>ON SCALAR STRING</literal> keywords for semantic clarity.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT <replaceable class="parameter">expression</replaceable> } ON EMPTY</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines the return value if no JSON value is found. The default is <literal>NULL</literal>.
+ If you use <literal>EMPTY [ARRAY]</literal> or <literal>EMPTY OBJECT</literal>,
+ an empty JSON array [] or object {} is returned, respectively.
+ If you use <literal>DEFAULT <replaceable>expression</replaceable></literal>,
+ the provided <replaceable>expression</replaceable> is evaluated and cast
+ to the type specified in the <command>RETURNING</command> clause.
+ </para>
+ <para>
+ You cannot use this clause together with the <literal>WRAPPER</literal> clause.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT <replaceable class="parameter">expression</replaceable> } ON ERROR</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines the return value if an unhandled error occurs. The default is <literal>NULL</literal>.
+ If you use <literal>EMPTY [ARRAY]</literal> or <literal>EMPTY OBJECT</literal>,
+ an empty JSON array [] or object {} are returned, respectively.
+ If you use <literal>DEFAULT <replaceable>expression</replaceable></literal>,
+ the provided <replaceable>expression</replaceable> is evaluated and cast
+ to the type specified in the <command>RETURNING</command> clause.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Extract all film genres listed in the <structname>my_films</structname> table:
+ </para>
+ <screen>
+SELECT
+ JSON_QUERY(js, '$.favorites[*].kind' WITH WRAPPER ERROR ON ERROR)
+FROM my_films;
+ json_query
+------------
+ ["comedy", "horror", "thriller", "drama"]
+(1 row)
+</screen>
+
+ <para>
+ Note that the same query will result in an error if you omit the
+ <command>WITH WRAPPER</command> clause, as it returns multiple SQL/JSON items:
+ </para>
+ <screen>
+SELECT
+ JSON_QUERY(js, '$.favorites[*].kind' ERROR ON ERROR)
+FROM my_films;
+ERROR: more than one SQL/JSON item
+</screen>
+
+ <para>
+ Compare the effect of different <literal>WRAPPER</literal> clauses:
+ </para>
+ <screen>
+SELECT
+ js,
+ JSON_QUERY(js, 'lax $[*]') AS "without",
+ JSON_QUERY(js, 'lax $[*]' WITH WRAPPER) AS "with uncond",
+ JSON_QUERY(js, 'lax $[*]' WITH CONDITIONAL WRAPPER) AS "with cond"
+FROM
+ (VALUES (jsonb '[]'), ('[1]'), ('[[1,2,3]]'), ('[{"a": 1}]'), ('[1, null, "2"]')) foo(js);
+ js | without | with uncond | with cond
+----------------+-----------+----------------+----------------
+ [] | (null) | (null) | (null)
+ [1] | 1 | [1] | [1]
+ [[1, 2, 3]] | [1, 2, 3] | [[1, 2, 3]] | [1, 2, 3]
+ [{"a": 1}] | {"a": 1} | [{"a": 1}] | {"a": 1}
+ [1, null, "2"] | (null) | [1, null, "2"] | [1, null, "2"]
+(5 rows)
+</screen>
+
+<para>Compare quote handling for scalar types with and without the <command>OMIT QUOTES</command> clause:
+</para>
+ <screen>
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text);
+ json_query
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES);
+ json_query
+------------
+ aaa
+(1 row)
+</screen>
+ </refsect1>
+ </refentry>
+
<refentry id="functions-isjson-predicate">
<refnamediv>
<refname>IS JSON</refname>
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index b691e20ab0..3b6b667570 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -46,6 +46,7 @@
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/datum.h"
+#include "utils/jsonpath.h"
#include "utils/lsyscache.h"
#include "utils/typcache.h"
@@ -84,6 +85,40 @@ static void ExecBuildAggTransCall(ExprState *state, AggState *aggstate,
bool nullcheck);
+static ExprState *
+ExecInitExprInternal(Expr *node, PlanState *parent, ParamListInfo ext_params,
+ Datum *caseval, bool *casenull)
+{
+ ExprState *state;
+ ExprEvalStep scratch = {0};
+
+ /* Special case: NULL expression produces a NULL ExprState pointer */
+ if (node == NULL)
+ return NULL;
+
+ /* Initialize ExprState with empty step list */
+ state = makeNode(ExprState);
+ state->expr = node;
+ state->parent = parent;
+ state->ext_params = ext_params;
+ state->innermost_caseval = caseval;
+ state->innermost_casenull = casenull;
+
+ /* Insert EEOP_*_FETCHSOME steps as needed */
+ ExecInitExprSlots(state, (Node *) node);
+
+ /* Compile the expression proper */
+ ExecInitExprRec(node, state, &state->resvalue, &state->resnull);
+
+ /* Finally, append a DONE step */
+ scratch.opcode = EEOP_DONE;
+ ExprEvalPushStep(state, &scratch);
+
+ ExecReadyExpr(state);
+
+ return state;
+}
+
/*
* ExecInitExpr: prepare an expression tree for execution
*
@@ -122,32 +157,7 @@ static void ExecBuildAggTransCall(ExprState *state, AggState *aggstate,
ExprState *
ExecInitExpr(Expr *node, PlanState *parent)
{
- ExprState *state;
- ExprEvalStep scratch = {0};
-
- /* Special case: NULL expression produces a NULL ExprState pointer */
- if (node == NULL)
- return NULL;
-
- /* Initialize ExprState with empty step list */
- state = makeNode(ExprState);
- state->expr = node;
- state->parent = parent;
- state->ext_params = NULL;
-
- /* Insert EEOP_*_FETCHSOME steps as needed */
- ExecInitExprSlots(state, (Node *) node);
-
- /* Compile the expression proper */
- ExecInitExprRec(node, state, &state->resvalue, &state->resnull);
-
- /* Finally, append a DONE step */
- scratch.opcode = EEOP_DONE;
- ExprEvalPushStep(state, &scratch);
-
- ExecReadyExpr(state);
-
- return state;
+ return ExecInitExprInternal(node, parent, NULL, NULL, NULL);
}
/*
@@ -159,32 +169,20 @@ ExecInitExpr(Expr *node, PlanState *parent)
ExprState *
ExecInitExprWithParams(Expr *node, ParamListInfo ext_params)
{
- ExprState *state;
- ExprEvalStep scratch = {0};
-
- /* Special case: NULL expression produces a NULL ExprState pointer */
- if (node == NULL)
- return NULL;
-
- /* Initialize ExprState with empty step list */
- state = makeNode(ExprState);
- state->expr = node;
- state->parent = NULL;
- state->ext_params = ext_params;
-
- /* Insert EEOP_*_FETCHSOME steps as needed */
- ExecInitExprSlots(state, (Node *) node);
-
- /* Compile the expression proper */
- ExecInitExprRec(node, state, &state->resvalue, &state->resnull);
-
- /* Finally, append a DONE step */
- scratch.opcode = EEOP_DONE;
- ExprEvalPushStep(state, &scratch);
-
- ExecReadyExpr(state);
+ return ExecInitExprInternal(node, NULL, ext_params, NULL, NULL);
+}
- return state;
+/*
+ * ExecInitExprWithCaseValue: prepare an expression tree for execution
+ *
+ * This is the same as ExecInitExpr, except that a pointer to the value for
+ * CasTestExpr is passed here.
+ */
+ExprState *
+ExecInitExprWithCaseValue(Expr *node, PlanState *parent,
+ Datum *caseval, bool *casenull)
+{
+ return ExecInitExprInternal(node, parent, NULL, caseval, casenull);
}
/*
@@ -2207,6 +2205,112 @@ ExecInitExprRec(Expr *node, ExprState *state,
break;
}
+ case T_JsonExpr:
+ {
+ JsonExpr *jexpr = castNode(JsonExpr, node);
+ ListCell *argexprlc;
+ ListCell *argnamelc;
+
+ scratch.opcode = EEOP_JSONEXPR;
+ scratch.d.jsonexpr.jsexpr = jexpr;
+
+ scratch.d.jsonexpr.formatted_expr =
+ palloc(sizeof(*scratch.d.jsonexpr.formatted_expr));
+
+ ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
+ &scratch.d.jsonexpr.formatted_expr->value,
+ &scratch.d.jsonexpr.formatted_expr->isnull);
+
+ scratch.d.jsonexpr.pathspec =
+ palloc(sizeof(*scratch.d.jsonexpr.pathspec));
+
+ ExecInitExprRec((Expr *) jexpr->path_spec, state,
+ &scratch.d.jsonexpr.pathspec->value,
+ &scratch.d.jsonexpr.pathspec->isnull);
+
+ scratch.d.jsonexpr.res_expr =
+ palloc(sizeof(*scratch.d.jsonexpr.res_expr));
+
+ scratch.d.jsonexpr.result_expr = jexpr->result_coercion
+ ? ExecInitExprWithCaseValue((Expr *) jexpr->result_coercion->expr,
+ state->parent,
+ &scratch.d.jsonexpr.res_expr->value,
+ &scratch.d.jsonexpr.res_expr->isnull)
+ : NULL;
+
+ scratch.d.jsonexpr.default_on_empty = !jexpr->on_empty ? NULL :
+ ExecInitExpr((Expr *) jexpr->on_empty->default_expr,
+ state->parent);
+
+ scratch.d.jsonexpr.default_on_error =
+ ExecInitExpr((Expr *) jexpr->on_error->default_expr,
+ state->parent);
+
+ if (jexpr->omit_quotes ||
+ (jexpr->result_coercion && jexpr->result_coercion->via_io))
+ {
+ Oid typinput;
+
+ /* lookup the result type's input function */
+ getTypeInputInfo(jexpr->returning->typid, &typinput,
+ &scratch.d.jsonexpr.input.typioparam);
+ fmgr_info(typinput, &scratch.d.jsonexpr.input.func);
+ }
+
+ scratch.d.jsonexpr.args = NIL;
+
+ forboth(argexprlc, jexpr->passing_values,
+ argnamelc, jexpr->passing_names)
+ {
+ Expr *argexpr = (Expr *) lfirst(argexprlc);
+ Value *argname = (Value *) lfirst(argnamelc);
+ JsonPathVariableEvalContext *var = palloc(sizeof(*var));
+
+ var->name = pstrdup(argname->val.str);
+ var->typid = exprType((Node *) argexpr);
+ var->typmod = exprTypmod((Node *) argexpr);
+ var->estate = ExecInitExpr(argexpr, state->parent);
+ var->econtext = NULL;
+ var->evaluated = false;
+ var->value = (Datum) 0;
+ var->isnull = true;
+
+ scratch.d.jsonexpr.args =
+ lappend(scratch.d.jsonexpr.args, var);
+ }
+
+ scratch.d.jsonexpr.cache = NULL;
+
+ if (jexpr->coercions)
+ {
+ JsonCoercion **coercion;
+ struct JsonCoercionState *cstate;
+ Datum *caseval;
+ bool *casenull;
+
+ scratch.d.jsonexpr.coercion_expr =
+ palloc(sizeof(*scratch.d.jsonexpr.coercion_expr));
+
+ caseval = &scratch.d.jsonexpr.coercion_expr->value;
+ casenull = &scratch.d.jsonexpr.coercion_expr->isnull;
+
+ for (cstate = &scratch.d.jsonexpr.coercions.null,
+ coercion = &jexpr->coercions->null;
+ coercion <= &jexpr->coercions->composite;
+ coercion++, cstate++)
+ {
+ cstate->coercion = *coercion;
+ cstate->estate = *coercion ?
+ ExecInitExprWithCaseValue((Expr *)(*coercion)->expr,
+ state->parent,
+ caseval, casenull) : NULL;
+ }
+ }
+
+ ExprEvalPushStep(state, &scratch);
+ break;
+ }
+
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(node));
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 7956489aaa..5708d0fdde 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -57,14 +57,18 @@
#include "postgres.h"
#include "access/heaptoast.h"
+#include "access/xact.h"
+#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/sequence.h"
#include "executor/execExpr.h"
#include "executor/nodeSubplan.h"
#include "funcapi.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "parser/parsetree.h"
+#include "parser/parse_expr.h"
#include "pgstat.h"
#include "utils/array.h"
#include "utils/builtins.h"
@@ -74,8 +78,10 @@
#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/resowner.h"
#include "utils/timestamp.h"
#include "utils/typcache.h"
#include "utils/xml.h"
@@ -437,6 +443,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_ALTERNATIVE_SUBPLAN,
&&CASE_EEOP_JSON_CTOR,
&&CASE_EEOP_IS_JSON,
+ &&CASE_EEOP_JSONEXPR,
&&CASE_EEOP_AGG_STRICT_DESERIALIZE,
&&CASE_EEOP_AGG_DESERIALIZE,
&&CASE_EEOP_AGG_STRICT_INPUT_CHECK_ARGS,
@@ -1798,7 +1805,13 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
{
/* too complex for an inline implementation */
ExecEvalAggOrderedTransTuple(state, op, econtext);
+ EEO_NEXT();
+ }
+ EEO_CASE(EEOP_JSONEXPR)
+ {
+ /* too complex for an inline implementation */
+ ExecEvalJson(state, op, econtext);
EEO_NEXT();
}
@@ -4450,3 +4463,533 @@ ExecAggPlainTransByRef(AggState *aggstate, AggStatePerTrans pertrans,
MemoryContextSwitchTo(oldContext);
}
+
+/*
+ * Evaluate a JSON error/empty behavior result.
+ */
+static Datum
+ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
+ ExprState *default_estate, bool *is_null)
+{
+ *is_null = false;
+
+ switch (behavior->btype)
+ {
+ case JSON_BEHAVIOR_EMPTY_ARRAY:
+ return JsonbPGetDatum(JsonbMakeEmptyArray());
+
+ case JSON_BEHAVIOR_EMPTY_OBJECT:
+ return JsonbPGetDatum(JsonbMakeEmptyObject());
+
+ case JSON_BEHAVIOR_TRUE:
+ return BoolGetDatum(true);
+
+ case JSON_BEHAVIOR_FALSE:
+ return BoolGetDatum(false);
+
+ case JSON_BEHAVIOR_NULL:
+ case JSON_BEHAVIOR_UNKNOWN:
+ *is_null = true;
+ return (Datum) 0;
+
+ case JSON_BEHAVIOR_DEFAULT:
+ return ExecEvalExpr(default_estate, econtext, is_null);
+
+ default:
+ elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype);
+ return (Datum) 0;
+ }
+}
+
+/*
+ * Evaluate a coercion of a JSON item to the target type.
+ */
+static Datum
+ExecEvalJsonExprCoercion(ExprEvalStep *op, ExprContext *econtext,
+ Datum res, bool *isNull, void *p, bool *error)
+{
+ ExprState *estate = p;
+
+ if (estate) /* coerce using specified expression */
+ return ExecEvalExpr(estate, econtext, isNull);
+
+ if (op->d.jsonexpr.jsexpr->op != IS_JSON_EXISTS)
+ {
+ JsonCoercion *coercion = op->d.jsonexpr.jsexpr->result_coercion;
+ JsonExpr *jexpr = op->d.jsonexpr.jsexpr;
+ Jsonb *jb = *isNull ? NULL : DatumGetJsonbP(res);
+
+ if ((coercion && coercion->via_io) ||
+ (jexpr->omit_quotes && !*isNull &&
+ JB_ROOT_IS_SCALAR(jb)))
+ {
+ /* strip quotes and call typinput function */
+ char *str = *isNull ? NULL : JsonbUnquote(jb);
+
+ return InputFunctionCall(&op->d.jsonexpr.input.func, str,
+ op->d.jsonexpr.input.typioparam,
+ jexpr->returning->typmod);
+ }
+ else if (coercion && coercion->via_populate)
+ return json_populate_type(res, JSONBOID,
+ jexpr->returning->typid,
+ jexpr->returning->typmod,
+ &op->d.jsonexpr.cache,
+ econtext->ecxt_per_query_memory,
+ isNull);
+ }
+
+ if (op->d.jsonexpr.result_expr)
+ {
+ op->d.jsonexpr.res_expr->value = res;
+ op->d.jsonexpr.res_expr->isnull = *isNull;
+
+ res = ExecEvalExpr(op->d.jsonexpr.result_expr, econtext, isNull);
+ }
+
+ return res;
+}
+
+/*
+ * Evaluate a JSON path variable caching computed value.
+ */
+int
+EvalJsonPathVar(void *cxt, char *varName, int varNameLen,
+ JsonbValue *val, JsonbValue *baseObject)
+{
+ JsonPathVariableEvalContext *var = NULL;
+ List *vars = cxt;
+ ListCell *lc;
+ int id = 1;
+
+ if (!varName)
+ return list_length(vars);
+
+ foreach(lc, vars)
+ {
+ var = lfirst(lc);
+
+ if (!strncmp(var->name, varName, varNameLen))
+ break;
+
+ var = NULL;
+ id++;
+ }
+
+ if (!var)
+ return -1;
+
+ if (!var->evaluated)
+ {
+ var->value = ExecEvalExpr(var->estate, var->econtext, &var->isnull);
+ var->evaluated = true;
+ }
+
+ if (var->isnull)
+ {
+ val->type = jbvNull;
+ return 0;
+ }
+
+ JsonItemFromDatum(var->value, var->typid, var->typmod, val);
+
+ *baseObject = *val;
+ return id;
+}
+
+/*
+ * Prepare SQL/JSON item coercion to the output type. Returned a datum of the
+ * corresponding SQL type and a pointer to the coercion state.
+ */
+Datum
+ExecPrepareJsonItemCoercion(JsonbValue *item,
+ JsonReturning *returning,
+ struct JsonCoercionsState *coercions,
+ struct JsonCoercionState **pcoercion)
+{
+ struct JsonCoercionState *coercion;
+ Datum res;
+ JsonbValue buf;
+
+ if (item->type == jbvBinary &&
+ JsonContainerIsScalar(item->val.binary.data))
+ {
+ bool res PG_USED_FOR_ASSERTS_ONLY;
+
+ res = JsonbExtractScalar(item->val.binary.data, &buf);
+ item = &buf;
+ Assert(res);
+ }
+
+ /* get coercion state reference and datum of the corresponding SQL type */
+ switch (item->type)
+ {
+ case jbvNull:
+ coercion = &coercions->null;
+ res = (Datum) 0;
+ break;
+
+ case jbvString:
+ coercion = &coercions->string;
+ res = PointerGetDatum(
+ cstring_to_text_with_len(item->val.string.val,
+ item->val.string.len));
+ break;
+
+ case jbvNumeric:
+ coercion = &coercions->numeric;
+ res = NumericGetDatum(item->val.numeric);
+ break;
+
+ case jbvBool:
+ coercion = &coercions->boolean;
+ res = BoolGetDatum(item->val.boolean);
+ break;
+
+ case jbvDatetime:
+ res = item->val.datetime.value;
+ switch (item->val.datetime.typid)
+ {
+ case DATEOID:
+ coercion = &coercions->date;
+ break;
+ case TIMEOID:
+ coercion = &coercions->time;
+ break;
+ case TIMETZOID:
+ coercion = &coercions->timetz;
+ break;
+ case TIMESTAMPOID:
+ coercion = &coercions->timestamp;
+ break;
+ case TIMESTAMPTZOID:
+ coercion = &coercions->timestamptz;
+ break;
+ default:
+ elog(ERROR, "unexpected jsonb datetime type oid %d",
+ item->val.datetime.typid);
+ return (Datum) 0;
+ }
+ break;
+
+ case jbvArray:
+ case jbvObject:
+ case jbvBinary:
+ coercion = &coercions->composite;
+ res = JsonbPGetDatum(JsonbValueToJsonb(item));
+ break;
+
+ default:
+ elog(ERROR, "unexpected jsonb value type %d", item->type);
+ return (Datum) 0;
+ }
+
+ *pcoercion = coercion;
+
+ return res;
+}
+
+typedef Datum (*JsonFunc)(ExprEvalStep *op, ExprContext *econtext,
+ Datum item, bool *resnull, void *p, bool *error);
+
+static Datum
+ExecEvalJsonExprSubtrans(JsonFunc func, ExprEvalStep *op,
+ ExprContext *econtext,
+ Datum res, bool *resnull,
+ void *p, bool *error, bool subtrans)
+{
+ if (subtrans)
+ {
+ /*
+ * We should catch exceptions of category ERRCODE_DATA_EXCEPTION
+ * and execute the corresponding ON ERROR behavior then.
+ */
+ MemoryContext oldcontext = CurrentMemoryContext;
+ ResourceOwner oldowner = CurrentResourceOwner;
+
+ Assert(error);
+
+ BeginInternalSubTransaction(NULL);
+ /* Want to execute expressions inside function's memory context */
+ MemoryContextSwitchTo(oldcontext);
+
+ PG_TRY();
+ {
+ res = func(op, econtext, res, resnull, p, error);
+
+ /* Commit the inner transaction, return to outer xact context */
+ ReleaseCurrentSubTransaction();
+ MemoryContextSwitchTo(oldcontext);
+ CurrentResourceOwner = oldowner;
+ }
+ PG_CATCH();
+ {
+ ErrorData *edata;
+
+ /* Save error info in oldcontext */
+ MemoryContextSwitchTo(oldcontext);
+ edata = CopyErrorData();
+ FlushErrorState();
+
+ /* Abort the inner transaction */
+ RollbackAndReleaseCurrentSubTransaction();
+ MemoryContextSwitchTo(oldcontext);
+ CurrentResourceOwner = oldowner;
+
+ if (ERRCODE_TO_CATEGORY(edata->sqlerrcode) !=
+ ERRCODE_DATA_EXCEPTION)
+ ReThrowError(edata);
+
+ res = (Datum) 0;
+ *error = true;
+ }
+ PG_END_TRY();
+
+ return res;
+ }
+ else
+ {
+ /* No need to use subtransactions. */
+ return func(op, econtext, res, resnull, p, error);
+ }
+}
+
+
+typedef struct
+{
+ JsonPath *path;
+ bool *error;
+ bool coercionInSubtrans;
+} ExecEvalJsonExprContext;
+
+static Datum
+ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
+ Datum item, bool *resnull, void *pcxt,
+ bool *error)
+{
+ ExecEvalJsonExprContext *cxt = pcxt;
+ JsonPath *path = cxt->path;
+ JsonExpr *jexpr = op->d.jsonexpr.jsexpr;
+ ExprState *estate = NULL;
+ bool empty = false;
+ Datum res = (Datum) 0;
+
+ switch (jexpr->op)
+ {
+ case IS_JSON_QUERY:
+ res = JsonPathQuery(item, path, jexpr->wrapper, &empty, error,
+ op->d.jsonexpr.args);
+ *resnull = !DatumGetPointer(res);
+ if (error && *error)
+ return (Datum) 0;
+ break;
+
+ case IS_JSON_VALUE:
+ {
+ struct JsonCoercionState *jcstate;
+ JsonbValue *jbv = JsonPathValue(item, path, &empty, error,
+ op->d.jsonexpr.args);
+
+ if (error && *error)
+ return (Datum) 0;
+
+ if (!jbv) /* NULL or empty */
+ break;
+
+ Assert(!empty);
+
+ *resnull = false;
+
+ /* coerce scalar item to the output type */
+ if (jexpr->returning->typid == JSONOID ||
+ jexpr->returning->typid == JSONBOID)
+ {
+ /* Use result coercion from json[b] to the output type */
+ res = JsonbPGetDatum(JsonbValueToJsonb(jbv));
+ break;
+ }
+
+ /* Use coercion from SQL/JSON item type to the output type */
+ res = ExecPrepareJsonItemCoercion(jbv,
+ op->d.jsonexpr.jsexpr->returning,
+ &op->d.jsonexpr.coercions,
+ &jcstate);
+
+ if (jcstate->coercion &&
+ (jcstate->coercion->via_io ||
+ jcstate->coercion->via_populate))
+ {
+ if (error)
+ {
+ *error = true;
+ return (Datum) 0;
+ }
+ /*
+ * Coercion via I/O means here that the cast to the target
+ * type simply does not exist.
+ */
+ ereport(ERROR,
+ /*
+ * XXX Standard says about a separate error code
+ * ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE
+ * but does not define its number.
+ */
+ (errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+ errmsg("SQL/JSON item cannot be cast to target type")));
+ }
+ else if (!jcstate->estate)
+ return res; /* no coercion */
+
+ /* coerce using specific expression */
+ estate = jcstate->estate;
+ op->d.jsonexpr.coercion_expr->value = res;
+ op->d.jsonexpr.coercion_expr->isnull = *resnull;
+ break;
+ }
+
+ case IS_JSON_EXISTS:
+ {
+ bool exists = JsonPathExists(item, path,
+ op->d.jsonexpr.args,
+ error);
+
+ *resnull = error && *error;
+ res = BoolGetDatum(exists);
+
+ if (!op->d.jsonexpr.result_expr)
+ return res;
+
+ /* coerce using result expression */
+ estate = op->d.jsonexpr.result_expr;
+ op->d.jsonexpr.res_expr->value = res;
+ op->d.jsonexpr.res_expr->isnull = *resnull;
+ break;
+ }
+
+ default:
+ elog(ERROR, "unrecognized SQL/JSON expression op %d", jexpr->op);
+ return (Datum) 0;
+ }
+
+ if (empty)
+ {
+ Assert(jexpr->on_empty); /* it is not JSON_EXISTS */
+
+ if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
+ {
+ if (error)
+ {
+ *error = true;
+ return (Datum) 0;
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_NO_SQL_JSON_ITEM),
+ errmsg("no SQL/JSON item")));
+ }
+
+ if (jexpr->on_empty->btype == JSON_BEHAVIOR_DEFAULT)
+ /*
+ * Execute DEFAULT expression as a coercion expression, because
+ * its result is already coerced to the target type.
+ */
+ estate = op->d.jsonexpr.default_on_empty;
+ else
+ /* Execute ON EMPTY behavior */
+ res = ExecEvalJsonBehavior(econtext, jexpr->on_empty,
+ op->d.jsonexpr.default_on_empty,
+ resnull);
+ }
+
+ return ExecEvalJsonExprSubtrans(ExecEvalJsonExprCoercion, op, econtext,
+ res, resnull, estate, error,
+ cxt->coercionInSubtrans);
+}
+
+bool
+ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr,
+ struct JsonCoercionsState *coercions)
+{
+ if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+ return false;
+
+ if (jsexpr->op == IS_JSON_EXISTS)
+ return false;
+
+ if (!coercions)
+ return true;
+
+ return false;
+}
+
+/* ----------------------------------------------------------------
+ * ExecEvalJson
+ * ----------------------------------------------------------------
+ */
+void
+ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
+{
+ ExecEvalJsonExprContext cxt;
+ JsonExpr *jexpr = op->d.jsonexpr.jsexpr;
+ Datum item;
+ Datum res = (Datum) 0;
+ JsonPath *path;
+ ListCell *lc;
+ bool error = false;
+ bool needSubtrans;
+ bool throwErrors = jexpr->on_error->btype == JSON_BEHAVIOR_ERROR;
+
+ *op->resnull = true; /* until we get a result */
+ *op->resvalue = (Datum) 0;
+
+ if (op->d.jsonexpr.formatted_expr->isnull || op->d.jsonexpr.pathspec->isnull)
+ {
+ /* execute domain checks for NULLs */
+ (void) ExecEvalJsonExprCoercion(op, econtext, res, op->resnull,
+ NULL, NULL);
+
+ Assert(*op->resnull);
+ *op->resnull = true;
+
+ return;
+ }
+
+ item = op->d.jsonexpr.formatted_expr->value;
+ path = DatumGetJsonPathP(op->d.jsonexpr.pathspec->value);
+
+ /* reset JSON path variable contexts */
+ foreach(lc, op->d.jsonexpr.args)
+ {
+ JsonPathVariableEvalContext *var = lfirst(lc);
+
+ var->econtext = econtext;
+ var->evaluated = false;
+ }
+
+ needSubtrans = ExecEvalJsonNeedsSubTransaction(jexpr, &op->d.jsonexpr.coercions);
+
+ cxt.path = path;
+ cxt.error = throwErrors ? NULL : &error;
+ cxt.coercionInSubtrans = !needSubtrans && !throwErrors;
+ Assert(!needSubtrans || cxt.error);
+
+ res = ExecEvalJsonExprSubtrans(ExecEvalJsonExpr, op, econtext, item,
+ op->resnull, &cxt, cxt.error,
+ needSubtrans);
+
+ if (error)
+ {
+ /* Execute ON ERROR behavior */
+ res = ExecEvalJsonBehavior(econtext, jexpr->on_error,
+ op->d.jsonexpr.default_on_error,
+ op->resnull);
+
+ /* result is already coerced in DEFAULT behavior case */
+ if (jexpr->on_error->btype != JSON_BEHAVIOR_DEFAULT)
+ res = ExecEvalJsonExprCoercion(op, econtext, res,
+ op->resnull,
+ NULL, NULL);
+ }
+
+ *op->resvalue = res;
+}
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index cca5c117a0..211eca4078 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2362,6 +2362,13 @@ llvm_compile_expr(ExprState *state)
LLVMBuildBr(b, opblocks[opno + 1]);
break;
+
+ case EEOP_JSONEXPR:
+ build_EvalXFunc(b, mod, "ExecEvalJson",
+ v_state, v_econtext, op);
+ LLVMBuildBr(b, opblocks[i + 1]);
+ break;
+
case EEOP_LAST:
Assert(false);
break;
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index eb3028c8cb..6114b6739b 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2431,6 +2431,91 @@ _copyJsonArrayQueryCtor(const JsonArrayQueryCtor *from)
return newnode;
}
+/*
+ * _copyJsonExpr
+ */
+static JsonExpr *
+_copyJsonExpr(const JsonExpr *from)
+{
+ JsonExpr *newnode = makeNode(JsonExpr);
+
+ COPY_SCALAR_FIELD(op);
+ COPY_NODE_FIELD(formatted_expr);
+ COPY_NODE_FIELD(result_coercion);
+ COPY_NODE_FIELD(format);
+ COPY_NODE_FIELD(path_spec);
+ COPY_NODE_FIELD(passing_values);
+ COPY_NODE_FIELD(passing_names);
+ COPY_NODE_FIELD(returning);
+ COPY_NODE_FIELD(on_error);
+ COPY_NODE_FIELD(on_empty);
+ COPY_NODE_FIELD(coercions);
+ COPY_SCALAR_FIELD(wrapper);
+ COPY_SCALAR_FIELD(omit_quotes);
+ COPY_LOCATION_FIELD(location);
+
+ return newnode;
+}
+
+/*
+ * _copyJsonCoercion
+ */
+static JsonCoercion *
+_copyJsonCoercion(const JsonCoercion *from)
+{
+ JsonCoercion *newnode = makeNode(JsonCoercion);
+
+ COPY_NODE_FIELD(expr);
+ COPY_SCALAR_FIELD(via_populate);
+ COPY_SCALAR_FIELD(via_io);
+ COPY_SCALAR_FIELD(collation);
+
+ return newnode;
+}
+
+/*
+ * _copylJsonItemCoercions
+ */
+static JsonItemCoercions *
+_copyJsonItemCoercions(const JsonItemCoercions *from)
+{
+ JsonItemCoercions *newnode = makeNode(JsonItemCoercions);
+
+ COPY_NODE_FIELD(null);
+ COPY_NODE_FIELD(string);
+ COPY_NODE_FIELD(numeric);
+ COPY_NODE_FIELD(boolean);
+ COPY_NODE_FIELD(date);
+ COPY_NODE_FIELD(time);
+ COPY_NODE_FIELD(timetz);
+ COPY_NODE_FIELD(timestamp);
+ COPY_NODE_FIELD(timestamptz);
+ COPY_NODE_FIELD(composite);
+
+ return newnode;
+}
+
+
+/*
+ * _copyJsonFuncExpr
+ */
+static JsonFuncExpr *
+_copyJsonFuncExpr(const JsonFuncExpr *from)
+{
+ JsonFuncExpr *newnode = makeNode(JsonFuncExpr);
+
+ COPY_SCALAR_FIELD(op);
+ COPY_NODE_FIELD(common);
+ COPY_NODE_FIELD(output);
+ COPY_NODE_FIELD(on_empty);
+ COPY_NODE_FIELD(on_error);
+ COPY_SCALAR_FIELD(wrapper);
+ COPY_SCALAR_FIELD(omit_quotes);
+ COPY_LOCATION_FIELD(location);
+
+ return newnode;
+}
+
/*
* _copyJsonIsPredicate
*/
@@ -2448,6 +2533,51 @@ _copyJsonIsPredicate(const JsonIsPredicate *from)
return newnode;
}
+/*
+ * _copyJsonBehavior
+ */
+static JsonBehavior *
+_copyJsonBehavior(const JsonBehavior *from)
+{
+ JsonBehavior *newnode = makeNode(JsonBehavior);
+
+ COPY_SCALAR_FIELD(btype);
+ COPY_NODE_FIELD(default_expr);
+
+ return newnode;
+}
+
+/*
+ * _copyJsonCommon
+ */
+static JsonCommon *
+_copyJsonCommon(const JsonCommon *from)
+{
+ JsonCommon *newnode = makeNode(JsonCommon);
+
+ COPY_NODE_FIELD(expr);
+ COPY_NODE_FIELD(pathspec);
+ COPY_STRING_FIELD(pathname);
+ COPY_NODE_FIELD(passing);
+ COPY_LOCATION_FIELD(location);
+
+ return newnode;
+}
+
+/*
+ * _copyJsonArgument
+ */
+static JsonArgument *
+_copyJsonArgument(const JsonArgument *from)
+{
+ JsonArgument *newnode = makeNode(JsonArgument);
+
+ COPY_NODE_FIELD(val);
+ COPY_STRING_FIELD(name);
+
+ return newnode;
+}
+
/* ****************************************************************
* pathnodes.h copy functions
*
@@ -5381,6 +5511,27 @@ copyObjectImpl(const void *from)
case T_JsonIsPredicate:
retval = _copyJsonIsPredicate(from);
break;
+ case T_JsonFuncExpr:
+ retval = _copyJsonFuncExpr(from);
+ break;
+ case T_JsonExpr:
+ retval = _copyJsonExpr(from);
+ break;
+ case T_JsonCommon:
+ retval = _copyJsonCommon(from);
+ break;
+ case T_JsonBehavior:
+ retval = _copyJsonBehavior(from);
+ break;
+ case T_JsonArgument:
+ retval = _copyJsonArgument(from);
+ break;
+ case T_JsonCoercion:
+ retval = _copyJsonCoercion(from);
+ break;
+ case T_JsonItemCoercions:
+ retval = _copyJsonItemCoercions(from);
+ break;
/*
* RELATION NODES
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 23a4ff93e0..ed3ff3a6d6 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -875,6 +875,76 @@ _equalJsonIsPredicate(const JsonIsPredicate *a,
return true;
}
+/*
+ * _equalJsonBehavior
+ */
+static bool
+_equalJsonBehavior(const JsonBehavior *a, const JsonBehavior *b)
+{
+ COMPARE_SCALAR_FIELD(btype);
+ COMPARE_NODE_FIELD(default_expr);
+
+ return true;
+}
+
+/*
+ * _equalJsonExpr
+ */
+static bool
+_equalJsonExpr(const JsonExpr *a, const JsonExpr *b)
+{
+ COMPARE_SCALAR_FIELD(op);
+ COMPARE_NODE_FIELD(formatted_expr);
+ COMPARE_NODE_FIELD(result_coercion);
+ COMPARE_NODE_FIELD(format);
+ COMPARE_NODE_FIELD(path_spec);
+ COMPARE_NODE_FIELD(passing_values);
+ COMPARE_NODE_FIELD(passing_names);
+ COMPARE_NODE_FIELD(returning);
+ COMPARE_NODE_FIELD(on_error);
+ COMPARE_NODE_FIELD(on_empty);
+ COMPARE_NODE_FIELD(coercions);
+ COMPARE_SCALAR_FIELD(wrapper);
+ COMPARE_SCALAR_FIELD(omit_quotes);
+ COMPARE_LOCATION_FIELD(location);
+
+ return true;
+}
+
+/*
+ * _equalJsonCoercion
+ */
+static bool
+_equalJsonCoercion(const JsonCoercion *a, const JsonCoercion *b)
+{
+ COMPARE_NODE_FIELD(expr);
+ COMPARE_SCALAR_FIELD(via_populate);
+ COMPARE_SCALAR_FIELD(via_io);
+ COMPARE_SCALAR_FIELD(collation);
+
+ return true;
+}
+
+/*
+ * _equalJsonItemCoercions
+ */
+static bool
+_equalJsonItemCoercions(const JsonItemCoercions *a, const JsonItemCoercions *b)
+{
+ COMPARE_NODE_FIELD(null);
+ COMPARE_NODE_FIELD(string);
+ COMPARE_NODE_FIELD(numeric);
+ COMPARE_NODE_FIELD(boolean);
+ COMPARE_NODE_FIELD(date);
+ COMPARE_NODE_FIELD(time);
+ COMPARE_NODE_FIELD(timetz);
+ COMPARE_NODE_FIELD(timestamp);
+ COMPARE_NODE_FIELD(timestamptz);
+ COMPARE_NODE_FIELD(composite);
+
+ return true;
+}
+
/*
* Stuff from pathnodes.h
*/
@@ -3282,6 +3352,18 @@ equal(const void *a, const void *b)
case T_JsonIsPredicate:
retval = _equalJsonIsPredicate(a, b);
break;
+ case T_JsonBehavior:
+ retval = _equalJsonBehavior(a, b);
+ break;
+ case T_JsonExpr:
+ retval = _equalJsonExpr(a, b);
+ break;
+ case T_JsonCoercion:
+ retval = _equalJsonCoercion(a, b);
+ break;
+ case T_JsonItemCoercions:
+ retval = _equalJsonItemCoercions(a, b);
+ break;
/*
* RELATION NODES
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 1d772528ca..226996809d 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -848,6 +848,21 @@ makeJsonValueExpr(Expr *expr, JsonFormat *format)
return jve;
}
+/*
+ * makeJsonBehavior -
+ * creates a JsonBehavior node
+ */
+JsonBehavior *
+makeJsonBehavior(JsonBehaviorType type, Node *default_expr)
+{
+ JsonBehavior *behavior = makeNode(JsonBehavior);
+
+ behavior->btype = type;
+ behavior->default_expr = default_expr;
+
+ return behavior;
+}
+
/*
* makeJsonEncoding -
* converts JSON encoding name to enum JsonEncoding
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index a600efedb5..ca86bda28e 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -271,6 +271,12 @@ exprType(const Node *expr)
case T_JsonIsPredicate:
type = BOOLOID;
break;
+ case T_JsonExpr:
+ type = ((const JsonExpr *) expr)->returning->typid;
+ break;
+ case T_JsonCoercion:
+ type = exprType(((const JsonCoercion *) expr)->expr);
+ break;
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
type = InvalidOid; /* keep compiler quiet */
@@ -507,7 +513,11 @@ exprTypmod(const Node *expr)
case T_JsonValueExpr:
return exprTypmod((Node *) ((const JsonValueExpr *) expr)->formatted_expr);
case T_JsonCtorExpr:
- return -1; /* ((const JsonCtorExpr *) expr)->returning->typmod; */
+ return ((const JsonCtorExpr *) expr)->returning->typmod;
+ case T_JsonExpr:
+ return ((JsonExpr *) expr)->returning->typmod;
+ case T_JsonCoercion:
+ return exprTypmod(((const JsonCoercion *) expr)->expr);
default:
break;
}
@@ -939,6 +949,21 @@ exprCollation(const Node *expr)
case T_JsonIsPredicate:
coll = InvalidOid; /* result is always an boolean type */
break;
+ case T_JsonExpr:
+ {
+ JsonExpr *jexpr = (JsonExpr *) expr;
+ JsonCoercion *coercion = jexpr->result_coercion;
+
+ if (!coercion)
+ coll = InvalidOid;
+ else if (coercion->expr)
+ coll = exprCollation(coercion->expr);
+ else if (coercion->via_io || coercion->via_populate)
+ coll = coercion->collation;
+ else
+ coll = InvalidOid;
+ }
+ break;
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
coll = InvalidOid; /* keep compiler quiet */
@@ -1159,6 +1184,21 @@ exprSetCollation(Node *expr, Oid collation)
case T_JsonIsPredicate:
Assert(!OidIsValid(collation)); /* result is always boolean */
break;
+ case T_JsonExpr:
+ {
+ JsonExpr *jexpr = (JsonExpr *) expr;
+ JsonCoercion *coercion = jexpr->result_coercion;
+
+ if (!coercion)
+ Assert(!OidIsValid(collation));
+ else if (coercion->expr)
+ exprSetCollation(coercion->expr, collation);
+ else if (coercion->via_io || coercion->via_populate)
+ coercion->collation = collation;
+ else
+ Assert(!OidIsValid(collation));
+ }
+ break;
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
break;
@@ -1608,6 +1648,15 @@ 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;
default:
/* for any other node type it's just unknown... */
loc = -1;
@@ -2329,6 +2378,54 @@ expression_tree_walker(Node *node,
break;
case T_JsonIsPredicate:
return walker(((JsonIsPredicate *) node)->expr, context);
+ case T_JsonExpr:
+ {
+ JsonExpr *jexpr = (JsonExpr *) node;
+
+ if (walker(jexpr->formatted_expr, context))
+ return true;
+ if (walker(jexpr->result_coercion, context))
+ return true;
+ if (walker(jexpr->passing_values, context))
+ return true;
+ /* we assume walker doesn't care about passing_names */
+ if (jexpr->on_empty &&
+ walker(jexpr->on_empty->default_expr, context))
+ return true;
+ if (walker(jexpr->on_error->default_expr, context))
+ return true;
+ if (walker(jexpr->coercions, context))
+ return true;
+ }
+ break;
+ case T_JsonCoercion:
+ return walker(((JsonCoercion *) node)->expr, context);
+ case T_JsonItemCoercions:
+ {
+ JsonItemCoercions *coercions = (JsonItemCoercions *) node;
+
+ if (walker(coercions->null, context))
+ return true;
+ if (walker(coercions->string, context))
+ return true;
+ if (walker(coercions->numeric, context))
+ return true;
+ if (walker(coercions->boolean, context))
+ return true;
+ if (walker(coercions->date, context))
+ return true;
+ if (walker(coercions->time, context))
+ return true;
+ if (walker(coercions->timetz, context))
+ return true;
+ if (walker(coercions->timestamp, context))
+ return true;
+ if (walker(coercions->timestamptz, context))
+ return true;
+ if (walker(coercions->composite, context))
+ return true;
+ }
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(node));
@@ -3288,6 +3385,7 @@ expression_tree_mutator(Node *node,
return (Node *) newnode;
}
+ break;
case T_JsonIsPredicate:
{
JsonIsPredicate *pred = (JsonIsPredicate *) node;
@@ -3298,6 +3396,55 @@ expression_tree_mutator(Node *node,
return (Node *) newnode;
}
+ break;
+ 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, JsonCoercion *);
+ MUTATE(newnode->passing_values, jexpr->passing_values, List *);
+ /* assume mutator does not care about passing_names */
+ if (newnode->on_empty)
+ MUTATE(newnode->on_empty->default_expr,
+ jexpr->on_empty->default_expr, Node *);
+ MUTATE(newnode->on_error->default_expr,
+ jexpr->on_error->default_expr, Node *);
+ return (Node *) newnode;
+ }
+ break;
+ case T_JsonCoercion:
+ {
+ JsonCoercion *coercion = (JsonCoercion *) node;
+ JsonCoercion *newnode;
+
+ FLATCOPY(newnode, coercion, JsonCoercion);
+ MUTATE(newnode->expr, coercion->expr, Node *);
+ return (Node *) newnode;
+ }
+ break;
+ case T_JsonItemCoercions:
+ {
+ JsonItemCoercions *coercions = (JsonItemCoercions *) node;
+ JsonItemCoercions *newnode;
+
+ FLATCOPY(newnode, coercions, JsonItemCoercions);
+ MUTATE(newnode->null, coercions->null, JsonCoercion *);
+ MUTATE(newnode->string, coercions->string, JsonCoercion *);
+ MUTATE(newnode->numeric, coercions->numeric, JsonCoercion *);
+ MUTATE(newnode->boolean, coercions->boolean, JsonCoercion *);
+ MUTATE(newnode->date, coercions->date, JsonCoercion *);
+ MUTATE(newnode->time, coercions->time, JsonCoercion *);
+ MUTATE(newnode->timetz, coercions->timetz, JsonCoercion *);
+ MUTATE(newnode->timestamp, coercions->timestamp, JsonCoercion *);
+ MUTATE(newnode->timestamptz, coercions->timestamptz, JsonCoercion *);
+ MUTATE(newnode->composite, coercions->composite, JsonCoercion *);
+ return (Node *) newnode;
+ }
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(node));
@@ -4105,6 +4252,43 @@ raw_expression_tree_walker(Node *node,
break;
case T_JsonIsPredicate:
return walker(((JsonIsPredicate *) node)->expr, context);
+ case T_JsonArgument:
+ return walker(((JsonArgument *) node)->val, context);
+ case T_JsonCommon:
+ {
+ JsonCommon *jc = (JsonCommon *) node;
+
+ if (walker(jc->expr, context))
+ return true;
+ if (walker(jc->pathspec, context))
+ return true;
+ if (walker(jc->passing, context))
+ return true;
+ }
+ break;
+ case T_JsonBehavior:
+ {
+ JsonBehavior *jb = (JsonBehavior *) node;
+
+ if (jb->btype == JSON_BEHAVIOR_DEFAULT &&
+ walker(jb->default_expr, context))
+ return true;
+ }
+ break;
+ case T_JsonFuncExpr:
+ {
+ JsonFuncExpr *jfe = (JsonFuncExpr *) node;
+
+ if (walker(jfe->common, context))
+ return true;
+ if (jfe->output && walker(jfe->output, context))
+ return true;
+ if (walker(jfe->on_empty, context))
+ return true;
+ if (walker(jfe->on_error, context))
+ return true;
+ }
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(node));
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 3b11690614..88a2f9e609 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1765,6 +1765,64 @@ _outJsonIsPredicate(StringInfo str, const JsonIsPredicate *node)
WRITE_LOCATION_FIELD(location);
}
+static void
+_outJsonBehavior(StringInfo str, const JsonBehavior *node)
+{
+ WRITE_NODE_TYPE("JSONBEHAVIOR");
+
+ WRITE_ENUM_FIELD(btype, JsonBehaviorType);
+ WRITE_NODE_FIELD(default_expr);
+}
+
+static void
+_outJsonExpr(StringInfo str, const JsonExpr *node)
+{
+ WRITE_NODE_TYPE("JSONEXPR");
+
+ WRITE_ENUM_FIELD(op, JsonExprOp);
+ WRITE_NODE_FIELD(formatted_expr);
+ WRITE_NODE_FIELD(result_coercion);
+ WRITE_NODE_FIELD(format);
+ WRITE_NODE_FIELD(path_spec);
+ WRITE_NODE_FIELD(passing_values);
+ WRITE_NODE_FIELD(passing_names);
+ WRITE_NODE_FIELD(returning);
+ WRITE_NODE_FIELD(on_error);
+ WRITE_NODE_FIELD(on_empty);
+ WRITE_NODE_FIELD(coercions);
+ WRITE_ENUM_FIELD(wrapper, JsonWrapper);
+ WRITE_BOOL_FIELD(omit_quotes);
+ WRITE_LOCATION_FIELD(location);
+}
+
+static void
+_outJsonCoercion(StringInfo str, const JsonCoercion *node)
+{
+ WRITE_NODE_TYPE("JSONCOERCION");
+
+ WRITE_NODE_FIELD(expr);
+ WRITE_BOOL_FIELD(via_populate);
+ WRITE_BOOL_FIELD(via_io);
+ WRITE_OID_FIELD(collation);
+}
+
+static void
+_outJsonItemCoercions(StringInfo str, const JsonItemCoercions *node)
+{
+ WRITE_NODE_TYPE("JSONITEMCOERCIONS");
+
+ WRITE_NODE_FIELD(null);
+ WRITE_NODE_FIELD(string);
+ WRITE_NODE_FIELD(numeric);
+ WRITE_NODE_FIELD(boolean);
+ WRITE_NODE_FIELD(date);
+ WRITE_NODE_FIELD(time);
+ WRITE_NODE_FIELD(timetz);
+ WRITE_NODE_FIELD(timestamp);
+ WRITE_NODE_FIELD(timestamptz);
+ WRITE_NODE_FIELD(composite);
+}
+
/*****************************************************************************
*
* Stuff from pathnodes.h.
@@ -4408,6 +4466,18 @@ outNode(StringInfo str, const void *obj)
case T_JsonIsPredicate:
_outJsonIsPredicate(str, obj);
break;
+ case T_JsonBehavior:
+ _outJsonBehavior(str, obj);
+ break;
+ case T_JsonExpr:
+ _outJsonExpr(str, obj);
+ break;
+ case T_JsonCoercion:
+ _outJsonCoercion(str, obj);
+ break;
+ case T_JsonItemCoercions:
+ _outJsonItemCoercions(str, obj);
+ break;
default:
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 8f87789a11..4bd99344e5 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1408,6 +1408,84 @@ _readJsonCtorExpr(void)
READ_DONE();
}
+/*
+ * _readJsonBehavior
+ */
+static JsonBehavior *
+_readJsonBehavior(void)
+{
+ READ_LOCALS(JsonBehavior);
+
+ READ_ENUM_FIELD(btype, JsonBehaviorType);
+ READ_NODE_FIELD(default_expr);
+
+ READ_DONE();
+}
+
+/*
+ * _readJsonExpr
+ */
+static JsonExpr *
+_readJsonExpr(void)
+{
+ READ_LOCALS(JsonExpr);
+
+ READ_ENUM_FIELD(op, JsonExprOp);
+ READ_NODE_FIELD(formatted_expr);
+ READ_NODE_FIELD(result_coercion);
+ READ_NODE_FIELD(format);
+ READ_NODE_FIELD(path_spec);
+ READ_NODE_FIELD(passing_values);
+ READ_NODE_FIELD(passing_names);
+ READ_NODE_FIELD(returning);
+ READ_NODE_FIELD(on_error);
+ READ_NODE_FIELD(on_empty);
+ READ_NODE_FIELD(coercions);
+ READ_ENUM_FIELD(wrapper, JsonWrapper);
+ READ_BOOL_FIELD(omit_quotes);
+ READ_LOCATION_FIELD(location);
+
+ READ_DONE();
+}
+
+/*
+ * _readJsonCoercion
+ */
+static JsonCoercion *
+_readJsonCoercion(void)
+{
+ READ_LOCALS(JsonCoercion);
+
+ READ_NODE_FIELD(expr);
+ READ_BOOL_FIELD(via_populate);
+ READ_BOOL_FIELD(via_io);
+ READ_OID_FIELD(collation);
+
+ READ_DONE();
+}
+
+/*
+ * _readJsonItemCoercions
+ */
+static JsonItemCoercions *
+_readJsonItemCoercions(void)
+{
+ READ_LOCALS(JsonItemCoercions);
+
+ READ_NODE_FIELD(null);
+ READ_NODE_FIELD(string);
+ READ_NODE_FIELD(numeric);
+ READ_NODE_FIELD(boolean);
+ READ_NODE_FIELD(date);
+ READ_NODE_FIELD(time);
+ READ_NODE_FIELD(timetz);
+ READ_NODE_FIELD(timestamp);
+ READ_NODE_FIELD(timestamptz);
+ READ_NODE_FIELD(composite);
+
+ READ_DONE();
+}
+
/*
* _readJsonIsPredicate
*/
@@ -2971,6 +3049,14 @@ parseNodeString(void)
return_value = _readJsonCtorExpr();
else if (MATCH("JSONISPREDICATE", 15))
return_value = _readJsonIsPredicate();
+ else if (MATCH("JSONBEHAVIOR", 12))
+ return_value = _readJsonBehavior();
+ else if (MATCH("JSONEXPR", 8))
+ return_value = _readJsonExpr();
+ else if (MATCH("JSONCOERCION", 12))
+ return_value = _readJsonCoercion();
+ else if (MATCH("JSONITEMCOERCIONS", 17))
+ return_value = _readJsonItemCoercions();
else
{
elog(ERROR, "badly formatted node string \"%.32s\"...", token);
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 945aa93374..cf1ee17638 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -4245,7 +4245,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 48b6b63c17..51e7d91bdd 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -28,6 +28,7 @@
#include "catalog/pg_type.h"
#include "executor/executor.h"
#include "executor/functions.h"
+#include "executor/execExpr.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
@@ -1069,6 +1070,18 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
context, 0);
}
+ /* JsonExpr is parallel-unsafe if subtransactions can be used. */
+ else if (IsA(node, JsonExpr))
+ {
+ JsonExpr *jsexpr = (JsonExpr *) node;
+
+ if (ExecEvalJsonNeedsSubTransaction(jsexpr, NULL))
+ {
+ context->max_hazard = PROPARALLEL_UNSAFE;
+ return true;
+ }
+ }
+
/* Recurse to check arguments */
return expression_tree_walker(node,
max_parallel_hazard_walker,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4701634b5c..29d1e1917d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -251,6 +251,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
PartitionBoundSpec *partboundspec;
RoleSpec *rolespec;
struct SelectLimit *selectlimit;
+ JsonBehavior *jsbehavior;
+ struct
+ {
+ JsonBehavior *on_empty;
+ JsonBehavior *on_error;
+ } on_behavior;
+ JsonQuotes js_quotes;
}
%type <node> stmt schema_stmt
@@ -603,7 +610,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_representation
json_value_expr
json_func_expr
+ json_value_func_expr
+ json_query_expr
+ json_exists_predicate
+ json_api_common_syntax
+ json_context_item
+ json_argument
json_output_clause_opt
+ json_returning_clause_opt
json_value_constructor
json_object_constructor
json_object_constructor_args_opt
@@ -615,15 +629,43 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_aggregate_func
json_object_aggregate_constructor
json_array_aggregate_constructor
+ json_path_specification
%type <list> json_name_and_value_list
json_value_expr_list
json_array_aggregate_order_by_clause_opt
+ json_arguments
+ json_passing_clause_opt
+
+%type <str> json_table_path_name
+ json_as_path_name_clause_opt
%type <ival> json_encoding
json_encoding_clause_opt
+ json_wrapper_clause_opt
+ json_wrapper_behavior
+ json_conditional_or_unconditional_opt
json_predicate_type_constraint_opt
+%type <jsbehavior> json_behavior_error
+ json_behavior_null
+ json_behavior_true
+ json_behavior_false
+ json_behavior_unknown
+ json_behavior_empty_array
+ json_behavior_empty_object
+ json_behavior_default
+ json_value_behavior
+ json_query_behavior
+ json_exists_error_behavior
+ json_exists_error_clause_opt
+
+%type <on_behavior> json_value_on_behavior_clause_opt
+ json_query_on_behavior_clause_opt
+
+%type <js_quotes> json_quotes_behavior
+ json_quotes_clause_opt
+
%type <boolean> json_key_uniqueness_constraint_opt
json_object_constructor_null_clause_opt
json_array_constructor_null_clause_opt
@@ -663,8 +705,8 @@ 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 CONCURRENTLY CONFIGURATION CONFLICT CONNECTION CONSTRAINT
- CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE
+ COMMITTED 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
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
@@ -674,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 FIRST_P FLOAT_P FOLLOWING FOR
@@ -690,7 +732,8 @@ 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
+ JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_EXISTS JSON_OBJECT JSON_OBJECTAGG
+ JSON_QUERY JSON_VALUE
KEY KEYS KEEP
@@ -705,7 +748,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
@@ -713,7 +756,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 REFERENCES REFERENCING
REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
@@ -723,7 +766,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
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 STRIP_P
SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P
TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
@@ -731,7 +774,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
@@ -799,7 +842,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
* blame any funny behavior of UNBOUNDED on the SQL standard, though.
*/
%nonassoc UNBOUNDED /* ideally should have same precedence as IDENT */
-%nonassoc ABSENT
+%nonassoc ERROR_P EMPTY_P DEFAULT ABSENT /* JSON error/empty behavior */
+%nonassoc FALSE_P KEEP OMIT PASSING TRUE_P UNKNOWN
%nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP
%left Op OPERATOR /* multi-character ops and user-defined operators */
%left '+' '-'
@@ -14742,6 +14786,80 @@ opt_asymmetric: ASYMMETRIC
/* SQL/JSON support */
json_func_expr:
json_value_constructor
+ | json_value_func_expr
+ | json_query_expr
+ | json_exists_predicate
+ ;
+
+
+json_value_func_expr:
+ JSON_VALUE '('
+ json_api_common_syntax
+ json_returning_clause_opt
+ json_value_on_behavior_clause_opt
+ ')'
+ {
+ JsonFuncExpr *n = makeNode(JsonFuncExpr);
+ n->op = IS_JSON_VALUE;
+ n->common = (JsonCommon *) $3;
+ n->output = (JsonOutput *) $4;
+ n->on_empty = $5.on_empty;
+ n->on_error = $5.on_error;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+json_api_common_syntax:
+ json_context_item ',' json_path_specification
+ json_as_path_name_clause_opt
+ json_passing_clause_opt
+ {
+ JsonCommon *n = makeNode(JsonCommon);
+ n->expr = (JsonValueExpr *) $1;
+ n->pathspec = $3;
+ n->pathname = $4;
+ n->passing = $5;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+json_context_item:
+ json_value_expr { $$ = $1; }
+ ;
+
+json_path_specification:
+ a_expr { $$ = $1; }
+ ;
+
+json_as_path_name_clause_opt:
+ AS json_table_path_name { $$ = $2; }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
+json_table_path_name:
+ name { $$ = $1; }
+ ;
+
+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;
+ }
;
json_value_expr:
@@ -14780,6 +14898,153 @@ json_encoding:
name { $$ = makeJsonEncoding($1); }
;
+json_behavior_error:
+ ERROR_P { $$ = makeJsonBehavior(JSON_BEHAVIOR_ERROR, NULL); }
+ ;
+
+json_behavior_null:
+ NULL_P { $$ = makeJsonBehavior(JSON_BEHAVIOR_NULL, NULL); }
+ ;
+
+json_behavior_true:
+ TRUE_P { $$ = makeJsonBehavior(JSON_BEHAVIOR_TRUE, NULL); }
+ ;
+
+json_behavior_false:
+ FALSE_P { $$ = makeJsonBehavior(JSON_BEHAVIOR_FALSE, NULL); }
+ ;
+
+json_behavior_unknown:
+ UNKNOWN { $$ = makeJsonBehavior(JSON_BEHAVIOR_UNKNOWN, NULL); }
+ ;
+
+json_behavior_empty_array:
+ EMPTY_P ARRAY { $$ = makeJsonBehavior(JSON_BEHAVIOR_EMPTY_ARRAY, NULL); }
+ /* non-standard, for Oracle compatibility only */
+ | EMPTY_P { $$ = makeJsonBehavior(JSON_BEHAVIOR_EMPTY_ARRAY, NULL); }
+ ;
+
+json_behavior_empty_object:
+ EMPTY_P OBJECT_P { $$ = makeJsonBehavior(JSON_BEHAVIOR_EMPTY_OBJECT, NULL); }
+ ;
+
+json_behavior_default:
+ DEFAULT a_expr { $$ = makeJsonBehavior(JSON_BEHAVIOR_DEFAULT, $2); }
+ ;
+
+
+json_value_behavior:
+ json_behavior_null
+ | json_behavior_error
+ | json_behavior_default
+ ;
+
+json_value_on_behavior_clause_opt:
+ json_value_behavior ON EMPTY_P
+ { $$.on_empty = $1; $$.on_error = NULL; }
+ | json_value_behavior ON EMPTY_P json_value_behavior ON ERROR_P
+ { $$.on_empty = $1; $$.on_error = $4; }
+ | json_value_behavior ON ERROR_P
+ { $$.on_empty = NULL; $$.on_error = $1; }
+ | /* EMPTY */
+ { $$.on_empty = NULL; $$.on_error = NULL; }
+ ;
+
+json_query_expr:
+ JSON_QUERY '('
+ json_api_common_syntax
+ json_output_clause_opt
+ json_wrapper_clause_opt
+ json_quotes_clause_opt
+ json_query_on_behavior_clause_opt
+ ')'
+ {
+ JsonFuncExpr *n = makeNode(JsonFuncExpr);
+ n->op = IS_JSON_QUERY;
+ n->common = (JsonCommon *) $3;
+ n->output = (JsonOutput *) $4;
+ n->wrapper = $5;
+ if (n->wrapper != JSW_NONE && $6 != JS_QUOTES_UNSPEC)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("SQL/JSON QUOTES behavior shall not be specified when WITH WRAPPER is used"),
+ parser_errposition(@6)));
+ n->omit_quotes = $6 == JS_QUOTES_OMIT;
+ n->on_empty = $7.on_empty;
+ n->on_error = $7.on_error;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+json_wrapper_clause_opt:
+ json_wrapper_behavior WRAPPER { $$ = $1; }
+ | /* EMPTY */ { $$ = 0; }
+ ;
+
+json_wrapper_behavior:
+ WITHOUT array_opt { $$ = JSW_NONE; }
+ | WITH json_conditional_or_unconditional_opt array_opt { $$ = $2; }
+ ;
+
+array_opt:
+ ARRAY { }
+ | /* EMPTY */ { }
+ ;
+
+json_conditional_or_unconditional_opt:
+ CONDITIONAL { $$ = JSW_CONDITIONAL; }
+ | UNCONDITIONAL { $$ = JSW_UNCONDITIONAL; }
+ | /* EMPTY */ { $$ = JSW_UNCONDITIONAL; }
+ ;
+
+json_quotes_clause_opt:
+ json_quotes_behavior QUOTES json_on_scalar_string_opt { $$ = $1; }
+ | /* EMPTY */ { $$ = JS_QUOTES_UNSPEC; }
+ ;
+
+json_quotes_behavior:
+ KEEP { $$ = JS_QUOTES_KEEP; }
+ | OMIT { $$ = JS_QUOTES_OMIT; }
+ ;
+
+json_on_scalar_string_opt:
+ ON SCALAR STRING { }
+ | /* EMPTY */ { }
+ ;
+
+json_query_behavior:
+ json_behavior_error
+ | json_behavior_null
+ | json_behavior_empty_array
+ | json_behavior_empty_object
+ | json_behavior_default
+ ;
+
+json_query_on_behavior_clause_opt:
+ json_query_behavior ON EMPTY_P
+ { $$.on_empty = $1; $$.on_error = NULL; }
+ | json_query_behavior ON EMPTY_P json_query_behavior ON ERROR_P
+ { $$.on_empty = $1; $$.on_error = $4; }
+ | json_query_behavior ON ERROR_P
+ { $$.on_empty = NULL; $$.on_error = $1; }
+ | /* EMPTY */
+ { $$.on_empty = NULL; $$.on_error = NULL; }
+ ;
+
+json_returning_clause_opt:
+ RETURNING Typename
+ {
+ JsonOutput *n = makeNode(JsonOutput);
+ n->typeName = $2;
+ n->returning = makeNode(JsonReturning);
+ n->returning->format =
+ makeJsonFormat(JS_FORMAT_DEFAULT, JS_FORMAT_DEFAULT, @2);
+ $$ = (Node *) n;
+ }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
json_output_clause_opt:
RETURNING Typename json_format_clause_opt
{
@@ -14792,6 +15057,35 @@ json_output_clause_opt:
| /* EMPTY */ { $$ = NULL; }
;
+json_exists_predicate:
+ JSON_EXISTS '('
+ json_api_common_syntax
+ json_returning_clause_opt
+ json_exists_error_clause_opt
+ ')'
+ {
+ JsonFuncExpr *p = makeNode(JsonFuncExpr);
+ p->op = IS_JSON_EXISTS;
+ p->common = (JsonCommon *) $3;
+ p->output = (JsonOutput *) $4;
+ p->on_error = $5;
+ p->location = @1;
+ $$ = (Node *) p;
+ }
+ ;
+
+json_exists_error_clause_opt:
+ json_exists_error_behavior ON ERROR_P { $$ = $1; }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
+json_exists_error_behavior:
+ json_behavior_error
+ | json_behavior_true
+ | json_behavior_false
+ | json_behavior_unknown
+ ;
+
json_value_constructor:
json_object_constructor
| json_array_constructor
@@ -15399,6 +15693,7 @@ unreserved_keyword:
| COMMENTS
| COMMIT
| COMMITTED
+ | CONDITIONAL
| CONFIGURATION
| CONFLICT
| CONNECTION
@@ -15434,10 +15729,12 @@ unreserved_keyword:
| DOUBLE_P
| DROP
| EACH
+ | EMPTY_P
| ENABLE_P
| ENCODING
| ENCRYPTED
| ENUM_P
+ | ERROR_P
| ESCAPE
| EVENT
| EXCLUDE
@@ -15486,6 +15783,7 @@ unreserved_keyword:
| INVOKER
| ISOLATION
| JSON
+ | KEEP
| KEY
| KEYS
| LABEL
@@ -15530,6 +15828,7 @@ unreserved_keyword:
| OFF
| OIDS
| OLD
+ | OMIT
| OPERATOR
| OPTION
| OPTIONS
@@ -15559,6 +15858,7 @@ unreserved_keyword:
| PROGRAM
| PUBLICATION
| QUOTE
+ | QUOTES
| RANGE
| READ
| REASSIGN
@@ -15639,6 +15939,7 @@ unreserved_keyword:
| UESCAPE
| UNBOUNDED
| UNCOMMITTED
+ | UNCONDITIONAL
| UNENCRYPTED
| UNKNOWN
| UNLISTEN
@@ -15698,8 +15999,11 @@ col_name_keyword:
| INTERVAL
| JSON_ARRAY
| JSON_ARRAYAGG
+ | JSON_EXISTS
| JSON_OBJECT
| JSON_OBJECTAGG
+ | JSON_QUERY
+ | JSON_VALUE
| LEAST
| NATIONAL
| NCHAR
@@ -15767,6 +16071,7 @@ type_func_name_keyword:
| OVERLAPS
| RIGHT
| SIMILAR
+ | STRING
| TABLESAMPLE
| VERBOSE
;
diff --git a/src/backend/parser/parse_collate.c b/src/backend/parser/parse_collate.c
index bf800f5937..618d8f04a9 100644
--- a/src/backend/parser/parse_collate.c
+++ b/src/backend/parser/parse_collate.c
@@ -667,6 +667,10 @@ assign_collations_walker(Node *node, assign_collations_context *context)
&loccontext);
}
break;
+ case T_JsonExpr:
+ /* Context item and PASSING arguments are already
+ * marked with collations in parse_expr.c. */
+ break;
default:
/*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 4a3f97358e..747a045453 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -130,6 +130,8 @@ static Node *transformJsonArrayQueryCtor(ParseState *pstate,
static Node *transformJsonObjectAgg(ParseState *pstate, JsonObjectAgg *agg);
static Node *transformJsonArrayAgg(ParseState *pstate, JsonArrayAgg *agg);
static Node *transformJsonIsPredicate(ParseState *pstate, JsonIsPredicate *p);
+static Node *transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *p);
+static Node *transformJsonValueExpr(ParseState *pstate, JsonValueExpr *jve);
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,
@@ -402,6 +404,14 @@ transformExprRecurse(ParseState *pstate, Node *expr)
result = transformJsonIsPredicate(pstate, (JsonIsPredicate *) expr);
break;
+ case T_JsonFuncExpr:
+ result = transformJsonFuncExpr(pstate, (JsonFuncExpr *) expr);
+ break;
+
+ case T_JsonValueExpr:
+ result = transformJsonValueExpr(pstate, (JsonValueExpr *) expr);
+ break;
+
default:
/* should not reach here */
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
@@ -3680,8 +3690,8 @@ makeCaseTestExpr(Node *expr)
* default format otherwise.
*/
static Node *
-transformJsonValueExpr(ParseState *pstate, JsonValueExpr *ve,
- JsonFormatType default_format)
+transformJsonValueExprExt(ParseState *pstate, JsonValueExpr *ve,
+ JsonFormatType default_format, bool isarg)
{
Node *expr = transformExprRecurse(pstate, (Node *) ve->raw_expr);
Node *rawexpr;
@@ -3700,6 +3710,8 @@ transformJsonValueExpr(ParseState *pstate, JsonValueExpr *ve,
get_type_category_preferred(exprtype, &typcategory, &typispreferred);
+ rawexpr = expr;
+
if (ve->format->format != JS_FORMAT_DEFAULT)
{
if (ve->format->encoding != JS_ENC_DEFAULT && exprtype != BYTEAOID)
@@ -3717,6 +3729,36 @@ transformJsonValueExpr(ParseState *pstate, JsonValueExpr *ve,
else
format = ve->format->format;
}
+ else if (isarg)
+ {
+ /* Pass SQL/JSON item types directly without conversion to json[b]. */
+ switch (exprtype)
+ {
+ case TEXTOID:
+ case NUMERICOID:
+ case BOOLOID:
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case DATEOID:
+ case TIMEOID:
+ case TIMETZOID:
+ case TIMESTAMPOID:
+ case TIMESTAMPTZOID:
+ return expr;
+
+ default:
+ if (typcategory == TYPCATEGORY_STRING)
+ return coerce_to_specific_type(pstate, expr, TEXTOID,
+ "JSON_VALUE_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
@@ -3730,7 +3772,7 @@ transformJsonValueExpr(ParseState *pstate, JsonValueExpr *ve,
expr = orig;
- if (exprtype != BYTEAOID && typcategory != TYPCATEGORY_STRING)
+ if (!isarg && exprtype != BYTEAOID && typcategory != TYPCATEGORY_STRING)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg(ve->format->format == JS_FORMAT_DEFAULT ?
@@ -3777,10 +3819,30 @@ transformJsonValueExpr(ParseState *pstate, JsonValueExpr *ve,
expr = (Node *) ve;
}
}
+ else
+ expr = rawexpr;
return expr;
}
+/*
+ * Transform JSON value expression using FORMAT JSON by default.
+ */
+static Node *
+transformJsonValueExpr(ParseState *pstate, JsonValueExpr *jve)
+{
+ return transformJsonValueExprExt(pstate, jve, JS_FORMAT_JSON, false);
+}
+
+/*
+ * Transform JSON value expression using unspecified format by default.
+ */
+static Node *
+transformJsonValueExprDefault(ParseState *pstate, JsonValueExpr *jve)
+{
+ return transformJsonValueExprExt(pstate, jve, JS_FORMAT_DEFAULT, false);
+}
+
/*
* Checks specified output format for its applicability to the target type.
*/
@@ -4002,8 +4064,7 @@ transformJsonObjectCtor(ParseState *pstate, JsonObjectCtor *ctor)
{
JsonKeyValue *kv = castNode(JsonKeyValue, lfirst(lc));
Node *key = transformExprRecurse(pstate, (Node *) kv->key);
- Node *val = transformJsonValueExpr(pstate, kv->value,
- JS_FORMAT_DEFAULT);
+ Node *val = transformJsonValueExprDefault(pstate, kv->value);
args = lappend(args, key);
args = lappend(args, val);
@@ -4179,7 +4240,7 @@ transformJsonObjectAgg(ParseState *pstate, JsonObjectAgg *agg)
returning = transformJsonOutput(pstate, agg->ctor.output, true);
key = transformExprRecurse(pstate, (Node *) agg->arg->key);
- val = transformJsonValueExpr(pstate, agg->arg->value, JS_FORMAT_DEFAULT);
+ val = transformJsonValueExprDefault(pstate, agg->arg->value);
args = list_make2(key, val);
if (returning->format->format == JS_FORMAT_JSONB)
@@ -4235,7 +4296,7 @@ transformJsonArrayAgg(ParseState *pstate, JsonArrayAgg *agg)
returning = transformJsonOutput(pstate, agg->ctor.output, true);
- arg = transformJsonValueExpr(pstate, agg->arg, JS_FORMAT_DEFAULT);
+ arg = transformJsonValueExprDefault(pstate, agg->arg);
if (returning->format->format == JS_FORMAT_JSONB)
{
@@ -4279,8 +4340,7 @@ transformJsonArrayCtor(ParseState *pstate, JsonArrayCtor *ctor)
foreach(lc, ctor->exprs)
{
JsonValueExpr *jsval = castNode(JsonValueExpr, lfirst(lc));
- Node *val = transformJsonValueExpr(pstate, jsval,
- JS_FORMAT_DEFAULT);
+ Node *val = transformJsonValueExprDefault(pstate, jsval);
args = lappend(args, val);
}
@@ -4351,3 +4411,413 @@ transformJsonIsPredicate(ParseState *pstate, JsonIsPredicate *pred)
return makeJsonIsPredicate((Node *) expr, NULL, pred->value_type,
pred->unique_keys, pred->location);
}
+
+/*
+ * Transform a JSON PASSING clause.
+ */
+static void
+transformJsonPassingArgs(ParseState *pstate, 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 = transformJsonValueExprExt(pstate, arg->val,
+ format, true);
+
+ assign_expr_collations(pstate, expr);
+
+ *passing_values = lappend(*passing_values, expr);
+ *passing_names = lappend(*passing_names, makeString(arg->name));
+ }
+}
+
+/*
+ * Transform a JSON BEHAVIOR clause.
+ */
+static JsonBehavior *
+transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
+ JsonBehaviorType default_behavior)
+{
+ JsonBehaviorType behavior_type;
+ Node *default_expr;
+
+ behavior_type = behavior ? behavior->btype : default_behavior;
+ default_expr = behavior_type != JSON_BEHAVIOR_DEFAULT ? NULL :
+ transformExprRecurse(pstate, behavior->default_expr);
+
+ return makeJsonBehavior(behavior_type, default_expr);
+}
+
+/*
+ * Common code for JSON_VALUE, JSON_QUERY, JSON_EXISTS transformation
+ * into a JsonExpr node.
+ */
+static JsonExpr *
+transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func)
+{
+ JsonExpr *jsexpr = makeNode(JsonExpr);
+ Node *pathspec;
+ JsonFormatType format;
+
+ if (func->common->pathname)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("JSON_TABLE path name is not allowed here"),
+ parser_errposition(pstate, func->location)));
+
+ jsexpr->location = func->location;
+ jsexpr->op = func->op;
+ jsexpr->formatted_expr = transformJsonValueExpr(pstate, func->common->expr);
+
+ assign_expr_collations(pstate, jsexpr->formatted_expr);
+
+ /* format is determined by context item type */
+ format = exprType(jsexpr->formatted_expr) == JSONBOID ? JS_FORMAT_JSONB : JS_FORMAT_JSON;
+
+ jsexpr->result_coercion = NULL;
+ jsexpr->omit_quotes = false;
+
+ jsexpr->format = func->common->expr->format;
+
+ pathspec = transformExprRecurse(pstate, func->common->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 type %s, not type %s",
+ "jsonpath", format_type_be(exprType(pathspec))),
+ parser_errposition(pstate, exprLocation(pathspec))));
+
+ /* transform and coerce to json[b] passing arguments */
+ transformJsonPassingArgs(pstate, format, func->common->passing,
+ &jsexpr->passing_values, &jsexpr->passing_names);
+
+ if (func->op != IS_JSON_EXISTS)
+ jsexpr->on_empty = transformJsonBehavior(pstate, func->on_empty,
+ JSON_BEHAVIOR_NULL);
+
+ jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
+ func->op == IS_JSON_EXISTS ?
+ JSON_BEHAVIOR_FALSE :
+ JSON_BEHAVIOR_NULL);
+
+ return jsexpr;
+}
+
+/*
+ * Assign default JSON returning type from the specified format or from
+ * the context item type.
+ */
+static void
+assignDefaultJsonReturningType(Node *context_item, JsonFormat *context_format,
+ JsonReturning *ret)
+{
+ bool is_jsonb;
+
+ ret->format = copyObject(context_format);
+
+ if (ret->format->format == JS_FORMAT_DEFAULT)
+ is_jsonb = exprType(context_item) == JSONBOID;
+ else
+ is_jsonb = ret->format->format == JS_FORMAT_JSONB;
+
+ ret->typid = is_jsonb ? JSONBOID : JSONOID;
+ ret->typmod = -1;
+}
+
+/*
+ * Try to coerce expression to the output type or
+ * use json_populate_type() for composite, array and domain types or
+ * use coercion via I/O.
+ */
+static JsonCoercion *
+coerceJsonExpr(ParseState *pstate, Node *expr, const JsonReturning *returning)
+{
+ char typtype;
+ JsonCoercion *coercion = makeNode(JsonCoercion);
+
+ coercion->expr = coerceJsonFuncExpr(pstate, expr, returning, false);
+
+ if (coercion->expr)
+ {
+ if (coercion->expr == expr)
+ coercion->expr = NULL;
+
+ return coercion;
+ }
+
+ typtype = get_typtype(returning->typid);
+
+ if (returning->typid == RECORDOID ||
+ typtype == TYPTYPE_COMPOSITE ||
+ typtype == TYPTYPE_DOMAIN ||
+ type_is_array(returning->typid))
+ coercion->via_populate = true;
+ else
+ coercion->via_io = true;
+
+ return coercion;
+}
+
+/*
+ * Transform a JSON output clause of JSON_VALUE, JSON_QUERY, JSON_EXISTS.
+ */
+static void
+transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
+ JsonExpr *jsexpr)
+{
+ Node *expr = jsexpr->formatted_expr;
+
+ jsexpr->returning = transformJsonOutput(pstate, func->output, false);
+
+ /* JSON_VALUE returns text by default */
+ if (func->op == IS_JSON_VALUE && !OidIsValid(jsexpr->returning->typid))
+ {
+ jsexpr->returning->typid = TEXTOID;
+ jsexpr->returning->typmod = -1;
+ }
+
+ if (OidIsValid(jsexpr->returning->typid))
+ {
+ JsonReturning ret;
+
+ if (func->op == IS_JSON_VALUE &&
+ jsexpr->returning->typid != JSONOID &&
+ jsexpr->returning->typid != JSONBOID)
+ {
+ /* Forced coercion via I/O for JSON_VALUE for non-JSON types */
+ jsexpr->result_coercion = makeNode(JsonCoercion);
+ jsexpr->result_coercion->expr = NULL;
+ jsexpr->result_coercion->via_io = true;
+ return;
+ }
+
+ assignDefaultJsonReturningType(jsexpr->formatted_expr, jsexpr->format, &ret);
+
+ if (ret.typid != jsexpr->returning->typid ||
+ ret.typmod != jsexpr->returning->typmod)
+ {
+ Node *placeholder = makeCaseTestExpr(expr);
+
+ Assert(((CaseTestExpr *) placeholder)->typeId == ret.typid);
+ Assert(((CaseTestExpr *) placeholder)->typeMod == ret.typmod);
+
+ jsexpr->result_coercion = coerceJsonExpr(pstate, placeholder,
+ jsexpr->returning);
+ }
+ }
+ else
+ assignDefaultJsonReturningType(jsexpr->formatted_expr, jsexpr->format,
+ jsexpr->returning);
+}
+
+/*
+ * Coerce a expression in JSON DEFAULT behavior to the target output type.
+ */
+static Node *
+coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, Node *defexpr)
+{
+ int location;
+ Oid exprtype;
+
+ if (!defexpr)
+ return NULL;
+
+ exprtype = exprType(defexpr);
+ location = exprLocation(defexpr);
+
+ if (location < 0)
+ location = jsexpr->location;
+
+ defexpr = coerce_to_target_type(pstate,
+ defexpr,
+ exprtype,
+ jsexpr->returning->typid,
+ jsexpr->returning->typmod,
+ COERCION_EXPLICIT,
+ COERCE_IMPLICIT_CAST,
+ location);
+
+ if (!defexpr)
+ ereport(ERROR,
+ (errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast DEFAULT expression type %s to %s",
+ format_type_be(exprtype),
+ format_type_be(jsexpr->returning->typid)),
+ parser_errposition(pstate, location)));
+
+ return defexpr;
+}
+
+/*
+ * Initialize SQL/JSON item coercion from the SQL type "typid" to the target
+ * "returning" type.
+ */
+static JsonCoercion *
+initJsonItemCoercion(ParseState *pstate, Oid typid,
+ const JsonReturning *returning)
+{
+ Node *expr;
+
+ if (typid == UNKNOWNOID)
+ {
+ expr = (Node *) makeNullConst(UNKNOWNOID, -1, InvalidOid);
+ }
+ else
+ {
+ CaseTestExpr *placeholder = makeNode(CaseTestExpr);
+
+ placeholder->typeId = typid;
+ placeholder->typeMod = -1;
+ placeholder->collation = InvalidOid;
+
+ expr = (Node *) placeholder;
+ }
+
+ return coerceJsonExpr(pstate, expr, returning);
+}
+
+static void
+initJsonItemCoercions(ParseState *pstate, JsonItemCoercions *coercions,
+ const JsonReturning *returning, Oid contextItemTypeId)
+{
+ struct
+ {
+ JsonCoercion **coercion;
+ Oid typid;
+ } *p,
+ coercionTypids[] =
+ {
+ { &coercions->null, UNKNOWNOID },
+ { &coercions->string, TEXTOID },
+ { &coercions->numeric, NUMERICOID },
+ { &coercions->boolean, BOOLOID },
+ { &coercions->date, DATEOID },
+ { &coercions->time, TIMEOID },
+ { &coercions->timetz, TIMETZOID },
+ { &coercions->timestamp, TIMESTAMPOID },
+ { &coercions->timestamptz, TIMESTAMPTZOID },
+ { &coercions->composite, contextItemTypeId },
+ { NULL, InvalidOid }
+ };
+
+ for (p = coercionTypids; p->coercion; p++)
+ *p->coercion = initJsonItemCoercion(pstate, p->typid, returning);
+}
+
+/*
+ * Transform JSON_VALUE, JSON_QUERY, JSON_EXISTS functions into a JsonExpr node.
+ */
+static Node *
+transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
+{
+ JsonExpr *jsexpr = transformJsonExprCommon(pstate, func);
+ const char *func_name = NULL;
+ Node *contextItemExpr = jsexpr->formatted_expr;
+
+ switch (func->op)
+ {
+ case IS_JSON_VALUE:
+ func_name = "JSON_VALUE";
+
+ transformJsonFuncExprOutput(pstate, func, jsexpr);
+
+ jsexpr->returning->format->format = JS_FORMAT_DEFAULT;
+ jsexpr->returning->format->encoding = JS_ENC_DEFAULT;
+
+ jsexpr->on_empty->default_expr =
+ coerceDefaultJsonExpr(pstate, jsexpr,
+ jsexpr->on_empty->default_expr);
+
+ jsexpr->on_error->default_expr =
+ coerceDefaultJsonExpr(pstate, jsexpr,
+ jsexpr->on_error->default_expr);
+
+ jsexpr->coercions = makeNode(JsonItemCoercions);
+ initJsonItemCoercions(pstate, jsexpr->coercions, jsexpr->returning,
+ exprType(contextItemExpr));
+
+ break;
+
+ case IS_JSON_QUERY:
+ func_name = "JSON_QUERY";
+
+ transformJsonFuncExprOutput(pstate, func, jsexpr);
+
+ jsexpr->on_empty->default_expr =
+ coerceDefaultJsonExpr(pstate, jsexpr,
+ jsexpr->on_empty->default_expr);
+
+ jsexpr->on_error->default_expr =
+ coerceDefaultJsonExpr(pstate, jsexpr,
+ jsexpr->on_error->default_expr);
+
+ jsexpr->wrapper = func->wrapper;
+ jsexpr->omit_quotes = func->omit_quotes;
+
+ break;
+
+ case IS_JSON_EXISTS:
+ func_name = "JSON_EXISTS";
+
+ jsexpr->returning = transformJsonOutput(pstate, func->output, false);
+
+ jsexpr->returning->format->format = JS_FORMAT_DEFAULT;
+ jsexpr->returning->format->encoding = JS_ENC_DEFAULT;
+
+ if (!OidIsValid(jsexpr->returning->typid))
+ {
+ jsexpr->returning->typid = BOOLOID;
+ jsexpr->returning->typmod = -1;
+ }
+ else if (jsexpr->returning->typid != BOOLOID)
+ {
+ CaseTestExpr *placeholder = makeNode(CaseTestExpr);
+ int location = exprLocation((Node *) jsexpr);
+
+ placeholder->typeId = BOOLOID;
+ placeholder->typeMod = -1;
+ placeholder->collation = InvalidOid;
+
+ jsexpr->result_coercion = makeNode(JsonCoercion);
+ jsexpr->result_coercion->expr =
+ coerce_to_target_type(pstate, (Node *) placeholder, BOOLOID,
+ jsexpr->returning->typid,
+ jsexpr->returning->typmod,
+ COERCION_EXPLICIT,
+ COERCE_IMPLICIT_CAST,
+ location);
+
+ if (!jsexpr->result_coercion->expr)
+ ereport(ERROR,
+ (errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast type %s to %s",
+ format_type_be(BOOLOID),
+ format_type_be(jsexpr->returning->typid)),
+ parser_coercion_errposition(pstate, location, (Node *) jsexpr)));
+
+ if (jsexpr->result_coercion->expr == (Node *) placeholder)
+ jsexpr->result_coercion->expr = NULL;
+ }
+ break;
+ }
+
+ if (exprType(contextItemExpr) != JSONBOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s() is not yet implemented for json type", func_name),
+ parser_errposition(pstate, func->location)));
+
+ return (Node *) jsexpr;
+}
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index b191acb124..a43695574b 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1944,6 +1944,21 @@ FigureColnameInternal(Node *node, char **name)
case T_JsonArrayAgg:
*name = "json_arrayagg";
return 2;
+ case T_JsonFuncExpr:
+ /* make SQL/JSON functions act like a regular function */
+ switch (((JsonFuncExpr *) node)->op)
+ {
+ case IS_JSON_QUERY:
+ *name = "json_query";
+ return 2;
+ case IS_JSON_VALUE:
+ *name = "json_value";
+ return 2;
+ case IS_JSON_EXISTS:
+ *name = "json_exists";
+ return 2;
+ }
+ break;
default:
break;
}
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 2d70812629..6fdcc28f33 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -2331,3 +2331,65 @@ jsonb_float8(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(retValue);
}
+
+/*
+ * Construct an empty array jsonb.
+ */
+Jsonb *
+JsonbMakeEmptyArray(void)
+{
+ JsonbValue jbv;
+
+ jbv.type = jbvArray;
+ jbv.val.array.elems = NULL;
+ jbv.val.array.nElems = 0;
+ jbv.val.array.rawScalar = false;
+
+ return JsonbValueToJsonb(&jbv);
+}
+
+/*
+ * Construct an empty object jsonb.
+ */
+Jsonb *
+JsonbMakeEmptyObject(void)
+{
+ JsonbValue jbv;
+
+ jbv.type = jbvObject;
+ jbv.val.object.pairs = NULL;
+ jbv.val.object.nPairs = 0;
+
+ return JsonbValueToJsonb(&jbv);
+}
+
+/*
+ * Convert jsonb to a C-string stripping quotes from scalar strings.
+ */
+char *
+JsonbUnquote(Jsonb *jb)
+{
+ if (JB_ROOT_IS_SCALAR(jb))
+ {
+ JsonbValue v;
+
+ 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 3fcb19ec94..a268501046 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -2628,11 +2628,11 @@ populate_array_dim_jsonb(PopulateArrayContext *ctx, /* context */
check_stack_depth();
- if (jbv->type != jbvBinary || !JsonContainerIsArray(jbc))
+ if (jbv->type != jbvBinary ||
+ !JsonContainerIsArray(jbc) ||
+ JsonContainerIsScalar(jbc))
populate_array_report_expected_array(ctx, ndim - 1);
- Assert(!JsonContainerIsScalar(jbc));
-
it = JsonbIteratorInit(jbc);
tok = JsonbIteratorNext(&it, &val, true);
@@ -3104,6 +3104,50 @@ populate_record_field(ColumnIOData *col,
}
}
+/* recursively populate specified type from a json/jsonb value */
+Datum
+json_populate_type(Datum json_val, Oid json_type, Oid typid, int32 typmod,
+ void **cache, MemoryContext mcxt, bool *isnull)
+{
+ 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)
+ *cache = MemoryContextAllocZero(mcxt, sizeof(ColumnIOData));
+
+ return populate_record_field(*cache , typid, typmod, NULL, mcxt,
+ PointerGetDatum(NULL), &jsv, isnull);
+}
+
static RecordIOData *
allocate_record_info(MemoryContext mcxt, int ncolumns)
{
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index f146767bfc..2aa57ba373 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -86,12 +86,16 @@ typedef struct JsonBaseObjectInfo
int id;
} JsonBaseObjectInfo;
+typedef int (*JsonPathVarCallback) (void *vars, char *varName, int varNameLen,
+ JsonbValue *val, JsonbValue *baseObject);
+
/*
* Context of jsonpath execution.
*/
typedef struct JsonPathExecContext
{
- Jsonb *vars; /* variables to substitute into jsonpath */
+ void *vars; /* variables to substitute into jsonpath */
+ JsonPathVarCallback getVar;
JsonbValue *root; /* for $ evaluation */
JsonbValue *current; /* for @ evaluation */
JsonBaseObjectInfo baseObject; /* "base object" for .keyvalue()
@@ -173,7 +177,8 @@ typedef JsonPathBool (*JsonPathPredicateCallback) (JsonPathItem *jsp,
void *param);
typedef Numeric (*BinaryArithmFunc) (Numeric num1, Numeric num2, bool *error);
-static JsonPathExecResult executeJsonPath(JsonPath *path, Jsonb *vars,
+static JsonPathExecResult executeJsonPath(JsonPath *path, void *vars,
+ JsonPathVarCallback getVar,
Jsonb *json, bool throwErrors,
JsonValueList *result, bool useTz);
static JsonPathExecResult executeItem(JsonPathExecContext *cxt,
@@ -225,7 +230,10 @@ static JsonPathExecResult appendBoolResult(JsonPathExecContext *cxt,
static void getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item,
JsonbValue *value);
static void getJsonPathVariable(JsonPathExecContext *cxt,
- JsonPathItem *variable, Jsonb *vars, JsonbValue *value);
+ JsonPathItem *variable, JsonbValue *value);
+static int getJsonPathVariableFromJsonb(void *varsJsonb, char *varName,
+ int varNameLen, JsonbValue *val,
+ JsonbValue *baseObject);
static int JsonbArraySize(JsonbValue *jb);
static JsonPathBool executeComparison(JsonPathItem *cmp, JsonbValue *lv,
JsonbValue *rv, void *p);
@@ -283,7 +291,8 @@ jsonb_path_exists_internal(FunctionCallInfo fcinfo, bool tz)
silent = PG_GETARG_BOOL(3);
}
- res = executeJsonPath(jp, vars, jb, !silent, NULL, tz);
+ res = executeJsonPath(jp, vars, getJsonPathVariableFromJsonb,
+ jb, !silent, NULL, tz);
PG_FREE_IF_COPY(jb, 0);
PG_FREE_IF_COPY(jp, 1);
@@ -338,7 +347,8 @@ jsonb_path_match_internal(FunctionCallInfo fcinfo, bool tz)
silent = PG_GETARG_BOOL(3);
}
- (void) executeJsonPath(jp, vars, jb, !silent, &found, tz);
+ (void) executeJsonPath(jp, vars, getJsonPathVariableFromJsonb,
+ jb, !silent, &found, tz);
PG_FREE_IF_COPY(jb, 0);
PG_FREE_IF_COPY(jp, 1);
@@ -416,7 +426,8 @@ jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz)
vars = PG_GETARG_JSONB_P_COPY(2);
silent = PG_GETARG_BOOL(3);
- (void) executeJsonPath(jp, vars, jb, !silent, &found, tz);
+ (void) executeJsonPath(jp, vars, getJsonPathVariableFromJsonb,
+ jb, !silent, &found, tz);
funcctx->user_fctx = JsonValueListGetList(&found);
@@ -463,7 +474,8 @@ jsonb_path_query_array_internal(FunctionCallInfo fcinfo, bool tz)
Jsonb *vars = PG_GETARG_JSONB_P(2);
bool silent = PG_GETARG_BOOL(3);
- (void) executeJsonPath(jp, vars, jb, !silent, &found, tz);
+ (void) executeJsonPath(jp, vars, getJsonPathVariableFromJsonb,
+ jb, !silent, &found, tz);
PG_RETURN_JSONB_P(JsonbValueToJsonb(wrapItemsInArray(&found)));
}
@@ -494,7 +506,8 @@ jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
Jsonb *vars = PG_GETARG_JSONB_P(2);
bool silent = PG_GETARG_BOOL(3);
- (void) executeJsonPath(jp, vars, jb, !silent, &found, tz);
+ (void) executeJsonPath(jp, vars, getJsonPathVariableFromJsonb,
+ jb, !silent, &found, tz);
if (JsonValueListLength(&found) >= 1)
PG_RETURN_JSONB_P(JsonbValueToJsonb(JsonValueListHead(&found)));
@@ -536,8 +549,9 @@ jsonb_path_query_first_tz(PG_FUNCTION_ARGS)
* In other case it tries to find all the satisfied result items.
*/
static JsonPathExecResult
-executeJsonPath(JsonPath *path, Jsonb *vars, Jsonb *json, bool throwErrors,
- JsonValueList *result, bool useTz)
+executeJsonPath(JsonPath *path, void *vars, JsonPathVarCallback getVar,
+ Jsonb *json, bool throwErrors, JsonValueList *result,
+ bool useTz)
{
JsonPathExecContext cxt;
JsonPathExecResult res;
@@ -549,22 +563,16 @@ executeJsonPath(JsonPath *path, Jsonb *vars, Jsonb *json, bool throwErrors,
if (!JsonbExtractScalar(&json->root, &jbv))
JsonbInitBinary(&jbv, json);
- if (vars && !JsonContainerIsObject(&vars->root))
- {
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("\"vars\" argument is not an object"),
- errdetail("Jsonpath parameters should be encoded as key-value pairs of \"vars\" object.")));
- }
-
cxt.vars = vars;
+ cxt.getVar = getVar;
cxt.laxMode = (path->header & JSONPATH_LAX) != 0;
cxt.ignoreStructuralErrors = cxt.laxMode;
cxt.root = &jbv;
cxt.current = &jbv;
cxt.baseObject.jbc = NULL;
cxt.baseObject.id = 0;
- cxt.lastGeneratedObjectId = vars ? 2 : 1;
+ /* 1 + number of base objects in vars */
+ cxt.lastGeneratedObjectId = 1 + getVar(vars, NULL, 0, NULL, NULL);
cxt.innermostArraySize = -1;
cxt.throwErrors = throwErrors;
cxt.useTz = useTz;
@@ -2092,7 +2100,7 @@ getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item,
&value->val.string.len);
break;
case jpiVariable:
- getJsonPathVariable(cxt, item, cxt->vars, value);
+ getJsonPathVariable(cxt, item, value);
return;
default:
elog(ERROR, "unexpected jsonpath item type");
@@ -2104,42 +2112,63 @@ getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item,
*/
static void
getJsonPathVariable(JsonPathExecContext *cxt, JsonPathItem *variable,
- Jsonb *vars, JsonbValue *value)
+ JsonbValue *value)
{
char *varName;
int varNameLength;
+ JsonbValue baseObject;
+ int baseObjectId;
+
+ Assert(variable->type == jpiVariable);
+ varName = jspGetString(variable, &varNameLength);
+
+ if (!cxt->vars ||
+ (baseObjectId = cxt->getVar(cxt->vars, varName, varNameLength, value,
+ &baseObject)) < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("could not find jsonpath variable \"%s\"",
+ pnstrdup(varName, varNameLength))));
+
+ if (baseObjectId > 0)
+ setBaseObject(cxt, &baseObject, baseObjectId);
+}
+
+static int
+getJsonPathVariableFromJsonb(void *varsJsonb, char *varName, int varNameLength,
+ JsonbValue *value, JsonbValue *baseObject)
+{
+ Jsonb *vars = varsJsonb;
JsonbValue tmp;
JsonbValue *v;
- if (!vars)
+ if (!varName)
{
- value->type = jbvNull;
- return;
+ if (vars && !JsonContainerIsObject(&vars->root))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("\"vars\" argument is not an object"),
+ errdetail("Jsonpath parameters should be encoded as key-value pairs of \"vars\" object.")));
+ }
+
+ return vars ? 1 : 0; /* count of base objects */
}
- Assert(variable->type == jpiVariable);
- varName = jspGetString(variable, &varNameLength);
tmp.type = jbvString;
tmp.val.string.val = varName;
tmp.val.string.len = varNameLength;
v = findJsonbValueFromContainer(&vars->root, JB_FOBJECT, &tmp);
- if (v)
- {
- *value = *v;
- pfree(v);
- }
- else
- {
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_OBJECT),
- errmsg("could not find jsonpath variable \"%s\"",
- pnstrdup(varName, varNameLength))));
- }
+ if (!v)
+ return -1;
- JsonbInitBinary(&tmp, vars);
- setBaseObject(cxt, &tmp, 1);
+ *value = *v;
+ pfree(v);
+
+ JsonbInitBinary(baseObject, vars);
+ return 1;
}
/**************** Support functions for JsonPath execution *****************/
@@ -2853,3 +2882,244 @@ compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
return DatumGetInt32(DirectFunctionCall2(cmpfunc, val1, val2));
}
+
+/********************Interface to pgsql's executor***************************/
+
+bool
+JsonPathExists(Datum jb, JsonPath *jp, List *vars, bool *error)
+{
+ JsonPathExecResult res = executeJsonPath(jp, vars, EvalJsonPathVar,
+ DatumGetJsonbP(jb), !error, NULL,
+ false /* XXX */);
+
+ Assert(error || !jperIsError(res));
+
+ if (error && jperIsError(res))
+ *error = true;
+
+ return res == jperOk;
+}
+
+Datum
+JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
+ bool *error, List *vars)
+{
+ JsonbValue *first;
+ bool wrap;
+ JsonValueList found = {0};
+ JsonPathExecResult res PG_USED_FOR_ASSERTS_ONLY;
+ int count;
+
+ res = executeJsonPath(jp, vars, EvalJsonPathVar, DatumGetJsonbP(jb), !error,
+ &found, false /* XXX */);
+
+ Assert(error || !jperIsError(res));
+
+ if (error && jperIsError(res))
+ {
+ *error = true;
+ *empty = false;
+ return (Datum) 0;
+ }
+
+ count = JsonValueListLength(&found);
+
+ first = count ? JsonValueListHead(&found) : NULL;
+
+ if (!first)
+ wrap = false;
+ else if (wrapper == JSW_NONE)
+ wrap = false;
+ else if (wrapper == JSW_UNCONDITIONAL)
+ wrap = true;
+ else if (wrapper == JSW_CONDITIONAL)
+ wrap = count > 1 ||
+ IsAJsonbScalar(first) ||
+ (first->type == jbvBinary &&
+ JsonContainerIsScalar(first->val.binary.data));
+ else
+ {
+ elog(ERROR, "unrecognized json wrapper %d", wrapper);
+ wrap = false;
+ }
+
+ if (wrap)
+ return JsonbPGetDatum(JsonbValueToJsonb(wrapItemsInArray(&found)));
+
+ if (count > 1)
+ {
+ if (error)
+ {
+ *error = true;
+ return (Datum) 0;
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+ errmsg("JSON path expression in JSON_QUERY should return "
+ "singleton item without wrapper"),
+ errhint("use WITH WRAPPER clause to wrap SQL/JSON item "
+ "sequence into array")));
+ }
+
+ if (first)
+ return JsonbPGetDatum(JsonbValueToJsonb(first));
+
+ *empty = true;
+ return PointerGetDatum(NULL);
+}
+
+JsonbValue *
+JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
+{
+ JsonbValue *res;
+ JsonValueList found = { 0 };
+ JsonPathExecResult jper PG_USED_FOR_ASSERTS_ONLY;
+ int count;
+
+ jper = executeJsonPath(jp, vars, EvalJsonPathVar, DatumGetJsonbP(jb), !error,
+ &found, false /* XXX */);
+
+ Assert(error || !jperIsError(jper));
+
+ if (error && jperIsError(jper))
+ {
+ *error = true;
+ *empty = false;
+ return NULL;
+ }
+
+ count = JsonValueListLength(&found);
+
+ *empty = !count;
+
+ if (*empty)
+ return NULL;
+
+ if (count > 1)
+ {
+ if (error)
+ {
+ *error = true;
+ return NULL;
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+ errmsg("JSON path expression in JSON_VALUE should return "
+ "singleton scalar item")));
+ }
+
+ res = JsonValueListHead(&found);
+
+ if (res->type == jbvBinary &&
+ JsonContainerIsScalar(res->val.binary.data))
+ JsonbExtractScalar(res->val.binary.data, res);
+
+ if (!IsAJsonbScalar(res))
+ {
+ if (error)
+ {
+ *error = true;
+ return NULL;
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+ errmsg("JSON path expression in JSON_VALUE should return "
+ "singleton scalar item")));
+ }
+
+ if (res->type == jbvNull)
+ return NULL;
+
+ return res;
+}
+
+static void
+JsonbValueInitNumericDatum(JsonbValue *jbv, Datum num)
+{
+ jbv->type = jbvNumeric;
+ jbv->val.numeric = DatumGetNumeric(num);
+}
+
+void
+JsonItemFromDatum(Datum val, Oid typid, int32 typmod, JsonbValue *res)
+{
+ switch (typid)
+ {
+ case BOOLOID:
+ res->type = jbvBool;
+ res->val.boolean = DatumGetBool(val);
+ break;
+ case NUMERICOID:
+ JsonbValueInitNumericDatum(res, val);
+ break;
+ case INT2OID:
+ JsonbValueInitNumericDatum(res, DirectFunctionCall1(int2_numeric, val));
+ break;
+ case INT4OID:
+ JsonbValueInitNumericDatum(res, DirectFunctionCall1(int4_numeric, val));
+ break;
+ case INT8OID:
+ JsonbValueInitNumericDatum(res, DirectFunctionCall1(int8_numeric, val));
+ break;
+ case FLOAT4OID:
+ JsonbValueInitNumericDatum(res, DirectFunctionCall1(float4_numeric, val));
+ break;
+ case FLOAT8OID:
+ JsonbValueInitNumericDatum(res, DirectFunctionCall1(float8_numeric, val));
+ break;
+ case TEXTOID:
+ case VARCHAROID:
+ res->type = jbvString;
+ res->val.string.val = VARDATA_ANY(val);
+ res->val.string.len = VARSIZE_ANY_EXHDR(val);
+ break;
+ case DATEOID:
+ case TIMEOID:
+ case TIMETZOID:
+ case TIMESTAMPOID:
+ case TIMESTAMPTZOID:
+ res->type = jbvDatetime;
+ res->val.datetime.value = val;
+ res->val.datetime.typid = typid;
+ res->val.datetime.typmod = typmod;
+ res->val.datetime.tz = 0;
+ break;
+ case JSONBOID:
+ {
+ JsonbValue *jbv = res;
+ Jsonb *jb = DatumGetJsonbP(val);
+
+ if (JsonContainerIsScalar(&jb->root))
+ {
+ bool res PG_USED_FOR_ASSERTS_ONLY;
+
+ res = JsonbExtractScalar(&jb->root, jbv);
+ Assert(res);
+ }
+ else
+ JsonbInitBinary(jbv, jb);
+ break;
+ }
+ case JSONOID:
+ {
+ text *txt = DatumGetTextP(val);
+ char *str = text_to_cstring(txt);
+ Jsonb *jb =
+ DatumGetJsonbP(DirectFunctionCall1(jsonb_in,
+ CStringGetDatum(str)));
+
+ pfree(str);
+
+ JsonItemFromDatum(JsonbPGetDatum(jb), JSONBOID, -1, res);
+ break;
+ }
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("only bool, numeric and text types could be "
+ "casted to supported jsonpath types.")));
+ }
+}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index e05723ef31..89857e7305 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -485,6 +485,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 ")
@@ -7623,6 +7625,7 @@ isSimpleNode(Node *node, Node *parentNode, int prettyFlags)
case T_WindowFunc:
case T_FuncExpr:
case T_JsonCtorExpr:
+ case T_JsonExpr:
/* function-like: name(..) or name[..] */
return true;
@@ -7740,6 +7743,7 @@ isSimpleNode(Node *node, Node *parentNode, int prettyFlags)
case T_Aggref: /* own parentheses */
case T_WindowFunc: /* own parentheses */
case T_CaseExpr: /* other separators */
+ case T_JsonExpr: /* own parentheses */
return true;
default:
return false;
@@ -7905,6 +7909,19 @@ get_rule_expr_paren(Node *node, deparse_context *context,
appendStringInfoChar(context->buf, ')');
}
+
+/*
+ * 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
*/
@@ -7948,6 +7965,82 @@ get_json_returning(JsonReturning *returning, StringInfo buf,
get_json_format(returning->format, buf);
}
+static void
+get_json_behavior(JsonBehavior *behavior, deparse_context *context,
+ const char *on)
+{
+ switch (behavior->btype)
+ {
+ case JSON_BEHAVIOR_DEFAULT:
+ appendStringInfoString(context->buf, " DEFAULT ");
+ get_rule_expr(behavior->default_expr, context, false);
+ break;
+
+ case JSON_BEHAVIOR_EMPTY:
+ appendStringInfoString(context->buf, " EMPTY");
+ break;
+
+ case JSON_BEHAVIOR_EMPTY_ARRAY:
+ appendStringInfoString(context->buf, " EMPTY ARRAY");
+ break;
+
+ case JSON_BEHAVIOR_EMPTY_OBJECT:
+ appendStringInfoString(context->buf, " EMPTY OBJECT");
+ break;
+
+ case JSON_BEHAVIOR_ERROR:
+ appendStringInfoString(context->buf, " ERROR");
+ break;
+
+ case JSON_BEHAVIOR_FALSE:
+ appendStringInfoString(context->buf, " FALSE");
+ break;
+
+ case JSON_BEHAVIOR_NULL:
+ appendStringInfoString(context->buf, " NULL");
+ break;
+
+ case JSON_BEHAVIOR_TRUE:
+ appendStringInfoString(context->buf, " TRUE");
+ break;
+
+ case JSON_BEHAVIOR_UNKNOWN:
+ appendStringInfoString(context->buf, " UNKNOWN");
+ break;
+ }
+
+ 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 == IS_JSON_QUERY)
+ {
+ if (jsexpr->wrapper == JSW_CONDITIONAL)
+ appendStringInfo(context->buf, " WITH CONDITIONAL WRAPPER");
+
+ if (jsexpr->wrapper == JSW_UNCONDITIONAL)
+ appendStringInfo(context->buf, " WITH UNCONDITIONAL WRAPPER");
+
+ if (jsexpr->omit_quotes)
+ appendStringInfo(context->buf, " OMIT QUOTES");
+ }
+
+ if (jsexpr->op != IS_JSON_EXISTS &&
+ jsexpr->on_empty->btype != default_behavior)
+ get_json_behavior(jsexpr->on_empty, context, "EMPTY");
+
+ if (jsexpr->on_error->btype != default_behavior)
+ get_json_behavior(jsexpr->on_error, context, "ERROR");
+}
+
/* ----------
* get_rule_expr - Parse back an expression
*
@@ -9120,6 +9213,7 @@ get_rule_expr(Node *node, deparse_context *context,
}
break;
+
case T_JsonValueExpr:
{
JsonValueExpr *jve = (JsonValueExpr *) node;
@@ -9167,6 +9261,62 @@ get_rule_expr(Node *node, deparse_context *context,
}
break;
+ case T_JsonExpr:
+ {
+ JsonExpr *jexpr = (JsonExpr *) node;
+
+ switch (jexpr->op)
+ {
+ case IS_JSON_QUERY:
+ appendStringInfoString(buf, "JSON_QUERY(");
+ break;
+ case IS_JSON_VALUE:
+ appendStringInfoString(buf, "JSON_VALUE(");
+ break;
+ case IS_JSON_EXISTS:
+ appendStringInfoString(buf, "JSON_EXISTS(");
+ 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",
+ ((Value *) lfirst(lc1))->val.str);
+ }
+ }
+
+ if (jexpr->op != IS_JSON_EXISTS ||
+ jexpr->returning->typid != BOOLOID)
+ get_json_returning(jexpr->returning, context->buf,
+ jexpr->op == IS_JSON_QUERY);
+
+ get_json_expr_options(jexpr, context,
+ jexpr->op == IS_JSON_EXISTS ?
+ JSON_BEHAVIOR_FALSE : JSON_BEHAVIOR_NULL);
+
+ appendStringInfoString(buf, ")");
+ }
+ break;
+
case T_List:
{
char *sep;
@@ -9263,6 +9413,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:
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index d08e1e1413..62681d58c6 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -20,6 +20,7 @@
/* forward references to avoid circularity */
struct ExprEvalStep;
struct SubscriptingRefState;
+struct JsonbValue;
/* Bits in ExprState->flags (see also execnodes.h for public flag bits): */
/* expression's interpreter has been initialized */
@@ -221,6 +222,7 @@ typedef enum ExprEvalOp
EEOP_ALTERNATIVE_SUBPLAN,
EEOP_JSON_CTOR,
EEOP_IS_JSON,
+ EEOP_JSONEXPR,
/* aggregation related nodes */
EEOP_AGG_STRICT_DESERIALIZE,
@@ -659,6 +661,50 @@ typedef struct ExprEvalStep
JsonIsPredicate *pred; /* original expression node */
} is_json;
+ /* for EEOP_JSONEXPR */
+ struct
+ {
+ JsonExpr *jsexpr; /* original expression node */
+
+ struct
+ {
+ FmgrInfo func; /* typinput function for output type */
+ Oid typioparam;
+ } input; /* I/O info for output type */
+
+ NullableDatum
+ *formatted_expr, /* formatted context item value */
+ *res_expr, /* result item */
+ *coercion_expr, /* input for JSON item coercion */
+ *pathspec; /* path specification value */
+
+ ExprState *result_expr; /* coerced to output type */
+ ExprState *default_on_empty; /* ON EMPTY DEFAULT expression */
+ ExprState *default_on_error; /* ON ERROR DEFAULT expression */
+ List *args; /* passing arguments */
+
+ void *cache; /* cache for json_populate_type() */
+
+ struct JsonCoercionsState
+ {
+ struct JsonCoercionState
+ {
+ JsonCoercion *coercion; /* coercion expression */
+ ExprState *estate; /* coercion expression state */
+ } null,
+ string,
+ numeric,
+ boolean,
+ date,
+ time,
+ timetz,
+ timestamp,
+ timestamptz,
+ composite;
+ } coercions; /* states for coercion from SQL/JSON item
+ * types directly to the output type */
+ } jsonexpr;
+
} d;
} ExprEvalStep;
@@ -760,6 +806,14 @@ extern void ExecEvalWholeRowVar(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
extern void ExecEvalSysVar(ExprState *state, ExprEvalStep *op,
ExprContext *econtext, TupleTableSlot *slot);
+extern void ExecEvalJson(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext);
+extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item,
+ JsonReturning *returning,
+ struct JsonCoercionsState *coercions,
+ struct JsonCoercionState **pjcstate);
+extern bool ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr,
+ struct JsonCoercionsState *);
extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup,
ExprContext *aggcontext);
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index c7deeac662..19467d3282 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -251,6 +251,8 @@ ExecProcNode(PlanState *node)
*/
extern ExprState *ExecInitExpr(Expr *node, PlanState *parent);
extern ExprState *ExecInitExprWithParams(Expr *node, ParamListInfo ext_params);
+extern ExprState *ExecInitExprWithCaseValue(Expr *node, PlanState *parent,
+ Datum *caseval, bool *casenull);
extern ExprState *ExecInitQual(List *qual, PlanState *parent);
extern ExprState *ExecInitCheck(List *qual, PlanState *parent);
extern List *ExecInitExprList(List *nodes, PlanState *parent);
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 14a9d6640f..cb8ec2096b 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -108,6 +108,7 @@ extern VacuumRelation *makeVacuumRelation(RangeVar *relation, Oid oid, List *va_
extern JsonFormat *makeJsonFormat(JsonFormatType type, JsonEncoding encoding,
int location);
extern JsonValueExpr *makeJsonValueExpr(Expr *expr, JsonFormat *format);
+extern JsonBehavior *makeJsonBehavior(JsonBehaviorType type, Node *expr);
extern Node *makeJsonKeyValue(Node *key, Node *value);
extern Node *makeJsonIsPredicate(Node *expr, JsonFormat *format,
JsonValueType vtype, bool unique_keys,
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index e3a2a886d6..127cf3d887 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -202,6 +202,9 @@ typedef enum NodeTag
T_JsonReturning,
T_JsonValueExpr,
T_JsonCtorExpr,
+ T_JsonExpr,
+ T_JsonCoercion,
+ T_JsonItemCoercions,
/*
* TAGS FOR EXPRESSION STATE NODES (execnodes.h)
@@ -490,8 +493,13 @@ typedef enum NodeTag
T_JsonArrayQueryCtor,
T_JsonObjectAgg,
T_JsonArrayAgg,
+ T_JsonFuncExpr,
T_JsonIsPredicate,
+ T_JsonExistsPredicate,
+ T_JsonCommon,
+ T_JsonArgument,
T_JsonKeyValue,
+ T_JsonBehavior,
T_JsonOutput,
/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ead2763edf..96116fcf42 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1488,6 +1488,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])
@@ -1499,6 +1516,48 @@ 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;
+
+/*
+ * JsonCommon -
+ * representation of common syntax of functions using JSON path
+ */
+typedef struct JsonCommon
+{
+ NodeTag type;
+ JsonValueExpr *expr; /* context item expression */
+ Node *pathspec; /* JSON path specification expression */
+ char *pathname; /* path name, if any */
+ List *passing; /* list of PASSING clause arguments, if any */
+ int location; /* token location, or -1 if unknown */
+} JsonCommon;
+
+/*
+ * JsonFuncExpr -
+ * untransformed representation of JSON function expressions
+ */
+typedef struct JsonFuncExpr
+{
+ NodeTag type;
+ JsonExprOp op; /* expression type */
+ JsonCommon *common; /* common syntax */
+ JsonOutput *output; /* output clause, if specified */
+ JsonBehavior *on_empty; /* ON EMPTY behavior, if specified */
+ JsonBehavior *on_error; /* ON ERROR behavior, if specified */
+ JsonWrapper wrapper; /* array wrapper behavior (JSON_QUERY only) */
+ bool omit_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 891c593948..3b092ef443 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1189,6 +1189,17 @@ typedef struct XmlExpr
int location; /* token location, or -1 if unknown */
} XmlExpr;
+/*
+ * JsonExprOp -
+ * enumeration of JSON functions using JSON path
+ */
+typedef enum JsonExprOp
+{
+ IS_JSON_VALUE, /* JSON_VALUE() */
+ IS_JSON_QUERY, /* JSON_QUERY() */
+ IS_JSON_EXISTS /* JSON_EXISTS() */
+} JsonExprOp;
+
/*
* JsonEncoding -
* representation of JSON ENCODING clause
@@ -1212,6 +1223,34 @@ typedef enum JsonFormatType
JS_FORMAT_JSONB /* implicit internal format for RETURNING jsonb */
} JsonFormatType;
+/*
+ * JsonBehaviorType -
+ * enumeration of behavior types used in JSON ON ... BEHAVIOR clause
+ */
+typedef enum JsonBehaviorType
+{
+ JSON_BEHAVIOR_NULL,
+ 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;
+
+/*
+ * JsonWrapper -
+ * representation of WRAPPER clause for JSON_QUERY()
+ */
+typedef enum JsonWrapper
+{
+ JSW_NONE,
+ JSW_CONDITIONAL,
+ JSW_UNCONDITIONAL,
+} JsonWrapper;
+
/*
* JsonFormat -
* representation of JSON FORMAT clause
@@ -1299,6 +1338,73 @@ typedef struct JsonIsPredicate
int location; /* token location, or -1 if unknown */
} JsonIsPredicate;
+/*
+ * JsonBehavior -
+ * representation of JSON ON ... BEHAVIOR clause
+ */
+typedef struct JsonBehavior
+{
+ NodeTag type;
+ JsonBehaviorType btype; /* behavior type */
+ Node *default_expr; /* default expression, if any */
+} JsonBehavior;
+
+/*
+ * JsonCoercion -
+ * coercion from SQL/JSON item types to SQL types
+ */
+typedef struct JsonCoercion
+{
+ NodeTag type;
+ Node *expr; /* resulting expression coerced to target type */
+ bool via_populate; /* coerce result using json_populate_type()? */
+ bool via_io; /* coerce result using type input function? */
+ Oid collation; /* collation for coercion via I/O or populate */
+} JsonCoercion;
+
+/*
+ * JsonItemCoercions -
+ * expressions for coercion from SQL/JSON item types directly to the
+ * output SQL type
+ */
+typedef struct JsonItemCoercions
+{
+ NodeTag type;
+ JsonCoercion *null;
+ JsonCoercion *string;
+ JsonCoercion *numeric;
+ JsonCoercion *boolean;
+ JsonCoercion *date;
+ JsonCoercion *time;
+ JsonCoercion *timetz;
+ JsonCoercion *timestamp;
+ JsonCoercion *timestamptz;
+ JsonCoercion *composite; /* arrays and objects */
+} JsonItemCoercions;
+
+/*
+ * JsonExpr -
+ * transformed representation of JSON_VALUE(), JSON_QUERY(), JSON_EXISTS()
+ */
+typedef struct JsonExpr
+{
+ Expr xpr;
+ JsonExprOp op; /* json function ID */
+ Node *formatted_expr; /* formatted context item expression */
+ JsonCoercion *result_coercion; /* resulting coercion to RETURNING type */
+ JsonFormat *format; /* context item format (JSON/JSONB) */
+ Node *path_spec; /* JSON path specification expression */
+ List *passing_names; /* PASSING argument names */
+ List *passing_values; /* PASSING argument values */
+ JsonReturning *returning; /* RETURNING clause type/format info */
+ JsonBehavior *on_empty; /* ON EMPTY behavior */
+ JsonBehavior *on_error; /* ON ERROR behavior */
+ JsonItemCoercions *coercions; /* coercions for JSON_VALUE */
+ JsonWrapper wrapper; /* WRAPPER for JSON_QUERY */
+ bool omit_quotes; /* KEEP/OMIT QUOTES for JSON_QUERY */
+ int location; /* token location, or -1 if unknown */
+} JsonExpr;
+
/* ----------------
* NullTest
*
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f8b06d035f..c74d78398a 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -89,6 +89,7 @@ PG_KEYWORD("comments", COMMENTS, UNRESERVED_KEYWORD)
PG_KEYWORD("commit", COMMIT, UNRESERVED_KEYWORD)
PG_KEYWORD("committed", COMMITTED, UNRESERVED_KEYWORD)
PG_KEYWORD("concurrently", CONCURRENTLY, TYPE_FUNC_NAME_KEYWORD)
+PG_KEYWORD("conditional", CONDITIONAL, UNRESERVED_KEYWORD)
PG_KEYWORD("configuration", CONFIGURATION, UNRESERVED_KEYWORD)
PG_KEYWORD("conflict", CONFLICT, UNRESERVED_KEYWORD)
PG_KEYWORD("connection", CONNECTION, UNRESERVED_KEYWORD)
@@ -142,11 +143,13 @@ PG_KEYWORD("double", DOUBLE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("drop", DROP, UNRESERVED_KEYWORD)
PG_KEYWORD("each", EACH, UNRESERVED_KEYWORD)
PG_KEYWORD("else", ELSE, RESERVED_KEYWORD)
+PG_KEYWORD("empty", EMPTY_P, UNRESERVED_KEYWORD)
PG_KEYWORD("enable", ENABLE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("encoding", ENCODING, UNRESERVED_KEYWORD)
PG_KEYWORD("encrypted", ENCRYPTED, UNRESERVED_KEYWORD)
PG_KEYWORD("end", END_P, RESERVED_KEYWORD)
PG_KEYWORD("enum", ENUM_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("error", ERROR_P, UNRESERVED_KEYWORD)
PG_KEYWORD("escape", ESCAPE, UNRESERVED_KEYWORD)
PG_KEYWORD("event", EVENT, UNRESERVED_KEYWORD)
PG_KEYWORD("except", EXCEPT, RESERVED_KEYWORD)
@@ -226,8 +229,12 @@ PG_KEYWORD("join", JOIN, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("json", JSON, UNRESERVED_KEYWORD)
PG_KEYWORD("json_array", JSON_ARRAY, COL_NAME_KEYWORD)
PG_KEYWORD("json_arrayagg", JSON_ARRAYAGG, COL_NAME_KEYWORD)
+PG_KEYWORD("json_exists", JSON_EXISTS, COL_NAME_KEYWORD)
PG_KEYWORD("json_object", JSON_OBJECT, COL_NAME_KEYWORD)
PG_KEYWORD("json_objectagg", JSON_OBJECTAGG, COL_NAME_KEYWORD)
+PG_KEYWORD("json_query", JSON_QUERY, COL_NAME_KEYWORD)
+PG_KEYWORD("json_value", JSON_VALUE, COL_NAME_KEYWORD)
+PG_KEYWORD("keep", KEEP, UNRESERVED_KEYWORD)
PG_KEYWORD("key", KEY, UNRESERVED_KEYWORD)
PG_KEYWORD("keys", KEYS, UNRESERVED_KEYWORD)
PG_KEYWORD("label", LABEL, UNRESERVED_KEYWORD)
@@ -291,6 +298,7 @@ PG_KEYWORD("off", OFF, UNRESERVED_KEYWORD)
PG_KEYWORD("offset", OFFSET, RESERVED_KEYWORD)
PG_KEYWORD("oids", OIDS, UNRESERVED_KEYWORD)
PG_KEYWORD("old", OLD, UNRESERVED_KEYWORD)
+PG_KEYWORD("omit", OMIT, UNRESERVED_KEYWORD)
PG_KEYWORD("on", ON, RESERVED_KEYWORD)
PG_KEYWORD("only", ONLY, RESERVED_KEYWORD)
PG_KEYWORD("operator", OPERATOR, UNRESERVED_KEYWORD)
@@ -332,6 +340,7 @@ PG_KEYWORD("procedures", PROCEDURES, UNRESERVED_KEYWORD)
PG_KEYWORD("program", PROGRAM, UNRESERVED_KEYWORD)
PG_KEYWORD("publication", PUBLICATION, UNRESERVED_KEYWORD)
PG_KEYWORD("quote", QUOTE, UNRESERVED_KEYWORD)
+PG_KEYWORD("quotes", QUOTES, UNRESERVED_KEYWORD)
PG_KEYWORD("range", RANGE, UNRESERVED_KEYWORD)
PG_KEYWORD("read", READ, UNRESERVED_KEYWORD)
PG_KEYWORD("real", REAL, COL_NAME_KEYWORD)
@@ -401,6 +410,7 @@ PG_KEYWORD("stdout", STDOUT, UNRESERVED_KEYWORD)
PG_KEYWORD("storage", STORAGE, UNRESERVED_KEYWORD)
PG_KEYWORD("stored", STORED, UNRESERVED_KEYWORD)
PG_KEYWORD("strict", STRICT_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("string", STRING, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("strip", STRIP_P, UNRESERVED_KEYWORD)
PG_KEYWORD("subscription", SUBSCRIPTION, UNRESERVED_KEYWORD)
PG_KEYWORD("substring", SUBSTRING, COL_NAME_KEYWORD)
@@ -435,6 +445,7 @@ PG_KEYWORD("types", TYPES_P, UNRESERVED_KEYWORD)
PG_KEYWORD("uescape", UESCAPE, UNRESERVED_KEYWORD)
PG_KEYWORD("unbounded", UNBOUNDED, UNRESERVED_KEYWORD)
PG_KEYWORD("uncommitted", UNCOMMITTED, UNRESERVED_KEYWORD)
+PG_KEYWORD("unconditional", UNCONDITIONAL, UNRESERVED_KEYWORD)
PG_KEYWORD("unencrypted", UNENCRYPTED, UNRESERVED_KEYWORD)
PG_KEYWORD("union", UNION, RESERVED_KEYWORD)
PG_KEYWORD("unique", UNIQUE, RESERVED_KEYWORD)
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index 9053ad78f6..4404ec7984 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -404,6 +404,9 @@ extern char *JsonbToCString(StringInfo out, JsonbContainer *in,
int estimated_len);
extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in,
int estimated_len);
+extern Jsonb *JsonbMakeEmptyArray(void);
+extern Jsonb *JsonbMakeEmptyObject(void);
+extern char *JsonbUnquote(Jsonb *jb);
extern bool JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res);
extern const char *JsonbTypeName(JsonbValue *jb);
diff --git a/src/include/utils/jsonfuncs.h b/src/include/utils/jsonfuncs.h
index 038a1aa138..6872974b96 100644
--- a/src/include/utils/jsonfuncs.h
+++ b/src/include/utils/jsonfuncs.h
@@ -58,4 +58,8 @@ extern Jsonb *transform_jsonb_string_values(Jsonb *jsonb, void *action_state,
extern text *transform_json_string_values(text *json, void *action_state,
JsonTransformStringValuesAction transform_action);
+extern Datum json_populate_type(Datum json_val, Oid json_type,
+ Oid typid, int32 typmod,
+ void **cache, MemoryContext mcxt, bool *isnull);
+
#endif
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 4ef0880d90..eb87aa096a 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -16,7 +16,9 @@
#include "fmgr.h"
#include "nodes/pg_list.h"
+#include "nodes/primnodes.h"
#include "utils/jsonb.h"
+#include "utils/jsonfuncs.h"
typedef struct
{
@@ -248,4 +250,34 @@ extern JsonPathParseResult *parsejsonpath(const char *str, int len);
extern int jspConvertRegexFlags(uint32 xflags);
+/*
+ * Evaluation of jsonpath
+ */
+
+/* External variable passed into jsonpath. */
+typedef struct JsonPathVariableEvalContext
+{
+ char *name;
+ Oid typid;
+ int32 typmod;
+ struct ExprContext *econtext;
+ struct ExprState *estate;
+ Datum value;
+ bool isnull;
+ bool evaluated;
+} JsonPathVariableEvalContext;
+
+/* SQL/JSON item */
+extern void JsonItemFromDatum(Datum val, Oid typid, int32 typmod,
+ JsonbValue *res);
+
+extern bool JsonPathExists(Datum jb, JsonPath *path, List *vars, bool *error);
+extern Datum JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper,
+ bool *empty, bool *error, List *vars);
+extern JsonbValue *JsonPathValue(Datum jb, JsonPath *jp, bool *empty,
+ bool *error, List *vars);
+
+extern int EvalJsonPathVar(void *vars, char *varName, int varNameLen,
+ JsonbValue *val, JsonbValue *baseObject);
+
#endif
diff --git a/src/test/regress/expected/json_sqljson.out b/src/test/regress/expected/json_sqljson.out
new file mode 100644
index 0000000000..bb62634314
--- /dev/null
+++ b/src/test/regress/expected/json_sqljson.out
@@ -0,0 +1,15 @@
+-- JSON_EXISTS
+SELECT JSON_EXISTS(NULL FORMAT JSON, '$');
+ERROR: JSON_EXISTS() is not yet implemented for json type
+LINE 1: SELECT JSON_EXISTS(NULL FORMAT JSON, '$');
+ ^
+-- JSON_VALUE
+SELECT JSON_VALUE(NULL FORMAT JSON, '$');
+ERROR: JSON_VALUE() is not yet implemented for json type
+LINE 1: SELECT JSON_VALUE(NULL FORMAT JSON, '$');
+ ^
+-- JSON_QUERY
+SELECT JSON_QUERY(NULL FORMAT JSON, '$');
+ERROR: JSON_QUERY() is not yet implemented for json type
+LINE 1: SELECT JSON_QUERY(NULL FORMAT JSON, '$');
+ ^
diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out
new file mode 100644
index 0000000000..06a83cc33f
--- /dev/null
+++ b/src/test/regress/expected/jsonb_sqljson.out
@@ -0,0 +1,1061 @@
+-- JSON_EXISTS
+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');
+ 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');
+ 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)
+
+-- extension: RETURNING clause
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING bool);
+ json_exists
+-------------
+ t
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING bool);
+ json_exists
+-------------
+ f
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING int);
+ json_exists
+-------------
+ 1
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING int);
+ json_exists
+-------------
+ 0
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING text);
+ json_exists
+-------------
+ true
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING text);
+ json_exists
+-------------
+ false
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR);
+ json_exists
+-------------
+ false
+(1 row)
+
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
+ERROR: cannot cast type boolean to jsonb
+LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
+ ^
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
+ERROR: cannot cast type boolean to real
+LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
+ ^
+-- JSON_VALUE
+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 '1', '$.a' RETURNING sqljsonb_int_not_null);
+ERROR: domain sqljsonb_int_not_null does not allow null values
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL 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 NULL ON ERROR);
+ERROR: domain sqljsonb_int_not_null does not allow null values
+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);
+ json_value
+------------
+
+(1 row)
+
+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
+------------
+
+(1 row)
+
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON ERROR);
+ json_value
+------------
+
+(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);
+ json_value
+------------
+ 3
+(1 row)
+
+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
+ 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)
+
+-- Test timestamptz passing and output
+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 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
+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 shall 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 shall 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 shall not be specified when WITH WRAPPER is used
+LINE 1: ...N_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTE...
+ ^
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTES);
+ERROR: SQL/JSON QUOTES behavior shall not be specified when WITH WRAPPER is used
+LINE 1: ...N_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTE...
+ ^
+-- 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)
+
+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);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
+ json_query
+------------
+ "empty"
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
+ json_query
+------------
+ []
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
+ json_query
+------------
+ {}
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
+ERROR: no SQL/JSON item
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
+ json_query
+------------
+
+(1 row)
+
+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);
+ json_query
+------------
+ \x7b7d
+(1 row)
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJECT ON ERROR);
+ json_query
+------------
+ \x7b7d
+(1 row)
+
+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
+ 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)
+
+-- 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 * 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)
+
+-- 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 * 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);
+ERROR: domain sqljsonb_int_not_null does not allow null values
+-- 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' || i)::int 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")
+ CONSTRAINT test_jsonb_constraint6
+ CHECK (JSON_EXISTS(js::jsonb, 'strict $.a' RETURNING int TRUE ON ERROR) < 2)
+);
+\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'::text || i)::integer 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"))
+ "test_jsonb_constraint6" CHECK (JSON_EXISTS(js::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2)
+
+SELECT check_clause
+FROM information_schema.check_constraints
+WHERE constraint_name LIKE 'test_jsonb_constraint%';
+ check_clause
+--------------------------------------------------------------------------------------------------------------------------
+ ((js IS JSON))
+ (JSON_EXISTS((js)::jsonb, '$."a"' PASSING (i + 5) AS int, (i)::text AS txt, ARRAY[1, 2, 3] AS arr))
+ ((JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT (('12'::text || i))::integer ON EMPTY ERROR ON ERROR) > i))
+ ((JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb))
+ ((JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C")))
+ ((JSON_EXISTS((js)::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2))
+(6 rows)
+
+SELECT pg_get_expr(adbin, adrelid) FROM pg_attrdef WHERE adrelid = 'test_jsonb_constraints'::regclass;
+ 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;
+-- 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, unexpected IDENT_P at or near " " of jsonpath input
+-- Test parallel JSON_VALUE()
+CREATE TABLE test_parallel_jsonb_value AS
+SELECT i::text::jsonb AS js
+FROM generate_series(1, 1000000) i;
+-- Should be non-parallel due to subtransactions
+EXPLAIN (COSTS OFF)
+SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
+ QUERY PLAN
+---------------------------------------------
+ Aggregate
+ -> Seq Scan on test_parallel_jsonb_value
+(2 rows)
+
+SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
+ sum
+--------------
+ 500000500000
+(1 row)
+
+-- Should be parallel
+EXPLAIN (COSTS OFF)
+SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value;
+ QUERY PLAN
+------------------------------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 2
+ -> Partial Aggregate
+ -> Parallel Seq Scan on test_parallel_jsonb_value
+(5 rows)
+
+SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value;
+ sum
+--------------
+ 500000500000
+(1 row)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index e3e9da0c8c..f53db9cd7d 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -100,7 +100,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 json_sqljson jsonb_sqljson
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index d25bf870bd..f2b632b785 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -172,6 +172,8 @@ test: jsonpath
test: jsonpath_encoding
test: jsonb_jsonpath
test: sqljson
+test: json_sqljson
+test: jsonb_sqljson
test: plancache
test: limit
test: plpgsql
diff --git a/src/test/regress/sql/json_sqljson.sql b/src/test/regress/sql/json_sqljson.sql
new file mode 100644
index 0000000000..4f30fa46b9
--- /dev/null
+++ b/src/test/regress/sql/json_sqljson.sql
@@ -0,0 +1,11 @@
+-- JSON_EXISTS
+
+SELECT JSON_EXISTS(NULL FORMAT JSON, '$');
+
+-- JSON_VALUE
+
+SELECT JSON_VALUE(NULL FORMAT JSON, '$');
+
+-- JSON_QUERY
+
+SELECT JSON_QUERY(NULL FORMAT JSON, '$');
diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql
new file mode 100644
index 0000000000..ac7bbd83e6
--- /dev/null
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -0,0 +1,320 @@
+-- JSON_EXISTS
+
+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');
+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');
+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);
+
+-- extension: RETURNING clause
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING bool);
+SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING bool);
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING int);
+SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING int);
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING text);
+SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING text);
+SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR);
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
+
+
+-- JSON_VALUE
+
+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 '1', '$.a' RETURNING sqljsonb_int_not_null);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL 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
+ 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);
+
+-- Test timestamptz passing and output
+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 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
+
+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);
+
+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
+ 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;
+
+-- 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 * 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);
+
+-- Extension: array types returning
+SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' 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);
+
+-- 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' || i)::int 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")
+ CONSTRAINT test_jsonb_constraint6
+ CHECK (JSON_EXISTS(js::jsonb, 'strict $.a' RETURNING int TRUE ON ERROR) < 2)
+);
+
+\d test_jsonb_constraints
+
+SELECT check_clause
+FROM information_schema.check_constraints
+WHERE constraint_name LIKE 'test_jsonb_constraint%';
+
+SELECT pg_get_expr(adbin, adrelid) FROM pg_attrdef WHERE adrelid = 'test_jsonb_constraints'::regclass;
+
+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;
+
+-- 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');
+
+-- Test parallel JSON_VALUE()
+CREATE TABLE test_parallel_jsonb_value AS
+SELECT i::text::jsonb AS js
+FROM generate_series(1, 1000000) i;
+
+-- Should be non-parallel due to subtransactions
+EXPLAIN (COSTS OFF)
+SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
+SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
+
+-- Should be parallel
+EXPLAIN (COSTS OFF)
+SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value;
+SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value;
+
--
2.25.4
--------------D62846C9ABCC2CD81535460E
Content-Type: text/x-patch; charset=UTF-8;
name="0003-IS-JSON-predicate-v48.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-IS-JSON-predicate-v48.patch"
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH 4/4] SQLJSON query functions v48
In-Reply-To: <no-message-id-1879567@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox