public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v41 1/2] Subscripting for jsonb 9+ messages / 5 participants [nested] [flat]
* [PATCH v41 1/2] Subscripting for jsonb @ 2020-12-18 16:19 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2020-12-18 16:19 UTC (permalink / raw) Subscripting implementation for jsonb. It does not support slices, does not have a limit for number of subscripts and for assignment expects a replace value to be of jsonb type. There is also one functional difference in assignment via subscripting from jsonb_set, when an original jsonb container is NULL, subscripting replaces it with an empty jsonb and proceed with assignment. For the sake of code reuse, some parts of jsonb functionality were rearranged to allow use the same functions for jsonb_set and assign subscripting operation. The original idea belongs to Oleg Bartunov. Reviewed-by: Tom Lane, Arthur Zakirov, Pavel Stehule --- doc/src/sgml/json.sgml | 48 ++++ src/backend/utils/adt/Makefile | 1 + src/backend/utils/adt/jsonb_util.c | 76 ++++- src/backend/utils/adt/jsonbsubs.c | 413 ++++++++++++++++++++++++++++ src/backend/utils/adt/jsonfuncs.c | 180 ++++++------ src/include/catalog/pg_proc.dat | 4 + src/include/catalog/pg_type.dat | 3 +- src/include/utils/jsonb.h | 6 +- src/test/regress/expected/jsonb.out | 272 +++++++++++++++++- src/test/regress/sql/jsonb.sql | 84 +++++- 10 files changed, 982 insertions(+), 105 deletions(-) create mode 100644 src/backend/utils/adt/jsonbsubs.c diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index 5b9a5557a4..100d1a60f4 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -602,6 +602,54 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu </para> </sect2> + <sect2 id="jsonb-subscripting"> + <title><type>jsonb</type> Subscripting</title> + <para> + <type>jsonb</type> data type supports array-style subscripting expressions + to extract or update particular elements. It's possible to use multiple + subscripting expressions to extract nested values. In this case, a chain of + subscripting expressions follows the same rules as the + <literal>path</literal> argument in <literal>jsonb_set</literal> function, + e.g. in case of arrays it is a 0-based operation or that negative integers + that appear in <literal>path</literal> count from the end of JSON arrays. + The result of subscripting expressions is always jsonb data type. An + example of subscripting syntax: +<programlisting> +-- Extract value by key +SELECT ('{"a": 1}'::jsonb)['a']; + +-- Extract nested value by key path +SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c']; + +-- Extract element by index +SELECT ('[1, "2", null]'::jsonb)[1]; + +-- Update value by key, note the single quotes - the assigned value +-- needs to be of jsonb type as well +UPDATE table_name SET jsonb_field['key'] = '1'; + +-- Select records using where clause with subscripting. Since the result of +-- subscripting is jsonb and we basically want to compare two jsonb objects, we +-- need to put the value in double quotes to be able to convert it to jsonb. +SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"'; +</programlisting> + + Subscripting for <type>jsonb</type> does not support slice expressions, + even if it contains an array. + + In case if source <type>jsonb</type> is <literal>NULL</literal>, assignment + via subscripting will proceed as if it was an empty JSON object: +<programlisting> +-- If jsonb_field here is NULL, the result is {"a": 1} +UPDATE table_name SET jsonb_field['a'] = '1'; + +-- If jsonb_field here is NULL, the result is [1] +UPDATE table_name SET jsonb_field[0] = '1'; +</programlisting> + + </para> + </sect2> + <sect2> <title>Transforms</title> diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile index 82732146d3..279ff15ade 100644 --- a/src/backend/utils/adt/Makefile +++ b/src/backend/utils/adt/Makefile @@ -50,6 +50,7 @@ OBJS = \ jsonb_op.o \ jsonb_util.o \ jsonfuncs.o \ + jsonbsubs.o \ jsonpath.o \ jsonpath_exec.o \ jsonpath_gram.o \ diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c index 4eeffa1424..41a1c1f9bb 100644 --- a/src/backend/utils/adt/jsonb_util.c +++ b/src/backend/utils/adt/jsonb_util.c @@ -68,18 +68,29 @@ static JsonbValue *pushJsonbValueScalar(JsonbParseState **pstate, JsonbIteratorToken seq, JsonbValue *scalarVal); +JsonbValue * +JsonbToJsonbValue(Jsonb *jsonb) +{ + JsonbValue *val = (JsonbValue *) palloc(sizeof(JsonbValue)); + + val->type = jbvBinary; + val->val.binary.data = &jsonb->root; + val->val.binary.len = VARSIZE(jsonb) - VARHDRSZ; + + return val; +} + /* * Turn an in-memory JsonbValue into a Jsonb for on-disk storage. * - * There isn't a JsonbToJsonbValue(), because generally we find it more - * convenient to directly iterate through the Jsonb representation and only - * really convert nested scalar values. JsonbIteratorNext() does this, so that - * clients of the iteration code don't have to directly deal with the binary - * representation (JsonbDeepContains() is a notable exception, although all - * exceptions are internal to this module). In general, functions that accept - * a JsonbValue argument are concerned with the manipulation of scalar values, - * or simple containers of scalar values, where it would be inconvenient to - * deal with a great amount of other state. + * Generally we find it more convenient to directly iterate through the Jsonb + * representation and only really convert nested scalar values. + * JsonbIteratorNext() does this, so that clients of the iteration code don't + * have to directly deal with the binary representation (JsonbDeepContains() is + * a notable exception, although all exceptions are internal to this module). + * In general, functions that accept a JsonbValue argument are concerned with + * the manipulation of scalar values, or simple containers of scalar values, + * where it would be inconvenient to deal with a great amount of other state. */ Jsonb * JsonbValueToJsonb(JsonbValue *val) @@ -563,6 +574,30 @@ pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq, JsonbValue *res = NULL; JsonbValue v; JsonbIteratorToken tok; + int i; + + if (jbval && (seq == WJB_ELEM || seq == WJB_VALUE) && jbval->type == jbvObject) + { + pushJsonbValue(pstate, WJB_BEGIN_OBJECT, NULL); + for (i = 0; i < jbval->val.object.nPairs; i++) + { + pushJsonbValue(pstate, WJB_KEY, &jbval->val.object.pairs[i].key); + pushJsonbValue(pstate, WJB_VALUE, &jbval->val.object.pairs[i].value); + } + + return pushJsonbValue(pstate, WJB_END_OBJECT, NULL); + } + + if (jbval && (seq == WJB_ELEM || seq == WJB_VALUE) && jbval->type == jbvArray) + { + pushJsonbValue(pstate, WJB_BEGIN_ARRAY, NULL); + for (i = 0; i < jbval->val.array.nElems; i++) + { + pushJsonbValue(pstate, WJB_ELEM, &jbval->val.array.elems[i]); + } + + return pushJsonbValue(pstate, WJB_END_ARRAY, NULL); + } if (!jbval || (seq != WJB_ELEM && seq != WJB_VALUE) || jbval->type != jbvBinary) @@ -573,9 +608,30 @@ pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq, /* unpack the binary and add each piece to the pstate */ it = JsonbIteratorInit(jbval->val.binary.data); + + if ((jbval->val.binary.data->header & JB_FSCALAR) && *pstate) + { + tok = JsonbIteratorNext(&it, &v, true); + Assert(tok == WJB_BEGIN_ARRAY); + Assert(v.type == jbvArray && v.val.array.rawScalar); + + tok = JsonbIteratorNext(&it, &v, true); + Assert(tok == WJB_ELEM); + + res = pushJsonbValueScalar(pstate, seq, &v); + + tok = JsonbIteratorNext(&it, &v, true); + Assert(tok == WJB_END_ARRAY); + Assert(it == NULL); + + return res; + } + while ((tok = JsonbIteratorNext(&it, &v, false)) != WJB_DONE) res = pushJsonbValueScalar(pstate, tok, - tok < WJB_BEGIN_ARRAY ? &v : NULL); + tok < WJB_BEGIN_ARRAY || + (tok == WJB_BEGIN_ARRAY && + v.val.array.rawScalar) ? &v : NULL); return res; } diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c new file mode 100644 index 0000000000..306c37b5a6 --- /dev/null +++ b/src/backend/utils/adt/jsonbsubs.c @@ -0,0 +1,413 @@ +/*------------------------------------------------------------------------- + * + * jsonbsubs.c + * Subscripting support functions for jsonb. + * + * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * src/backend/utils/adt/jsonbsubs.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "executor/execExpr.h" +#include "nodes/makefuncs.h" +#include "nodes/nodeFuncs.h" +#include "nodes/subscripting.h" +#include "parser/parse_coerce.h" +#include "parser/parse_expr.h" +#include "utils/jsonb.h" +#include "utils/jsonfuncs.h" +#include "utils/builtins.h" +#include "utils/lsyscache.h" + + +/* SubscriptingRefState.workspace for jsonb subscripting execution */ +typedef struct JsonbSubWorkspace +{ + bool expectArray; /* jsonb root is expected to be an array */ + Oid *indexOid; /* OID of coerced subscript expression, + could be only integer or text */ + Datum *index; /* Subscript values in Datum format */ +} JsonbSubWorkspace; + + +/* + * Finish parse analysis of a SubscriptingRef expression for a jsonb. + * + * Transform the subscript expressions, coerce them to text, + * and determine the result type of the SubscriptingRef node. + */ +static void +jsonb_subscript_transform(SubscriptingRef *sbsref, + List *indirection, + ParseState *pstate, + bool isSlice, + bool isAssignment) +{ + List *upperIndexpr = NIL; + ListCell *idx; + + /* + * Transform and convert the subscript expressions. Jsonb subscripting does + * not support slices, look only and the upper index. + */ + foreach(idx, indirection) + { + A_Indices *ai = lfirst_node(A_Indices, idx); + Node *subExpr; + + if (isSlice) + { + Node *expr = ai->uidx ? ai->uidx : ai->lidx; + + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("jsonb subscript does not support slices"), + parser_errposition(pstate, exprLocation(expr)))); + } + + if (ai->uidx) + { + Oid subExprType = InvalidOid, + targetType = UNKNOWNOID; + + subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind); + subExprType = exprType(subExpr); + + if (subExprType != UNKNOWNOID) + { + Oid targets[2] = {INT4OID, TEXTOID}; + + /* + * Jsonb can handle multiple subscript types, but cases when a + * subscript could be coerced to multiple target types must be + * avoided, similar to overloaded functions. It could be + * possibly extend with jsonpath in the future. + */ + for (int i = 0; i < 2; i++) + { + if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT)) + { + /* + * One type has already succeeded, it means there are + * two coercion targets possible, failure. + */ + if (targetType != UNKNOWNOID) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("subscript type is not supported"), + errhint("Jsonb subscript must be coerced " + "only to one type, integer or text."), + parser_errposition(pstate, exprLocation(subExpr)))); + + targetType = targets[i]; + } + } + + /* + * No suitable types were found, failure. + */ + if (targetType == UNKNOWNOID) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("subscript type is not supported"), + errhint("Jsonb subscript must be coercet to either integer or text"), + parser_errposition(pstate, exprLocation(subExpr)))); + } + else + targetType = TEXTOID; + + /* + * We known from can_coerce_type that coercion will succeed, so + * coerce_type could be used. Note the implicit coercion context, + * which is required to handle subscripts of different types, + * similar to overloaded functions. + */ + subExpr = coerce_type(pstate, + subExpr, subExprType, + targetType, -1, + COERCION_IMPLICIT, + COERCE_IMPLICIT_CAST, + -1); + if (subExpr == NULL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("jsonb subscript must have text type"), + parser_errposition(pstate, exprLocation(subExpr)))); + } + else + { + /* + * Slice with omitted upper bound. Should not happen as we already + * errored out on slice earlier, but handle this just in case. + */ + Assert(isSlice && ai->is_slice); + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("jsonb subscript does not support slices"), + parser_errposition(pstate, exprLocation(ai->uidx)))); + } + + upperIndexpr = lappend(upperIndexpr, subExpr); + } + + /* store the transformed lists into the SubscriptRef node */ + sbsref->refupperindexpr = upperIndexpr; + sbsref->reflowerindexpr = NIL; + + /* Determine the result type of the subscripting operation; always jsonb */ + sbsref->refrestype = JSONBOID; + sbsref->reftypmod = -1; +} + +/* + * During execution, process the subscripts in a SubscriptingRef expression. + * + * The subscript expressions are already evaluated in Datum form in the + * SubscriptingRefState's arrays. Check and convert them as necessary. + * + * If any subscript is NULL, we throw error in assignment cases, or in fetch + * cases set result to NULL and return false (instructing caller to skip the + * rest of the SubscriptingRef sequence). + */ +static bool +jsonb_subscript_check_subscripts(ExprState *state, + ExprEvalStep *op, + ExprContext *econtext) +{ + SubscriptingRefState *sbsrefstate = op->d.sbsref_subscript.state; + JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace; + + /* + * In case if the first subscript is an integer, the source jsonb is + * expected to be an array. This information is not used directly, all such + * cases are handled within corresponding jsonb assign functions. But if + * the source jsonb is NULL the expected type will be used to construct an + * empty source. + */ + if (sbsrefstate->numupper > 0 && sbsrefstate->upperprovided[0] && + !sbsrefstate->upperindexnull[0] && workspace->indexOid[0] == INT4OID) + workspace->expectArray = true; + + /* Process upper subscripts */ + for (int i = 0; i < sbsrefstate->numupper; i++) + { + if (sbsrefstate->upperprovided[i]) + { + /* If any index expr yields NULL, result is NULL or error */ + if (sbsrefstate->upperindexnull[i]) + { + if (sbsrefstate->isassignment) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("jsonb subscript in assignment must not be null"))); + *op->resnull = true; + return false; + } + + /* + * For jsonb fetch and assign functions we need to provide path in + * text format. Convert if it's not already text. + */ + if (workspace->indexOid[i] == INT4OID) + { + Datum datum = sbsrefstate->upperindex[i]; + char *cs = DatumGetCString(DirectFunctionCall1(int4out, datum)); + workspace->index[i] = CStringGetTextDatum(cs); + } + else + workspace->index[i] = sbsrefstate->upperindex[i]; + } + } + + return true; +} + +/* + * Evaluate SubscriptingRef fetch for a jsonb element. + * + * Source container is in step's result variable (it's known not NULL, since + * we set fetch_strict to true). + */ +static void +jsonb_subscript_fetch(ExprState *state, + ExprEvalStep *op, + ExprContext *econtext) +{ + SubscriptingRefState *sbsrefstate = op->d.sbsref.state; + JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace; + Jsonb *jsonbSource; + + /* Should not get here if source jsonb (or any subscript) is null */ + Assert(!(*op->resnull)); + + jsonbSource = DatumGetJsonbP(*op->resvalue); + *op->resvalue = jsonb_get_element(jsonbSource, + workspace->index, + sbsrefstate->numupper, + op->resnull, + false); +} + +/* + * Evaluate SubscriptingRef assignment for a jsonb element assignment. + * + * Input container (possibly null) is in result area, replacement value is in + * SubscriptingRefState's replacevalue/replacenull. + */ +static void +jsonb_subscript_assign(ExprState *state, + ExprEvalStep *op, + ExprContext *econtext) +{ + SubscriptingRefState *sbsrefstate = op->d.sbsref.state; + JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace; + Jsonb *jsonbSource; + JsonbValue *replacevalue; + + if (sbsrefstate->replacenull) + { + replacevalue = (JsonbValue *) palloc(sizeof(JsonbValue)); + replacevalue->type = jbvNull; + } + else + replacevalue = + JsonbToJsonbValue(DatumGetJsonbP(sbsrefstate->replacevalue)); + + /* + * In case if the input container is null, set up an empty jsonb and + * proceed with the assignment. + */ + if (*op->resnull) + { + JsonbValue *newSource = (JsonbValue *) palloc(sizeof(JsonbValue)); + + /* + * To avoid any surprising results, set up an empty jsonb array in case + * of an array is expected (i.e. the first subscript is integer), + * otherwise jsonb object. + */ + if (workspace->expectArray) + { + newSource->type = jbvArray; + newSource->val.array.nElems = 0; + newSource->val.array.rawScalar = false; + } + else + { + newSource->type = jbvObject; + newSource->val.object.nPairs = 0; + } + + jsonbSource = JsonbValueToJsonb(newSource); + *op->resnull = false; + } + else + jsonbSource = DatumGetJsonbP(*op->resvalue); + + *op->resvalue = jsonb_set_element(jsonbSource, + workspace->index, + sbsrefstate->numupper, + replacevalue); + /* The result is never NULL, so no need to change *op->resnull */ +} + +/* + * Compute old jsonb element value for a SubscriptingRef assignment + * expression. Will only be called if the new-value subexpression + * contains SubscriptingRef or FieldStore. This is the same as the + * regular fetch case, except that we have to handle a null jsonb, + * and the value should be stored into the SubscriptingRefState's + * prevvalue/prevnull fields. + */ +static void +jsonb_subscript_fetch_old(ExprState *state, + ExprEvalStep *op, + ExprContext *econtext) +{ + SubscriptingRefState *sbsrefstate = op->d.sbsref.state; + + if (*op->resnull) + { + /* whole jsonb is null, so any element is too */ + sbsrefstate->prevvalue = (Datum) 0; + sbsrefstate->prevnull = true; + } + else + { + Jsonb *jsonbSource = DatumGetJsonbP(*op->resvalue); + sbsrefstate->prevvalue = jsonb_get_element(jsonbSource, + sbsrefstate->upperindex, + sbsrefstate->numupper, + &sbsrefstate->prevnull, + false); + } +} + +/* + * Set up execution state for a jsonb subscript operation. Opposite to the + * arrays subscription, there is no limit for number of subscripts as jsonb + * type itself doesn't have nesting limits. + */ +static void +jsonb_exec_setup(const SubscriptingRef *sbsref, + SubscriptingRefState *sbsrefstate, + SubscriptExecSteps *methods) +{ + JsonbSubWorkspace *workspace; + ListCell *lc; + int nupper = sbsref->refupperindexpr->length; + char *ptr; + + /* Allocate type-specific workspace with space for per-subscript data */ + workspace = palloc0(MAXALIGN(sizeof(JsonbSubWorkspace)) + + nupper * (sizeof(Datum) + sizeof(Oid))); + workspace->expectArray = false; + ptr = ((char *) workspace) + MAXALIGN(sizeof(JsonbSubWorkspace)); + workspace->indexOid = (Oid *) ptr; + ptr += nupper * sizeof(Oid); + workspace->index = (Datum *) ptr; + + sbsrefstate->workspace = workspace; + + /* Collect subscript data types necessary at execution time */ + foreach(lc, sbsref->refupperindexpr) + { + Node *expr = lfirst(lc); + int i = foreach_current_index(lc); + + workspace->indexOid[i] = exprType(expr); + } + + /* + * Pass back pointers to appropriate step execution functions. + */ + methods->sbs_check_subscripts = jsonb_subscript_check_subscripts; + methods->sbs_fetch = jsonb_subscript_fetch; + methods->sbs_assign = jsonb_subscript_assign; + methods->sbs_fetch_old = jsonb_subscript_fetch_old; +} + +/* + * jsonb_subscript_handler + * Subscripting handler for jsonb. + * + */ +Datum +jsonb_subscript_handler(PG_FUNCTION_ARGS) +{ + static const SubscriptRoutines sbsroutines = { + .transform = jsonb_subscript_transform, + .exec_setup = jsonb_exec_setup, + .fetch_strict = true, /* fetch returns NULL for NULL inputs */ + .fetch_leakproof = true, /* fetch returns NULL for bad subscript */ + .store_leakproof = false /* ... but assignment throws error */ + }; + + PG_RETURN_POINTER(&sbsroutines); +} diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index 69100feab7..5a0ba6b220 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -461,18 +461,18 @@ static Datum populate_domain(DomainIOData *io, Oid typid, const char *colname, /* functions supporting jsonb_delete, jsonb_set and jsonb_concat */ static JsonbValue *IteratorConcat(JsonbIterator **it1, JsonbIterator **it2, JsonbParseState **state); -static JsonbValue *setPath(JsonbIterator **it, Datum *path_elems, +extern JsonbValue *setPath(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, - JsonbParseState **st, int level, Jsonb *newval, + JsonbParseState **st, int level, JsonbValue *newval, int op_type); static void setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, JsonbParseState **st, int level, - Jsonb *newval, uint32 npairs, int op_type); + JsonbValue *newval, uint32 npairs, int op_type); static void setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, JsonbParseState **st, - int level, Jsonb *newval, uint32 nelems, int op_type); -static void addJsonbToParseState(JsonbParseState **jbps, Jsonb *jb); + int level, + JsonbValue *newval, uint32 nelems, int op_type); /* function supporting iterate_json_values */ static void iterate_values_scalar(void *state, char *token, JsonTokenType tokentype); @@ -1448,13 +1448,9 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); Datum *pathtext; bool *pathnulls; + bool isnull; int npath; - int i; - bool have_object = false, - have_array = false; - JsonbValue *jbvp = NULL; - JsonbValue jbvbuf; - JsonbContainer *container; + Datum res; /* * If the array contains any null elements, return NULL, on the grounds @@ -1469,9 +1465,26 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) deconstruct_array(path, TEXTOID, -1, false, TYPALIGN_INT, &pathtext, &pathnulls, &npath); - /* Identify whether we have object, array, or scalar at top-level */ - container = &jb->root; + res = jsonb_get_element(jb, pathtext, npath, &isnull, as_text); + if (isnull) + PG_RETURN_NULL(); + else + PG_RETURN_DATUM(res); +} + +Datum +jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text) +{ + JsonbContainer *container = &jb->root; + JsonbValue *jbvp = NULL; + int i; + bool have_object = false, + have_array = false; + + *isnull = false; + + /* Identify whether we have object, array, or scalar at top-level */ if (JB_ROOT_IS_OBJECT(jb)) have_object = true; else if (JB_ROOT_IS_ARRAY(jb) && !JB_ROOT_IS_SCALAR(jb)) @@ -1496,7 +1509,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) { if (as_text) { - PG_RETURN_TEXT_P(cstring_to_text(JsonbToCString(NULL, + return PointerGetDatum(cstring_to_text(JsonbToCString(NULL, container, VARSIZE(jb)))); } @@ -1512,22 +1525,25 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) if (have_object) { jbvp = getKeyJsonValueFromContainer(container, - VARDATA(pathtext[i]), - VARSIZE(pathtext[i]) - VARHDRSZ, - &jbvbuf); + VARDATA(path[i]), + VARSIZE(path[i]) - VARHDRSZ, + NULL); } else if (have_array) { long lindex; uint32 index; - char *indextext = TextDatumGetCString(pathtext[i]); + char *indextext = TextDatumGetCString(path[i]); char *endptr; errno = 0; lindex = strtol(indextext, &endptr, 10); if (endptr == indextext || *endptr != '\0' || errno != 0 || lindex > INT_MAX || lindex < INT_MIN) - PG_RETURN_NULL(); + { + *isnull = true; + return PointerGetDatum(NULL); + } if (lindex >= 0) { @@ -1545,7 +1561,10 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) nelements = JsonContainerSize(container); if (-lindex > nelements) - PG_RETURN_NULL(); + { + *isnull = true; + return PointerGetDatum(NULL); + } else index = nelements + lindex; } @@ -1555,11 +1574,15 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) else { /* scalar, extraction yields a null */ - PG_RETURN_NULL(); + *isnull = true; + return PointerGetDatum(NULL); } if (jbvp == NULL) - PG_RETURN_NULL(); + { + *isnull = true; + return PointerGetDatum(NULL); + } else if (i == npath - 1) break; @@ -1581,9 +1604,12 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) if (as_text) { if (jbvp->type == jbvNull) - PG_RETURN_NULL(); + { + *isnull = true; + return PointerGetDatum(NULL); + } - PG_RETURN_TEXT_P(JsonbValueAsText(jbvp)); + return PointerGetDatum(JsonbValueAsText(jbvp)); } else { @@ -1594,6 +1620,28 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) } } +Datum +jsonb_set_element(Jsonb* jb, Datum *path, int path_len, + JsonbValue *newval) +{ + JsonbValue *res; + JsonbParseState *state = NULL; + JsonbIterator *it; + bool *path_nulls = palloc0(path_len * sizeof(bool)); + + if (newval->type == jbvArray && newval->val.array.rawScalar) + *newval = newval->val.array.elems[0]; + + it = JsonbIteratorInit(&jb->root); + + res = setPath(&it, path, path_nulls, path_len, &state, 0, + newval, JB_PATH_CREATE); + + pfree(path_nulls); + + PG_RETURN_JSONB_P(JsonbValueToJsonb(res)); +} + /* * Return the text representation of the given JsonbValue. */ @@ -4151,58 +4199,6 @@ jsonb_strip_nulls(PG_FUNCTION_ARGS) PG_RETURN_POINTER(JsonbValueToJsonb(res)); } -/* - * Add values from the jsonb to the parse state. - * - * If the parse state container is an object, the jsonb is pushed as - * a value, not a key. - * - * This needs to be done using an iterator because pushJsonbValue doesn't - * like getting jbvBinary values, so we can't just push jb as a whole. - */ -static void -addJsonbToParseState(JsonbParseState **jbps, Jsonb *jb) -{ - JsonbIterator *it; - JsonbValue *o = &(*jbps)->contVal; - JsonbValue v; - JsonbIteratorToken type; - - it = JsonbIteratorInit(&jb->root); - - Assert(o->type == jbvArray || o->type == jbvObject); - - if (JB_ROOT_IS_SCALAR(jb)) - { - (void) JsonbIteratorNext(&it, &v, false); /* skip array header */ - Assert(v.type == jbvArray); - (void) JsonbIteratorNext(&it, &v, false); /* fetch scalar value */ - - switch (o->type) - { - case jbvArray: - (void) pushJsonbValue(jbps, WJB_ELEM, &v); - break; - case jbvObject: - (void) pushJsonbValue(jbps, WJB_VALUE, &v); - break; - default: - elog(ERROR, "unexpected parent of nested structure"); - } - } - else - { - while ((type = JsonbIteratorNext(&it, &v, false)) != WJB_DONE) - { - if (type == WJB_KEY || type == WJB_VALUE || type == WJB_ELEM) - (void) pushJsonbValue(jbps, type, &v); - else - (void) pushJsonbValue(jbps, type, NULL); - } - } - -} - /* * SQL function jsonb_pretty (jsonb) * @@ -4474,7 +4470,8 @@ jsonb_set(PG_FUNCTION_ARGS) { Jsonb *in = PG_GETARG_JSONB_P(0); ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); - Jsonb *newval = PG_GETARG_JSONB_P(2); + Jsonb *newjsonb = PG_GETARG_JSONB_P(2); + JsonbValue *newval = JsonbToJsonbValue(newjsonb); bool create = PG_GETARG_BOOL(3); JsonbValue *res = NULL; Datum *path_elems; @@ -4632,7 +4629,8 @@ jsonb_insert(PG_FUNCTION_ARGS) { Jsonb *in = PG_GETARG_JSONB_P(0); ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); - Jsonb *newval = PG_GETARG_JSONB_P(2); + Jsonb *newjsonb = PG_GETARG_JSONB_P(2); + JsonbValue *newval = JsonbToJsonbValue(newjsonb); bool after = PG_GETARG_BOOL(3); JsonbValue *res = NULL; Datum *path_elems; @@ -4787,10 +4785,10 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2, * All path elements before the last must already exist * whatever bits in op_type are set, or nothing is done. */ -static JsonbValue * +JsonbValue * setPath(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, - JsonbParseState **st, int level, Jsonb *newval, int op_type) + JsonbParseState **st, int level, JsonbValue *newval, int op_type) { JsonbValue v; JsonbIteratorToken r; @@ -4843,11 +4841,11 @@ setPath(JsonbIterator **it, Datum *path_elems, static void setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, JsonbParseState **st, int level, - Jsonb *newval, uint32 npairs, int op_type) + JsonbValue *newval, uint32 npairs, int op_type) { - JsonbValue v; int i; - JsonbValue k; + JsonbValue k, + v; bool done = false; if (level >= path_len || path_nulls[level]) @@ -4864,7 +4862,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, newkey.val.string.val = VARDATA_ANY(path_elems[level]); (void) pushJsonbValue(st, WJB_KEY, &newkey); - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_VALUE, newval); } for (i = 0; i < npairs; i++) @@ -4895,7 +4893,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, if (!(op_type & JB_PATH_DELETE)) { (void) pushJsonbValue(st, WJB_KEY, &k); - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_VALUE, newval); } done = true; } @@ -4918,7 +4916,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, newkey.val.string.val = VARDATA_ANY(path_elems[level]); (void) pushJsonbValue(st, WJB_KEY, &newkey); - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_VALUE, newval); } (void) pushJsonbValue(st, r, &k); @@ -4950,7 +4948,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, static void setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, JsonbParseState **st, int level, - Jsonb *newval, uint32 nelems, int op_type) + JsonbValue *newval, uint32 nelems, int op_type) { JsonbValue v; int idx, @@ -4998,7 +4996,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, (op_type & JB_PATH_CREATE_OR_INSERT)) { Assert(newval != NULL); - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_ELEM, newval); done = true; } @@ -5014,7 +5012,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, r = JsonbIteratorNext(it, &v, true); /* skip */ if (op_type & (JB_PATH_INSERT_BEFORE | JB_PATH_CREATE)) - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_ELEM, newval); /* * We should keep current value only in case of @@ -5025,7 +5023,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, (void) pushJsonbValue(st, r, &v); if (op_type & (JB_PATH_INSERT_AFTER | JB_PATH_REPLACE)) - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_ELEM, newval); done = true; } @@ -5059,7 +5057,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, if ((op_type & JB_PATH_CREATE_OR_INSERT) && !done && level == path_len - 1 && i == nelems - 1) { - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_ELEM, newval); } } } diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 139f4a08bd..feae8cc4b0 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -11239,6 +11239,10 @@ { oid => '9256', descr => 'raw array subscripting support', proname => 'raw_array_subscript_handler', prorettype => 'internal', proargtypes => 'internal', prosrc => 'raw_array_subscript_handler' }, +# type subscripting support +{ oid => '6098', descr => 'jsonb subscripting logic', + proname => 'jsonb_subscript_handler', prorettype => 'internal', + proargtypes => 'internal', prosrc => 'jsonb_subscript_handler' }, # collation management functions { oid => '3445', descr => 'import collations from operating system', diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat index 62018f063a..4a530ca907 100644 --- a/src/include/catalog/pg_type.dat +++ b/src/include/catalog/pg_type.dat @@ -444,7 +444,8 @@ { oid => '3802', array_type_oid => '3807', descr => 'Binary JSON', typname => 'jsonb', typlen => '-1', typbyval => 'f', typcategory => 'U', typinput => 'jsonb_in', typoutput => 'jsonb_out', typreceive => 'jsonb_recv', - typsend => 'jsonb_send', typalign => 'i', typstorage => 'x' }, + typsend => 'jsonb_send', typalign => 'i', typstorage => 'x', + typsubscript => 'jsonb_subscript_handler' }, { oid => '4072', array_type_oid => '4073', descr => 'JSON path', typname => 'jsonpath', typlen => '-1', typbyval => 'f', typcategory => 'U', typinput => 'jsonpath_in', typoutput => 'jsonpath_out', diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index 5860011693..64f1ccbf77 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -392,6 +392,7 @@ extern JsonbValue *pushJsonbValue(JsonbParseState **pstate, extern JsonbIterator *JsonbIteratorInit(JsonbContainer *container); extern JsonbIteratorToken JsonbIteratorNext(JsonbIterator **it, JsonbValue *val, bool skipNested); +extern JsonbValue *JsonbToJsonbValue(Jsonb *jsonb); extern Jsonb *JsonbValueToJsonb(JsonbValue *val); extern bool JsonbDeepContains(JsonbIterator **val, JsonbIterator **mContained); @@ -407,5 +408,8 @@ extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in, extern bool JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res); extern const char *JsonbTypeName(JsonbValue *jb); - +extern Datum jsonb_set_element(Jsonb *jb, Datum *path, int path_len, + JsonbValue *newval); +extern Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath, + bool *isnull, bool as_text); #endif /* __JSONB_H__ */ diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 1e6c6ef200..bb3f25ec3f 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -4599,7 +4599,7 @@ select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'use_ {"a": 1, "b": null} (1 row) -\pset null +\pset null '' -- jsonb_insert select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"'); jsonb_insert @@ -4729,6 +4729,276 @@ HINT: Try using the function jsonb_set to replace key value. select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true); ERROR: cannot replace existing key HINT: Try using the function jsonb_set to replace key value. +-- jsonb subscript +select ('123'::jsonb)['a']; + jsonb +------- + +(1 row) + +select ('123'::jsonb)[0]; + jsonb +------- + +(1 row) + +select ('123'::jsonb)[NULL]; + jsonb +------- + +(1 row) + +select ('{"a": 1}'::jsonb)['a']; + jsonb +------- + 1 +(1 row) + +select ('{"a": 1}'::jsonb)[0]; + jsonb +------- + +(1 row) + +select ('{"a": 1}'::jsonb)['not_exist']; + jsonb +------- + +(1 row) + +select ('{"a": 1}'::jsonb)[NULL]; + jsonb +------- + +(1 row) + +select ('[1, "2", null]'::jsonb)['a']; + jsonb +------- + +(1 row) + +select ('[1, "2", null]'::jsonb)[0]; + jsonb +------- + 1 +(1 row) + +select ('[1, "2", null]'::jsonb)['1']; + jsonb +------- + "2" +(1 row) + +select ('[1, "2", null]'::jsonb)[1.0]; +ERROR: subscript type is not supported +LINE 1: select ('[1, "2", null]'::jsonb)[1.0]; + ^ +HINT: Jsonb subscript must be coercet to either integer or text +select ('[1, "2", null]'::jsonb)[2]; + jsonb +------- + null +(1 row) + +select ('[1, "2", null]'::jsonb)[3]; + jsonb +------- + +(1 row) + +select ('[1, "2", null]'::jsonb)[-2]; + jsonb +------- + "2" +(1 row) + +select ('[1, "2", null]'::jsonb)[1]['a']; + jsonb +------- + +(1 row) + +select ('[1, "2", null]'::jsonb)[1][0]; + jsonb +------- + +(1 row) + +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b']; + jsonb +------- + "c" +(1 row) + +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']; + jsonb +----------- + [1, 2, 3] +(1 row) + +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1]; + jsonb +------- + 2 +(1 row) + +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a']; + jsonb +------- + +(1 row) + +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']; + jsonb +--------------- + {"a2": "aaa"} +(1 row) + +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']; + jsonb +------- + "aaa" +(1 row) + +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3']; + jsonb +------- + +(1 row) + +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1']; + jsonb +----------------------- + ["aaa", "bbb", "ccc"] +(1 row) + +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2]; + jsonb +------- + "ccc" +(1 row) + +-- slices are not supported +select ('{"a": 1}'::jsonb)['a':'b']; +ERROR: jsonb subscript does not support slices +LINE 1: select ('{"a": 1}'::jsonb)['a':'b']; + ^ +select ('[1, "2", null]'::jsonb)[1:2]; +ERROR: jsonb subscript does not support slices +LINE 1: select ('[1, "2", null]'::jsonb)[1:2]; + ^ +select ('[1, "2", null]'::jsonb)[:2]; +ERROR: jsonb subscript does not support slices +LINE 1: select ('[1, "2", null]'::jsonb)[:2]; + ^ +select ('[1, "2", null]'::jsonb)[1:]; +ERROR: jsonb subscript does not support slices +LINE 1: select ('[1, "2", null]'::jsonb)[1:]; + ^ +select ('[1, "2", null]'::jsonb)[:]; +ERROR: jsonb subscript does not support slices +create TEMP TABLE test_jsonb_subscript ( + id int, + test_json jsonb +); +insert into test_jsonb_subscript values +(1, '{}'), -- empty jsonb +(2, '{"key": "value"}'); -- jsonb with data +-- update empty jsonb +update test_jsonb_subscript set test_json['a'] = '1' where id = 1; +select * from test_jsonb_subscript; + id | test_json +----+------------------ + 2 | {"key": "value"} + 1 | {"a": 1} +(2 rows) + +-- update jsonb with some data +update test_jsonb_subscript set test_json['a'] = '1' where id = 2; +select * from test_jsonb_subscript; + id | test_json +----+-------------------------- + 1 | {"a": 1} + 2 | {"a": 1, "key": "value"} +(2 rows) + +-- replace jsonb +update test_jsonb_subscript set test_json['a'] = '"test"'; +select * from test_jsonb_subscript; + id | test_json +----+------------------------------- + 1 | {"a": "test"} + 2 | {"a": "test", "key": "value"} +(2 rows) + +-- replace by object +update test_jsonb_subscript set test_json['a'] = '{"b": 1}'::jsonb; +select * from test_jsonb_subscript; + id | test_json +----+--------------------------------- + 1 | {"a": {"b": 1}} + 2 | {"a": {"b": 1}, "key": "value"} +(2 rows) + +-- replace by array +update test_jsonb_subscript set test_json['a'] = '[1, 2, 3]'::jsonb; +select * from test_jsonb_subscript; + id | test_json +----+---------------------------------- + 1 | {"a": [1, 2, 3]} + 2 | {"a": [1, 2, 3], "key": "value"} +(2 rows) + +-- use jsonb subscription in where clause +select * from test_jsonb_subscript where test_json['key'] = '"value"'; + id | test_json +----+---------------------------------- + 2 | {"a": [1, 2, 3], "key": "value"} +(1 row) + +select * from test_jsonb_subscript where test_json['key_doesnt_exists'] = '"value"'; + id | test_json +----+----------- +(0 rows) + +select * from test_jsonb_subscript where test_json['key'] = '"wrong_value"'; + id | test_json +----+----------- +(0 rows) + +-- NULL +update test_jsonb_subscript set test_json[NULL] = '1'; +ERROR: jsonb subscript in assignment must not be null +update test_jsonb_subscript set test_json['another_key'] = NULL; +select * from test_jsonb_subscript; + id | test_json +----+------------------------------------------------------- + 1 | {"a": [1, 2, 3], "another_key": null} + 2 | {"a": [1, 2, 3], "key": "value", "another_key": null} +(2 rows) + +-- NULL as jsonb source +insert into test_jsonb_subscript values (3, NULL); +update test_jsonb_subscript set test_json['a'] = '1' where id = 3; +select * from test_jsonb_subscript; + id | test_json +----+------------------------------------------------------- + 1 | {"a": [1, 2, 3], "another_key": null} + 2 | {"a": [1, 2, 3], "key": "value", "another_key": null} + 3 | {"a": 1} +(3 rows) + +update test_jsonb_subscript set test_json = NULL where id = 3; +update test_jsonb_subscript set test_json[0] = '1'; +select * from test_jsonb_subscript; + id | test_json +----+--------------------------------------------------------------- + 1 | {"0": 1, "a": [1, 2, 3], "another_key": null} + 2 | {"0": 1, "a": [1, 2, 3], "key": "value", "another_key": null} + 3 | [1] +(3 rows) + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); to_tsvector diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index b6409767f6..20aa8fe0e2 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1177,7 +1177,7 @@ select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'retu select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'delete_key') as delete_key; select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'use_json_null') as use_json_null; -\pset null +\pset null '' -- jsonb_insert select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"'); @@ -1208,6 +1208,88 @@ select jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"', true); select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"'); select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true); +-- jsonb subscript +select ('123'::jsonb)['a']; +select ('123'::jsonb)[0]; +select ('123'::jsonb)[NULL]; +select ('{"a": 1}'::jsonb)['a']; +select ('{"a": 1}'::jsonb)[0]; +select ('{"a": 1}'::jsonb)['not_exist']; +select ('{"a": 1}'::jsonb)[NULL]; +select ('[1, "2", null]'::jsonb)['a']; +select ('[1, "2", null]'::jsonb)[0]; +select ('[1, "2", null]'::jsonb)['1']; +select ('[1, "2", null]'::jsonb)[1.0]; +select ('[1, "2", null]'::jsonb)[2]; +select ('[1, "2", null]'::jsonb)[3]; +select ('[1, "2", null]'::jsonb)[-2]; +select ('[1, "2", null]'::jsonb)[1]['a']; +select ('[1, "2", null]'::jsonb)[1][0]; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b']; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1]; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a']; +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']; +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']; +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3']; +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1']; +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2]; + +-- slices are not supported +select ('{"a": 1}'::jsonb)['a':'b']; +select ('[1, "2", null]'::jsonb)[1:2]; +select ('[1, "2", null]'::jsonb)[:2]; +select ('[1, "2", null]'::jsonb)[1:]; +select ('[1, "2", null]'::jsonb)[:]; + +create TEMP TABLE test_jsonb_subscript ( + id int, + test_json jsonb +); + +insert into test_jsonb_subscript values +(1, '{}'), -- empty jsonb +(2, '{"key": "value"}'); -- jsonb with data + +-- update empty jsonb +update test_jsonb_subscript set test_json['a'] = '1' where id = 1; +select * from test_jsonb_subscript; + +-- update jsonb with some data +update test_jsonb_subscript set test_json['a'] = '1' where id = 2; +select * from test_jsonb_subscript; + +-- replace jsonb +update test_jsonb_subscript set test_json['a'] = '"test"'; +select * from test_jsonb_subscript; + +-- replace by object +update test_jsonb_subscript set test_json['a'] = '{"b": 1}'::jsonb; +select * from test_jsonb_subscript; + +-- replace by array +update test_jsonb_subscript set test_json['a'] = '[1, 2, 3]'::jsonb; +select * from test_jsonb_subscript; + +-- use jsonb subscription in where clause +select * from test_jsonb_subscript where test_json['key'] = '"value"'; +select * from test_jsonb_subscript where test_json['key_doesnt_exists'] = '"value"'; +select * from test_jsonb_subscript where test_json['key'] = '"wrong_value"'; + +-- NULL +update test_jsonb_subscript set test_json[NULL] = '1'; +update test_jsonb_subscript set test_json['another_key'] = NULL; +select * from test_jsonb_subscript; + +-- NULL as jsonb source +insert into test_jsonb_subscript values (3, NULL); +update test_jsonb_subscript set test_json['a'] = '1' where id = 3; +select * from test_jsonb_subscript; + +update test_jsonb_subscript set test_json = NULL where id = 3; +update test_jsonb_subscript set test_json[0] = '1'; +select * from test_jsonb_subscript; + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); -- 2.21.0 --bnxkghd4qzx6dtmd Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v41-0002-Filling-gaps-in-jsonb.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL/JSON json_table plan clause @ 2025-02-04 03:05 Amit Langote <[email protected]> 2025-02-04 11:50 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Amit Langote @ 2025-02-04 03:05 UTC (permalink / raw) To: Nikita Malakhov <[email protected]>; +Cc: pgsql-hackers Hi Nikita, On Wed, Dec 18, 2024 at 12:11 AM Nikita Malakhov <[email protected]> wrote: > > Hi hackers! > > This thread is further work continued in [1], where Amit Langote > suggested starting discussion on the remaining SQL/JSON feature > 'PLAN clause for JSON_TABLE' anew. > > We'd like to help with merging SQL/JSON patches into vanilla, > and have adapted PLAN clause to recent changes in JSON_TABLE > function. Thanks for working on this. > While doing this we've found that some tests with the PLAN clause > were incorrect, along with JSON_TABLE behavior with this clause. > We've corrected this behavior, but these corrections required reverting > some removed and heavily refactored code, so we'd be glad for review > and feedback on this patch. Sorry, I don't fully understand this paragraph. Do you mean that there might be bugs in the existing JSON_TABLE() functionality that was committed into v17? -- Thanks, Amit Langote ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL/JSON json_table plan clause 2025-02-04 03:05 Re: SQL/JSON json_table plan clause Amit Langote <[email protected]> @ 2025-02-04 11:50 ` Nikita Malakhov <[email protected]> 2026-07-04 21:02 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Nikita Malakhov @ 2025-02-04 11:50 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: pgsql-hackers Hi Amit! No, JSON_TABLE is ok. I've meant that in the previous approach to PLAN clause implementation there were some wrong tests. On Tue, Feb 4, 2025 at 6:05 AM Amit Langote <[email protected]> wrote: > Hi Nikita, > > On Wed, Dec 18, 2024 at 12:11 AM Nikita Malakhov <[email protected]> > wrote: > > > > Hi hackers! > > > > This thread is further work continued in [1], where Amit Langote > > suggested starting discussion on the remaining SQL/JSON feature > > 'PLAN clause for JSON_TABLE' anew. > > > > We'd like to help with merging SQL/JSON patches into vanilla, > > and have adapted PLAN clause to recent changes in JSON_TABLE > > function. > > Thanks for working on this. > > > While doing this we've found that some tests with the PLAN clause > > were incorrect, along with JSON_TABLE behavior with this clause. > > We've corrected this behavior, but these corrections required reverting > > some removed and heavily refactored code, so we'd be glad for review > > and feedback on this patch. > > Sorry, I don't fully understand this paragraph. Do you mean that > there might be bugs in the existing JSON_TABLE() functionality that > was committed into v17? > > -- > Thanks, Amit Langote > -- Regards, Nikita Malakhov Postgres Professional The Russian Postgres Company https://postgrespro.ru/ ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL/JSON json_table plan clause 2025-02-04 03:05 Re: SQL/JSON json_table plan clause Amit Langote <[email protected]> 2025-02-04 11:50 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> @ 2026-07-04 21:02 ` Alexander Korotkov <[email protected]> 2026-07-07 23:38 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Alexander Korotkov @ 2026-07-04 21:02 UTC (permalink / raw) To: Nikita Malakhov <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers Hi Nikita! On Fri, May 15, 2026 at 2:53 PM Nikita Malakhov <[email protected]> wrote: > Again, Alexander and Amit, thanks for the review. I've rebased the patch and made > some changes according to Alexander's notes. I've slightly rearranged files between > patches for it to be easier to review, so now there are 3 patches: > v23-0001-json-table-plan-clause.patch - main code changes > v23-0002-json-table-plan-tests.patch - test cases and out file changes > v23-0003-json-table-plan-docs.patch - docs package > > <...> > >1. IsA(planstate, JsonTableSiblingJoin) is wrong. planstate is not a > >node, thus IsA() can't be applied to it. You should instead do > >IsA(planstate->plan, JsonTableSiblingJoin). It wasn't catched, > >because regression tests don't exercise this branch. So, you also > >need to improve the coverage. > - done; > > >2. get_json_table() with patch uses JSON_BEHAVIOR_EMPTY as the default > >value for deparsing, while parsing still uses > >JSON_BEHAVIOR_EMPTY_ARRAY. Looks plain wrong. I'm not sure what is > >intention here. > - looks like leftover from older version, changed to default behavior, so unnecessary > emission of ERROR is omitted; > > >3. PLAN clause is always emitted during deparsing even if user didn't > >specify anything. I would prefer to skip PLAN clause in this case > >unless there is strong reason to do the opposite (this reason must be > >pointed if any). > - done, this made test cases much more readable; > > >4. Unused typedefs in src/tools/pgindent/typedefs.list: > >JsonTableScanState, JsonPathSpec, JsonTablePlanStateType, > >JsonTableJoinState. > - done; > > >5. Empty comment in JsonTablePlanState definition. Pointed by Amit, > >but not fixed. > - done; > > >6. Rename passingArgs to passing_Args for no reason in parse_jsontable.c. > - done; > > >7. Patch lacks documentation (also pointed by Amit) > - done, documentation is provided in separate patch; > > >8. Patch could use pgindent run. > - not done yet but would provide a newer version with it. Thank you. I've made following additional changes. 1. Actually, I don't see my previous item 3 fully addressed. We still had PLAN clause deparsed in the case user didn't specify it. I see this as an undesirable behavior. And there are at least two ways to fix this: don't output PLAN clause if it's not the default, remember if user specified as a special flag or even original clause. I found first way (don't output default) more appealing as it don't require additional struct members and we already do this in other similar cases (NULLS FIRST/LAST, ON EMPTY/ON ERROR). So, I did this for PLAN clause. 2. Simplification for JsonTablePlanNextRow(): no reason to check (planstate->advanceNested || planstate->nested) if we already issued break for (planstate->nested == NULL). 3. Similar simplification for transformJsonTableNestedColumns(). ------ Regards, Alexander Korotkov Supabase Attachments: [application/octet-stream] v24-0001-JSON_TABLE-PLAN-Clause-1-3.patch (41.5K, ../../CAPpHfdu-1oDzhKHXRF0vVSqab5Q3V5Sx1iQ5+tOmL_+nqY5_tA@mail.gmail.com/2-v24-0001-JSON_TABLE-PLAN-Clause-1-3.patch) download | inline diff: From abdacdd26b6abc7e2bd5bd97f5fb3dc4abf1eb7f Mon Sep 17 00:00:00 2001 From: Nikita Malakhov <[email protected]> Date: Fri, 15 May 2026 14:37:50 +0300 Subject: [PATCH v24 1/3] JSON_TABLE PLAN Clause (1/3) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch adds the PLAN clauses for JSON_TABLE, which allow the user to specify how data from nested paths are joined, allowing considerable freedom in shaping the tabular output of JSON_TABLE. PLAN DEFAULT allows the user to specify the global strategies when dealing with sibling or child nested paths. The is often sufficient to achieve the necessary goal, and is considerably simpler than the full PLAN clause, which allows the user to specify the strategy to be used for each named nested path. The first patch in series introduces main core changes and grammar extension. Author: Nikita Glukhov <[email protected]> Author: Teodor Sigaev <[email protected]> Author: Oleg Bartunov <[email protected]> Author: Alexander Korotkov <[email protected]> Author: Andrew Dunstan <[email protected]> Author: Amit Langote <[email protected]> Author: Anton Melnikov <[email protected]> Author: Nikita Malakhov <[email protected]> Reviewers have included (in no particular order) Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby, Álvaro Herrera, jian he Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com --- src/backend/nodes/makefuncs.c | 54 ++++ src/backend/parser/gram.y | 101 ++++++- src/backend/parser/parse_jsontable.c | 384 ++++++++++++++++++++++---- src/backend/utils/adt/jsonpath_exec.c | 171 +++++++++--- src/backend/utils/adt/ruleutils.c | 99 +++++++ src/include/nodes/makefuncs.h | 5 + src/include/nodes/parsenodes.h | 43 +++ src/include/nodes/primnodes.h | 2 + src/tools/pgindent/typedefs.list | 3 + 9 files changed, 755 insertions(+), 107 deletions(-) diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 40b09958ac2..cdc02b274ff 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -963,6 +963,60 @@ makeJsonBehavior(JsonBehaviorType btype, Node *expr, int location) return behavior; } +/* + * makeJsonTableDefaultPlan - + * creates a JsonTablePlanSpec node to represent a "default" JSON_TABLE plan + * with given join strategy + */ +Node * +makeJsonTableDefaultPlan(JsonTablePlanJoinType join_type, int location) +{ + JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec); + + n->plan_type = JSTP_DEFAULT; + n->join_type = join_type; + n->location = location; + + return (Node *) n; +} + +/* + * makeJsonTableSimplePlan - + * creates a JsonTablePlanSpec node to represent a "simple" JSON_TABLE plan + * for given PATH + */ +Node * +makeJsonTableSimplePlan(char *pathname, int location) +{ + JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec); + + n->plan_type = JSTP_SIMPLE; + n->pathname = pathname; + n->location = location; + + return (Node *) n; +} + +/* + * makeJsonTableJoinedPlan - + * creates a JsonTablePlanSpec node to represent join between the given + * pair of plans + */ +Node * +makeJsonTableJoinedPlan(JsonTablePlanJoinType type, Node *plan1, Node *plan2, + int location) +{ + JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec); + + n->plan_type = JSTP_JOINED; + n->join_type = type; + n->plan1 = castNode(JsonTablePlanSpec, plan1); + n->plan2 = castNode(JsonTablePlanSpec, plan2); + n->location = location; + + return (Node *) n; +} + /* * makeJsonKeyValue - * creates a JsonKeyValue node diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index ff4e1388c55..9e05a314707 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -672,6 +672,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); json_table json_table_column_definition json_table_column_path_clause_opt + json_table_plan_clause_opt + json_table_plan + json_table_plan_simple + json_table_plan_outer + json_table_plan_inner + json_table_plan_union + json_table_plan_cross + json_table_plan_primary %type <list> json_name_and_value_list json_value_expr_list json_array_aggregate_order_by_clause_opt @@ -683,6 +691,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <ival> json_behavior_type json_predicate_type_constraint json_quotes_clause_opt + json_table_default_plan_choices + json_table_default_plan_inner_outer + json_table_default_plan_union_cross json_wrapper_behavior %type <boolean> json_key_uniqueness_constraint_opt json_object_constructor_null_clause_opt @@ -15188,6 +15199,7 @@ json_table: json_value_expr ',' a_expr json_table_path_name_opt json_passing_clause_opt COLUMNS '(' json_table_column_definition_list ')' + json_table_plan_clause_opt json_on_error_clause_opt ')' { @@ -15199,13 +15211,15 @@ json_table: castNode(A_Const, $5)->val.node.type != T_String) ereport(ERROR, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("only string constants are supported in JSON_TABLE path specification"), + errmsg("only string constants are supported in" + " JSON_TABLE path specification"), parser_errposition(@5)); pathstring = castNode(A_Const, $5)->val.sval.sval; n->pathspec = makeJsonTablePathSpec(pathstring, $6, @5, @6); n->passing = $7; n->columns = $10; - n->on_error = (JsonBehavior *) $12; + n->planspec = (JsonTablePlanSpec *) $12; + n->on_error = (JsonBehavior *) $13; n->location = @1; $$ = (Node *) n; } @@ -15297,8 +15311,7 @@ json_table_column_definition: JsonTableColumn *n = makeNode(JsonTableColumn); n->coltype = JTC_NESTED; - n->pathspec = (JsonTablePathSpec *) - makeJsonTablePathSpec($3, NULL, @3, -1); + n->pathspec = makeJsonTablePathSpec($3, NULL, @3, -1); n->columns = $6; n->location = @1; $$ = (Node *) n; @@ -15309,8 +15322,7 @@ json_table_column_definition: JsonTableColumn *n = makeNode(JsonTableColumn); n->coltype = JTC_NESTED; - n->pathspec = (JsonTablePathSpec *) - makeJsonTablePathSpec($3, $5, @3, @5); + n->pathspec = makeJsonTablePathSpec($3, $5, @3, @5); n->columns = $8; n->location = @1; $$ = (Node *) n; @@ -15329,6 +15341,83 @@ json_table_column_path_clause_opt: { $$ = NULL; } ; +json_table_plan_clause_opt: + PLAN '(' json_table_plan ')' + { $$ = $3; } + | PLAN DEFAULT '(' json_table_default_plan_choices ')' + { $$ = makeJsonTableDefaultPlan($4, @1); } + | /* EMPTY */ + { $$ = NULL; } + ; + +json_table_plan: + json_table_plan_simple + | json_table_plan_outer + | json_table_plan_inner + | json_table_plan_union + | json_table_plan_cross + ; + +json_table_plan_simple: + name + { $$ = makeJsonTableSimplePlan($1, @1); } + ; + +json_table_plan_outer: + json_table_plan_simple OUTER_P json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_OUTER, $1, $3, @1); } + ; + +json_table_plan_inner: + json_table_plan_simple INNER_P json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_INNER, $1, $3, @1); } + ; + +json_table_plan_union: + json_table_plan_primary UNION json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_UNION, $1, $3, @1); } + | json_table_plan_union UNION json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_UNION, $1, $3, @1); } + ; + +json_table_plan_cross: + json_table_plan_primary CROSS json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_CROSS, $1, $3, @1); } + | json_table_plan_cross CROSS json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_CROSS, $1, $3, @1); } + ; + +json_table_plan_primary: + json_table_plan_simple + { $$ = $1; } + | '(' json_table_plan ')' + { + castNode(JsonTablePlanSpec, $2)->location = @1; + $$ = $2; + } + ; + +json_table_default_plan_choices: + json_table_default_plan_inner_outer + { $$ = $1 | JSTP_JOIN_UNION; } + | json_table_default_plan_union_cross + { $$ = $1 | JSTP_JOIN_OUTER; } + | json_table_default_plan_inner_outer ',' json_table_default_plan_union_cross + { $$ = $1 | $3; } + | json_table_default_plan_union_cross ',' json_table_default_plan_inner_outer + { $$ = $1 | $3; } + ; + +json_table_default_plan_inner_outer: + INNER_P { $$ = JSTP_JOIN_INNER; } + | OUTER_P { $$ = JSTP_JOIN_OUTER; } + ; + +json_table_default_plan_union_cross: + UNION { $$ = JSTP_JOIN_UNION; } + | CROSS { $$ = JSTP_JOIN_CROSS; } + ; + /***************************************************************************** * * Type syntax diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c index 32a1e8629b2..7ac8cef3601 100644 --- a/src/backend/parser/parse_jsontable.c +++ b/src/backend/parser/parse_jsontable.c @@ -39,17 +39,22 @@ typedef struct JsonTableParseContext } JsonTableParseContext; static JsonTablePlan *transformJsonTableColumns(JsonTableParseContext *cxt, + JsonTablePlanSpec *planspec, List *columns, List *passingArgs, JsonTablePathSpec *pathspec); static JsonTablePlan *transformJsonTableNestedColumns(JsonTableParseContext *cxt, + JsonTablePlanSpec *plan, List *passingArgs, List *columns); static JsonFuncExpr *transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, - List *passingArgs); + List *passingArgs, + bool errorOnError); static bool isCompositeType(Oid typid); -static JsonTablePlan *makeJsonTablePathScan(JsonTablePathSpec *pathspec, +static JsonTablePlan *makeJsonTablePathScan(JsonTableParseContext *cxt, + JsonTablePathSpec *pathspec, + JsonTablePlanSpec *planspec, bool errorOnError, int colMin, int colMax, JsonTablePlan *childplan); @@ -57,8 +62,14 @@ static void CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt, List *columns); static bool LookupPathOrColumnName(JsonTableParseContext *cxt, char *name); static char *generateJsonTablePathName(JsonTableParseContext *cxt); -static JsonTablePlan *makeJsonTableSiblingJoin(JsonTablePlan *lplan, +static void validateJsonTableChildPlan(ParseState *pstate, + JsonTablePlanSpec *plan, + List *columns); +static JsonTablePlan *makeJsonTableSiblingJoin(bool cross, + JsonTablePlan *lplan, JsonTablePlan *rplan); +static void + appendJsonTableColumns(JsonTableParseContext *cxt, List *columns, List *passingArgs); /* * transformJsonTable - @@ -76,6 +87,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt) TableFunc *tf; JsonFuncExpr *jfe; JsonExpr *je; + JsonTablePlanSpec *plan = jt->planspec; JsonTablePathSpec *rootPathSpec = jt->pathspec; bool is_lateral; JsonTableParseContext cxt = {pstate}; @@ -94,8 +106,21 @@ transformJsonTable(ParseState *pstate, JsonTable *jt) parser_errposition(pstate, jt->on_error->location)); cxt.pathNameId = 0; + if (rootPathSpec->name == NULL) + { + if (jt->planspec != NULL) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE expression"), + errdetail("JSON_TABLE path must contain" + " explicit AS pathname specification if" + " explicit PLAN clause is used"), + parser_errposition(pstate, rootPathSpec->location))); + rootPathSpec->name = generateJsonTablePathName(&cxt); + } + cxt.pathNames = list_make1(rootPathSpec->name); CheckDuplicateColumnOrPathNames(&cxt, jt->columns); @@ -135,7 +160,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt) */ cxt.jt = jt; cxt.tf = tf; - tf->plan = (Node *) transformJsonTableColumns(&cxt, jt->columns, + tf->plan = (Node *) transformJsonTableColumns(&cxt, plan, jt->columns, jt->passing, rootPathSpec); @@ -246,25 +271,110 @@ generateJsonTablePathName(JsonTableParseContext *cxt) * their type/collation information to cxt->tf. */ static JsonTablePlan * -transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, +transformJsonTableColumns(JsonTableParseContext *cxt, + JsonTablePlanSpec *planspec, + List *columns, List *passingArgs, JsonTablePathSpec *pathspec) { - ParseState *pstate = cxt->pstate; JsonTable *jt = cxt->jt; TableFunc *tf = cxt->tf; - ListCell *col; - bool ordinality_found = false; + JsonTablePathScan *scan; + JsonTablePlanSpec *childPlanSpec; + bool defaultPlan = planspec == NULL || + planspec->plan_type == JSTP_DEFAULT; bool errorOnError = jt->on_error && jt->on_error->btype == JSON_BEHAVIOR_ERROR; - Oid contextItemTypid = exprType(tf->docexpr); int colMin, colMax; - JsonTablePlan *childplan; + JsonTablePlan *childplan = NULL; /* Start of column range */ colMin = list_length(tf->colvalexprs); + if (defaultPlan) + childPlanSpec = planspec; + else + { + /* validate parent and child plans */ + JsonTablePlanSpec *parentPlanSpec; + + if (planspec->plan_type == JSTP_JOINED) + { + if (planspec->join_type != JSTP_JOIN_INNER && + planspec->join_type != JSTP_JOIN_OUTER) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan clause"), + errdetail("Expected INNER or OUTER."), + parser_errposition(cxt->pstate, planspec->location))); + + parentPlanSpec = planspec->plan1; + childPlanSpec = planspec->plan2; + + Assert(parentPlanSpec->plan_type != JSTP_JOINED); + Assert(parentPlanSpec->pathname); + } + else + { + parentPlanSpec = planspec; + childPlanSpec = NULL; + } + + if (strcmp(parentPlanSpec->pathname, pathspec->name) != 0) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("PATH name mismatch: expected %s but %s is given.", + pathspec->name, parentPlanSpec->pathname), + parser_errposition(cxt->pstate, planspec->location))); + + validateJsonTableChildPlan(cxt->pstate, childPlanSpec, columns); + } + + appendJsonTableColumns(cxt, columns, passingArgs); + + /* End of column range. */ + if (list_length(tf->colvalexprs) == colMin) + { + /* No columns in this Scan beside the nested ones. */ + colMax = colMin = -1; + } + else + colMax = list_length(tf->colvalexprs) - 1; + + if (childPlanSpec || defaultPlan) + { + /* transform recursively nested columns */ + childplan = transformJsonTableNestedColumns(cxt, childPlanSpec, + columns, passingArgs); + } + + /* transform only non-nested columns */ + scan = (JsonTablePathScan *) makeJsonTablePathScan(cxt, pathspec, + planspec, + errorOnError, + colMin, + colMax, + childplan); + + return (JsonTablePlan *) scan; +} + +/* Append transformed non-nested JSON_TABLE columns to the TableFunc node */ +static void +appendJsonTableColumns(JsonTableParseContext *cxt, List *columns, List *passingArgs) +{ + ListCell *col; + ParseState *pstate = cxt->pstate; + JsonTable *jt = cxt->jt; + TableFunc *tf = cxt->tf; + bool ordinality_found = false; + JsonBehavior *on_error = jt->on_error; + bool errorOnError = on_error && + on_error->btype == JSON_BEHAVIOR_ERROR; + Oid contextItemTypid = exprType(tf->docexpr); + foreach(col, columns) { JsonTableColumn *rawc = castNode(JsonTableColumn, lfirst(col)); @@ -273,12 +383,9 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, Oid typcoll = InvalidOid; Node *colexpr; - if (rawc->coltype != JTC_NESTED) - { - Assert(rawc->name); + if (rawc->name) tf->colnames = lappend(tf->colnames, makeString(pstrdup(rawc->name))); - } /* * Determine the type and typmod for the new column. FOR ORDINALITY @@ -324,7 +431,7 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, param->typeMod = -1; jfe = transformJsonTableColumn(rawc, (Node *) param, - passingArgs); + passingArgs, errorOnError); colexpr = transformExpr(pstate, (Node *) jfe, EXPR_KIND_FROM_FUNCTION); @@ -349,22 +456,6 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, tf->colcollations = lappend_oid(tf->colcollations, typcoll); tf->colvalexprs = lappend(tf->colvalexprs, colexpr); } - - /* End of column range. */ - if (list_length(tf->colvalexprs) == colMin) - { - /* No columns in this Scan beside the nested ones. */ - colMax = colMin = -1; - } - else - colMax = list_length(tf->colvalexprs) - 1; - - /* Recursively transform nested columns */ - childplan = transformJsonTableNestedColumns(cxt, passingArgs, columns); - - /* Create a "parent" scan responsible for all columns handled above. */ - return makeJsonTablePathScan(pathspec, errorOnError, colMin, colMax, - childplan); } /* @@ -395,7 +486,7 @@ isCompositeType(Oid typid) */ static JsonFuncExpr * transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, - List *passingArgs) + List *passingArgs, bool errorOnError) { Node *pathspec; JsonFuncExpr *jfexpr = makeNode(JsonFuncExpr); @@ -437,6 +528,8 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, jfexpr->output->returning->format = jtc->format; jfexpr->on_empty = jtc->on_empty; jfexpr->on_error = jtc->on_error; + if (jfexpr->on_error == NULL && errorOnError) + jfexpr->on_error = makeJsonBehavior(JSON_BEHAVIOR_ERROR, NULL, -1); jfexpr->quotes = jtc->quotes; jfexpr->wrapper = jtc->wrapper; jfexpr->location = jtc->location; @@ -444,57 +537,137 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, return jfexpr; } +static JsonTableColumn * +findNestedJsonTableColumn(List *columns, const char *pathname) +{ + ListCell *lc; + + foreach(lc, columns) + { + JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc)); + + if (jtc->coltype == JTC_NESTED && + jtc->pathspec->name && + !strcmp(jtc->pathspec->name, pathname)) + return jtc; + } + + return NULL; +} + /* * Recursively transform nested columns and create child plan(s) that will be * used to evaluate their row patterns. + * + * Default plan is transformed into a cross/union join of its nested columns. + * Simple and outer/inner plans are transformed into a JsonTablePlan by + * finding and transforming corresponding nested column. + * Sibling plans are recursively transformed into a JsonTableSiblingJoin. */ static JsonTablePlan * transformJsonTableNestedColumns(JsonTableParseContext *cxt, - List *passingArgs, - List *columns) + JsonTablePlanSpec *planspec, + List *columns, + List *passingArgs) { - JsonTablePlan *plan = NULL; - ListCell *lc; + JsonTableColumn *jtc = NULL; - /* - * If there are multiple NESTED COLUMNS clauses in 'columns', their - * respective plans will be combined using a "sibling join" plan, which - * effectively does a UNION of the sets of rows coming from each nested - * plan. - */ - foreach(lc, columns) + if (!planspec || planspec->plan_type == JSTP_DEFAULT) { - JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc)); - JsonTablePlan *nested; + /* unspecified or default plan */ + JsonTablePlan *plan = NULL; + ListCell *lc; + bool cross = planspec && (planspec->join_type & JSTP_JOIN_CROSS); - if (jtc->coltype != JTC_NESTED) - continue; + /* + * If there are multiple NESTED COLUMNS clauses in 'columns', their + * respective plans will be combined using a "sibling join" plan, + * which effectively does a UNION of the sets of rows coming from each + * nested plan. + */ + foreach(lc, columns) + { + JsonTableColumn *col = castNode(JsonTableColumn, lfirst(lc)); + JsonTablePlan *nested; - if (jtc->pathspec->name == NULL) - jtc->pathspec->name = generateJsonTablePathName(cxt); + if (col->coltype != JTC_NESTED) + continue; + + if (col->pathspec->name == NULL) + { + col->pathspec->name = generateJsonTablePathName(cxt); + } - nested = transformJsonTableColumns(cxt, jtc->columns, passingArgs, - jtc->pathspec); + nested = transformJsonTableColumns(cxt, planspec, col->columns, + passingArgs, + col->pathspec); - if (plan) - plan = makeJsonTableSiblingJoin(plan, nested); + /* Join nested plan with previous sibling nested plans. */ + if (plan) + plan = makeJsonTableSiblingJoin(cross, plan, nested); + else + plan = nested; + } + + return plan; + } + else if (planspec->plan_type == JSTP_SIMPLE) + { + jtc = findNestedJsonTableColumn(columns, planspec->pathname); + } + else if (planspec->plan_type == JSTP_JOINED) + { + if (planspec->join_type == JSTP_JOIN_INNER || + planspec->join_type == JSTP_JOIN_OUTER) + { + Assert(planspec->plan1->plan_type == JSTP_SIMPLE); + jtc = findNestedJsonTableColumn(columns, planspec->plan1->pathname); + } else - plan = nested; + { + JsonTablePlan *lplan = transformJsonTableNestedColumns(cxt, + planspec->plan1, + columns, + passingArgs); + JsonTablePlan *rplan = transformJsonTableNestedColumns(cxt, + planspec->plan2, + columns, + passingArgs); + + return makeJsonTableSiblingJoin(planspec->join_type == JSTP_JOIN_CROSS, + lplan, rplan); + } } + else + elog(ERROR, "invalid JSON_TABLE plan type %d", planspec->plan_type); - return plan; + if (!jtc) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan clause"), + errdetail("PATH name was %s not found in nested columns list.", + planspec->pathname), + parser_errposition(cxt->pstate, planspec->location))); + + return transformJsonTableColumns(cxt, planspec, jtc->columns, + passingArgs, + jtc->pathspec); } /* - * Create a JsonTablePlan for given path and ON ERROR behavior. + * Create transformed JSON_TABLE parent plan node by appending all non-nested + * columns to the TableFunc node and remembering their indices in the + * colvalexprs list. * - * colMin and colMin give the range of columns computed by this scan in the + * colMin and colMax give the range of columns computed by this scan in the * global flat list of column expressions that will be passed to the * JSON_TABLE's TableFunc. Both are -1 when all of columns are nested and * thus computed by 'childplan'. */ static JsonTablePlan * -makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError, +makeJsonTablePathScan(JsonTableParseContext *cxt, JsonTablePathSpec *pathspec, + JsonTablePlanSpec *planspec, + bool errorOnError, int colMin, int colMax, JsonTablePlan *childplan) { @@ -518,6 +691,11 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError, scan->colMin = colMin; scan->colMax = colMax; + if (scan->child) + scan->outerJoin = planspec == NULL || + (planspec->join_type & JSTP_JOIN_OUTER); + /* else: default plan case, no children found */ + return (JsonTablePlan *) scan; } @@ -529,13 +707,101 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError, * sets of rows from 'lplan' and 'rplan'. */ static JsonTablePlan * -makeJsonTableSiblingJoin(JsonTablePlan *lplan, JsonTablePlan *rplan) +makeJsonTableSiblingJoin(bool cross, JsonTablePlan *lplan, JsonTablePlan *rplan) { JsonTableSiblingJoin *join = makeNode(JsonTableSiblingJoin); join->plan.type = T_JsonTableSiblingJoin; join->lplan = lplan; join->rplan = rplan; + join->cross = cross; return (JsonTablePlan *) join; } + +/* Collect sibling path names from plan to the specified list. */ +static void +collectSiblingPathsInJsonTablePlan(JsonTablePlanSpec *plan, List **paths) +{ + if (plan->plan_type == JSTP_SIMPLE) + *paths = lappend(*paths, plan->pathname); + else if (plan->plan_type == JSTP_JOINED) + { + if (plan->join_type == JSTP_JOIN_INNER || + plan->join_type == JSTP_JOIN_OUTER) + { + Assert(plan->plan1->plan_type == JSTP_SIMPLE); + *paths = lappend(*paths, plan->plan1->pathname); + } + else if (plan->join_type == JSTP_JOIN_CROSS || + plan->join_type == JSTP_JOIN_UNION) + { + collectSiblingPathsInJsonTablePlan(plan->plan1, paths); + collectSiblingPathsInJsonTablePlan(plan->plan2, paths); + } + else + elog(ERROR, "invalid JSON_TABLE join type %d", + plan->join_type); + } +} + +/* + * Validate child JSON_TABLE plan by checking that: + * - all nested columns have path names specified + * - all nested columns have corresponding node in the sibling plan + * - plan does not contain duplicate or extra nodes + */ +static void +validateJsonTableChildPlan(ParseState *pstate, JsonTablePlanSpec *plan, + List *columns) +{ + ListCell *lc1; + List *siblings = NIL; + int nchildren = 0; + + if (plan) + collectSiblingPathsInJsonTablePlan(plan, &siblings); + + foreach(lc1, columns) + { + JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc1)); + + if (jtc->coltype == JTC_NESTED) + { + ListCell *lc2; + bool found = false; + + if (jtc->pathspec->name == NULL) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("nested JSON_TABLE columns must contain" + " an explicit AS pathname specification" + " if an explicit PLAN clause is used"), + parser_errposition(pstate, jtc->location)); + + /* find nested path name in the list of sibling path names */ + foreach(lc2, siblings) + { + if ((found = !strcmp(jtc->pathspec->name, lfirst(lc2)))) + break; + } + + if (!found) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE specification"), + errdetail("PLAN clause for nested path %s was not found.", + jtc->pathspec->name), + parser_errposition(pstate, jtc->location)); + + nchildren++; + } + } + + if (list_length(siblings) > nchildren) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan clause"), + errdetail("PLAN clause contains some extra or duplicate sibling nodes."), + parser_errposition(pstate, plan ? plan->location : -1)); +} diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index 9bf8ecdcd0c..b69c87d8587 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -240,6 +240,14 @@ typedef struct JsonTablePlanState /* Parent plan, if this is a nested plan */ struct JsonTablePlanState *parent; + + /* Join type */ + bool cross; + bool outerJoin; + /* Planning control fields */ + bool advanceNested; + bool advanceRight; + bool reset; } JsonTablePlanState; /* Random number to identify JsonTableExecContext for sanity checking */ @@ -388,13 +396,13 @@ static JsonTablePlanState *JsonTableInitPlan(JsonTableExecContext *cxt, MemoryContext mcxt); static void JsonTableSetDocument(TableFuncScanState *state, Datum value); static void JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item); +static void JsonTableRescan(JsonTablePlanState *planstate); static bool JsonTableFetchRow(TableFuncScanState *state); static Datum JsonTableGetValue(TableFuncScanState *state, int colnum, Oid typid, int32 typmod, bool *isnull); static void JsonTableDestroyOpaque(TableFuncScanState *state); static bool JsonTablePlanScanNextRow(JsonTablePlanState *planstate); static void JsonTableResetNestedPlan(JsonTablePlanState *planstate); -static bool JsonTablePlanJoinNextRow(JsonTablePlanState *planstate); static bool JsonTablePlanNextRow(JsonTablePlanState *planstate); const TableFuncRoutine JsonbTableRoutine = @@ -4514,6 +4522,7 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan, JsonTablePathScan *scan = (JsonTablePathScan *) plan; int i; + planstate->outerJoin = scan->outerJoin; planstate->path = DatumGetJsonPathP(scan->path->value->constvalue); planstate->args = args; planstate->mcxt = AllocSetContextCreate(mcxt, "JsonTableExecContext", @@ -4533,6 +4542,8 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan, { JsonTableSiblingJoin *join = (JsonTableSiblingJoin *) plan; + planstate->cross = join->cross; + planstate->left = JsonTableInitPlan(cxt, join->lplan, parentstate, args, mcxt); planstate->right = JsonTableInitPlan(cxt, join->rplan, parentstate, @@ -4587,11 +4598,7 @@ JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item) JsonValueListClear(&planstate->found); } - /* Reset plan iterator to the beginning of the item list */ - JsonValueListInitIterator(&planstate->found, &planstate->iter); - planstate->current.value = PointerGetDatum(NULL); - planstate->current.isnull = true; - planstate->ordinal = 0; + JsonTableRescan(planstate); } /* @@ -4602,16 +4609,93 @@ JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item) static bool JsonTablePlanNextRow(JsonTablePlanState *planstate) { - if (IsA(planstate->plan, JsonTablePathScan)) - return JsonTablePlanScanNextRow(planstate); - else if (IsA(planstate->plan, JsonTableSiblingJoin)) - return JsonTablePlanJoinNextRow(planstate); + if (IsA(planstate->plan, JsonTableSiblingJoin)) + { + if (planstate->advanceRight) + { + /* fetch next inner row */ + if (JsonTablePlanNextRow(planstate->right)) + return true; + + /* inner rows are exhausted */ + if (planstate->cross) + planstate->advanceRight = false; /* next outer row */ + else + return false; /* end of scan */ + } + + while (!planstate->advanceRight) + { + /* fetch next outer row */ + bool more = JsonTablePlanNextRow(planstate->left); + + if (planstate->cross) + { + if (!more) + return false; /* end of scan */ + + JsonTableRescan(planstate->right); + + if (!JsonTablePlanNextRow(planstate->right)) + continue; /* next outer row */ + + planstate->advanceRight = true; /* next inner row */ + } + else if (!more) + { + if (!JsonTablePlanNextRow(planstate->right)) + return false; /* end of scan */ + + planstate->advanceRight = true; /* next inner row */ + } + + break; + } + } else - elog(ERROR, "invalid JsonTablePlan %d", (int) planstate->plan->type); + { + /* reset context item if requested */ + if (planstate->reset) + { + JsonTablePlanState *parent = planstate->parent; + + Assert(parent != NULL && !parent->current.isnull); + JsonTableResetRowPattern(planstate, parent->current.value); + planstate->reset = false; + } + + if (planstate->advanceNested) + { + /* fetch next nested row */ + planstate->advanceNested = JsonTablePlanNextRow(planstate->nested); + if (planstate->advanceNested) + return true; + } + + for (;;) + { + if (!JsonTablePlanScanNextRow(planstate)) + return false; + + if (planstate->nested == NULL) + break; + + JsonTableResetNestedPlan(planstate->nested); + planstate->advanceNested = JsonTablePlanNextRow(planstate->nested); + + if (!planstate->advanceNested && !planstate->outerJoin) + continue; - Assert(false); - /* Appease compiler */ - return false; + /* + * We have a row to return: either the nested plan produced one, + * or this is an outer join and we emit the parent row with the + * nested columns set to NULL. + */ + break; + } + } + + return true; } /* @@ -4697,47 +4781,27 @@ JsonTableResetNestedPlan(JsonTablePlanState *planstate) { JsonTablePlanState *parent = planstate->parent; - if (!parent->current.isnull) - JsonTableResetRowPattern(planstate, parent->current.value); + planstate->reset = true; + planstate->advanceNested = false; + + if (planstate->nested) + JsonTableResetNestedPlan(planstate->nested); /* * If this plan itself has a child nested plan, it will be reset when * the caller calls JsonTablePlanNextRow() on this plan. */ + if (!parent->current.isnull) + JsonTableResetRowPattern(planstate, parent->current.value); } else if (IsA(planstate->plan, JsonTableSiblingJoin)) { JsonTableResetNestedPlan(planstate->left); JsonTableResetNestedPlan(planstate->right); + planstate->advanceRight = false; } } -/* - * Fetch the next row from a JsonTableSiblingJoin. - * - * This is essentially a UNION between the rows from left and right siblings. - */ -static bool -JsonTablePlanJoinNextRow(JsonTablePlanState *planstate) -{ - - /* Fetch row from left sibling. */ - if (!JsonTablePlanNextRow(planstate->left)) - { - /* - * Left sibling ran out of rows, so start fetching from the right - * sibling. - */ - if (!JsonTablePlanNextRow(planstate->right)) - { - /* Right sibling ran out of rows too, so there are no more rows. */ - return false; - } - } - - return true; -} - /* * JsonTableFetchRow * Prepare the next "current" row for upcoming GetValue calls. @@ -4802,3 +4866,26 @@ JsonTableGetValue(TableFuncScanState *state, int colnum, return result; } + +/* Recursively reset planstate and its child nodes */ +static void +JsonTableRescan(JsonTablePlanState *planstate) +{ + if (IsA(planstate->plan, JsonTablePathScan)) + { + /* Reset plan iterator to the beginning of the item list */ + JsonValueListInitIterator(&planstate->found, &planstate->iter); + planstate->current.value = PointerGetDatum(NULL); + planstate->current.isnull = true; + planstate->ordinal = 0; + + if (planstate->nested) + JsonTableRescan(planstate->nested); + } + else if (IsA(planstate->plan, JsonTableSiblingJoin)) + { + JsonTableRescan(planstate->left); + JsonTableRescan(planstate->right); + planstate->advanceRight = false; + } +} diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 88de5c0481c..8b725ed1adb 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -12675,6 +12675,89 @@ get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan, } } +/* + * json_table_plan_is_default - does this plan match the implicit default? + * + * When no PLAN clause is given, JSON_TABLE builds a default plan that joins + * every nested path to its parent with OUTER and every set of sibling paths + * with UNION (see transformJsonTableColumns()). Such a plan is fully implied + * by the NESTED COLUMNS structure, so we need not (and, to match the input, + * should not) print a PLAN clause for it; we only deparse a PLAN clause when + * the plan deviates from the default, i.e. uses an INNER or CROSS join + * somewhere. This follows the usual ruleutils convention of omitting a clause + * that merely restates the default (cf. get_json_expr_options() for ON + * EMPTY/ON ERROR, or the NULLS FIRST/LAST handling in get_rule_orderby()). + */ +static bool +json_table_plan_is_default(JsonTablePlan *plan) +{ + if (IsA(plan, JsonTablePathScan)) + { + JsonTablePathScan *scan = castNode(JsonTablePathScan, plan); + + if (scan->child) + { + if (!scan->outerJoin) + return false; /* INNER is not the default */ + return json_table_plan_is_default(scan->child); + } + + return true; + } + else + { + JsonTableSiblingJoin *join = castNode(JsonTableSiblingJoin, plan); + + if (join->cross) + return false; /* CROSS is not the default */ + return json_table_plan_is_default(join->lplan) && + json_table_plan_is_default(join->rplan); + } +} + +/* + * get_json_table_plan - Parse back a JSON_TABLE plan + */ +static void +get_json_table_plan(TableFunc *tf, JsonTablePlan *plan, deparse_context *context, + bool parenthesize) +{ + if (parenthesize) + appendStringInfoChar(context->buf, '('); + + if (IsA(plan, JsonTablePathScan)) + { + JsonTablePathScan *s = castNode(JsonTablePathScan, plan); + + appendStringInfoString(context->buf, quote_identifier(s->path->name)); + + if (s->child) + { + appendStringInfoString(context->buf, + s->outerJoin ? " OUTER " : " INNER "); + get_json_table_plan(tf, s->child, context, + IsA(s->child, JsonTableSiblingJoin)); + } + } + else if (IsA(plan, JsonTableSiblingJoin)) + { + JsonTableSiblingJoin *j = (JsonTableSiblingJoin *) plan; + + get_json_table_plan(tf, j->lplan, context, + IsA(j->lplan, JsonTableSiblingJoin) || + castNode(JsonTablePathScan, j->lplan)->child); + + appendStringInfoString(context->buf, j->cross ? " CROSS " : " UNION "); + + get_json_table_plan(tf, j->rplan, context, + IsA(j->rplan, JsonTableSiblingJoin) || + castNode(JsonTablePathScan, j->rplan)->child); + } + + if (parenthesize) + appendStringInfoChar(context->buf, ')'); +} + /* * get_json_table_columns - Parse back JSON_TABLE columns */ @@ -12684,6 +12767,7 @@ get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan, bool showimplicit) { StringInfo buf = context->buf; + JsonExpr *jexpr = castNode(JsonExpr, tf->docexpr); ListCell *lc_colname; ListCell *lc_coltype; ListCell *lc_coltypmod; @@ -12763,6 +12847,9 @@ get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan, default_behavior = JSON_BEHAVIOR_NULL; } + if (jexpr->on_error->btype == JSON_BEHAVIOR_ERROR) + default_behavior = JSON_BEHAVIOR_ERROR; + appendStringInfoString(buf, " PATH "); get_json_path_spec(colexpr->path_spec, context, showimplicit); @@ -12840,6 +12927,18 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit) get_json_table_columns(tf, castNode(JsonTablePathScan, tf->plan), context, showimplicit); + /* + * Deparse a PLAN clause only for a non-default plan; the default plan is + * implied by the NESTED COLUMNS structure (see + * json_table_plan_is_default). + */ + if (root->child && !json_table_plan_is_default((JsonTablePlan *) root)) + { + appendStringInfoChar(buf, ' '); + appendContextKeyword(context, "PLAN ", 0, 0, 0); + get_json_table_plan(tf, (JsonTablePlan *) root, context, true); + } + if (jexpr->on_error->btype != JSON_BEHAVIOR_EMPTY_ARRAY) get_json_behavior(jexpr->on_error, context, "ERROR"); diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index bf54d39feb0..4c9d2ec7c09 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -124,5 +124,10 @@ extern JsonTablePath *makeJsonTablePath(Const *pathvalue, char *pathname); extern JsonTablePathSpec *makeJsonTablePathSpec(char *string, char *name, int string_location, int name_location); +extern Node *makeJsonTableDefaultPlan(JsonTablePlanJoinType join_type, + int location); +extern Node *makeJsonTableSimplePlan(char *pathname, int location); +extern Node *makeJsonTableJoinedPlan(JsonTablePlanJoinType type, + Node *plan1, Node *plan2, int location); #endif /* MAKEFUNC_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index e03556399ab..77f69f94607 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1982,6 +1982,48 @@ typedef struct JsonTablePathSpec ParseLoc location; /* location of 'string' */ } JsonTablePathSpec; +/* + * JsonTablePlanType - + * flags for JSON_TABLE plan node types representation + */ +typedef enum JsonTablePlanType +{ + JSTP_DEFAULT, + JSTP_SIMPLE, + JSTP_JOINED, +} JsonTablePlanType; + +/* + * JsonTablePlanJoinType - + * JSON_TABLE join types for JSTP_JOINED plans + */ +typedef enum JsonTablePlanJoinType +{ + JSTP_JOIN_INNER = 0x01, + JSTP_JOIN_OUTER = 0x02, + JSTP_JOIN_CROSS = 0x04, + JSTP_JOIN_UNION = 0x08, +} JsonTablePlanJoinType; + +/* + * JsonTablePlanSpec - + * untransformed representation of JSON_TABLE's PLAN clause + */ +typedef struct JsonTablePlanSpec +{ + NodeTag type; + + JsonTablePlanType plan_type; /* plan type */ + JsonTablePlanJoinType join_type; /* join type (for joined plan only) */ + char *pathname; /* path name (for simple plan only) */ + + /* For joined plans */ + struct JsonTablePlanSpec *plan1; /* first joined plan */ + struct JsonTablePlanSpec *plan2; /* second joined plan */ + + ParseLoc location; /* token location, or -1 if unknown */ +} JsonTablePlanSpec; + /* * JsonTable - * untransformed representation of JSON_TABLE @@ -1993,6 +2035,7 @@ typedef struct JsonTable JsonTablePathSpec *pathspec; /* JSON path specification */ List *passing; /* list of PASSING clause arguments, if any */ List *columns; /* list of JsonTableColumn */ + JsonTablePlanSpec *planspec; /* join plan, if specified */ JsonBehavior *on_error; /* ON ERROR behavior */ Alias *alias; /* table alias in FROM clause */ bool lateral; /* does it have LATERAL prefix? */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index bb05aeebee4..cacef7d4151 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1926,6 +1926,7 @@ typedef struct JsonTablePathScan /* Plan(s) for nested columns, if any. */ JsonTablePlan *child; + bool outerJoin; /* outer or inner join for nested columns? */ /* * 0-based index in TableFunc.colvalexprs of the 1st and the last column @@ -1947,6 +1948,7 @@ typedef struct JsonTableSiblingJoin JsonTablePlan *lplan; JsonTablePlan *rplan; + bool cross; } JsonTableSiblingJoin; /* ---------------- diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 117e7379f10..887a61fa7f3 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1508,7 +1508,10 @@ JsonTablePathScan JsonTablePathSpec JsonTablePlan JsonTablePlanRowSource +JsonTablePlanSpec JsonTablePlanState +JsonTablePlanJoinType +JsonTablePlanType JsonTableSiblingJoin JsonTokenType JsonTransformStringValuesAction -- 2.39.5 (Apple Git-154) [application/octet-stream] v24-0002-JSON_TABLE-PLAN-Clause-2-3.patch (43.4K, ../../CAPpHfdu-1oDzhKHXRF0vVSqab5Q3V5Sx1iQ5+tOmL_+nqY5_tA@mail.gmail.com/3-v24-0002-JSON_TABLE-PLAN-Clause-2-3.patch) download | inline diff: From 5b45593a6528b9a1c05f88a8f7484dbbd414f73f Mon Sep 17 00:00:00 2001 From: Nikita Malakhov <[email protected]> Date: Fri, 15 May 2026 14:39:41 +0300 Subject: [PATCH v24 2/3] JSON_TABLE PLAN Clause (2/3) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch adds the PLAN clauses for JSON_TABLE, which allow the user to specify how data from nested paths are joined, allowing considerable freedom in shaping the tabular output of JSON_TABLE. PLAN DEFAULT allows the user to specify the global strategies when dealing with sibling or child nested paths. The is often sufficient to achieve the necessary goal, and is considerably simpler than the full PLAN clause, which allows the user to specify the strategy to be used for each named nested path. The second patch in series provides test cases and results changes for PLAN clause. Author: Nikita Glukhov <[email protected]> Author: Teodor Sigaev <[email protected]> Author: Oleg Bartunov <[email protected]> Author: Alexander Korotkov <[email protected]> Author: Andrew Dunstan <[email protected]> Author: Amit Langote <[email protected]> Author: Anton Melnikov <[email protected]> Author: Nikita Malakhov <[email protected]> Reviewers have included (in no particular order) Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby, Álvaro Herrera, jian he Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com --- .../regress/expected/sqljson_jsontable.out | 633 +++++++++++++++++- src/test/regress/sql/sqljson_jsontable.sql | 417 ++++++++++++ 2 files changed, 1049 insertions(+), 1 deletion(-) diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index 458c5aaa5b0..83dc7b78e1f 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -774,6 +774,222 @@ SELECT * FROM JSON_TABLE( ERROR: duplicate JSON_TABLE column or path name: a LINE 10: NESTED PATH '$' AS a ^ +-- JSON_TABLE: nested paths and plans +-- Should fail (JSON_TABLE columns must contain explicit AS path +-- specifications if explicit PLAN clause is used) +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' -- AS <path name> required here + COLUMNS ( + foo int PATH '$' + ) + PLAN DEFAULT (UNION) +) jt; +ERROR: invalid JSON_TABLE expression +LINE 2: jsonb '[]', '$' -- AS <path name> required here + ^ +DETAIL: JSON_TABLE path must contain explicit AS pathname specification if explicit PLAN clause is used +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( -- AS <path name> required here + foo int PATH '$' + ) + ) + PLAN DEFAULT (UNION) +) jt; + foo +----- + +(1 row) + +-- JSON_TABLE: plan validation +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p1) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 12: PLAN (p1) + ^ +DETAIL: PATH name mismatch: expected p0 but p1 is given. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 4: NESTED PATH '$' AS p1 COLUMNS ( + ^ +DETAIL: PLAN clause for nested path p1 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER p3) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 4: NESTED PATH '$' AS p1 COLUMNS ( + ^ +DETAIL: PLAN clause for nested path p1 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 UNION p1 UNION p11) +) jt; +ERROR: invalid JSON_TABLE plan clause +LINE 12: PLAN (p0 UNION p1 UNION p11) + ^ +DETAIL: Expected INNER or OUTER. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p13)) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 8: NESTED PATH '$' AS p2 COLUMNS ( + ^ +DETAIL: PLAN clause for nested path p2 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 5: NESTED PATH '$' AS p11 COLUMNS ( foo ... + ^ +DETAIL: PLAN clause for nested path p11 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 UNION p11) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE plan clause +LINE 12: PLAN (p0 OUTER ((p1 UNION p11) CROSS p2)) + ^ +DETAIL: PLAN clause contains some extra or duplicate sibling nodes. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER p11) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 6: NESTED PATH '$' AS p12 COLUMNS ( bar ... + ^ +DETAIL: PLAN clause for nested path p12 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 9: NESTED PATH '$' AS p21 COLUMNS ( baz ... + ^ +DETAIL: PLAN clause for nested path p21 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))) +) jt; + bar | foo | baz +-----+-----+----- +(0 rows) + +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' -- without root path name + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)) +) jt; +ERROR: invalid JSON_TABLE expression +LINE 2: jsonb 'null', 'strict $[*]' -- without root path name + ^ +DETAIL: JSON_TABLE path must contain explicit AS pathname specification if explicit PLAN clause is used -- JSON_TABLE: plan execution CREATE TEMP TABLE jsonb_table_test (js jsonb); INSERT INTO jsonb_table_test @@ -813,6 +1029,325 @@ from 4 | -1 | 2 | 2 | | (11 rows) +-- default plan (outer, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, union) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(11 rows) + +-- specific plan (p outer (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb union pc)) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(11 rows) + +-- specific plan (p outer (pc union pb)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pc union pb)) + ) jt; + n | a | c | b +---+----+----+--- + 1 | 1 | | + 2 | 2 | 10 | + 2 | 2 | | + 2 | 2 | 20 | + 2 | 2 | | 1 + 2 | 2 | | 2 + 2 | 2 | | 3 + 3 | 3 | | 1 + 3 | 3 | | 2 + 4 | -1 | | 1 + 4 | -1 | | 2 +(11 rows) + +-- default plan (inner, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (inner) + ) jt; + n | a | b | c +---+----+---+---- + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(10 rows) + +-- specific plan (p inner (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb union pc)) + ) jt; + n | a | b | c +---+----+---+---- + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(10 rows) + +-- default plan (inner, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (cross, inner) + ) jt; + n | a | b | c +---+---+---+---- + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 +(9 rows) + +-- specific plan (p inner (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb cross pc)) + ) jt; + n | a | b | c +---+---+---+---- + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 +(9 rows) + +-- default plan (outer, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, cross) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 + 3 | 3 | | + 4 | -1 | | +(12 rows) + +-- specific plan (p outer (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb cross pc)) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 + 3 | 3 | | + 4 | -1 | | +(12 rows) + +select + jt.*, b1 + 100 as b +from + json_table (jsonb + '[ + {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]}, + {"a": 2, "b": [10, 20], "c": [1, null, 2]}, + {"x": "3", "b": [11, 22, 33, 44]} + ]', + '$[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on error, + nested path 'strict $.b[*]' as pb columns ( + b text format json path '$', + nested path 'strict $[*]' as pb1 columns ( + b1 int path '$' + ) + ), + nested path 'strict $.c[*]' as pc columns ( + c text format json path '$', + nested path 'strict $[*]' as pc1 columns ( + c1 int path '$' + ) + ) + ) + --plan default(outer, cross) + plan(p outer ((pb inner pb1) cross (pc outer pc1))) + ) jt; + n | a | b | b1 | c | c1 | b +---+---+--------------+-----+------+----+----- + 1 | 1 | [1, 10] | 1 | 1 | | 101 + 1 | 1 | [1, 10] | 1 | null | | 101 + 1 | 1 | [1, 10] | 1 | 2 | | 101 + 1 | 1 | [1, 10] | 10 | 1 | | 110 + 1 | 1 | [1, 10] | 10 | null | | 110 + 1 | 1 | [1, 10] | 10 | 2 | | 110 + 1 | 1 | [2] | 2 | 1 | | 102 + 1 | 1 | [2] | 2 | null | | 102 + 1 | 1 | [2] | 2 | 2 | | 102 + 1 | 1 | [3, 30, 300] | 3 | 1 | | 103 + 1 | 1 | [3, 30, 300] | 3 | null | | 103 + 1 | 1 | [3, 30, 300] | 3 | 2 | | 103 + 1 | 1 | [3, 30, 300] | 30 | 1 | | 130 + 1 | 1 | [3, 30, 300] | 30 | null | | 130 + 1 | 1 | [3, 30, 300] | 30 | 2 | | 130 + 1 | 1 | [3, 30, 300] | 300 | 1 | | 400 + 1 | 1 | [3, 30, 300] | 300 | null | | 400 + 1 | 1 | [3, 30, 300] | 300 | 2 | | 400 + 2 | 2 | | | | | + 3 | | | | | | +(20 rows) + -- PASSING arguments are passed to nested paths and their columns' paths SELECT * FROM @@ -876,6 +1411,7 @@ SELECT * FROM ) ) ); +CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo'); \sv jsonb_table_view_nested CREATE OR REPLACE VIEW public.jsonb_table_view_nested AS SELECT id, @@ -913,7 +1449,102 @@ CREATE OR REPLACE VIEW public.jsonb_table_view_nested AS ) ) ) +CREATE OR REPLACE VIEW public.jsonb_table_view AS + SELECT id, + "int", + text, + "char(4)", + bool, + "numeric", + domain, + js, + jb, + jst, + jsc, + jsv, + jsb, + jsbq, + aaa, + aaa1, + exists1, + exists2, + exists3, + js2, + jsb2w, + jsb2q, + ia, + ta, + jba, + a1, + b1, + a11, + a21, + a22 + FROM JSON_TABLE( + 'null'::jsonb, '$[*]' AS json_table_path_1 + PASSING + 1 + 2 AS a, + '"foo"'::json AS "b c" + COLUMNS ( + id FOR ORDINALITY, + "int" integer PATH '$', + text text PATH '$', + "char(4)" character(4) PATH '$', + bool boolean PATH '$', + "numeric" numeric PATH '$', + domain jsonb_test_domain PATH '$', + js json PATH '$', + jb jsonb PATH '$', + jst text FORMAT JSON PATH '$', + jsc character(4) FORMAT JSON PATH '$', + jsv character varying(4) FORMAT JSON PATH '$', + jsb jsonb PATH '$', + jsbq jsonb PATH '$' OMIT QUOTES, + aaa integer PATH '$."aaa"', + aaa1 integer PATH '$."aaa"', + exists1 boolean EXISTS PATH '$."aaa"', + exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, + exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, + js2 json PATH '$', + jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, + jsb2q jsonb PATH '$' OMIT QUOTES, + ia integer[] PATH '$', + ta text[] PATH '$', + jba jsonb[] PATH '$', + NESTED PATH '$[1]' AS p1 + COLUMNS ( + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + NESTED PATH '$[*]' AS "p1 1" + COLUMNS ( + a11 text PATH '$."a11"' + ) + ), + NESTED PATH '$[2]' AS p2 + COLUMNS ( + NESTED PATH '$[*]' AS "p2:1" + COLUMNS ( + a21 text PATH '$."a21"' + ), + NESTED PATH '$[*]' AS p22 + COLUMNS ( + a22 text PATH '$."a22"' + ) + ) + ) + PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))) + ); +EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Table Function Scan on "json_table" + Output: "json_table".id, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22 + Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$', js json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsbq jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES, jsb2q jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"')))) +(3 rows) + +DROP VIEW jsonb_table_view; DROP VIEW jsonb_table_view_nested; +DROP DOMAIN jsonb_test_domain; CREATE TABLE s (js jsonb); INSERT INTO s VALUES ('{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]},{"z22": [32, 204,145]}]},"c": 3}'), @@ -1152,7 +1783,7 @@ CREATE OR REPLACE VIEW public.json_table_view9 AS FROM JSON_TABLE( '"a"'::text, '$' AS json_table_path_0 COLUMNS ( - a text PATH '$' + a text PATH '$' NULL ON EMPTY ) ERROR ON ERROR ) DROP VIEW json_table_view8, json_table_view9; diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index 154eea79c76..27b86d8a281 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -376,6 +376,170 @@ SELECT * FROM JSON_TABLE( ) ) jt; +-- JSON_TABLE: nested paths and plans +-- Should fail (JSON_TABLE columns must contain explicit AS path +-- specifications if explicit PLAN clause is used) +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' -- AS <path name> required here + COLUMNS ( + foo int PATH '$' + ) + PLAN DEFAULT (UNION) +) jt; +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( -- AS <path name> required here + foo int PATH '$' + ) + ) + PLAN DEFAULT (UNION) +) jt; + +-- JSON_TABLE: plan validation +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p1) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER p3) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 UNION p1 UNION p11) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p13)) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p2)) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 UNION p11) CROSS p2)) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER p11) CROSS p2)) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2)) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' -- without root path name + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)) +) jt; -- JSON_TABLE: plan execution @@ -405,6 +569,170 @@ from ) ) jt; +-- default plan (outer, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, union) + ) jt; +-- specific plan (p outer (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb union pc)) + ) jt; +-- specific plan (p outer (pc union pb)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pc union pb)) + ) jt; +-- default plan (inner, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (inner) + ) jt; +-- specific plan (p inner (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb union pc)) + ) jt; +-- default plan (inner, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (cross, inner) + ) jt; +-- specific plan (p inner (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb cross pc)) + ) jt; +-- default plan (outer, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, cross) + ) jt; +-- specific plan (p outer (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb cross pc)) + ) jt; +select + jt.*, b1 + 100 as b +from + json_table (jsonb + '[ + {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]}, + {"a": 2, "b": [10, 20], "c": [1, null, 2]}, + {"x": "3", "b": [11, 22, 33, 44]} + ]', + '$[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on error, + nested path 'strict $.b[*]' as pb columns ( + b text format json path '$', + nested path 'strict $[*]' as pb1 columns ( + b1 int path '$' + ) + ), + nested path 'strict $.c[*]' as pc columns ( + c text format json path '$', + nested path 'strict $[*]' as pc1 columns ( + c1 int path '$' + ) + ) + ) + --plan default(outer, cross) + plan(p outer ((pb inner pb1) cross (pc outer pc1))) + ) jt; -- PASSING arguments are passed to nested paths and their columns' paths SELECT * @@ -450,8 +778,97 @@ SELECT * FROM ) ); +CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo'); \sv jsonb_table_view_nested +CREATE OR REPLACE VIEW public.jsonb_table_view AS + SELECT id, + "int", + text, + "char(4)", + bool, + "numeric", + domain, + js, + jb, + jst, + jsc, + jsv, + jsb, + jsbq, + aaa, + aaa1, + exists1, + exists2, + exists3, + js2, + jsb2w, + jsb2q, + ia, + ta, + jba, + a1, + b1, + a11, + a21, + a22 + FROM JSON_TABLE( + 'null'::jsonb, '$[*]' AS json_table_path_1 + PASSING + 1 + 2 AS a, + '"foo"'::json AS "b c" + COLUMNS ( + id FOR ORDINALITY, + "int" integer PATH '$', + text text PATH '$', + "char(4)" character(4) PATH '$', + bool boolean PATH '$', + "numeric" numeric PATH '$', + domain jsonb_test_domain PATH '$', + js json PATH '$', + jb jsonb PATH '$', + jst text FORMAT JSON PATH '$', + jsc character(4) FORMAT JSON PATH '$', + jsv character varying(4) FORMAT JSON PATH '$', + jsb jsonb PATH '$', + jsbq jsonb PATH '$' OMIT QUOTES, + aaa integer PATH '$."aaa"', + aaa1 integer PATH '$."aaa"', + exists1 boolean EXISTS PATH '$."aaa"', + exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, + exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, + js2 json PATH '$', + jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, + jsb2q jsonb PATH '$' OMIT QUOTES, + ia integer[] PATH '$', + ta text[] PATH '$', + jba jsonb[] PATH '$', + NESTED PATH '$[1]' AS p1 + COLUMNS ( + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + NESTED PATH '$[*]' AS "p1 1" + COLUMNS ( + a11 text PATH '$."a11"' + ) + ), + NESTED PATH '$[2]' AS p2 + COLUMNS ( + NESTED PATH '$[*]' AS "p2:1" + COLUMNS ( + a21 text PATH '$."a21"' + ), + NESTED PATH '$[*]' AS p22 + COLUMNS ( + a22 text PATH '$."a22"' + ) + ) + ) + PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))) + ); +EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; +DROP VIEW jsonb_table_view; DROP VIEW jsonb_table_view_nested; +DROP DOMAIN jsonb_test_domain; CREATE TABLE s (js jsonb); INSERT INTO s VALUES -- 2.39.5 (Apple Git-154) [application/octet-stream] v24-0003-JSON_TABLE-PLAN-Clause-3-3.patch (9.6K, ../../CAPpHfdu-1oDzhKHXRF0vVSqab5Q3V5Sx1iQ5+tOmL_+nqY5_tA@mail.gmail.com/4-v24-0003-JSON_TABLE-PLAN-Clause-3-3.patch) download | inline diff: From 4d3be029396ea85f6260ea9f9cf538ab62145cf1 Mon Sep 17 00:00:00 2001 From: Nikita Malakhov <[email protected]> Date: Fri, 15 May 2026 14:41:17 +0300 Subject: [PATCH v24 3/3] JSON_TABLE PLAN Clause (3/3) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch adds the PLAN clauses for JSON_TABLE, which allow the user to specify how data from nested paths are joined, allowing considerable freedom in shaping the tabular output of JSON_TABLE. PLAN DEFAULT allows the user to specify the global strategies when dealing with sibling or child nested paths. The is often sufficient to achieve the necessary goal, and is considerably simpler than the full PLAN clause, which allows the user to specify the strategy to be used for each named nested path. The third patch in series provides documentation for PLAN clause. Author: Nikita Glukhov <[email protected]> Author: Teodor Sigaev <[email protected]> Author: Oleg Bartunov <[email protected]> Author: Alexander Korotkov <[email protected]> Author: Andrew Dunstan <[email protected]> Author: Amit Langote <[email protected]> Author: Anton Melnikov <[email protected]> Author: Nikita Malakhov <[email protected]> Reviewers have included (in no particular order) Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby, Álvaro Herrera, jian he Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com --- doc/src/sgml/func/func-json.sgml | 170 ++++++++++++++++++++++++++++++- 1 file changed, 168 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml index 3d97e2b5375..d6114e97341 100644 --- a/doc/src/sgml/func/func-json.sgml +++ b/doc/src/sgml/func/func-json.sgml @@ -3663,6 +3663,11 @@ JSON_TABLE ( <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> <optional> PASSING { <replaceable>value</replaceable> AS <replaceable>varname</replaceable> } <optional>, ...</optional> </optional> COLUMNS ( <replaceable class="parameter">json_table_column</replaceable> <optional>, ...</optional> ) <optional> { <literal>ERROR</literal> | <literal>EMPTY</literal> <optional>ARRAY</optional>} <literal>ON ERROR</literal> </optional> + <optional> + PLAN ( <replaceable class="parameter">json_table_plan</replaceable> ) | + PLAN DEFAULT ( { INNER | OUTER } <optional> , { CROSS | UNION } </optional> + | { CROSS | UNION } <optional> , { INNER | OUTER } </optional> ) + </optional> ) <phrase> @@ -3679,6 +3684,16 @@ where <replaceable class="parameter">json_table_column</replaceable> is: | <replaceable>name</replaceable> <replaceable>type</replaceable> EXISTS <optional> PATH <replaceable>path_expression</replaceable> </optional> <optional> { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR </optional> | NESTED <optional> PATH </optional> <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> ) +<phrase> +<replaceable>json_table_plan</replaceable> is: +</phrase> + <replaceable>json_path_name</replaceable> <optional> { OUTER | INNER } <replaceable>json_table_plan_primary</replaceable> </optional> + | <replaceable>json_table_plan_primary</replaceable> { UNION <replaceable>json_table_plan_primary</replaceable> } <optional>...</optional> + | <replaceable>json_table_plan_primary</replaceable> { CROSS <replaceable>json_table_plan_primary</replaceable> } <optional>...</optional> +<phrase> +<replaceable>json_table_plan_primary</replaceable> is: +</phrase> + <replaceable>json_path_name</replaceable> | ( <replaceable>json_table_plan</replaceable> ) </synopsis> <para> @@ -3842,6 +3857,11 @@ where <replaceable class="parameter">json_table_column</replaceable> is: in a single function invocation rather than chaining several <function>JSON_TABLE</function> expressions in an SQL statement. </para> + + <para> + You can use the <literal>PLAN</literal> clause to define how + to join the columns returned by <literal>NESTED PATH</literal> clauses. + </para> </listitem> </varlistentry> </variablelist> @@ -3866,9 +3886,121 @@ where <replaceable class="parameter">json_table_column</replaceable> is: <para> The optional <replaceable>json_path_name</replaceable> serves as an - identifier of the provided <replaceable>path_expression</replaceable>. - The name must be unique and distinct from the column names. + identifier of the provided <replaceable>json_path_specification</replaceable>. + The path name must be unique and distinct from the column names. + When using the <literal>PLAN</literal> clause, you must specify the names + for all the paths, including the row pattern. Each path name can appear in + the <literal>PLAN</literal> clause only once. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <literal>PLAN</literal> ( <replaceable>json_table_plan</replaceable> ) + </term> + <listitem> + + <para> + Defines how to join the data returned by <literal>NESTED PATH</literal> + clauses to the constructed view. + </para> + <para> + To join columns with parent/child relationship, you can use: + </para> + <variablelist> + <varlistentry> + <term> + <literal>INNER</literal> + </term> + <listitem> + + <para> + Use <literal>INNER JOIN</literal>, so that the parent row + is omitted from the output if it does not have any child rows + after joining the data returned by <literal>NESTED PATH</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <literal>OUTER</literal> + </term> + <listitem> + + <para> + Use <literal>LEFT OUTER JOIN</literal>, so that the parent row + is always included into the output even if it does not have any child rows + after joining the data returned by <literal>NESTED PATH</literal>, with NULL values + inserted into the child columns if the corresponding + values are missing. + </para> + <para> + This is the default option for joining columns with parent/child relationship. + </para> + </listitem> + </varlistentry> + </variablelist> + <para> + To join sibling columns, you can use: </para> + + <variablelist> + <varlistentry> + <term> + <literal>UNION</literal> + </term> + <listitem> + + <para> + Generate one row for each value produced by each of the sibling + columns. The columns from the other siblings are set to null. + </para> + <para> + This is the default option for joining sibling columns. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <literal>CROSS</literal> + </term> + <listitem> + + <para> + Generate one row for each combination of values from the sibling columns. + </para> + </listitem> + </varlistentry> + + </variablelist> + + </listitem> + </varlistentry> + + <varlistentry> + <term> + <literal>PLAN DEFAULT</literal> ( <literal><replaceable>OUTER | INNER</replaceable> <optional>, <replaceable>UNION | CROSS</replaceable> </optional></literal> ) + </term> + <listitem> + <para> + The terms can also be specified in reverse order. The + <literal>INNER</literal> or <literal>OUTER</literal> option defines the + joining plan for parent/child columns, while <literal>UNION</literal> or + <literal>CROSS</literal> affects joins of sibling columns. This form + of <literal>PLAN</literal> overrides the default plan for + all columns at once. Even though the path names are not included in the + <literal>PLAN DEFAULT</literal> form, to conform to the SQL/JSON standard + they must be provided for all the paths if the <literal>PLAN</literal> + clause is used. + </para> + <para> + <literal>PLAN DEFAULT</literal> is simpler than specifying a complete + <literal>PLAN</literal>, and is often all that is required to get the desired + output. + </para> </listitem> </varlistentry> @@ -4082,5 +4214,39 @@ COLUMNS ( </screen> </para> + + <para> + Find a director that has done films in two different genres: +<screen> +SELECT + director1 AS director, title1, kind1, title2, kind2 +FROM + my_films, + JSON_TABLE ( js, '$.favorites' AS favs COLUMNS ( + NESTED PATH '$[*]' AS films1 COLUMNS ( + kind1 text PATH '$.kind', + NESTED PATH '$.films[*]' AS film1 COLUMNS ( + title1 text PATH '$.title', + director1 text PATH '$.director') + ), + NESTED PATH '$[*]' AS films2 COLUMNS ( + kind2 text PATH '$.kind', + NESTED PATH '$.films[*]' AS film2 COLUMNS ( + title2 text PATH '$.title', + director2 text PATH '$.director' + ) + ) + ) + PLAN (favs OUTER ((films1 INNER film1) CROSS (films2 INNER film2))) + ) AS jt + WHERE kind1 > kind2 AND director1 = director2; + + director | title1 | kind1 | title2 | kind2 +------------------+---------+----------+--------+-------- + Alfred Hitchcock | Vertigo | thriller | Psycho | horror +(1 row) +</screen> + </para> + </sect2> </sect1> -- 2.39.5 (Apple Git-154) ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL/JSON json_table plan clause 2025-02-04 03:05 Re: SQL/JSON json_table plan clause Amit Langote <[email protected]> 2025-02-04 11:50 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 2026-07-04 21:02 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> @ 2026-07-07 23:38 ` Alexander Korotkov <[email protected]> 2026-07-08 15:35 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Alexander Korotkov @ 2026-07-07 23:38 UTC (permalink / raw) To: Nikita Malakhov <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers Hi! On Sun, Jul 5, 2026 at 12:02 AM Alexander Korotkov <[email protected]> wrote: > On Fri, May 15, 2026 at 2:53 PM Nikita Malakhov <[email protected]> wrote: > > Again, Alexander and Amit, thanks for the review. I've rebased the patch and made > > some changes according to Alexander's notes. I've slightly rearranged files between > > patches for it to be easier to review, so now there are 3 patches: > > v23-0001-json-table-plan-clause.patch - main code changes > > v23-0002-json-table-plan-tests.patch - test cases and out file changes > > v23-0003-json-table-plan-docs.patch - docs package > > > > <...> > > >1. IsA(planstate, JsonTableSiblingJoin) is wrong. planstate is not a > > >node, thus IsA() can't be applied to it. You should instead do > > >IsA(planstate->plan, JsonTableSiblingJoin). It wasn't catched, > > >because regression tests don't exercise this branch. So, you also > > >need to improve the coverage. > > - done; > > > > >2. get_json_table() with patch uses JSON_BEHAVIOR_EMPTY as the default > > >value for deparsing, while parsing still uses > > >JSON_BEHAVIOR_EMPTY_ARRAY. Looks plain wrong. I'm not sure what is > > >intention here. > > - looks like leftover from older version, changed to default behavior, so unnecessary > > emission of ERROR is omitted; > > > > >3. PLAN clause is always emitted during deparsing even if user didn't > > >specify anything. I would prefer to skip PLAN clause in this case > > >unless there is strong reason to do the opposite (this reason must be > > >pointed if any). > > - done, this made test cases much more readable; > > > > >4. Unused typedefs in src/tools/pgindent/typedefs.list: > > >JsonTableScanState, JsonPathSpec, JsonTablePlanStateType, > > >JsonTableJoinState. > > - done; > > > > >5. Empty comment in JsonTablePlanState definition. Pointed by Amit, > > >but not fixed. > > - done; > > > > >6. Rename passingArgs to passing_Args for no reason in parse_jsontable.c. > > - done; > > > > >7. Patch lacks documentation (also pointed by Amit) > > - done, documentation is provided in separate patch; > > > > >8. Patch could use pgindent run. > > - not done yet but would provide a newer version with it. > > Thank you. I've made following additional changes. > > 1. Actually, I don't see my previous item 3 fully addressed. We > still had PLAN clause deparsed in the case user didn't specify it. I > see this as an undesirable behavior. And there are at least two ways > to fix this: don't output PLAN clause if it's not the default, > remember if user specified as a special flag or even original clause. > I found first way (don't output default) more appealing as it don't > require additional struct members and we already do this in other > similar cases (NULLS FIRST/LAST, ON EMPTY/ON ERROR). So, I did this > for PLAN clause. > 2. Simplification for JsonTablePlanNextRow(): no reason to check > (planstate->advanceNested || planstate->nested) if we already issued > break for (planstate->nested == NULL). > 3. Similar simplification for transformJsonTableNestedColumns(). I made some additional changes to the patch. In the previous versions PLAN qual required name for root path, but didn't required names for nested paths. I've rechecked with standard, and it appears that standard claims the opposite: name is not required for the root path, but required for nested paths. So, I decided to allow skipping name for both root path (as standard claims), and nested path (our extension to standard). Corresponding changes made to code, tests and documentation. Also, I merged 3 patches into 1, and edited sql_features.txt. I'm going to push this if no objections. ------ Regards, Alexander Korotkov Supabase Attachments: [application/octet-stream] v25-0001-JSON_TABLE-PLAN-clause.patch (94.4K, ../../CAPpHfdtJB2APBjD7Npa58Cg4P0KqFjNKq_mT4tpNWN2y+5zKoA@mail.gmail.com/2-v25-0001-JSON_TABLE-PLAN-clause.patch) download | inline diff: From 10d5cdb84ddda0dae6bdf0d18f9f5dcad9cbea60 Mon Sep 17 00:00:00 2001 From: Alexander Korotkov <[email protected]> Date: Wed, 8 Jul 2026 02:31:41 +0300 Subject: [PATCH v25] JSON_TABLE PLAN clause MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch adds the PLAN clauses for JSON_TABLE, which allow the user to specify how data from nested paths are joined, allowing considerable freedom in shaping the tabular output of JSON_TABLE. PLAN DEFAULT allows the user to specify the global strategies when dealing with sibling or child nested paths. This is often sufficient to achieve the necessary goal, and is considerably simpler than the full PLAN clause, which allows the user to specify the strategy to be used for each named nested path. Path names may be attached to the row pattern and to each NESTED path using AS. Unlike the SQL/JSON standard, which requires a name for every NESTED path when a PLAN clause is present, PostgreSQL does not require them and generates a name for any path left unnamed; a specific PLAN() can only reference paths by name, so unnamed paths it must mention are reported as not covered by the plan. Author: Nikita Malakhov <[email protected]> Co-authored-by: Nikita Glukhov <[email protected]> Co-authored-by: Teodor Sigaev <[email protected]> Co-authored-by: Oleg Bartunov <[email protected]> Co-authored-by: Alexander Korotkov <[email protected]> Co-authored-by: Andrew Dunstan <[email protected]> Co-authored-by: Amit Langote <[email protected]> Co-authored-by: Anton Melnikov <[email protected]> Reviewed-by: Andres Freund <[email protected]> Reviewed-by: Pavel Stehule <[email protected]> Reviewed-by: Andrew Alsup <[email protected]> Reviewed-by: Erik Rijkers <[email protected]> Reviewed-by: Zhihong Yu <[email protected]> Reviewed-by: Himanshu Upadhyaya <[email protected]> Reviewed-by: Daniel Gustafsson <[email protected]> Reviewed-by: Justin Pryzby <[email protected]> Reviewed-by: Álvaro Herrera <[email protected]> Reviewed-by: jian he <[email protected]> Reviewed-by: Vladlen Popolitov <[email protected]> Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com Discussion: https://postgr.es/m/CAN-LCVP7HXmGu-WcinsHvdKqMGEdv=1Y67H4U58F6Y=Q0M5GyQ@mail.gmail.com --- doc/src/sgml/func/func-json.sgml | 176 ++++- src/backend/catalog/sql_features.txt | 4 +- src/backend/nodes/makefuncs.c | 54 ++ src/backend/parser/gram.y | 101 ++- src/backend/parser/parse_jsontable.c | 382 ++++++++-- src/backend/utils/adt/jsonpath_exec.c | 171 +++-- src/backend/utils/adt/ruleutils.c | 99 +++ src/include/nodes/makefuncs.h | 5 + src/include/nodes/parsenodes.h | 43 ++ src/include/nodes/primnodes.h | 2 + .../regress/expected/sqljson_jsontable.out | 655 +++++++++++++++++- src/test/regress/sql/sqljson_jsontable.sql | 434 ++++++++++++ src/tools/pgindent/typedefs.list | 3 + 13 files changed, 2017 insertions(+), 112 deletions(-) diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml index 3d97e2b5375..541d02d4a6e 100644 --- a/doc/src/sgml/func/func-json.sgml +++ b/doc/src/sgml/func/func-json.sgml @@ -3663,6 +3663,11 @@ JSON_TABLE ( <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> <optional> PASSING { <replaceable>value</replaceable> AS <replaceable>varname</replaceable> } <optional>, ...</optional> </optional> COLUMNS ( <replaceable class="parameter">json_table_column</replaceable> <optional>, ...</optional> ) <optional> { <literal>ERROR</literal> | <literal>EMPTY</literal> <optional>ARRAY</optional>} <literal>ON ERROR</literal> </optional> + <optional> + PLAN ( <replaceable class="parameter">json_table_plan</replaceable> ) | + PLAN DEFAULT ( { INNER | OUTER } <optional> , { CROSS | UNION } </optional> + | { CROSS | UNION } <optional> , { INNER | OUTER } </optional> ) + </optional> ) <phrase> @@ -3679,6 +3684,16 @@ where <replaceable class="parameter">json_table_column</replaceable> is: | <replaceable>name</replaceable> <replaceable>type</replaceable> EXISTS <optional> PATH <replaceable>path_expression</replaceable> </optional> <optional> { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR </optional> | NESTED <optional> PATH </optional> <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> ) +<phrase> +<replaceable>json_table_plan</replaceable> is: +</phrase> + <replaceable>json_path_name</replaceable> <optional> { OUTER | INNER } <replaceable>json_table_plan_primary</replaceable> </optional> + | <replaceable>json_table_plan_primary</replaceable> { UNION <replaceable>json_table_plan_primary</replaceable> } <optional>...</optional> + | <replaceable>json_table_plan_primary</replaceable> { CROSS <replaceable>json_table_plan_primary</replaceable> } <optional>...</optional> +<phrase> +<replaceable>json_table_plan_primary</replaceable> is: +</phrase> + <replaceable>json_path_name</replaceable> | ( <replaceable>json_table_plan</replaceable> ) </synopsis> <para> @@ -3842,6 +3857,11 @@ where <replaceable class="parameter">json_table_column</replaceable> is: in a single function invocation rather than chaining several <function>JSON_TABLE</function> expressions in an SQL statement. </para> + + <para> + You can use the <literal>PLAN</literal> clause to define how + to join the columns returned by <literal>NESTED PATH</literal> clauses. + </para> </listitem> </varlistentry> </variablelist> @@ -3866,12 +3886,130 @@ where <replaceable class="parameter">json_table_column</replaceable> is: <para> The optional <replaceable>json_path_name</replaceable> serves as an - identifier of the provided <replaceable>path_expression</replaceable>. - The name must be unique and distinct from the column names. + identifier of the provided <replaceable>json_path_specification</replaceable>. + The path name must be unique and distinct from the column names. Each + path name can appear in the <literal>PLAN</literal> clause only once. + </para> + <para> + The SQL/JSON standard requires an explicit path name for every + <literal>NESTED PATH</literal> when a <literal>PLAN</literal> or + <literal>PLAN DEFAULT</literal> clause is present, while the row pattern + path name always remains optional. As an extension, + <productname>PostgreSQL</productname> does not require any path name: a + name is generated for any path left unnamed. Note, however, that a + specific <literal>PLAN</literal> (<replaceable>json_table_plan</replaceable>) + can only refer to paths by name, so every path that such a plan must + mention has to be given a name explicitly. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <literal>PLAN</literal> ( <replaceable>json_table_plan</replaceable> ) + </term> + <listitem> + + <para> + Defines how to join the data returned by <literal>NESTED PATH</literal> + clauses to the constructed view. + </para> + <para> + To join columns with parent/child relationship, you can use: + </para> + <variablelist> + <varlistentry> + <term> + <literal>INNER</literal> + </term> + <listitem> + + <para> + Use <literal>INNER JOIN</literal>, so that the parent row + is omitted from the output if it does not have any child rows + after joining the data returned by <literal>NESTED PATH</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <literal>OUTER</literal> + </term> + <listitem> + + <para> + Use <literal>LEFT OUTER JOIN</literal>, so that the parent row + is always included into the output even if it does not have any child rows + after joining the data returned by <literal>NESTED PATH</literal>, with NULL values + inserted into the child columns if the corresponding + values are missing. + </para> + <para> + This is the default option for joining columns with parent/child relationship. + </para> + </listitem> + </varlistentry> + </variablelist> + <para> + To join sibling columns, you can use: + </para> + + <variablelist> + <varlistentry> + <term> + <literal>UNION</literal> + </term> + <listitem> + + <para> + Generate one row for each value produced by each of the sibling + columns. The columns from the other siblings are set to null. + </para> + <para> + This is the default option for joining sibling columns. </para> </listitem> </varlistentry> + <varlistentry> + <term> + <literal>CROSS</literal> + </term> + <listitem> + + <para> + Generate one row for each combination of values from the sibling columns. + </para> + </listitem> + </varlistentry> + + </variablelist> + + </listitem> + </varlistentry> + + <varlistentry> + <term> + <literal>PLAN DEFAULT</literal> ( <literal><replaceable>OUTER | INNER</replaceable> <optional>, <replaceable>UNION | CROSS</replaceable> </optional></literal> ) + </term> + <listitem> + <para> + The terms can also be specified in reverse order. The + <literal>INNER</literal> or <literal>OUTER</literal> option defines the + joining plan for parent/child columns, while <literal>UNION</literal> or + <literal>CROSS</literal> affects joins of sibling columns. This form + of <literal>PLAN</literal> overrides the default plan for + all columns at once. + </para> + <para> + <literal>PLAN DEFAULT</literal> is simpler than specifying a complete + <literal>PLAN</literal>, and is often all that is required to get the desired + output. + </para> + </listitem> + </varlistentry> + <varlistentry> <term> { <literal>ERROR</literal> | <literal>EMPTY</literal> } <literal>ON ERROR</literal> @@ -4082,5 +4220,39 @@ COLUMNS ( </screen> </para> + + <para> + Find a director that has done films in two different genres: +<screen> +SELECT + director1 AS director, title1, kind1, title2, kind2 +FROM + my_films, + JSON_TABLE ( js, '$.favorites' AS favs COLUMNS ( + NESTED PATH '$[*]' AS films1 COLUMNS ( + kind1 text PATH '$.kind', + NESTED PATH '$.films[*]' AS film1 COLUMNS ( + title1 text PATH '$.title', + director1 text PATH '$.director') + ), + NESTED PATH '$[*]' AS films2 COLUMNS ( + kind2 text PATH '$.kind', + NESTED PATH '$.films[*]' AS film2 COLUMNS ( + title2 text PATH '$.title', + director2 text PATH '$.director' + ) + ) + ) + PLAN (favs OUTER ((films1 INNER film1) CROSS (films2 INNER film2))) + ) AS jt + WHERE kind1 > kind2 AND director1 = director2; + + director | title1 | kind1 | title2 | kind2 +------------------+---------+----------+--------+-------- + Alfred Hitchcock | Vertigo | thriller | Psycho | horror +(1 row) +</screen> + </para> + </sect2> </sect1> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 626054cbcef..55f073e2262 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -650,7 +650,7 @@ T814 Colon in JSON_OBJECT or JSON_OBJECTAGG YES T821 Basic SQL/JSON query operators YES T822 SQL/JSON: IS JSON WITH UNIQUE KEYS predicate YES T823 SQL/JSON: PASSING clause YES -T824 JSON_TABLE: specific PLAN clause NO +T824 JSON_TABLE: specific PLAN clause YES T825 SQL/JSON: ON EMPTY and ON ERROR clauses YES T826 General value expression in ON ERROR or ON EMPTY clauses YES T827 JSON_TABLE: sibling NESTED COLUMNS clauses YES @@ -664,7 +664,7 @@ T834 SQL/JSON path language: wildcard member accessor YES T835 SQL/JSON path language: filter expressions YES T836 SQL/JSON path language: starts with predicate YES T837 SQL/JSON path language: regex_like predicate YES -T838 JSON_TABLE: PLAN DEFAULT clause NO +T838 JSON_TABLE: PLAN DEFAULT clause YES T839 Formatted cast of datetimes to/from character strings NO T840 Hex integer literals in SQL/JSON path language YES T851 SQL/JSON: optional keywords for default syntax YES diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 40b09958ac2..cdc02b274ff 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -963,6 +963,60 @@ makeJsonBehavior(JsonBehaviorType btype, Node *expr, int location) return behavior; } +/* + * makeJsonTableDefaultPlan - + * creates a JsonTablePlanSpec node to represent a "default" JSON_TABLE plan + * with given join strategy + */ +Node * +makeJsonTableDefaultPlan(JsonTablePlanJoinType join_type, int location) +{ + JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec); + + n->plan_type = JSTP_DEFAULT; + n->join_type = join_type; + n->location = location; + + return (Node *) n; +} + +/* + * makeJsonTableSimplePlan - + * creates a JsonTablePlanSpec node to represent a "simple" JSON_TABLE plan + * for given PATH + */ +Node * +makeJsonTableSimplePlan(char *pathname, int location) +{ + JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec); + + n->plan_type = JSTP_SIMPLE; + n->pathname = pathname; + n->location = location; + + return (Node *) n; +} + +/* + * makeJsonTableJoinedPlan - + * creates a JsonTablePlanSpec node to represent join between the given + * pair of plans + */ +Node * +makeJsonTableJoinedPlan(JsonTablePlanJoinType type, Node *plan1, Node *plan2, + int location) +{ + JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec); + + n->plan_type = JSTP_JOINED; + n->join_type = type; + n->plan1 = castNode(JsonTablePlanSpec, plan1); + n->plan2 = castNode(JsonTablePlanSpec, plan2); + n->location = location; + + return (Node *) n; +} + /* * makeJsonKeyValue - * creates a JsonKeyValue node diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index ff4e1388c55..9e05a314707 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -672,6 +672,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); json_table json_table_column_definition json_table_column_path_clause_opt + json_table_plan_clause_opt + json_table_plan + json_table_plan_simple + json_table_plan_outer + json_table_plan_inner + json_table_plan_union + json_table_plan_cross + json_table_plan_primary %type <list> json_name_and_value_list json_value_expr_list json_array_aggregate_order_by_clause_opt @@ -683,6 +691,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <ival> json_behavior_type json_predicate_type_constraint json_quotes_clause_opt + json_table_default_plan_choices + json_table_default_plan_inner_outer + json_table_default_plan_union_cross json_wrapper_behavior %type <boolean> json_key_uniqueness_constraint_opt json_object_constructor_null_clause_opt @@ -15188,6 +15199,7 @@ json_table: json_value_expr ',' a_expr json_table_path_name_opt json_passing_clause_opt COLUMNS '(' json_table_column_definition_list ')' + json_table_plan_clause_opt json_on_error_clause_opt ')' { @@ -15199,13 +15211,15 @@ json_table: castNode(A_Const, $5)->val.node.type != T_String) ereport(ERROR, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("only string constants are supported in JSON_TABLE path specification"), + errmsg("only string constants are supported in" + " JSON_TABLE path specification"), parser_errposition(@5)); pathstring = castNode(A_Const, $5)->val.sval.sval; n->pathspec = makeJsonTablePathSpec(pathstring, $6, @5, @6); n->passing = $7; n->columns = $10; - n->on_error = (JsonBehavior *) $12; + n->planspec = (JsonTablePlanSpec *) $12; + n->on_error = (JsonBehavior *) $13; n->location = @1; $$ = (Node *) n; } @@ -15297,8 +15311,7 @@ json_table_column_definition: JsonTableColumn *n = makeNode(JsonTableColumn); n->coltype = JTC_NESTED; - n->pathspec = (JsonTablePathSpec *) - makeJsonTablePathSpec($3, NULL, @3, -1); + n->pathspec = makeJsonTablePathSpec($3, NULL, @3, -1); n->columns = $6; n->location = @1; $$ = (Node *) n; @@ -15309,8 +15322,7 @@ json_table_column_definition: JsonTableColumn *n = makeNode(JsonTableColumn); n->coltype = JTC_NESTED; - n->pathspec = (JsonTablePathSpec *) - makeJsonTablePathSpec($3, $5, @3, @5); + n->pathspec = makeJsonTablePathSpec($3, $5, @3, @5); n->columns = $8; n->location = @1; $$ = (Node *) n; @@ -15329,6 +15341,83 @@ json_table_column_path_clause_opt: { $$ = NULL; } ; +json_table_plan_clause_opt: + PLAN '(' json_table_plan ')' + { $$ = $3; } + | PLAN DEFAULT '(' json_table_default_plan_choices ')' + { $$ = makeJsonTableDefaultPlan($4, @1); } + | /* EMPTY */ + { $$ = NULL; } + ; + +json_table_plan: + json_table_plan_simple + | json_table_plan_outer + | json_table_plan_inner + | json_table_plan_union + | json_table_plan_cross + ; + +json_table_plan_simple: + name + { $$ = makeJsonTableSimplePlan($1, @1); } + ; + +json_table_plan_outer: + json_table_plan_simple OUTER_P json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_OUTER, $1, $3, @1); } + ; + +json_table_plan_inner: + json_table_plan_simple INNER_P json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_INNER, $1, $3, @1); } + ; + +json_table_plan_union: + json_table_plan_primary UNION json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_UNION, $1, $3, @1); } + | json_table_plan_union UNION json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_UNION, $1, $3, @1); } + ; + +json_table_plan_cross: + json_table_plan_primary CROSS json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_CROSS, $1, $3, @1); } + | json_table_plan_cross CROSS json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_CROSS, $1, $3, @1); } + ; + +json_table_plan_primary: + json_table_plan_simple + { $$ = $1; } + | '(' json_table_plan ')' + { + castNode(JsonTablePlanSpec, $2)->location = @1; + $$ = $2; + } + ; + +json_table_default_plan_choices: + json_table_default_plan_inner_outer + { $$ = $1 | JSTP_JOIN_UNION; } + | json_table_default_plan_union_cross + { $$ = $1 | JSTP_JOIN_OUTER; } + | json_table_default_plan_inner_outer ',' json_table_default_plan_union_cross + { $$ = $1 | $3; } + | json_table_default_plan_union_cross ',' json_table_default_plan_inner_outer + { $$ = $1 | $3; } + ; + +json_table_default_plan_inner_outer: + INNER_P { $$ = JSTP_JOIN_INNER; } + | OUTER_P { $$ = JSTP_JOIN_OUTER; } + ; + +json_table_default_plan_union_cross: + UNION { $$ = JSTP_JOIN_UNION; } + | CROSS { $$ = JSTP_JOIN_CROSS; } + ; + /***************************************************************************** * * Type syntax diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c index 32a1e8629b2..ef042830f02 100644 --- a/src/backend/parser/parse_jsontable.c +++ b/src/backend/parser/parse_jsontable.c @@ -39,17 +39,22 @@ typedef struct JsonTableParseContext } JsonTableParseContext; static JsonTablePlan *transformJsonTableColumns(JsonTableParseContext *cxt, + JsonTablePlanSpec *planspec, List *columns, List *passingArgs, JsonTablePathSpec *pathspec); static JsonTablePlan *transformJsonTableNestedColumns(JsonTableParseContext *cxt, + JsonTablePlanSpec *plan, List *passingArgs, List *columns); static JsonFuncExpr *transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, - List *passingArgs); + List *passingArgs, + bool errorOnError); static bool isCompositeType(Oid typid); -static JsonTablePlan *makeJsonTablePathScan(JsonTablePathSpec *pathspec, +static JsonTablePlan *makeJsonTablePathScan(JsonTableParseContext *cxt, + JsonTablePathSpec *pathspec, + JsonTablePlanSpec *planspec, bool errorOnError, int colMin, int colMax, JsonTablePlan *childplan); @@ -57,8 +62,14 @@ static void CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt, List *columns); static bool LookupPathOrColumnName(JsonTableParseContext *cxt, char *name); static char *generateJsonTablePathName(JsonTableParseContext *cxt); -static JsonTablePlan *makeJsonTableSiblingJoin(JsonTablePlan *lplan, +static void validateJsonTableChildPlan(JsonTableParseContext *cxt, + JsonTablePlanSpec *plan, + List *columns); +static JsonTablePlan *makeJsonTableSiblingJoin(bool cross, + JsonTablePlan *lplan, JsonTablePlan *rplan); +static void + appendJsonTableColumns(JsonTableParseContext *cxt, List *columns, List *passingArgs); /* * transformJsonTable - @@ -76,6 +87,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt) TableFunc *tf; JsonFuncExpr *jfe; JsonExpr *je; + JsonTablePlanSpec *plan = jt->planspec; JsonTablePathSpec *rootPathSpec = jt->pathspec; bool is_lateral; JsonTableParseContext cxt = {pstate}; @@ -94,8 +106,17 @@ transformJsonTable(ParseState *pstate, JsonTable *jt) parser_errposition(pstate, jt->on_error->location)); cxt.pathNameId = 0; + + /* + * Generate a name for the row pattern path if it was not given one. Path + * names are optional for every path, including when a PLAN clause is + * present; a specific PLAN() can only reference named paths, so an + * unnamed path that the plan must mention is caught later as a path name + * mismatch or a path not covered by the plan. + */ if (rootPathSpec->name == NULL) rootPathSpec->name = generateJsonTablePathName(&cxt); + cxt.pathNames = list_make1(rootPathSpec->name); CheckDuplicateColumnOrPathNames(&cxt, jt->columns); @@ -135,7 +156,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt) */ cxt.jt = jt; cxt.tf = tf; - tf->plan = (Node *) transformJsonTableColumns(&cxt, jt->columns, + tf->plan = (Node *) transformJsonTableColumns(&cxt, plan, jt->columns, jt->passing, rootPathSpec); @@ -246,25 +267,110 @@ generateJsonTablePathName(JsonTableParseContext *cxt) * their type/collation information to cxt->tf. */ static JsonTablePlan * -transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, +transformJsonTableColumns(JsonTableParseContext *cxt, + JsonTablePlanSpec *planspec, + List *columns, List *passingArgs, JsonTablePathSpec *pathspec) { - ParseState *pstate = cxt->pstate; JsonTable *jt = cxt->jt; TableFunc *tf = cxt->tf; - ListCell *col; - bool ordinality_found = false; + JsonTablePathScan *scan; + JsonTablePlanSpec *childPlanSpec; + bool defaultPlan = planspec == NULL || + planspec->plan_type == JSTP_DEFAULT; bool errorOnError = jt->on_error && jt->on_error->btype == JSON_BEHAVIOR_ERROR; - Oid contextItemTypid = exprType(tf->docexpr); int colMin, colMax; - JsonTablePlan *childplan; + JsonTablePlan *childplan = NULL; /* Start of column range */ colMin = list_length(tf->colvalexprs); + if (defaultPlan) + childPlanSpec = planspec; + else + { + /* validate parent and child plans */ + JsonTablePlanSpec *parentPlanSpec; + + if (planspec->plan_type == JSTP_JOINED) + { + if (planspec->join_type != JSTP_JOIN_INNER && + planspec->join_type != JSTP_JOIN_OUTER) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan clause"), + errdetail("Expected INNER or OUTER."), + parser_errposition(cxt->pstate, planspec->location))); + + parentPlanSpec = planspec->plan1; + childPlanSpec = planspec->plan2; + + Assert(parentPlanSpec->plan_type != JSTP_JOINED); + Assert(parentPlanSpec->pathname); + } + else + { + parentPlanSpec = planspec; + childPlanSpec = NULL; + } + + if (strcmp(parentPlanSpec->pathname, pathspec->name) != 0) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("PATH name mismatch: expected %s but %s is given.", + pathspec->name, parentPlanSpec->pathname), + parser_errposition(cxt->pstate, planspec->location))); + + validateJsonTableChildPlan(cxt, childPlanSpec, columns); + } + + appendJsonTableColumns(cxt, columns, passingArgs); + + /* End of column range. */ + if (list_length(tf->colvalexprs) == colMin) + { + /* No columns in this Scan beside the nested ones. */ + colMax = colMin = -1; + } + else + colMax = list_length(tf->colvalexprs) - 1; + + if (childPlanSpec || defaultPlan) + { + /* transform recursively nested columns */ + childplan = transformJsonTableNestedColumns(cxt, childPlanSpec, + columns, passingArgs); + } + + /* transform only non-nested columns */ + scan = (JsonTablePathScan *) makeJsonTablePathScan(cxt, pathspec, + planspec, + errorOnError, + colMin, + colMax, + childplan); + + return (JsonTablePlan *) scan; +} + +/* Append transformed non-nested JSON_TABLE columns to the TableFunc node */ +static void +appendJsonTableColumns(JsonTableParseContext *cxt, List *columns, List *passingArgs) +{ + ListCell *col; + ParseState *pstate = cxt->pstate; + JsonTable *jt = cxt->jt; + TableFunc *tf = cxt->tf; + bool ordinality_found = false; + JsonBehavior *on_error = jt->on_error; + bool errorOnError = on_error && + on_error->btype == JSON_BEHAVIOR_ERROR; + Oid contextItemTypid = exprType(tf->docexpr); + foreach(col, columns) { JsonTableColumn *rawc = castNode(JsonTableColumn, lfirst(col)); @@ -273,12 +379,9 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, Oid typcoll = InvalidOid; Node *colexpr; - if (rawc->coltype != JTC_NESTED) - { - Assert(rawc->name); + if (rawc->name) tf->colnames = lappend(tf->colnames, makeString(pstrdup(rawc->name))); - } /* * Determine the type and typmod for the new column. FOR ORDINALITY @@ -324,7 +427,7 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, param->typeMod = -1; jfe = transformJsonTableColumn(rawc, (Node *) param, - passingArgs); + passingArgs, errorOnError); colexpr = transformExpr(pstate, (Node *) jfe, EXPR_KIND_FROM_FUNCTION); @@ -349,22 +452,6 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, tf->colcollations = lappend_oid(tf->colcollations, typcoll); tf->colvalexprs = lappend(tf->colvalexprs, colexpr); } - - /* End of column range. */ - if (list_length(tf->colvalexprs) == colMin) - { - /* No columns in this Scan beside the nested ones. */ - colMax = colMin = -1; - } - else - colMax = list_length(tf->colvalexprs) - 1; - - /* Recursively transform nested columns */ - childplan = transformJsonTableNestedColumns(cxt, passingArgs, columns); - - /* Create a "parent" scan responsible for all columns handled above. */ - return makeJsonTablePathScan(pathspec, errorOnError, colMin, colMax, - childplan); } /* @@ -395,7 +482,7 @@ isCompositeType(Oid typid) */ static JsonFuncExpr * transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, - List *passingArgs) + List *passingArgs, bool errorOnError) { Node *pathspec; JsonFuncExpr *jfexpr = makeNode(JsonFuncExpr); @@ -437,6 +524,8 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, jfexpr->output->returning->format = jtc->format; jfexpr->on_empty = jtc->on_empty; jfexpr->on_error = jtc->on_error; + if (jfexpr->on_error == NULL && errorOnError) + jfexpr->on_error = makeJsonBehavior(JSON_BEHAVIOR_ERROR, NULL, -1); jfexpr->quotes = jtc->quotes; jfexpr->wrapper = jtc->wrapper; jfexpr->location = jtc->location; @@ -444,57 +533,137 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, return jfexpr; } +static JsonTableColumn * +findNestedJsonTableColumn(List *columns, const char *pathname) +{ + ListCell *lc; + + foreach(lc, columns) + { + JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc)); + + if (jtc->coltype == JTC_NESTED && + jtc->pathspec->name && + !strcmp(jtc->pathspec->name, pathname)) + return jtc; + } + + return NULL; +} + /* * Recursively transform nested columns and create child plan(s) that will be * used to evaluate their row patterns. + * + * Default plan is transformed into a cross/union join of its nested columns. + * Simple and outer/inner plans are transformed into a JsonTablePlan by + * finding and transforming corresponding nested column. + * Sibling plans are recursively transformed into a JsonTableSiblingJoin. */ static JsonTablePlan * transformJsonTableNestedColumns(JsonTableParseContext *cxt, - List *passingArgs, - List *columns) + JsonTablePlanSpec *planspec, + List *columns, + List *passingArgs) { - JsonTablePlan *plan = NULL; - ListCell *lc; + JsonTableColumn *jtc = NULL; - /* - * If there are multiple NESTED COLUMNS clauses in 'columns', their - * respective plans will be combined using a "sibling join" plan, which - * effectively does a UNION of the sets of rows coming from each nested - * plan. - */ - foreach(lc, columns) + if (!planspec || planspec->plan_type == JSTP_DEFAULT) { - JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc)); - JsonTablePlan *nested; + /* unspecified or default plan */ + JsonTablePlan *plan = NULL; + ListCell *lc; + bool cross = planspec && (planspec->join_type & JSTP_JOIN_CROSS); - if (jtc->coltype != JTC_NESTED) - continue; + /* + * If there are multiple NESTED COLUMNS clauses in 'columns', their + * respective plans will be combined using a "sibling join" plan, + * which effectively does a UNION of the sets of rows coming from each + * nested plan. + */ + foreach(lc, columns) + { + JsonTableColumn *col = castNode(JsonTableColumn, lfirst(lc)); + JsonTablePlan *nested; - if (jtc->pathspec->name == NULL) - jtc->pathspec->name = generateJsonTablePathName(cxt); + if (col->coltype != JTC_NESTED) + continue; + + if (col->pathspec->name == NULL) + { + col->pathspec->name = generateJsonTablePathName(cxt); + } - nested = transformJsonTableColumns(cxt, jtc->columns, passingArgs, - jtc->pathspec); + nested = transformJsonTableColumns(cxt, planspec, col->columns, + passingArgs, + col->pathspec); - if (plan) - plan = makeJsonTableSiblingJoin(plan, nested); + /* Join nested plan with previous sibling nested plans. */ + if (plan) + plan = makeJsonTableSiblingJoin(cross, plan, nested); + else + plan = nested; + } + + return plan; + } + else if (planspec->plan_type == JSTP_SIMPLE) + { + jtc = findNestedJsonTableColumn(columns, planspec->pathname); + } + else if (planspec->plan_type == JSTP_JOINED) + { + if (planspec->join_type == JSTP_JOIN_INNER || + planspec->join_type == JSTP_JOIN_OUTER) + { + Assert(planspec->plan1->plan_type == JSTP_SIMPLE); + jtc = findNestedJsonTableColumn(columns, planspec->plan1->pathname); + } else - plan = nested; + { + JsonTablePlan *lplan = transformJsonTableNestedColumns(cxt, + planspec->plan1, + columns, + passingArgs); + JsonTablePlan *rplan = transformJsonTableNestedColumns(cxt, + planspec->plan2, + columns, + passingArgs); + + return makeJsonTableSiblingJoin(planspec->join_type == JSTP_JOIN_CROSS, + lplan, rplan); + } } + else + elog(ERROR, "invalid JSON_TABLE plan type %d", planspec->plan_type); - return plan; + if (!jtc) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan clause"), + errdetail("PATH name was %s not found in nested columns list.", + planspec->pathname), + parser_errposition(cxt->pstate, planspec->location))); + + return transformJsonTableColumns(cxt, planspec, jtc->columns, + passingArgs, + jtc->pathspec); } /* - * Create a JsonTablePlan for given path and ON ERROR behavior. + * Create transformed JSON_TABLE parent plan node by appending all non-nested + * columns to the TableFunc node and remembering their indices in the + * colvalexprs list. * - * colMin and colMin give the range of columns computed by this scan in the + * colMin and colMax give the range of columns computed by this scan in the * global flat list of column expressions that will be passed to the * JSON_TABLE's TableFunc. Both are -1 when all of columns are nested and * thus computed by 'childplan'. */ static JsonTablePlan * -makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError, +makeJsonTablePathScan(JsonTableParseContext *cxt, JsonTablePathSpec *pathspec, + JsonTablePlanSpec *planspec, + bool errorOnError, int colMin, int colMax, JsonTablePlan *childplan) { @@ -518,6 +687,11 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError, scan->colMin = colMin; scan->colMax = colMax; + if (scan->child) + scan->outerJoin = planspec == NULL || + (planspec->join_type & JSTP_JOIN_OUTER); + /* else: default plan case, no children found */ + return (JsonTablePlan *) scan; } @@ -529,13 +703,103 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError, * sets of rows from 'lplan' and 'rplan'. */ static JsonTablePlan * -makeJsonTableSiblingJoin(JsonTablePlan *lplan, JsonTablePlan *rplan) +makeJsonTableSiblingJoin(bool cross, JsonTablePlan *lplan, JsonTablePlan *rplan) { JsonTableSiblingJoin *join = makeNode(JsonTableSiblingJoin); join->plan.type = T_JsonTableSiblingJoin; join->lplan = lplan; join->rplan = rplan; + join->cross = cross; return (JsonTablePlan *) join; } + +/* Collect sibling path names from plan to the specified list. */ +static void +collectSiblingPathsInJsonTablePlan(JsonTablePlanSpec *plan, List **paths) +{ + if (plan->plan_type == JSTP_SIMPLE) + *paths = lappend(*paths, plan->pathname); + else if (plan->plan_type == JSTP_JOINED) + { + if (plan->join_type == JSTP_JOIN_INNER || + plan->join_type == JSTP_JOIN_OUTER) + { + Assert(plan->plan1->plan_type == JSTP_SIMPLE); + *paths = lappend(*paths, plan->plan1->pathname); + } + else if (plan->join_type == JSTP_JOIN_CROSS || + plan->join_type == JSTP_JOIN_UNION) + { + collectSiblingPathsInJsonTablePlan(plan->plan1, paths); + collectSiblingPathsInJsonTablePlan(plan->plan2, paths); + } + else + elog(ERROR, "invalid JSON_TABLE join type %d", + plan->join_type); + } +} + +/* + * Validate child JSON_TABLE plan by checking that: + * - all nested columns have path names specified + * - all nested columns have corresponding node in the sibling plan + * - plan does not contain duplicate or extra nodes + */ +static void +validateJsonTableChildPlan(JsonTableParseContext *cxt, JsonTablePlanSpec *plan, + List *columns) +{ + ParseState *pstate = cxt->pstate; + ListCell *lc1; + List *siblings = NIL; + int nchildren = 0; + + if (plan) + collectSiblingPathsInJsonTablePlan(plan, &siblings); + + foreach(lc1, columns) + { + JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc1)); + + if (jtc->coltype == JTC_NESTED) + { + ListCell *lc2; + bool found = false; + + /* + * A NESTED path need not be named; generate a name if it was left + * unnamed. A generated name cannot be referenced by a specific + * PLAN(), so such a path is reported just below as a nested path + * not covered by the plan. + */ + if (jtc->pathspec->name == NULL) + jtc->pathspec->name = generateJsonTablePathName(cxt); + + /* find nested path name in the list of sibling path names */ + foreach(lc2, siblings) + { + if ((found = !strcmp(jtc->pathspec->name, lfirst(lc2)))) + break; + } + + if (!found) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE specification"), + errdetail("PLAN clause for nested path %s was not found.", + jtc->pathspec->name), + parser_errposition(pstate, jtc->location)); + + nchildren++; + } + } + + if (list_length(siblings) > nchildren) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan clause"), + errdetail("PLAN clause contains some extra or duplicate sibling nodes."), + parser_errposition(pstate, plan ? plan->location : -1)); +} diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index 9bf8ecdcd0c..b69c87d8587 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -240,6 +240,14 @@ typedef struct JsonTablePlanState /* Parent plan, if this is a nested plan */ struct JsonTablePlanState *parent; + + /* Join type */ + bool cross; + bool outerJoin; + /* Planning control fields */ + bool advanceNested; + bool advanceRight; + bool reset; } JsonTablePlanState; /* Random number to identify JsonTableExecContext for sanity checking */ @@ -388,13 +396,13 @@ static JsonTablePlanState *JsonTableInitPlan(JsonTableExecContext *cxt, MemoryContext mcxt); static void JsonTableSetDocument(TableFuncScanState *state, Datum value); static void JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item); +static void JsonTableRescan(JsonTablePlanState *planstate); static bool JsonTableFetchRow(TableFuncScanState *state); static Datum JsonTableGetValue(TableFuncScanState *state, int colnum, Oid typid, int32 typmod, bool *isnull); static void JsonTableDestroyOpaque(TableFuncScanState *state); static bool JsonTablePlanScanNextRow(JsonTablePlanState *planstate); static void JsonTableResetNestedPlan(JsonTablePlanState *planstate); -static bool JsonTablePlanJoinNextRow(JsonTablePlanState *planstate); static bool JsonTablePlanNextRow(JsonTablePlanState *planstate); const TableFuncRoutine JsonbTableRoutine = @@ -4514,6 +4522,7 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan, JsonTablePathScan *scan = (JsonTablePathScan *) plan; int i; + planstate->outerJoin = scan->outerJoin; planstate->path = DatumGetJsonPathP(scan->path->value->constvalue); planstate->args = args; planstate->mcxt = AllocSetContextCreate(mcxt, "JsonTableExecContext", @@ -4533,6 +4542,8 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan, { JsonTableSiblingJoin *join = (JsonTableSiblingJoin *) plan; + planstate->cross = join->cross; + planstate->left = JsonTableInitPlan(cxt, join->lplan, parentstate, args, mcxt); planstate->right = JsonTableInitPlan(cxt, join->rplan, parentstate, @@ -4587,11 +4598,7 @@ JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item) JsonValueListClear(&planstate->found); } - /* Reset plan iterator to the beginning of the item list */ - JsonValueListInitIterator(&planstate->found, &planstate->iter); - planstate->current.value = PointerGetDatum(NULL); - planstate->current.isnull = true; - planstate->ordinal = 0; + JsonTableRescan(planstate); } /* @@ -4602,16 +4609,93 @@ JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item) static bool JsonTablePlanNextRow(JsonTablePlanState *planstate) { - if (IsA(planstate->plan, JsonTablePathScan)) - return JsonTablePlanScanNextRow(planstate); - else if (IsA(planstate->plan, JsonTableSiblingJoin)) - return JsonTablePlanJoinNextRow(planstate); + if (IsA(planstate->plan, JsonTableSiblingJoin)) + { + if (planstate->advanceRight) + { + /* fetch next inner row */ + if (JsonTablePlanNextRow(planstate->right)) + return true; + + /* inner rows are exhausted */ + if (planstate->cross) + planstate->advanceRight = false; /* next outer row */ + else + return false; /* end of scan */ + } + + while (!planstate->advanceRight) + { + /* fetch next outer row */ + bool more = JsonTablePlanNextRow(planstate->left); + + if (planstate->cross) + { + if (!more) + return false; /* end of scan */ + + JsonTableRescan(planstate->right); + + if (!JsonTablePlanNextRow(planstate->right)) + continue; /* next outer row */ + + planstate->advanceRight = true; /* next inner row */ + } + else if (!more) + { + if (!JsonTablePlanNextRow(planstate->right)) + return false; /* end of scan */ + + planstate->advanceRight = true; /* next inner row */ + } + + break; + } + } else - elog(ERROR, "invalid JsonTablePlan %d", (int) planstate->plan->type); + { + /* reset context item if requested */ + if (planstate->reset) + { + JsonTablePlanState *parent = planstate->parent; + + Assert(parent != NULL && !parent->current.isnull); + JsonTableResetRowPattern(planstate, parent->current.value); + planstate->reset = false; + } + + if (planstate->advanceNested) + { + /* fetch next nested row */ + planstate->advanceNested = JsonTablePlanNextRow(planstate->nested); + if (planstate->advanceNested) + return true; + } + + for (;;) + { + if (!JsonTablePlanScanNextRow(planstate)) + return false; + + if (planstate->nested == NULL) + break; + + JsonTableResetNestedPlan(planstate->nested); + planstate->advanceNested = JsonTablePlanNextRow(planstate->nested); + + if (!planstate->advanceNested && !planstate->outerJoin) + continue; - Assert(false); - /* Appease compiler */ - return false; + /* + * We have a row to return: either the nested plan produced one, + * or this is an outer join and we emit the parent row with the + * nested columns set to NULL. + */ + break; + } + } + + return true; } /* @@ -4697,47 +4781,27 @@ JsonTableResetNestedPlan(JsonTablePlanState *planstate) { JsonTablePlanState *parent = planstate->parent; - if (!parent->current.isnull) - JsonTableResetRowPattern(planstate, parent->current.value); + planstate->reset = true; + planstate->advanceNested = false; + + if (planstate->nested) + JsonTableResetNestedPlan(planstate->nested); /* * If this plan itself has a child nested plan, it will be reset when * the caller calls JsonTablePlanNextRow() on this plan. */ + if (!parent->current.isnull) + JsonTableResetRowPattern(planstate, parent->current.value); } else if (IsA(planstate->plan, JsonTableSiblingJoin)) { JsonTableResetNestedPlan(planstate->left); JsonTableResetNestedPlan(planstate->right); + planstate->advanceRight = false; } } -/* - * Fetch the next row from a JsonTableSiblingJoin. - * - * This is essentially a UNION between the rows from left and right siblings. - */ -static bool -JsonTablePlanJoinNextRow(JsonTablePlanState *planstate) -{ - - /* Fetch row from left sibling. */ - if (!JsonTablePlanNextRow(planstate->left)) - { - /* - * Left sibling ran out of rows, so start fetching from the right - * sibling. - */ - if (!JsonTablePlanNextRow(planstate->right)) - { - /* Right sibling ran out of rows too, so there are no more rows. */ - return false; - } - } - - return true; -} - /* * JsonTableFetchRow * Prepare the next "current" row for upcoming GetValue calls. @@ -4802,3 +4866,26 @@ JsonTableGetValue(TableFuncScanState *state, int colnum, return result; } + +/* Recursively reset planstate and its child nodes */ +static void +JsonTableRescan(JsonTablePlanState *planstate) +{ + if (IsA(planstate->plan, JsonTablePathScan)) + { + /* Reset plan iterator to the beginning of the item list */ + JsonValueListInitIterator(&planstate->found, &planstate->iter); + planstate->current.value = PointerGetDatum(NULL); + planstate->current.isnull = true; + planstate->ordinal = 0; + + if (planstate->nested) + JsonTableRescan(planstate->nested); + } + else if (IsA(planstate->plan, JsonTableSiblingJoin)) + { + JsonTableRescan(planstate->left); + JsonTableRescan(planstate->right); + planstate->advanceRight = false; + } +} diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 88de5c0481c..8b725ed1adb 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -12675,6 +12675,89 @@ get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan, } } +/* + * json_table_plan_is_default - does this plan match the implicit default? + * + * When no PLAN clause is given, JSON_TABLE builds a default plan that joins + * every nested path to its parent with OUTER and every set of sibling paths + * with UNION (see transformJsonTableColumns()). Such a plan is fully implied + * by the NESTED COLUMNS structure, so we need not (and, to match the input, + * should not) print a PLAN clause for it; we only deparse a PLAN clause when + * the plan deviates from the default, i.e. uses an INNER or CROSS join + * somewhere. This follows the usual ruleutils convention of omitting a clause + * that merely restates the default (cf. get_json_expr_options() for ON + * EMPTY/ON ERROR, or the NULLS FIRST/LAST handling in get_rule_orderby()). + */ +static bool +json_table_plan_is_default(JsonTablePlan *plan) +{ + if (IsA(plan, JsonTablePathScan)) + { + JsonTablePathScan *scan = castNode(JsonTablePathScan, plan); + + if (scan->child) + { + if (!scan->outerJoin) + return false; /* INNER is not the default */ + return json_table_plan_is_default(scan->child); + } + + return true; + } + else + { + JsonTableSiblingJoin *join = castNode(JsonTableSiblingJoin, plan); + + if (join->cross) + return false; /* CROSS is not the default */ + return json_table_plan_is_default(join->lplan) && + json_table_plan_is_default(join->rplan); + } +} + +/* + * get_json_table_plan - Parse back a JSON_TABLE plan + */ +static void +get_json_table_plan(TableFunc *tf, JsonTablePlan *plan, deparse_context *context, + bool parenthesize) +{ + if (parenthesize) + appendStringInfoChar(context->buf, '('); + + if (IsA(plan, JsonTablePathScan)) + { + JsonTablePathScan *s = castNode(JsonTablePathScan, plan); + + appendStringInfoString(context->buf, quote_identifier(s->path->name)); + + if (s->child) + { + appendStringInfoString(context->buf, + s->outerJoin ? " OUTER " : " INNER "); + get_json_table_plan(tf, s->child, context, + IsA(s->child, JsonTableSiblingJoin)); + } + } + else if (IsA(plan, JsonTableSiblingJoin)) + { + JsonTableSiblingJoin *j = (JsonTableSiblingJoin *) plan; + + get_json_table_plan(tf, j->lplan, context, + IsA(j->lplan, JsonTableSiblingJoin) || + castNode(JsonTablePathScan, j->lplan)->child); + + appendStringInfoString(context->buf, j->cross ? " CROSS " : " UNION "); + + get_json_table_plan(tf, j->rplan, context, + IsA(j->rplan, JsonTableSiblingJoin) || + castNode(JsonTablePathScan, j->rplan)->child); + } + + if (parenthesize) + appendStringInfoChar(context->buf, ')'); +} + /* * get_json_table_columns - Parse back JSON_TABLE columns */ @@ -12684,6 +12767,7 @@ get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan, bool showimplicit) { StringInfo buf = context->buf; + JsonExpr *jexpr = castNode(JsonExpr, tf->docexpr); ListCell *lc_colname; ListCell *lc_coltype; ListCell *lc_coltypmod; @@ -12763,6 +12847,9 @@ get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan, default_behavior = JSON_BEHAVIOR_NULL; } + if (jexpr->on_error->btype == JSON_BEHAVIOR_ERROR) + default_behavior = JSON_BEHAVIOR_ERROR; + appendStringInfoString(buf, " PATH "); get_json_path_spec(colexpr->path_spec, context, showimplicit); @@ -12840,6 +12927,18 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit) get_json_table_columns(tf, castNode(JsonTablePathScan, tf->plan), context, showimplicit); + /* + * Deparse a PLAN clause only for a non-default plan; the default plan is + * implied by the NESTED COLUMNS structure (see + * json_table_plan_is_default). + */ + if (root->child && !json_table_plan_is_default((JsonTablePlan *) root)) + { + appendStringInfoChar(buf, ' '); + appendContextKeyword(context, "PLAN ", 0, 0, 0); + get_json_table_plan(tf, (JsonTablePlan *) root, context, true); + } + if (jexpr->on_error->btype != JSON_BEHAVIOR_EMPTY_ARRAY) get_json_behavior(jexpr->on_error, context, "ERROR"); diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index bf54d39feb0..4c9d2ec7c09 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -124,5 +124,10 @@ extern JsonTablePath *makeJsonTablePath(Const *pathvalue, char *pathname); extern JsonTablePathSpec *makeJsonTablePathSpec(char *string, char *name, int string_location, int name_location); +extern Node *makeJsonTableDefaultPlan(JsonTablePlanJoinType join_type, + int location); +extern Node *makeJsonTableSimplePlan(char *pathname, int location); +extern Node *makeJsonTableJoinedPlan(JsonTablePlanJoinType type, + Node *plan1, Node *plan2, int location); #endif /* MAKEFUNC_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index e03556399ab..77f69f94607 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1982,6 +1982,48 @@ typedef struct JsonTablePathSpec ParseLoc location; /* location of 'string' */ } JsonTablePathSpec; +/* + * JsonTablePlanType - + * flags for JSON_TABLE plan node types representation + */ +typedef enum JsonTablePlanType +{ + JSTP_DEFAULT, + JSTP_SIMPLE, + JSTP_JOINED, +} JsonTablePlanType; + +/* + * JsonTablePlanJoinType - + * JSON_TABLE join types for JSTP_JOINED plans + */ +typedef enum JsonTablePlanJoinType +{ + JSTP_JOIN_INNER = 0x01, + JSTP_JOIN_OUTER = 0x02, + JSTP_JOIN_CROSS = 0x04, + JSTP_JOIN_UNION = 0x08, +} JsonTablePlanJoinType; + +/* + * JsonTablePlanSpec - + * untransformed representation of JSON_TABLE's PLAN clause + */ +typedef struct JsonTablePlanSpec +{ + NodeTag type; + + JsonTablePlanType plan_type; /* plan type */ + JsonTablePlanJoinType join_type; /* join type (for joined plan only) */ + char *pathname; /* path name (for simple plan only) */ + + /* For joined plans */ + struct JsonTablePlanSpec *plan1; /* first joined plan */ + struct JsonTablePlanSpec *plan2; /* second joined plan */ + + ParseLoc location; /* token location, or -1 if unknown */ +} JsonTablePlanSpec; + /* * JsonTable - * untransformed representation of JSON_TABLE @@ -1993,6 +2035,7 @@ typedef struct JsonTable JsonTablePathSpec *pathspec; /* JSON path specification */ List *passing; /* list of PASSING clause arguments, if any */ List *columns; /* list of JsonTableColumn */ + JsonTablePlanSpec *planspec; /* join plan, if specified */ JsonBehavior *on_error; /* ON ERROR behavior */ Alias *alias; /* table alias in FROM clause */ bool lateral; /* does it have LATERAL prefix? */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index bb05aeebee4..cacef7d4151 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1926,6 +1926,7 @@ typedef struct JsonTablePathScan /* Plan(s) for nested columns, if any. */ JsonTablePlan *child; + bool outerJoin; /* outer or inner join for nested columns? */ /* * 0-based index in TableFunc.colvalexprs of the 1st and the last column @@ -1947,6 +1948,7 @@ typedef struct JsonTableSiblingJoin JsonTablePlan *lplan; JsonTablePlan *rplan; + bool cross; } JsonTableSiblingJoin; /* ---------------- diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index 458c5aaa5b0..e690d79c688 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -774,6 +774,244 @@ SELECT * FROM JSON_TABLE( ERROR: duplicate JSON_TABLE column or path name: a LINE 10: NESTED PATH '$' AS a ^ +-- JSON_TABLE: nested paths and plans +-- Path names are not required on the row pattern or the NESTED paths, even +-- when a PLAN clause is present; a name is generated for any path left +-- unnamed. The next two queries succeed. +-- PLAN DEFAULT with an unnamed row pattern +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' + COLUMNS ( + foo int PATH '$' + ) + PLAN DEFAULT (UNION) +) jt; + foo +----- + +(1 row) + +-- PLAN DEFAULT with an unnamed NESTED path +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( + foo int PATH '$' + ) + ) + PLAN DEFAULT (UNION) +) jt; + foo +----- + +(1 row) + +-- An unnamed NESTED path under an explicit PLAN() is also accepted, but the +-- generated name cannot be referenced by the plan, so it fails as a nested +-- path not covered by the plan. +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( + foo int PATH '$' + ) + ) + PLAN (path1) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 4: NESTED PATH '$' COLUMNS ( + ^ +DETAIL: PLAN clause for nested path json_table_path_0 was not found. +-- JSON_TABLE: plan validation +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p1) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 12: PLAN (p1) + ^ +DETAIL: PATH name mismatch: expected p0 but p1 is given. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 4: NESTED PATH '$' AS p1 COLUMNS ( + ^ +DETAIL: PLAN clause for nested path p1 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER p3) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 4: NESTED PATH '$' AS p1 COLUMNS ( + ^ +DETAIL: PLAN clause for nested path p1 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 UNION p1 UNION p11) +) jt; +ERROR: invalid JSON_TABLE plan clause +LINE 12: PLAN (p0 UNION p1 UNION p11) + ^ +DETAIL: Expected INNER or OUTER. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p13)) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 8: NESTED PATH '$' AS p2 COLUMNS ( + ^ +DETAIL: PLAN clause for nested path p2 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 5: NESTED PATH '$' AS p11 COLUMNS ( foo ... + ^ +DETAIL: PLAN clause for nested path p11 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 UNION p11) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE plan clause +LINE 12: PLAN (p0 OUTER ((p1 UNION p11) CROSS p2)) + ^ +DETAIL: PLAN clause contains some extra or duplicate sibling nodes. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER p11) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 6: NESTED PATH '$' AS p12 COLUMNS ( bar ... + ^ +DETAIL: PLAN clause for nested path p12 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE specification +LINE 9: NESTED PATH '$' AS p21 COLUMNS ( baz ... + ^ +DETAIL: PLAN clause for nested path p21 was not found. +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))) +) jt; + bar | foo | baz +-----+-----+----- +(0 rows) + +-- Should fail (top-level plan must join the row pattern using INNER or +-- OUTER, not a bare sibling CROSS) +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)) +) jt; +ERROR: invalid JSON_TABLE plan clause +LINE 12: PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)... + ^ +DETAIL: Expected INNER or OUTER. -- JSON_TABLE: plan execution CREATE TEMP TABLE jsonb_table_test (js jsonb); INSERT INTO jsonb_table_test @@ -813,6 +1051,325 @@ from 4 | -1 | 2 | 2 | | (11 rows) +-- default plan (outer, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, union) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(11 rows) + +-- specific plan (p outer (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb union pc)) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(11 rows) + +-- specific plan (p outer (pc union pb)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pc union pb)) + ) jt; + n | a | c | b +---+----+----+--- + 1 | 1 | | + 2 | 2 | 10 | + 2 | 2 | | + 2 | 2 | 20 | + 2 | 2 | | 1 + 2 | 2 | | 2 + 2 | 2 | | 3 + 3 | 3 | | 1 + 3 | 3 | | 2 + 4 | -1 | | 1 + 4 | -1 | | 2 +(11 rows) + +-- default plan (inner, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (inner) + ) jt; + n | a | b | c +---+----+---+---- + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(10 rows) + +-- specific plan (p inner (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb union pc)) + ) jt; + n | a | b | c +---+----+---+---- + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(10 rows) + +-- default plan (inner, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (cross, inner) + ) jt; + n | a | b | c +---+---+---+---- + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 +(9 rows) + +-- specific plan (p inner (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb cross pc)) + ) jt; + n | a | b | c +---+---+---+---- + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 +(9 rows) + +-- default plan (outer, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, cross) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 + 3 | 3 | | + 4 | -1 | | +(12 rows) + +-- specific plan (p outer (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb cross pc)) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 + 3 | 3 | | + 4 | -1 | | +(12 rows) + +select + jt.*, b1 + 100 as b +from + json_table (jsonb + '[ + {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]}, + {"a": 2, "b": [10, 20], "c": [1, null, 2]}, + {"x": "3", "b": [11, 22, 33, 44]} + ]', + '$[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on error, + nested path 'strict $.b[*]' as pb columns ( + b text format json path '$', + nested path 'strict $[*]' as pb1 columns ( + b1 int path '$' + ) + ), + nested path 'strict $.c[*]' as pc columns ( + c text format json path '$', + nested path 'strict $[*]' as pc1 columns ( + c1 int path '$' + ) + ) + ) + --plan default(outer, cross) + plan(p outer ((pb inner pb1) cross (pc outer pc1))) + ) jt; + n | a | b | b1 | c | c1 | b +---+---+--------------+-----+------+----+----- + 1 | 1 | [1, 10] | 1 | 1 | | 101 + 1 | 1 | [1, 10] | 1 | null | | 101 + 1 | 1 | [1, 10] | 1 | 2 | | 101 + 1 | 1 | [1, 10] | 10 | 1 | | 110 + 1 | 1 | [1, 10] | 10 | null | | 110 + 1 | 1 | [1, 10] | 10 | 2 | | 110 + 1 | 1 | [2] | 2 | 1 | | 102 + 1 | 1 | [2] | 2 | null | | 102 + 1 | 1 | [2] | 2 | 2 | | 102 + 1 | 1 | [3, 30, 300] | 3 | 1 | | 103 + 1 | 1 | [3, 30, 300] | 3 | null | | 103 + 1 | 1 | [3, 30, 300] | 3 | 2 | | 103 + 1 | 1 | [3, 30, 300] | 30 | 1 | | 130 + 1 | 1 | [3, 30, 300] | 30 | null | | 130 + 1 | 1 | [3, 30, 300] | 30 | 2 | | 130 + 1 | 1 | [3, 30, 300] | 300 | 1 | | 400 + 1 | 1 | [3, 30, 300] | 300 | null | | 400 + 1 | 1 | [3, 30, 300] | 300 | 2 | | 400 + 2 | 2 | | | | | + 3 | | | | | | +(20 rows) + -- PASSING arguments are passed to nested paths and their columns' paths SELECT * FROM @@ -876,6 +1433,7 @@ SELECT * FROM ) ) ); +CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo'); \sv jsonb_table_view_nested CREATE OR REPLACE VIEW public.jsonb_table_view_nested AS SELECT id, @@ -913,7 +1471,102 @@ CREATE OR REPLACE VIEW public.jsonb_table_view_nested AS ) ) ) +CREATE OR REPLACE VIEW public.jsonb_table_view AS + SELECT id, + "int", + text, + "char(4)", + bool, + "numeric", + domain, + js, + jb, + jst, + jsc, + jsv, + jsb, + jsbq, + aaa, + aaa1, + exists1, + exists2, + exists3, + js2, + jsb2w, + jsb2q, + ia, + ta, + jba, + a1, + b1, + a11, + a21, + a22 + FROM JSON_TABLE( + 'null'::jsonb, '$[*]' AS json_table_path_1 + PASSING + 1 + 2 AS a, + '"foo"'::json AS "b c" + COLUMNS ( + id FOR ORDINALITY, + "int" integer PATH '$', + text text PATH '$', + "char(4)" character(4) PATH '$', + bool boolean PATH '$', + "numeric" numeric PATH '$', + domain jsonb_test_domain PATH '$', + js json PATH '$', + jb jsonb PATH '$', + jst text FORMAT JSON PATH '$', + jsc character(4) FORMAT JSON PATH '$', + jsv character varying(4) FORMAT JSON PATH '$', + jsb jsonb PATH '$', + jsbq jsonb PATH '$' OMIT QUOTES, + aaa integer PATH '$."aaa"', + aaa1 integer PATH '$."aaa"', + exists1 boolean EXISTS PATH '$."aaa"', + exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, + exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, + js2 json PATH '$', + jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, + jsb2q jsonb PATH '$' OMIT QUOTES, + ia integer[] PATH '$', + ta text[] PATH '$', + jba jsonb[] PATH '$', + NESTED PATH '$[1]' AS p1 + COLUMNS ( + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + NESTED PATH '$[*]' AS "p1 1" + COLUMNS ( + a11 text PATH '$."a11"' + ) + ), + NESTED PATH '$[2]' AS p2 + COLUMNS ( + NESTED PATH '$[*]' AS "p2:1" + COLUMNS ( + a21 text PATH '$."a21"' + ), + NESTED PATH '$[*]' AS p22 + COLUMNS ( + a22 text PATH '$."a22"' + ) + ) + ) + PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))) + ); +EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Table Function Scan on "json_table" + Output: "json_table".id, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22 + Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$', js json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsbq jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES, jsb2q jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"')))) +(3 rows) + +DROP VIEW jsonb_table_view; DROP VIEW jsonb_table_view_nested; +DROP DOMAIN jsonb_test_domain; CREATE TABLE s (js jsonb); INSERT INTO s VALUES ('{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]},{"z22": [32, 204,145]}]},"c": 3}'), @@ -1152,7 +1805,7 @@ CREATE OR REPLACE VIEW public.json_table_view9 AS FROM JSON_TABLE( '"a"'::text, '$' AS json_table_path_0 COLUMNS ( - a text PATH '$' + a text PATH '$' NULL ON EMPTY ) ERROR ON ERROR ) DROP VIEW json_table_view8, json_table_view9; diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index 154eea79c76..14b75c8cc19 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -376,6 +376,187 @@ SELECT * FROM JSON_TABLE( ) ) jt; +-- JSON_TABLE: nested paths and plans +-- Path names are not required on the row pattern or the NESTED paths, even +-- when a PLAN clause is present; a name is generated for any path left +-- unnamed. The next two queries succeed. +-- PLAN DEFAULT with an unnamed row pattern +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' + COLUMNS ( + foo int PATH '$' + ) + PLAN DEFAULT (UNION) +) jt; +-- PLAN DEFAULT with an unnamed NESTED path +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( + foo int PATH '$' + ) + ) + PLAN DEFAULT (UNION) +) jt; +-- An unnamed NESTED path under an explicit PLAN() is also accepted, but the +-- generated name cannot be referenced by the plan, so it fails as a nested +-- path not covered by the plan. +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( + foo int PATH '$' + ) + ) + PLAN (path1) +) jt; + +-- JSON_TABLE: plan validation +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p1) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER p3) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 UNION p1 UNION p11) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p13)) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p2)) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 UNION p11) CROSS p2)) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER p11) CROSS p2)) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2)) +) jt; +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))) +) jt; +-- Should fail (top-level plan must join the row pattern using INNER or +-- OUTER, not a bare sibling CROSS) +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)) +) jt; -- JSON_TABLE: plan execution @@ -405,6 +586,170 @@ from ) ) jt; +-- default plan (outer, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, union) + ) jt; +-- specific plan (p outer (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb union pc)) + ) jt; +-- specific plan (p outer (pc union pb)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pc union pb)) + ) jt; +-- default plan (inner, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (inner) + ) jt; +-- specific plan (p inner (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb union pc)) + ) jt; +-- default plan (inner, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (cross, inner) + ) jt; +-- specific plan (p inner (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb cross pc)) + ) jt; +-- default plan (outer, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, cross) + ) jt; +-- specific plan (p outer (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb cross pc)) + ) jt; +select + jt.*, b1 + 100 as b +from + json_table (jsonb + '[ + {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]}, + {"a": 2, "b": [10, 20], "c": [1, null, 2]}, + {"x": "3", "b": [11, 22, 33, 44]} + ]', + '$[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on error, + nested path 'strict $.b[*]' as pb columns ( + b text format json path '$', + nested path 'strict $[*]' as pb1 columns ( + b1 int path '$' + ) + ), + nested path 'strict $.c[*]' as pc columns ( + c text format json path '$', + nested path 'strict $[*]' as pc1 columns ( + c1 int path '$' + ) + ) + ) + --plan default(outer, cross) + plan(p outer ((pb inner pb1) cross (pc outer pc1))) + ) jt; -- PASSING arguments are passed to nested paths and their columns' paths SELECT * @@ -450,8 +795,97 @@ SELECT * FROM ) ); +CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo'); \sv jsonb_table_view_nested +CREATE OR REPLACE VIEW public.jsonb_table_view AS + SELECT id, + "int", + text, + "char(4)", + bool, + "numeric", + domain, + js, + jb, + jst, + jsc, + jsv, + jsb, + jsbq, + aaa, + aaa1, + exists1, + exists2, + exists3, + js2, + jsb2w, + jsb2q, + ia, + ta, + jba, + a1, + b1, + a11, + a21, + a22 + FROM JSON_TABLE( + 'null'::jsonb, '$[*]' AS json_table_path_1 + PASSING + 1 + 2 AS a, + '"foo"'::json AS "b c" + COLUMNS ( + id FOR ORDINALITY, + "int" integer PATH '$', + text text PATH '$', + "char(4)" character(4) PATH '$', + bool boolean PATH '$', + "numeric" numeric PATH '$', + domain jsonb_test_domain PATH '$', + js json PATH '$', + jb jsonb PATH '$', + jst text FORMAT JSON PATH '$', + jsc character(4) FORMAT JSON PATH '$', + jsv character varying(4) FORMAT JSON PATH '$', + jsb jsonb PATH '$', + jsbq jsonb PATH '$' OMIT QUOTES, + aaa integer PATH '$."aaa"', + aaa1 integer PATH '$."aaa"', + exists1 boolean EXISTS PATH '$."aaa"', + exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, + exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, + js2 json PATH '$', + jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, + jsb2q jsonb PATH '$' OMIT QUOTES, + ia integer[] PATH '$', + ta text[] PATH '$', + jba jsonb[] PATH '$', + NESTED PATH '$[1]' AS p1 + COLUMNS ( + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + NESTED PATH '$[*]' AS "p1 1" + COLUMNS ( + a11 text PATH '$."a11"' + ) + ), + NESTED PATH '$[2]' AS p2 + COLUMNS ( + NESTED PATH '$[*]' AS "p2:1" + COLUMNS ( + a21 text PATH '$."a21"' + ), + NESTED PATH '$[*]' AS p22 + COLUMNS ( + a22 text PATH '$."a22"' + ) + ) + ) + PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))) + ); +EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; +DROP VIEW jsonb_table_view; DROP VIEW jsonb_table_view_nested; +DROP DOMAIN jsonb_test_domain; CREATE TABLE s (js jsonb); INSERT INTO s VALUES diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 117e7379f10..887a61fa7f3 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1508,7 +1508,10 @@ JsonTablePathScan JsonTablePathSpec JsonTablePlan JsonTablePlanRowSource +JsonTablePlanSpec JsonTablePlanState +JsonTablePlanJoinType +JsonTablePlanType JsonTableSiblingJoin JsonTokenType JsonTransformStringValuesAction -- 2.50.1 (Apple Git-155) ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL/JSON json_table plan clause 2025-02-04 03:05 Re: SQL/JSON json_table plan clause Amit Langote <[email protected]> 2025-02-04 11:50 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 2026-07-04 21:02 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 2026-07-07 23:38 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> @ 2026-07-08 15:35 ` Nikita Malakhov <[email protected]> 2026-07-08 17:03 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Nikita Malakhov @ 2026-07-08 15:35 UTC (permalink / raw) To: Alexander Korotkov <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers Hi! Alexander, thank you for your participation. I am very sorry, I've seen your previous email but was busy with other tasks and didn't have time to work on your notes. Thank you very much! On Wed, Jul 8, 2026 at 2:39 AM Alexander Korotkov <[email protected]> wrote: > Hi! > > On Sun, Jul 5, 2026 at 12:02 AM Alexander Korotkov <[email protected]> > wrote: > > On Fri, May 15, 2026 at 2:53 PM Nikita Malakhov <[email protected]> > wrote: > > > Again, Alexander and Amit, thanks for the review. I've rebased the > patch and made > > > some changes according to Alexander's notes. I've slightly rearranged > files between > > > patches for it to be easier to review, so now there are 3 patches: > > > v23-0001-json-table-plan-clause.patch - main code changes > > > v23-0002-json-table-plan-tests.patch - test cases and out file changes > > > v23-0003-json-table-plan-docs.patch - docs package > > > > > > <...> > > > >1. IsA(planstate, JsonTableSiblingJoin) is wrong. planstate is not a > > > >node, thus IsA() can't be applied to it. You should instead do > > > >IsA(planstate->plan, JsonTableSiblingJoin). It wasn't catched, > > > >because regression tests don't exercise this branch. So, you also > > > >need to improve the coverage. > > > - done; > > > > > > >2. get_json_table() with patch uses JSON_BEHAVIOR_EMPTY as the default > > > >value for deparsing, while parsing still uses > > > >JSON_BEHAVIOR_EMPTY_ARRAY. Looks plain wrong. I'm not sure what is > > > >intention here. > > > - looks like leftover from older version, changed to default behavior, > so unnecessary > > > emission of ERROR is omitted; > > > > > > >3. PLAN clause is always emitted during deparsing even if user didn't > > > >specify anything. I would prefer to skip PLAN clause in this case > > > >unless there is strong reason to do the opposite (this reason must be > > > >pointed if any). > > > - done, this made test cases much more readable; > > > > > > >4. Unused typedefs in src/tools/pgindent/typedefs.list: > > > >JsonTableScanState, JsonPathSpec, JsonTablePlanStateType, > > > >JsonTableJoinState. > > > - done; > > > > > > >5. Empty comment in JsonTablePlanState definition. Pointed by Amit, > > > >but not fixed. > > > - done; > > > > > > >6. Rename passingArgs to passing_Args for no reason in > parse_jsontable.c. > > > - done; > > > > > > >7. Patch lacks documentation (also pointed by Amit) > > > - done, documentation is provided in separate patch; > > > > > > >8. Patch could use pgindent run. > > > - not done yet but would provide a newer version with it. > > > > Thank you. I've made following additional changes. > > > > 1. Actually, I don't see my previous item 3 fully addressed. We > > still had PLAN clause deparsed in the case user didn't specify it. I > > see this as an undesirable behavior. And there are at least two ways > > to fix this: don't output PLAN clause if it's not the default, > > remember if user specified as a special flag or even original clause. > > I found first way (don't output default) more appealing as it don't > > require additional struct members and we already do this in other > > similar cases (NULLS FIRST/LAST, ON EMPTY/ON ERROR). So, I did this > > for PLAN clause. > > 2. Simplification for JsonTablePlanNextRow(): no reason to check > > (planstate->advanceNested || planstate->nested) if we already issued > > break for (planstate->nested == NULL). > > 3. Similar simplification for transformJsonTableNestedColumns(). > > I made some additional changes to the patch. In the previous versions > PLAN qual required name for root path, but didn't required names for > nested paths. I've rechecked with standard, and it appears that > standard claims the opposite: name is not required for the root path, > but required for nested paths. So, I decided to allow skipping name > for both root path (as standard claims), and nested path (our > extension to standard). Corresponding changes made to code, tests and > documentation. > > Also, I merged 3 patches into 1, and edited sql_features.txt. > > I'm going to push this if no objections. > > ------ > Regards, > Alexander Korotkov > Supabase > -- Regards, Nikita Malakhov Postgres Professional The Russian Postgres Company https://postgrespro.ru/ ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL/JSON json_table plan clause 2025-02-04 03:05 Re: SQL/JSON json_table plan clause Amit Langote <[email protected]> 2025-02-04 11:50 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 2026-07-04 21:02 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 2026-07-07 23:38 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 2026-07-08 15:35 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> @ 2026-07-08 17:03 ` Alexander Korotkov <[email protected]> 2026-07-09 11:35 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 2026-07-09 16:11 ` Re: SQL/JSON json_table plan clause Thom Brown <[email protected]> 0 siblings, 2 replies; 9+ messages in thread From: Alexander Korotkov @ 2026-07-08 17:03 UTC (permalink / raw) To: Nikita Malakhov <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers On Wed, Jul 8, 2026 at 6:35 PM Nikita Malakhov <[email protected]> wrote: > Alexander, thank you for your participation. I am very sorry, I've seen your previous > email but was busy with other tasks and didn't have time to work on your notes. > Thank you very much! OK, no problem. Are you good with the current shape of the patch? ------ Regards, Alexander Korotkov Supabase ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL/JSON json_table plan clause 2025-02-04 03:05 Re: SQL/JSON json_table plan clause Amit Langote <[email protected]> 2025-02-04 11:50 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 2026-07-04 21:02 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 2026-07-07 23:38 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 2026-07-08 15:35 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 2026-07-08 17:03 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> @ 2026-07-09 11:35 ` Nikita Malakhov <[email protected]> 1 sibling, 0 replies; 9+ messages in thread From: Nikita Malakhov @ 2026-07-09 11:35 UTC (permalink / raw) To: Alexander Korotkov <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers Hi Alexander! Don't have any objections. I've looked through your version of the patch, additional comments are very helpful. Thank you! On Wed, Jul 8, 2026 at 8:04 PM Alexander Korotkov <[email protected]> wrote: > On Wed, Jul 8, 2026 at 6:35 PM Nikita Malakhov <[email protected]> wrote: > > Alexander, thank you for your participation. I am very sorry, I've seen > your previous > > email but was busy with other tasks and didn't have time to work on your > notes. > > Thank you very much! > > OK, no problem. Are you good with the current shape of the patch? > > ------ > Regards, > Alexander Korotkov > Supabase > -- Regards, Nikita Malakhov Postgres Professional The Russian Postgres Company https://postgrespro.ru/ ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL/JSON json_table plan clause 2025-02-04 03:05 Re: SQL/JSON json_table plan clause Amit Langote <[email protected]> 2025-02-04 11:50 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 2026-07-04 21:02 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 2026-07-07 23:38 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 2026-07-08 15:35 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 2026-07-08 17:03 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> @ 2026-07-09 16:11 ` Thom Brown <[email protected]> 1 sibling, 0 replies; 9+ messages in thread From: Thom Brown @ 2026-07-09 16:11 UTC (permalink / raw) To: Alexander Korotkov <[email protected]>; +Cc: Nikita Malakhov <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers On Wed, 8 Jul 2026 at 18:04, Alexander Korotkov <[email protected]> wrote: > > On Wed, Jul 8, 2026 at 6:35 PM Nikita Malakhov <[email protected]> wrote: > > Alexander, thank you for your participation. I am very sorry, I've seen your previous > > email but was busy with other tasks and didn't have time to work on your notes. > > Thank you very much! > > OK, no problem. Are you good with the current shape of the patch? I've been giving this a test drive. I saw that this has been committed, and re-tested against that, and the issues I identified (unless I've misunderstood some functionality) seem to have survived. First, doesn't this need a catversion bump? This adds fields to existing node types. I only get the first column from the following. The second one disappears without error: SELECT * FROM JSON_TABLE( jsonb '[{"x":[1],"y":[2]}]', '$[*]' AS p0 COLUMNS ( NESTED PATH '$.x[*]' AS json_table_path_0 COLUMNS (x int PATH '$'), NESTED PATH '$.y[*]' COLUMNS (y int PATH '$') ) PLAN (p0 OUTER json_table_path_0) ) jt; x --- 1 (1 row) I have the following view: CREATE VIEW v_chain AS SELECT * FROM JSON_TABLE( jsonb '[{"x": [{"y": [1,2]}]}]', '$[*]' AS p0 COLUMNS ( NESTED PATH '$.x[*]' AS p1 COLUMNS ( NESTED PATH '$.y[*]' AS p11 COLUMNS ( y int PATH '$' ) ) ) PLAN (p0 OUTER (p1 INNER p11)) ) jt; When I dump it, I get: CREATE VIEW public.v_chain AS SELECT y FROM JSON_TABLE( '[{"x": [{"y": [1, 2]}]}]'::jsonb, '$[*]' AS p0 COLUMNS ( NESTED PATH '$."x"[*]' AS p1 COLUMNS ( NESTED PATH '$."y"[*]' AS p11 COLUMNS ( y integer PATH '$' ) ) ) PLAN (p0 OUTER p1 INNER p11) ) jt; The parentheses around "p1 INNER p11" aren't preserved. Another dump issue with the following: CREATE VIEW v_onempty AS SELECT * FROM JSON_TABLE( jsonb '{}', '$' AS p0 COLUMNS ( a int PATH '$.nosuch' ERROR ON EMPTY ) ERROR ON ERROR ) jt; This dumps as: CREATE VIEW public.v_onempty AS SELECT a FROM JSON_TABLE( '{}'::jsonb, '$' AS p0 COLUMNS ( a integer PATH '$."nosuch"' ) ERROR ON ERROR ) jt; It's lost "ERROR ON EMPTY". Another example: SELECT * FROM JSON_TABLE(jsonb '"mystring"', '$' COLUMNS (a int PATH '$') ERROR ON ERROR ) jt; This gives me: ERROR: invalid input syntax for type integer: "mystring" But the documentation says that the table-level clause "does not affect the errors that occur when evaluating columns". Am I missing something here? I haven't done any performance testing yet Thom ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-07-09 16:11 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-12-18 16:19 [PATCH v41 1/2] Subscripting for jsonb Dmitrii Dolgov <[email protected]> 2025-02-04 03:05 Re: SQL/JSON json_table plan clause Amit Langote <[email protected]> 2025-02-04 11:50 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 2026-07-04 21:02 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 2026-07-07 23:38 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 2026-07-08 15:35 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 2026-07-08 17:03 ` Re: SQL/JSON json_table plan clause Alexander Korotkov <[email protected]> 2026-07-09 11:35 ` Re: SQL/JSON json_table plan clause Nikita Malakhov <[email protected]> 2026-07-09 16:11 ` Re: SQL/JSON json_table plan clause Thom Brown <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox