public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexandra Wang <[email protected]>
To: Peter Eisentraut <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Nikita Glukhov <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: David E. Wheeler <[email protected]>
Cc: jian he <[email protected]>
Subject: Re: SQL:2023 JSON simplified accessor support
Date: Wed, 5 Feb 2025 01:20:30 -0600
Message-ID: <CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAK98qZ2QGcyJrJAFv9wjY6S8yP9dUVnmG9Gb4OXuzuMMuM1Z5Q@mail.gmail.com>
<[email protected]>
<[email protected]>
Hi hackers,
On Tue, Nov 26, 2024 at 3:12 AM Peter Eisentraut <[email protected]>
wrote:
> On 21.11.24 23:46, Andrew Dunstan wrote:
> >> Questions:
> >>
> >> 1. Since Nikita’s patches did not address the JSON data type, and JSON
> >> currently does not support subscripting, should we limit the initial
> >> feature set to JSONB dot-notation for now? In other words, if we aim
> >> to fully support JSON simplified accessors for the plain JSON type,
> >> should we handle support for plain JSON subscripting as a follow-up
> >> effort?
> >>
> >> 2. I have yet to have a more thorough review of Nikita’s patches.
> >> One area I am not familiar with is the hstore-related changes. How
> >> relevant is hstore to the JSON simplified accessor?
> >>
> >
> > We can't change the way the "->" operator works, as there could well be
> > uses of it in the field that rely on its current behaviour. But maybe we
> > could invent a new operator which is compliant with the standard
> > semantics for dot access, and call that. Then we'd get the best
> > performance, and also we might be able to implement it for the plain
> > JSON type. If that proves not possible we can think about not
> > implementing for plain JSON, but I'd rather not go there until we have
> to.
>
> Yes, I think writing a custom operator that is similar to "->" but has
> the required semantics is the best way forward. (Maybe it can be just a
> function?)
>
> > I don't think we should be including hstore changes here - we should
> > just be aiming at implementing the standard for JSON access. hstore
> > changes if any should be a separate feature. The aren't relevant to JSON
> > access, although they might use some of the same infrastructure,
> > depending on implementation.
>
> In a future version, the operator/function mentioned above could be a
> catalogued property of a type, similar to typsubscript. Then you could
> also apply this to other types. But let's leave that for later.
>
> If I understand it correctly, Nikita's patch uses the typsubscript
> support function to handle both bracket subscripting and dot notation.
> I'm not sure if it's right to mix these two together. Maybe I didn't
> understand that correctly.
>
I’ve been working on a custom operator-like function to support dot
notation in lax mode for JSONB. However, I realized this approach has
the following drawbacks:
1. Handling both dot notation and bracket subscripting together
becomes complicated, as we still need to consider jsonb’s existing
type subscript functions.
2. Chaining N dot-access operators causes multiple unnecessary
deserialization/serialization cycles: for each operator call, the source
jsonb binary is converted to an in-memory JsonbValue, then the
relevant field is extracted, and finally it’s turned back into a
binary jsonb object. This hurts performance. A direct use of the
jsonpath functions API seems more efficient.
3. Correctly applying lax mode requires different handling for the
first, middle, and last operators, which adds further complexity.
Because of these issues, I took a closer look at Nikita’s patch. His
solution generalizes the existing jsonb typesubscript support function
to handle both bracket subscripting and dot notation. It achieves this
by translating dot notation into a jsonpath expression during
transformation, and then calls JsonPathQuery at execution.
Overall, I find this approach more efficient for chained accessors and
more flexible for future enhancements.
I attached a minimized version of Nikita’s patch (v7):
- The first three patches are refactoring steps that could be squashed
if preferred.
- The last two patches implement dot notation and wildcard access,
respectively.
Changes in this new version:
- Removed code handling hstore, as Andrew pointed out it isn’t
directly relevant to JSON access and should be handled separately.
- Split tests for dot notation and wildcard access.
- Dropped the two patches in v6 that enabled non-parenthesized column
references (per Nikita’s suggestion, this will need its own separate
discussion).
For reference, I’ve also attached the operator-like function approach
in 0001-WIP-Operator-approach-JSONB-dot-notation.txt.
I’d appreciate any feedback and thoughts!
Best,
Alex
From 56241895578e0d16d66518b8470005779cd2132c Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 14 Jan 2025 15:14:35 -0600
Subject: [PATCH] [WIP] Operator apporach: JSONB dot notation
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Implement an operator-like function `jsonb_object_field_dot()` that
accesses JSONB object fields one at a time.
Array unwrapping (lax mode) is handled differently depending on
whether the operation is the first in a chain of indirections.
Similarly, conditional wrapping is handled differently depending on
whether the operation is the last in that chain.
TODO:
- Currently, this commit might generate incorrect results when mixing
dot notation access with existing subscripting access. Fixing this
should not be difficult. However, performance comparisons with the
alternative approach have led to postponing the fix.
- Ideally, the function would use JsonPath or a pipelined chain of
operators instead of handling each dot access
individually—essentially what JsonPathQuery() does—leading to the
alternative approach.
Performance comparison with the generic subscripting approach:
-- setup:
create table tbl(id int, col1 jsonb);
insert into tbl select i, '{"x":"vx", "y":[{"a":[1,2,3]}, {"b":[1, 2,
{"j":"vj"}]}]}' from generate_series(1, 100000)i;
-- query 1: chained dot access
SELECT id, (col1).x.b.j AS jsonb_operator FROM tbl;
-- pgbench -n -f <query 1>.sql test -T100
This patch:
number of transactions actually processed: 6620
latency average = 15.107 ms
tps = 66.193017 (without initial connection time)
Nikita's patch (generic subscripting using json_query()):
number of transactions actually processed: 8036
latency average = 12.445 ms
tps = 80.352075 (without initial connection time)
It is expected that this patch is less performant because it currently
deserializes and serializes the nested JSONB binary three times (once
for each invocation of the operator-like function).
-- query 2: single dot access
SELECT id, (col1).x AS jsonb_operator FROM tbl;
-- pgbench -n -f <query 2>.sql test -T100
This patch:
number of transactions actually processed: 5653
latency average = 17.691 ms
tps = 56.524496 (without initial connection time)
Nikita's patch:
number of transactions actually processed: 4989
latency average = 20.044 ms
tps = 49.890189 (without initial connection time)
This patch performs slightly better for single dot accesses, though
the margin is not significant.
---
src/backend/parser/parse_expr.c | 33 +++-
src/backend/parser/parse_func.c | 36 ++++
src/backend/utils/adt/jsonfuncs.c | 262 ++++++++++++++++++++++++++++
src/include/catalog/pg_operator.dat | 2 +-
src/include/catalog/pg_proc.dat | 4 +
src/include/parser/parse_func.h | 2 +
src/test/regress/expected/jsonb.out | 103 +++++++++++
src/test/regress/sql/jsonb.sql | 47 +++++
8 files changed, 481 insertions(+), 8 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index bad1df732ea..e3b6626983b 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -440,6 +440,8 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
List *subscripts = NIL;
int location = exprLocation(result);
ListCell *i;
+ bool json_accessor_chain_first = false;
+ bool json_accessor_chain_last = false;
/*
* We have to split any field-selection operations apart from
@@ -462,6 +464,8 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
else
{
Node *newresult;
+ Oid result_typid;
+ Oid result_basetypid;
Assert(IsA(n, String));
@@ -475,13 +479,28 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
false);
subscripts = NIL;
- newresult = ParseFuncOrColumn(pstate,
- list_make1(n),
- list_make1(result),
- last_srf,
- NULL,
- false,
- location);
+ result_typid = exprType(result);
+ result_basetypid = (result_typid == JSONOID || result_typid == JSONBOID) ?
+ result_typid : getBaseType(result_typid);
+
+ if (result_basetypid == JSONBOID)
+ {
+ json_accessor_chain_first = (i == list_head(ind->indirection));
+ if (lnext(ind->indirection, i) == NULL)
+ json_accessor_chain_last = true;
+ newresult = ParseJsonbSimplifiedAccessorObjectField(pstate,
+ strVal(n),
+ result,
+ location, result_basetypid, json_accessor_chain_first, json_accessor_chain_last);
+ }
+ else
+ newresult = ParseFuncOrColumn(pstate,
+ list_make1(n),
+ list_make1(result),
+ last_srf,
+ NULL,
+ false,
+ location);
if (newresult == NULL)
unknown_attribute(pstate, result, strVal(n), location);
result = newresult;
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 583bbbf232f..dba2a60fadc 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -1902,6 +1902,42 @@ FuncNameAsType(List *funcname)
return result;
}
+/*
+ * ParseJsonbSimplifiedAccessorObjectField -
+ * handles function calls with a single argument that is of json type.
+ * If the function call is actually a column projection, return a suitably
+ * transformed expression tree. If not, return NULL.
+ */
+Node *
+ParseJsonbSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname, Node *first_arg, int location,
+ Oid basetypid, bool first_op, bool last_op)
+{
+ FuncExpr *result;
+ Node *rexpr;
+
+ if (basetypid != JSONBOID)
+ elog(ERROR, "unsupported type OID: %u", basetypid);
+
+ rexpr = (Node *) makeConst(
+ TEXTOID,
+ -1,
+ InvalidOid,
+ -1,
+ CStringGetTextDatum(funcname),
+ false,
+ false);
+ result = makeFuncExpr(4100,
+ JSONBOID,
+ list_make4(first_arg, rexpr,
+ makeBoolConst(first_op, false),
+ makeBoolConst(last_op, false)),
+ InvalidOid,
+ InvalidOid,
+ COERCE_EXPLICIT_CALL);
+
+ return (Node *) result;
+}
+
/*
* ParseComplexProjection -
* handles function calls with a single argument that is of complex type.
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index c2e90f1a3bf..a32aef3bfac 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -879,6 +879,268 @@ jsonb_object_field(PG_FUNCTION_ARGS)
PG_RETURN_NULL();
}
+static void
+expand_jbvBinary_in_memory(const JsonbValue *binaryVal, JsonbValue *expandedVal)
+{
+ Assert(binaryVal->type == jbvBinary);
+ JsonbContainer *container = (JsonbContainer *) binaryVal->val.binary.data;
+ JsonbIterator *it;
+ JsonbValue v;
+ JsonbIteratorToken token;
+
+ it = JsonbIteratorInit(container);
+ token = JsonbIteratorNext(&it, &v, false);
+
+ if (token == WJB_BEGIN_OBJECT)
+ {
+ /*
+ * We'll read all keys/values until WJB_END_OBJECT and build
+ * expandedVal->type = jbvObject.
+ */
+ List *keyvals = NIL;
+
+ while ((token = JsonbIteratorNext(&it, &v, false)) != WJB_END_OBJECT)
+ {
+ if (token == WJB_KEY)
+ {
+ JsonbValue key;
+
+ key = v;
+ token = JsonbIteratorNext(&it, &v, true);
+ if (token == WJB_VALUE)
+ {
+ JsonbValue val;
+ JsonbValue *objPair;
+
+ if (v.type == jbvBinary)
+ {
+ /* Recursively expand nested objects/arrays. */
+ expand_jbvBinary_in_memory(&v, &val);
+ }
+ else
+ {
+ /* Scalar or already jbvObject/jbvArray. Copy as-is. */
+ val = v;
+ }
+
+ /*
+ * Build a small pair (key, value). We'll store them in a
+ * list.
+ */
+ objPair = palloc(sizeof(JsonbValue) * 2);
+ objPair[0] = key;
+ objPair[1] = val;
+ keyvals = lappend(keyvals, objPair);
+ }
+ }
+ }
+
+ /* Now convert our keyvals list into a jbvObject. */
+ int nPairs = list_length(keyvals);
+
+ expandedVal->type = jbvObject;
+ expandedVal->val.object.nPairs = nPairs;
+ expandedVal->val.object.pairs = palloc(sizeof(JsonbPair) * nPairs);
+
+ {
+ int i = 0;
+ ListCell *lc;
+
+ foreach(lc, keyvals)
+ {
+ JsonbValue *kv = (JsonbValue *) lfirst(lc);
+
+ /* kv[0] = key, kv[1] = value */
+
+ expandedVal->val.object.pairs[i].key = kv[0];
+ expandedVal->val.object.pairs[i].value = kv[1];
+ expandedVal->val.object.pairs[i].order = i;
+ i++;
+ }
+ }
+ }
+ else if (token == WJB_BEGIN_ARRAY)
+ {
+ /*
+ * We'll read array elems until WJB_END_ARRAY and build
+ * expandedVal->type = jbvArray.
+ */
+ List *elems = NIL;
+
+ while ((token = JsonbIteratorNext(&it, &v, true)) != WJB_END_ARRAY)
+ {
+ if (token == WJB_ELEM)
+ {
+ /* If it's jbvBinary, recursively expand again. */
+ JsonbValue val;
+
+ if (v.type == jbvBinary)
+ {
+ expand_jbvBinary_in_memory(&v, &val);
+ }
+ else
+ {
+ val = v;
+ }
+ JsonbValue *elemCopy = palloc(sizeof(JsonbValue));
+
+ *elemCopy = val;
+ elems = lappend(elems, elemCopy);
+ }
+ }
+
+ expandedVal->type = jbvArray;
+ expandedVal->val.array.nElems = list_length(elems);
+ expandedVal->val.array.rawScalar = false;
+ expandedVal->val.array.elems = palloc(sizeof(JsonbValue) * expandedVal->val.array.nElems);
+
+ {
+ int i = 0;
+ ListCell *lc;
+
+ foreach(lc, elems)
+ {
+ JsonbValue *vptr = (JsonbValue *) lfirst(lc);
+
+ expandedVal->val.array.elems[i++] = *vptr;
+ }
+ }
+ }
+ else
+ {
+ /*
+ * Possibly a scalar container (WJB_ELEM or WJB_VALUE with jbvNumeric,
+ * jbvString, etc.). If this container truly only has one scalar,
+ * token might be WJB_ELEM or similar. For simplicity, let's check
+ * tmp.type. If it's jbvString/jbvNumeric, copy it directly.
+ */
+ expandedVal->type = v.type;
+ expandedVal->val = v.val;
+ }
+}
+
+static List *
+jsonb_object_field_unwrap_array(JsonbContainer *jb, text *key, bool unwrap_nested)
+{
+ JsonbIterator *it;
+ JsonbValue v;
+ JsonbIteratorToken token;
+ List *resultList = NIL;
+ int arraySize;
+
+ it = JsonbIteratorInit(jb);
+ token = JsonbIteratorNext(&it, &v, false);
+
+ Assert(token == WJB_BEGIN_ARRAY);
+ arraySize = v.val.array.nElems;
+
+ /* Unwrap out-most array elements and extract the key value */
+ for (int i = 0; i < arraySize; i++)
+ {
+ token = JsonbIteratorNext(&it, &v, true);
+ if (token == WJB_ELEM && v.type == jbvBinary)
+ {
+ JsonbContainer *elemContainer = (JsonbContainer *) v.val.binary.data;
+
+ if (JsonContainerIsObject(elemContainer))
+ {
+ JsonbValue *extractedValue;
+ JsonbValue vbuf;
+
+ extractedValue = getKeyJsonValueFromContainer(elemContainer,
+ VARDATA_ANY(key),
+ VARSIZE_ANY_EXHDR(key),
+ &vbuf);
+ if (extractedValue != NULL)
+ {
+ JsonbValue *copy;
+
+ if (extractedValue->type == jbvBinary)
+ {
+ JsonbValue expanded;
+
+ expand_jbvBinary_in_memory(extractedValue, &expanded);
+
+ copy = palloc(sizeof(expanded));
+ *copy = expanded;
+ resultList = lappend(resultList, copy);
+ }
+ else
+ {
+ copy = palloc(sizeof(*extractedValue));
+ *copy = *extractedValue;
+ resultList = lappend(resultList, copy);
+ }
+ }
+ }
+ else if (unwrap_nested && JsonContainerIsArray(elemContainer))
+ {
+ resultList = jsonb_object_field_unwrap_array(elemContainer, key, false);
+ }
+ }
+ }
+ token = JsonbIteratorNext(&it, &v, true);
+
+ return resultList;
+}
+
+Datum
+jsonb_object_field_dot(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb = PG_GETARG_JSONB_P(0);
+ text *key = PG_GETARG_TEXT_PP(1);
+ bool first_op = PG_GETARG_BOOL(2);
+ bool last_op = PG_GETARG_BOOL(3);
+
+ if (JB_ROOT_IS_OBJECT(jb))
+ {
+ JsonbValue *v;
+ JsonbValue vbuf;
+
+ v = getKeyJsonValueFromContainer(&jb->root,
+ VARDATA_ANY(key),
+ VARSIZE_ANY_EXHDR(key),
+ &vbuf);
+
+ if (v != NULL)
+ PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
+ }
+ else if (JB_ROOT_IS_ARRAY(jb))
+ {
+ List *resultList;
+ JsonbValue resultVal;
+
+ resultList = jsonb_object_field_unwrap_array(&jb->root, key, !first_op);
+
+ if (list_length(resultList) == 0)
+ PG_RETURN_NULL();
+ else if (!last_op || list_length(resultList) > 1)
+ {
+ /* Conditional wrap result */
+ resultVal.type = jbvArray;
+ resultVal.val.array.rawScalar = false;
+ resultVal.val.array.nElems = list_length(resultList);
+ resultVal.val.array.elems = (JsonbValue *) palloc(sizeof(JsonbValue) * list_length(resultList));
+
+ int idx = 0;
+ ListCell *lc;
+ JsonbValue *elem;
+
+ foreach(lc, resultList)
+ {
+ elem = (JsonbValue *) lfirst(lc);
+ resultVal.val.array.elems[idx++] = *elem;
+ }
+ }
+ else
+ resultVal = *(JsonbValue *) linitial(resultList);
+
+ PG_RETURN_JSONB_P(JsonbValueToJsonb(&resultVal));
+ }
+
+ PG_RETURN_NULL();
+}
+
Datum
json_object_field_text(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index 6d9dc1528d6..70887d3fd4d 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3173,7 +3173,7 @@
{ oid => '3967', descr => 'get value from json as text with path elements',
oprname => '#>>', oprleft => 'json', oprright => '_text', oprresult => 'text',
oprcode => 'json_extract_path_text' },
-{ oid => '3211', descr => 'get jsonb object field',
+{ oid => '3211', oid_symbol => 'OID_JSONB_OBJECT_FIELD_OP', descr => 'get jsonb object field',
oprname => '->', oprleft => 'jsonb', oprright => 'text', oprresult => 'jsonb',
oprcode => 'jsonb_object_field' },
{ oid => '3477', descr => 'get jsonb object field as text',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5b8c2ad2a54..cabde2e07e8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9338,6 +9338,10 @@
{ oid => '3968', descr => 'get the type of a json value',
proname => 'json_typeof', prorettype => 'text', proargtypes => 'json',
prosrc => 'json_typeof' },
+{ oid => '4100',
+ proname => 'jsonb_object_field_dot', prorettype => 'jsonb',
+ proargtypes => 'jsonb text bool bool', proargnames => '{from_json, field_name, first_op, last_op}',
+ prosrc => 'jsonb_object_field_dot' },
# uuid
{ oid => '2952', descr => 'I/O',
diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h
index a6f24b83d84..745d2c75c93 100644
--- a/src/include/parser/parse_func.h
+++ b/src/include/parser/parse_func.h
@@ -34,6 +34,8 @@ typedef enum
extern Node *ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
Node *last_srf, FuncCall *fn, bool proc_call,
int location);
+extern Node *ParseJsonbSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname, Node *first_arg, int location,
+ Oid basetypid, bool first_op, bool last_op);
extern FuncDetailCode func_get_detail(List *funcname,
List *fargs, List *fargnames,
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 2baff931bf2..e0c0757f475 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5781,3 +5781,106 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
12345
(1 row)
+-- simple dot notation
+-- TODO: add comments
+CREATE OR REPLACE FUNCTION test_jsonb_dot_notation(
+ vep jsonb, -- value expression primary
+ jc text -- JSON simplified accessor operator chain
+)
+ RETURNS TABLE(dot_access jsonb, expected jsonb)
+ LANGUAGE plpgsql
+AS $$
+DECLARE
+ dyn_sql text;
+BEGIN
+ dyn_sql := format($f$
+ SELECT
+ (vep).%s AS dot_access,
+ json_query(vep, 'lax $.%s' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) AS expected
+ FROM (SELECT $1::jsonb AS vep) dummy
+ $f$, jc, jc);
+
+ -- -- OPTIONAL: Just to see the constructed SQL in logs
+-- RAISE NOTICE 'Executing: %', dyn_sql;
+
+ -- Execute the dynamic query, substituting p_col as parameter #1
+ RETURN QUERY EXECUTE dyn_sql USING vep;
+END;
+$$;
+-- access member object field of a json object
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42}'::jsonb, 'b');
+ dot_access | expected
+------------+----------
+ 42 | 42
+(1 row)
+
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42}'::jsonb, 'not_exist');
+ dot_access | expected
+------------+----------
+ |
+(1 row)
+
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42, "b":12}'::jsonb, 'b'); -- return last for duplicate key
+ dot_access | expected
+------------+----------
+ 12 | 12
+(1 row)
+
+select * from test_jsonb_dot_notation('{"a": 1, "b": 12, "b":42}'::jsonb, 'b');
+ dot_access | expected
+------------+----------
+ 42 | 42
+(1 row)
+
+select * from test_jsonb_dot_notation('{"a": 2, "b": {"c": 42}}'::jsonb, 'b.c');
+ dot_access | expected
+------------+----------
+ 42 | 42
+(1 row)
+
+select * from test_jsonb_dot_notation('{"a": 4, "b": {"c": {"d": [11, 12]}}}'::jsonb, 'b.c.d');
+ dot_access | expected
+------------+----------
+ [11, 12] | [11, 12]
+(1 row)
+
+-- access member object field of a json array: apply lax mode + conditional wrap
+-- unwrap the outer-most array into sequence and conditional wrap the results
+-- only unwrap the outer most array
+select * from test_jsonb_dot_notation('[{"x": 42}]'::jsonb, 'x');
+ dot_access | expected
+------------+----------
+ 42 | 42
+(1 row)
+
+select * from test_jsonb_dot_notation('["x"]'::jsonb, 'x');
+ dot_access | expected
+------------+----------
+ |
+(1 row)
+
+select * from test_jsonb_dot_notation('[[{"x": 42}]]'::jsonb, 'x');
+ dot_access | expected
+------------+----------
+ |
+(1 row)
+
+-- wrap the result into an array on the conditional of more than one matched object keys
+select * from test_jsonb_dot_notation('[{"x": 42}, {"x": {"y": {"z": 12}}}]'::jsonb, 'x');
+ dot_access | expected
+------------------------+------------------------
+ [42, {"y": {"z": 12}}] | [42, {"y": {"z": 12}}]
+(1 row)
+
+select * from test_jsonb_dot_notation('[{"x": 42}, [{"x": {"y": {"z": 12}}}]]'::jsonb, 'x');
+ dot_access | expected
+------------+----------
+ 42 | 42
+(1 row)
+
+select * from test_jsonb_dot_notation('[{"x": 42}, {"x": [{"y": 12}, {"y": {"z": 12}}]}]'::jsonb, 'x.y');
+ dot_access | expected
+-----------------+-----------------
+ [12, {"z": 12}] | [12, {"z": 12}]
+(1 row)
+
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 544bb610e2d..5a6d820b507 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1572,3 +1572,50 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- simple dot notation
+-- TODO: add comments
+
+CREATE OR REPLACE FUNCTION test_jsonb_dot_notation(
+ vep jsonb, -- value expression primary
+ jc text -- JSON simplified accessor operator chain
+)
+ RETURNS TABLE(dot_access jsonb, expected jsonb)
+ LANGUAGE plpgsql
+AS $$
+DECLARE
+ dyn_sql text;
+BEGIN
+ dyn_sql := format($f$
+ SELECT
+ (vep).%s AS dot_access,
+ json_query(vep, 'lax $.%s' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) AS expected
+ FROM (SELECT $1::jsonb AS vep) dummy
+ $f$, jc, jc);
+
+ -- -- OPTIONAL: Just to see the constructed SQL in logs
+-- RAISE NOTICE 'Executing: %', dyn_sql;
+
+ -- Execute the dynamic query, substituting p_col as parameter #1
+ RETURN QUERY EXECUTE dyn_sql USING vep;
+END;
+$$;
+
+-- access member object field of a json object
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42}'::jsonb, 'b');
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42}'::jsonb, 'not_exist');
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42, "b":12}'::jsonb, 'b'); -- return last for duplicate key
+select * from test_jsonb_dot_notation('{"a": 1, "b": 12, "b":42}'::jsonb, 'b');
+select * from test_jsonb_dot_notation('{"a": 2, "b": {"c": 42}}'::jsonb, 'b.c');
+select * from test_jsonb_dot_notation('{"a": 4, "b": {"c": {"d": [11, 12]}}}'::jsonb, 'b.c.d');
+
+-- access member object field of a json array: apply lax mode + conditional wrap
+-- unwrap the outer-most array into sequence and conditional wrap the results
+-- only unwrap the outer most array
+select * from test_jsonb_dot_notation('[{"x": 42}]'::jsonb, 'x');
+select * from test_jsonb_dot_notation('["x"]'::jsonb, 'x');
+select * from test_jsonb_dot_notation('[[{"x": 42}]]'::jsonb, 'x');
+-- wrap the result into an array on the conditional of more than one matched object keys
+select * from test_jsonb_dot_notation('[{"x": 42}, {"x": {"y": {"z": 12}}}]'::jsonb, 'x');
+select * from test_jsonb_dot_notation('[{"x": 42}, [{"x": {"y": {"z": 12}}}]]'::jsonb, 'x');
+select * from test_jsonb_dot_notation('[{"x": 42}, {"x": [{"y": 12}, {"y": {"z": 12}}]}]'::jsonb, 'x.y');
--
2.39.5 (Apple Git-154)
Attachments:
[text/plain] 0001-WIP-Operator-apporach-JSONB-dot-notation.txt (20.9K, ../CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com/3-0001-WIP-Operator-apporach-JSONB-dot-notation.txt)
download | inline diff:
From 56241895578e0d16d66518b8470005779cd2132c Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 14 Jan 2025 15:14:35 -0600
Subject: [PATCH] [WIP] Operator apporach: JSONB dot notation
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Implement an operator-like function `jsonb_object_field_dot()` that
accesses JSONB object fields one at a time.
Array unwrapping (lax mode) is handled differently depending on
whether the operation is the first in a chain of indirections.
Similarly, conditional wrapping is handled differently depending on
whether the operation is the last in that chain.
TODO:
- Currently, this commit might generate incorrect results when mixing
dot notation access with existing subscripting access. Fixing this
should not be difficult. However, performance comparisons with the
alternative approach have led to postponing the fix.
- Ideally, the function would use JsonPath or a pipelined chain of
operators instead of handling each dot access
individually—essentially what JsonPathQuery() does—leading to the
alternative approach.
Performance comparison with the generic subscripting approach:
-- setup:
create table tbl(id int, col1 jsonb);
insert into tbl select i, '{"x":"vx", "y":[{"a":[1,2,3]}, {"b":[1, 2,
{"j":"vj"}]}]}' from generate_series(1, 100000)i;
-- query 1: chained dot access
SELECT id, (col1).x.b.j AS jsonb_operator FROM tbl;
-- pgbench -n -f <query 1>.sql test -T100
This patch:
number of transactions actually processed: 6620
latency average = 15.107 ms
tps = 66.193017 (without initial connection time)
Nikita's patch (generic subscripting using json_query()):
number of transactions actually processed: 8036
latency average = 12.445 ms
tps = 80.352075 (without initial connection time)
It is expected that this patch is less performant because it currently
deserializes and serializes the nested JSONB binary three times (once
for each invocation of the operator-like function).
-- query 2: single dot access
SELECT id, (col1).x AS jsonb_operator FROM tbl;
-- pgbench -n -f <query 2>.sql test -T100
This patch:
number of transactions actually processed: 5653
latency average = 17.691 ms
tps = 56.524496 (without initial connection time)
Nikita's patch:
number of transactions actually processed: 4989
latency average = 20.044 ms
tps = 49.890189 (without initial connection time)
This patch performs slightly better for single dot accesses, though
the margin is not significant.
---
src/backend/parser/parse_expr.c | 33 +++-
src/backend/parser/parse_func.c | 36 ++++
src/backend/utils/adt/jsonfuncs.c | 262 ++++++++++++++++++++++++++++
src/include/catalog/pg_operator.dat | 2 +-
src/include/catalog/pg_proc.dat | 4 +
src/include/parser/parse_func.h | 2 +
src/test/regress/expected/jsonb.out | 103 +++++++++++
src/test/regress/sql/jsonb.sql | 47 +++++
8 files changed, 481 insertions(+), 8 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index bad1df732ea..e3b6626983b 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -440,6 +440,8 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
List *subscripts = NIL;
int location = exprLocation(result);
ListCell *i;
+ bool json_accessor_chain_first = false;
+ bool json_accessor_chain_last = false;
/*
* We have to split any field-selection operations apart from
@@ -462,6 +464,8 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
else
{
Node *newresult;
+ Oid result_typid;
+ Oid result_basetypid;
Assert(IsA(n, String));
@@ -475,13 +479,28 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
false);
subscripts = NIL;
- newresult = ParseFuncOrColumn(pstate,
- list_make1(n),
- list_make1(result),
- last_srf,
- NULL,
- false,
- location);
+ result_typid = exprType(result);
+ result_basetypid = (result_typid == JSONOID || result_typid == JSONBOID) ?
+ result_typid : getBaseType(result_typid);
+
+ if (result_basetypid == JSONBOID)
+ {
+ json_accessor_chain_first = (i == list_head(ind->indirection));
+ if (lnext(ind->indirection, i) == NULL)
+ json_accessor_chain_last = true;
+ newresult = ParseJsonbSimplifiedAccessorObjectField(pstate,
+ strVal(n),
+ result,
+ location, result_basetypid, json_accessor_chain_first, json_accessor_chain_last);
+ }
+ else
+ newresult = ParseFuncOrColumn(pstate,
+ list_make1(n),
+ list_make1(result),
+ last_srf,
+ NULL,
+ false,
+ location);
if (newresult == NULL)
unknown_attribute(pstate, result, strVal(n), location);
result = newresult;
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 583bbbf232f..dba2a60fadc 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -1902,6 +1902,42 @@ FuncNameAsType(List *funcname)
return result;
}
+/*
+ * ParseJsonbSimplifiedAccessorObjectField -
+ * handles function calls with a single argument that is of json type.
+ * If the function call is actually a column projection, return a suitably
+ * transformed expression tree. If not, return NULL.
+ */
+Node *
+ParseJsonbSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname, Node *first_arg, int location,
+ Oid basetypid, bool first_op, bool last_op)
+{
+ FuncExpr *result;
+ Node *rexpr;
+
+ if (basetypid != JSONBOID)
+ elog(ERROR, "unsupported type OID: %u", basetypid);
+
+ rexpr = (Node *) makeConst(
+ TEXTOID,
+ -1,
+ InvalidOid,
+ -1,
+ CStringGetTextDatum(funcname),
+ false,
+ false);
+ result = makeFuncExpr(4100,
+ JSONBOID,
+ list_make4(first_arg, rexpr,
+ makeBoolConst(first_op, false),
+ makeBoolConst(last_op, false)),
+ InvalidOid,
+ InvalidOid,
+ COERCE_EXPLICIT_CALL);
+
+ return (Node *) result;
+}
+
/*
* ParseComplexProjection -
* handles function calls with a single argument that is of complex type.
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index c2e90f1a3bf..a32aef3bfac 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -879,6 +879,268 @@ jsonb_object_field(PG_FUNCTION_ARGS)
PG_RETURN_NULL();
}
+static void
+expand_jbvBinary_in_memory(const JsonbValue *binaryVal, JsonbValue *expandedVal)
+{
+ Assert(binaryVal->type == jbvBinary);
+ JsonbContainer *container = (JsonbContainer *) binaryVal->val.binary.data;
+ JsonbIterator *it;
+ JsonbValue v;
+ JsonbIteratorToken token;
+
+ it = JsonbIteratorInit(container);
+ token = JsonbIteratorNext(&it, &v, false);
+
+ if (token == WJB_BEGIN_OBJECT)
+ {
+ /*
+ * We'll read all keys/values until WJB_END_OBJECT and build
+ * expandedVal->type = jbvObject.
+ */
+ List *keyvals = NIL;
+
+ while ((token = JsonbIteratorNext(&it, &v, false)) != WJB_END_OBJECT)
+ {
+ if (token == WJB_KEY)
+ {
+ JsonbValue key;
+
+ key = v;
+ token = JsonbIteratorNext(&it, &v, true);
+ if (token == WJB_VALUE)
+ {
+ JsonbValue val;
+ JsonbValue *objPair;
+
+ if (v.type == jbvBinary)
+ {
+ /* Recursively expand nested objects/arrays. */
+ expand_jbvBinary_in_memory(&v, &val);
+ }
+ else
+ {
+ /* Scalar or already jbvObject/jbvArray. Copy as-is. */
+ val = v;
+ }
+
+ /*
+ * Build a small pair (key, value). We'll store them in a
+ * list.
+ */
+ objPair = palloc(sizeof(JsonbValue) * 2);
+ objPair[0] = key;
+ objPair[1] = val;
+ keyvals = lappend(keyvals, objPair);
+ }
+ }
+ }
+
+ /* Now convert our keyvals list into a jbvObject. */
+ int nPairs = list_length(keyvals);
+
+ expandedVal->type = jbvObject;
+ expandedVal->val.object.nPairs = nPairs;
+ expandedVal->val.object.pairs = palloc(sizeof(JsonbPair) * nPairs);
+
+ {
+ int i = 0;
+ ListCell *lc;
+
+ foreach(lc, keyvals)
+ {
+ JsonbValue *kv = (JsonbValue *) lfirst(lc);
+
+ /* kv[0] = key, kv[1] = value */
+
+ expandedVal->val.object.pairs[i].key = kv[0];
+ expandedVal->val.object.pairs[i].value = kv[1];
+ expandedVal->val.object.pairs[i].order = i;
+ i++;
+ }
+ }
+ }
+ else if (token == WJB_BEGIN_ARRAY)
+ {
+ /*
+ * We'll read array elems until WJB_END_ARRAY and build
+ * expandedVal->type = jbvArray.
+ */
+ List *elems = NIL;
+
+ while ((token = JsonbIteratorNext(&it, &v, true)) != WJB_END_ARRAY)
+ {
+ if (token == WJB_ELEM)
+ {
+ /* If it's jbvBinary, recursively expand again. */
+ JsonbValue val;
+
+ if (v.type == jbvBinary)
+ {
+ expand_jbvBinary_in_memory(&v, &val);
+ }
+ else
+ {
+ val = v;
+ }
+ JsonbValue *elemCopy = palloc(sizeof(JsonbValue));
+
+ *elemCopy = val;
+ elems = lappend(elems, elemCopy);
+ }
+ }
+
+ expandedVal->type = jbvArray;
+ expandedVal->val.array.nElems = list_length(elems);
+ expandedVal->val.array.rawScalar = false;
+ expandedVal->val.array.elems = palloc(sizeof(JsonbValue) * expandedVal->val.array.nElems);
+
+ {
+ int i = 0;
+ ListCell *lc;
+
+ foreach(lc, elems)
+ {
+ JsonbValue *vptr = (JsonbValue *) lfirst(lc);
+
+ expandedVal->val.array.elems[i++] = *vptr;
+ }
+ }
+ }
+ else
+ {
+ /*
+ * Possibly a scalar container (WJB_ELEM or WJB_VALUE with jbvNumeric,
+ * jbvString, etc.). If this container truly only has one scalar,
+ * token might be WJB_ELEM or similar. For simplicity, let's check
+ * tmp.type. If it's jbvString/jbvNumeric, copy it directly.
+ */
+ expandedVal->type = v.type;
+ expandedVal->val = v.val;
+ }
+}
+
+static List *
+jsonb_object_field_unwrap_array(JsonbContainer *jb, text *key, bool unwrap_nested)
+{
+ JsonbIterator *it;
+ JsonbValue v;
+ JsonbIteratorToken token;
+ List *resultList = NIL;
+ int arraySize;
+
+ it = JsonbIteratorInit(jb);
+ token = JsonbIteratorNext(&it, &v, false);
+
+ Assert(token == WJB_BEGIN_ARRAY);
+ arraySize = v.val.array.nElems;
+
+ /* Unwrap out-most array elements and extract the key value */
+ for (int i = 0; i < arraySize; i++)
+ {
+ token = JsonbIteratorNext(&it, &v, true);
+ if (token == WJB_ELEM && v.type == jbvBinary)
+ {
+ JsonbContainer *elemContainer = (JsonbContainer *) v.val.binary.data;
+
+ if (JsonContainerIsObject(elemContainer))
+ {
+ JsonbValue *extractedValue;
+ JsonbValue vbuf;
+
+ extractedValue = getKeyJsonValueFromContainer(elemContainer,
+ VARDATA_ANY(key),
+ VARSIZE_ANY_EXHDR(key),
+ &vbuf);
+ if (extractedValue != NULL)
+ {
+ JsonbValue *copy;
+
+ if (extractedValue->type == jbvBinary)
+ {
+ JsonbValue expanded;
+
+ expand_jbvBinary_in_memory(extractedValue, &expanded);
+
+ copy = palloc(sizeof(expanded));
+ *copy = expanded;
+ resultList = lappend(resultList, copy);
+ }
+ else
+ {
+ copy = palloc(sizeof(*extractedValue));
+ *copy = *extractedValue;
+ resultList = lappend(resultList, copy);
+ }
+ }
+ }
+ else if (unwrap_nested && JsonContainerIsArray(elemContainer))
+ {
+ resultList = jsonb_object_field_unwrap_array(elemContainer, key, false);
+ }
+ }
+ }
+ token = JsonbIteratorNext(&it, &v, true);
+
+ return resultList;
+}
+
+Datum
+jsonb_object_field_dot(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb = PG_GETARG_JSONB_P(0);
+ text *key = PG_GETARG_TEXT_PP(1);
+ bool first_op = PG_GETARG_BOOL(2);
+ bool last_op = PG_GETARG_BOOL(3);
+
+ if (JB_ROOT_IS_OBJECT(jb))
+ {
+ JsonbValue *v;
+ JsonbValue vbuf;
+
+ v = getKeyJsonValueFromContainer(&jb->root,
+ VARDATA_ANY(key),
+ VARSIZE_ANY_EXHDR(key),
+ &vbuf);
+
+ if (v != NULL)
+ PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
+ }
+ else if (JB_ROOT_IS_ARRAY(jb))
+ {
+ List *resultList;
+ JsonbValue resultVal;
+
+ resultList = jsonb_object_field_unwrap_array(&jb->root, key, !first_op);
+
+ if (list_length(resultList) == 0)
+ PG_RETURN_NULL();
+ else if (!last_op || list_length(resultList) > 1)
+ {
+ /* Conditional wrap result */
+ resultVal.type = jbvArray;
+ resultVal.val.array.rawScalar = false;
+ resultVal.val.array.nElems = list_length(resultList);
+ resultVal.val.array.elems = (JsonbValue *) palloc(sizeof(JsonbValue) * list_length(resultList));
+
+ int idx = 0;
+ ListCell *lc;
+ JsonbValue *elem;
+
+ foreach(lc, resultList)
+ {
+ elem = (JsonbValue *) lfirst(lc);
+ resultVal.val.array.elems[idx++] = *elem;
+ }
+ }
+ else
+ resultVal = *(JsonbValue *) linitial(resultList);
+
+ PG_RETURN_JSONB_P(JsonbValueToJsonb(&resultVal));
+ }
+
+ PG_RETURN_NULL();
+}
+
Datum
json_object_field_text(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index 6d9dc1528d6..70887d3fd4d 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3173,7 +3173,7 @@
{ oid => '3967', descr => 'get value from json as text with path elements',
oprname => '#>>', oprleft => 'json', oprright => '_text', oprresult => 'text',
oprcode => 'json_extract_path_text' },
-{ oid => '3211', descr => 'get jsonb object field',
+{ oid => '3211', oid_symbol => 'OID_JSONB_OBJECT_FIELD_OP', descr => 'get jsonb object field',
oprname => '->', oprleft => 'jsonb', oprright => 'text', oprresult => 'jsonb',
oprcode => 'jsonb_object_field' },
{ oid => '3477', descr => 'get jsonb object field as text',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5b8c2ad2a54..cabde2e07e8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9338,6 +9338,10 @@
{ oid => '3968', descr => 'get the type of a json value',
proname => 'json_typeof', prorettype => 'text', proargtypes => 'json',
prosrc => 'json_typeof' },
+{ oid => '4100',
+ proname => 'jsonb_object_field_dot', prorettype => 'jsonb',
+ proargtypes => 'jsonb text bool bool', proargnames => '{from_json, field_name, first_op, last_op}',
+ prosrc => 'jsonb_object_field_dot' },
# uuid
{ oid => '2952', descr => 'I/O',
diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h
index a6f24b83d84..745d2c75c93 100644
--- a/src/include/parser/parse_func.h
+++ b/src/include/parser/parse_func.h
@@ -34,6 +34,8 @@ typedef enum
extern Node *ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
Node *last_srf, FuncCall *fn, bool proc_call,
int location);
+extern Node *ParseJsonbSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname, Node *first_arg, int location,
+ Oid basetypid, bool first_op, bool last_op);
extern FuncDetailCode func_get_detail(List *funcname,
List *fargs, List *fargnames,
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 2baff931bf2..e0c0757f475 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5781,3 +5781,106 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
12345
(1 row)
+-- simple dot notation
+-- TODO: add comments
+CREATE OR REPLACE FUNCTION test_jsonb_dot_notation(
+ vep jsonb, -- value expression primary
+ jc text -- JSON simplified accessor operator chain
+)
+ RETURNS TABLE(dot_access jsonb, expected jsonb)
+ LANGUAGE plpgsql
+AS $$
+DECLARE
+ dyn_sql text;
+BEGIN
+ dyn_sql := format($f$
+ SELECT
+ (vep).%s AS dot_access,
+ json_query(vep, 'lax $.%s' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) AS expected
+ FROM (SELECT $1::jsonb AS vep) dummy
+ $f$, jc, jc);
+
+ -- -- OPTIONAL: Just to see the constructed SQL in logs
+-- RAISE NOTICE 'Executing: %', dyn_sql;
+
+ -- Execute the dynamic query, substituting p_col as parameter #1
+ RETURN QUERY EXECUTE dyn_sql USING vep;
+END;
+$$;
+-- access member object field of a json object
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42}'::jsonb, 'b');
+ dot_access | expected
+------------+----------
+ 42 | 42
+(1 row)
+
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42}'::jsonb, 'not_exist');
+ dot_access | expected
+------------+----------
+ |
+(1 row)
+
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42, "b":12}'::jsonb, 'b'); -- return last for duplicate key
+ dot_access | expected
+------------+----------
+ 12 | 12
+(1 row)
+
+select * from test_jsonb_dot_notation('{"a": 1, "b": 12, "b":42}'::jsonb, 'b');
+ dot_access | expected
+------------+----------
+ 42 | 42
+(1 row)
+
+select * from test_jsonb_dot_notation('{"a": 2, "b": {"c": 42}}'::jsonb, 'b.c');
+ dot_access | expected
+------------+----------
+ 42 | 42
+(1 row)
+
+select * from test_jsonb_dot_notation('{"a": 4, "b": {"c": {"d": [11, 12]}}}'::jsonb, 'b.c.d');
+ dot_access | expected
+------------+----------
+ [11, 12] | [11, 12]
+(1 row)
+
+-- access member object field of a json array: apply lax mode + conditional wrap
+-- unwrap the outer-most array into sequence and conditional wrap the results
+-- only unwrap the outer most array
+select * from test_jsonb_dot_notation('[{"x": 42}]'::jsonb, 'x');
+ dot_access | expected
+------------+----------
+ 42 | 42
+(1 row)
+
+select * from test_jsonb_dot_notation('["x"]'::jsonb, 'x');
+ dot_access | expected
+------------+----------
+ |
+(1 row)
+
+select * from test_jsonb_dot_notation('[[{"x": 42}]]'::jsonb, 'x');
+ dot_access | expected
+------------+----------
+ |
+(1 row)
+
+-- wrap the result into an array on the conditional of more than one matched object keys
+select * from test_jsonb_dot_notation('[{"x": 42}, {"x": {"y": {"z": 12}}}]'::jsonb, 'x');
+ dot_access | expected
+------------------------+------------------------
+ [42, {"y": {"z": 12}}] | [42, {"y": {"z": 12}}]
+(1 row)
+
+select * from test_jsonb_dot_notation('[{"x": 42}, [{"x": {"y": {"z": 12}}}]]'::jsonb, 'x');
+ dot_access | expected
+------------+----------
+ 42 | 42
+(1 row)
+
+select * from test_jsonb_dot_notation('[{"x": 42}, {"x": [{"y": 12}, {"y": {"z": 12}}]}]'::jsonb, 'x.y');
+ dot_access | expected
+-----------------+-----------------
+ [12, {"z": 12}] | [12, {"z": 12}]
+(1 row)
+
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 544bb610e2d..5a6d820b507 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1572,3 +1572,50 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- simple dot notation
+-- TODO: add comments
+
+CREATE OR REPLACE FUNCTION test_jsonb_dot_notation(
+ vep jsonb, -- value expression primary
+ jc text -- JSON simplified accessor operator chain
+)
+ RETURNS TABLE(dot_access jsonb, expected jsonb)
+ LANGUAGE plpgsql
+AS $$
+DECLARE
+ dyn_sql text;
+BEGIN
+ dyn_sql := format($f$
+ SELECT
+ (vep).%s AS dot_access,
+ json_query(vep, 'lax $.%s' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) AS expected
+ FROM (SELECT $1::jsonb AS vep) dummy
+ $f$, jc, jc);
+
+ -- -- OPTIONAL: Just to see the constructed SQL in logs
+-- RAISE NOTICE 'Executing: %', dyn_sql;
+
+ -- Execute the dynamic query, substituting p_col as parameter #1
+ RETURN QUERY EXECUTE dyn_sql USING vep;
+END;
+$$;
+
+-- access member object field of a json object
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42}'::jsonb, 'b');
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42}'::jsonb, 'not_exist');
+select * from test_jsonb_dot_notation('{"a": 1, "b": 42, "b":12}'::jsonb, 'b'); -- return last for duplicate key
+select * from test_jsonb_dot_notation('{"a": 1, "b": 12, "b":42}'::jsonb, 'b');
+select * from test_jsonb_dot_notation('{"a": 2, "b": {"c": 42}}'::jsonb, 'b.c');
+select * from test_jsonb_dot_notation('{"a": 4, "b": {"c": {"d": [11, 12]}}}'::jsonb, 'b.c.d');
+
+-- access member object field of a json array: apply lax mode + conditional wrap
+-- unwrap the outer-most array into sequence and conditional wrap the results
+-- only unwrap the outer most array
+select * from test_jsonb_dot_notation('[{"x": 42}]'::jsonb, 'x');
+select * from test_jsonb_dot_notation('["x"]'::jsonb, 'x');
+select * from test_jsonb_dot_notation('[[{"x": 42}]]'::jsonb, 'x');
+-- wrap the result into an array on the conditional of more than one matched object keys
+select * from test_jsonb_dot_notation('[{"x": 42}, {"x": {"y": {"z": 12}}}]'::jsonb, 'x');
+select * from test_jsonb_dot_notation('[{"x": 42}, [{"x": {"y": {"z": 12}}}]]'::jsonb, 'x');
+select * from test_jsonb_dot_notation('[{"x": 42}, {"x": [{"y": 12}, {"y": {"z": 12}}]}]'::jsonb, 'x.y');
--
2.39.5 (Apple Git-154)
[application/octet-stream] v7-0001-Allow-transformation-only-of-a-sublist-of-subscri.patch (6.3K, ../CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com/4-v7-0001-Allow-transformation-only-of-a-sublist-of-subscri.patch)
download | inline diff:
From 5da3d365c8eb72bf467fe33c7173fedd61c9f96f Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Fri, 14 Oct 2022 15:35:22 +0300
Subject: [PATCH v7 1/5] Allow transformation only of a sublist of subscripts
---
src/backend/parser/parse_expr.c | 9 ++++-----
src/backend/parser/parse_node.c | 4 ++--
src/backend/parser/parse_target.c | 5 ++++-
src/backend/utils/adt/arraysubs.c | 6 ++++--
src/backend/utils/adt/jsonbsubs.c | 6 ++++--
src/include/nodes/subscripting.h | 2 +-
src/include/parser/parse_node.h | 2 +-
7 files changed, 20 insertions(+), 14 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index bad1df732ea..2c0f4a50b21 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -466,14 +466,13 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
Assert(IsA(n, String));
/* process subscripts before this field selection */
- if (subscripts)
+ while (subscripts)
result = (Node *) transformContainerSubscripts(pstate,
result,
exprType(result),
exprTypmod(result),
- subscripts,
+ &subscripts,
false);
- subscripts = NIL;
newresult = ParseFuncOrColumn(pstate,
list_make1(n),
@@ -488,12 +487,12 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
}
}
/* process trailing subscripts, if any */
- if (subscripts)
+ while (subscripts)
result = (Node *) transformContainerSubscripts(pstate,
result,
exprType(result),
exprTypmod(result),
- subscripts,
+ &subscripts,
false);
return result;
diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c
index d6feb16aef3..19a6b678e67 100644
--- a/src/backend/parser/parse_node.c
+++ b/src/backend/parser/parse_node.c
@@ -244,7 +244,7 @@ transformContainerSubscripts(ParseState *pstate,
Node *containerBase,
Oid containerType,
int32 containerTypMod,
- List *indirection,
+ List **indirection,
bool isAssignment)
{
SubscriptingRef *sbsref;
@@ -280,7 +280,7 @@ transformContainerSubscripts(ParseState *pstate,
* element. If any of the items are slice specifiers (lower:upper), then
* the subscript expression means a container slice operation.
*/
- foreach(idx, indirection)
+ foreach(idx, *indirection)
{
A_Indices *ai = lfirst_node(A_Indices, idx);
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 4aba0d9d4d5..39fd82f8371 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -936,9 +936,12 @@ transformAssignmentSubscripts(ParseState *pstate,
basenode,
containerType,
containerTypMod,
- subscripts,
+ &subscripts,
true);
+ if (subscripts)
+ elog(ERROR, "subscripting assignment is not supported");
+
typeNeeded = sbsref->refrestype;
typmodNeeded = sbsref->reftypmod;
diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c
index 562179b3799..edd85f7ba67 100644
--- a/src/backend/utils/adt/arraysubs.c
+++ b/src/backend/utils/adt/arraysubs.c
@@ -53,7 +53,7 @@ typedef struct ArraySubWorkspace
*/
static void
array_subscript_transform(SubscriptingRef *sbsref,
- List *indirection,
+ List **indirection,
ParseState *pstate,
bool isSlice,
bool isAssignment)
@@ -70,7 +70,7 @@ array_subscript_transform(SubscriptingRef *sbsref,
* indirection items to slices by treating the single subscript as the
* upper bound and supplying an assumed lower bound of 1.
*/
- foreach(idx, indirection)
+ foreach(idx, *indirection)
{
A_Indices *ai = lfirst_node(A_Indices, idx);
Node *subexpr;
@@ -151,6 +151,8 @@ array_subscript_transform(SubscriptingRef *sbsref,
list_length(upperIndexpr), MAXDIM)));
/* We need not check lowerIndexpr separately */
+ *indirection = NIL;
+
/*
* Determine the result type of the subscripting operation. It's the same
* as the array type if we're slicing, else it's the element type. In
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index de64d498512..8ad6aa1ad4f 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -41,7 +41,7 @@ typedef struct JsonbSubWorkspace
*/
static void
jsonb_subscript_transform(SubscriptingRef *sbsref,
- List *indirection,
+ List **indirection,
ParseState *pstate,
bool isSlice,
bool isAssignment)
@@ -53,7 +53,7 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
* Transform and convert the subscript expressions. Jsonb subscripting
* does not support slices, look only and the upper index.
*/
- foreach(idx, indirection)
+ foreach(idx, *indirection)
{
A_Indices *ai = lfirst_node(A_Indices, idx);
Node *subExpr;
@@ -159,6 +159,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
/* Determine the result type of the subscripting operation; always jsonb */
sbsref->refrestype = JSONBOID;
sbsref->reftypmod = -1;
+
+ *indirection = NIL;
}
/*
diff --git a/src/include/nodes/subscripting.h b/src/include/nodes/subscripting.h
index 234e8ad8012..ed2f1cefb49 100644
--- a/src/include/nodes/subscripting.h
+++ b/src/include/nodes/subscripting.h
@@ -93,7 +93,7 @@ struct SubscriptExecSteps;
* assignment must return.
*/
typedef void (*SubscriptTransform) (SubscriptingRef *sbsref,
- List *indirection,
+ List **indirection,
struct ParseState *pstate,
bool isSlice,
bool isAssignment);
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 994284019fb..5ae11ccec33 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -377,7 +377,7 @@ extern SubscriptingRef *transformContainerSubscripts(ParseState *pstate,
Node *containerBase,
Oid containerType,
int32 containerTypMod,
- List *indirection,
+ List **indirection,
bool isAssignment);
extern Const *make_const(ParseState *pstate, A_Const *aconst);
--
2.39.5 (Apple Git-154)
[application/octet-stream] v7-0004-Implement-read-only-dot-notation-for-jsonb-using-.patch (31.0K, ../CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com/5-v7-0004-Implement-read-only-dot-notation-for-jsonb-using-.patch)
download | inline diff:
From 53bc1da78b0feb7769c571fbcafb70c2214a9c46 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:17:53 +0300
Subject: [PATCH v7 4/5] Implement read-only dot notation for jsonb using
jsonpath
---
src/backend/utils/adt/jsonbsubs.c | 438 +++++++++++++++++++++++-----
src/include/nodes/primnodes.h | 2 +
src/test/regress/expected/jsonb.out | 265 ++++++++++++++++-
src/test/regress/sql/jsonb.sql | 56 ++++
4 files changed, 670 insertions(+), 91 deletions(-)
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index a0d38a0fd80..1ececb4efa2 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -15,12 +15,14 @@
#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/builtins.h"
#include "utils/jsonb.h"
+#include "utils/jsonpath.h"
/* SubscriptingRefState.workspace for jsonb subscripting execution */
@@ -30,8 +32,261 @@ typedef struct JsonbSubWorkspace
Oid *indexOid; /* OID of coerced subscript expression, could
* be only integer or text */
Datum *index; /* Subscript values in Datum format */
+ JsonPath *jsonpath; /* jsonpath for dot notation execution */
+ List vars; /* jsonpath vars */
} JsonbSubWorkspace;
+static bool
+jsonb_check_jsonpath_needed(List *indirection)
+{
+ ListCell *lc;
+
+ foreach(lc, indirection)
+ {
+ Node *accessor = lfirst(lc);
+
+ if (IsA(accessor, String) ||
+ IsA(accessor, A_Star))
+ return true;
+ else if (IsA(accessor, A_Indices))
+ {
+ A_Indices *ai = castNode(A_Indices, accessor);
+
+ if (!ai->uidx || ai->lidx)
+ {
+ Assert(ai->is_slice);
+ return true;
+ }
+ }
+ else
+ return true;
+ }
+
+ return false;
+}
+
+static JsonPathParseItem *
+make_jsonpath_item(JsonPathItemType type)
+{
+ JsonPathParseItem *v = palloc(sizeof(*v));
+
+ v->type = type;
+ v->next = NULL;
+
+ return v;
+}
+
+static JsonPathParseItem *
+make_jsonpath_item_int(int32 val, List **exprs)
+{
+ JsonPathParseItem *jpi = make_jsonpath_item(jpiNumeric);
+
+ jpi->value.numeric =
+ DatumGetNumeric(DirectFunctionCall1(int4_numeric, Int32GetDatum(val)));
+
+ *exprs = lappend(*exprs, makeConst(INT4OID, -1, InvalidOid, 4,
+ Int32GetDatum(val), false, true));
+
+ return jpi;
+}
+
+static Oid
+jsonb_subscript_type(Node *expr)
+{
+ if (expr && IsA(expr, String))
+ return TEXTOID;
+
+ return exprType(expr);
+}
+
+static Node *
+coerce_jsonpath_subscript(ParseState *pstate, Node *subExpr, Oid numtype)
+{
+ Oid subExprType = jsonb_subscript_type(subExpr);
+ Oid targetType = UNKNOWNOID;
+
+ if (subExprType != UNKNOWNOID)
+ {
+ Oid targets[2] = {numtype, 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 %s is not supported", format_type_be(subExprType)),
+ errhint("jsonb subscript must be coercible to only 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 %s is not supported", format_type_be(subExprType)),
+ errhint("jsonb subscript must be coercible 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))));
+
+ return subExpr;
+}
+
+static JsonPathParseItem *
+make_jsonpath_item_expr(ParseState *pstate, Node *expr, List **exprs)
+{
+ JsonPathParseItem *jpi;
+
+ expr = transformExpr(pstate, expr, pstate->p_expr_kind);
+
+ if (IsA(expr, Const))
+ {
+ Const *cnst = (Const *) expr;
+
+ if (cnst->consttype == INT4OID && !cnst->constisnull)
+ {
+ int32 val = DatumGetInt32(cnst->constvalue);
+
+ return make_jsonpath_item_int(val, exprs);
+ }
+ }
+
+ *exprs = lappend(*exprs, coerce_jsonpath_subscript(pstate, expr, NUMERICOID));
+
+ jpi = make_jsonpath_item(jpiVariable);
+ jpi->value.string.val = psprintf("%d", list_length(*exprs));
+ jpi->value.string.len = strlen(jpi->value.string.val);
+
+ return jpi;
+}
+
+static Node *
+jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection,
+ List **uexprs, List **lexprs)
+{
+ JsonPathParseResult jpres;
+ JsonPathParseItem *path = make_jsonpath_item(jpiRoot);
+ ListCell *lc;
+ Datum jsp;
+ int pathlen = 0;
+
+ *uexprs = NIL;
+ *lexprs = NIL;
+
+ jpres.expr = path;
+ jpres.lax = true;
+
+ foreach(lc, *indirection)
+ {
+ Node *accessor = lfirst(lc);
+ JsonPathParseItem *jpi;
+
+ if (IsA(accessor, String))
+ {
+ char *field = strVal(accessor);
+
+ jpi = make_jsonpath_item(jpiKey);
+ jpi->value.string.val = field;
+ jpi->value.string.len = strlen(field);
+
+ *uexprs = lappend(*uexprs, accessor);
+ }
+ else if (IsA(accessor, A_Star))
+ {
+ jpi = make_jsonpath_item(jpiAnyKey);
+
+ *uexprs = lappend(*uexprs, NULL);
+ }
+ else if (IsA(accessor, A_Indices))
+ {
+ A_Indices *ai = castNode(A_Indices, accessor);
+
+ jpi = make_jsonpath_item(jpiIndexArray);
+ jpi->value.array.nelems = 1;
+ jpi->value.array.elems = palloc(sizeof(jpi->value.array.elems[0]));
+
+ if (ai->is_slice)
+ {
+ while (list_length(*lexprs) < list_length(*uexprs))
+ *lexprs = lappend(*lexprs, NULL);
+
+ if (ai->lidx)
+ jpi->value.array.elems[0].from = make_jsonpath_item_expr(pstate, ai->lidx, lexprs);
+ else
+ jpi->value.array.elems[0].from = make_jsonpath_item_int(0, lexprs);
+
+ if (ai->uidx)
+ jpi->value.array.elems[0].to = make_jsonpath_item_expr(pstate, ai->uidx, uexprs);
+ else
+ {
+ jpi->value.array.elems[0].to = make_jsonpath_item(jpiLast);
+ *uexprs = lappend(*uexprs, NULL);
+ }
+ }
+ else
+ {
+ Assert(ai->uidx && !ai->lidx);
+ jpi->value.array.elems[0].from = make_jsonpath_item_expr(pstate, ai->uidx, uexprs);
+ jpi->value.array.elems[0].to = NULL;
+ }
+ }
+ else
+ break;
+
+ /* append path item */
+ path->next = jpi;
+ path = jpi;
+ pathlen++;
+ }
+
+ if (*lexprs)
+ {
+ while (list_length(*lexprs) < list_length(*uexprs))
+ *lexprs = lappend(*lexprs, NULL);
+ }
+
+ *indirection = list_delete_first_n(*indirection, pathlen);
+
+ jsp = jsonPathFromParseResult(&jpres, 0, NULL);
+
+ return (Node *) makeConst(JSONPATHOID, -1, InvalidOid, -1, jsp, false, false);
+}
/*
* Finish parse analysis of a SubscriptingRef expression for a jsonb.
@@ -49,19 +304,35 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
List *upperIndexpr = NIL;
ListCell *idx;
+ /* Determine the result type of the subscripting operation; always jsonb */
+ sbsref->refrestype = JSONBOID;
+ sbsref->reftypmod = -1;
+
+ if (jsonb_check_jsonpath_needed(*indirection))
+ {
+ sbsref->refprivate =
+ jsonb_subscript_make_jsonpath(pstate, indirection,
+ &sbsref->refupperindexpr,
+ &sbsref->reflowerindexpr);
+ return;
+ }
+
/*
* Transform and convert the subscript expressions. Jsonb subscripting
* does not support slices, look only and the upper index.
*/
foreach(idx, *indirection)
{
+ Node *i = lfirst(idx);
A_Indices *ai;
Node *subExpr;
- if (!IsA(lfirst(idx), A_Indices))
+ Assert(IsA(i, A_Indices));
+
+ if (!IsA(i, A_Indices))
break;
- ai = lfirst_node(A_Indices, idx);
+ ai = castNode(A_Indices, i);
if (isSlice)
{
@@ -75,71 +346,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
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 %s is not supported", format_type_be(subExprType)),
- errhint("jsonb subscript must be coercible to only 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 %s is not supported", format_type_be(subExprType)),
- errhint("jsonb subscript must be coercible 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))));
+ subExpr = coerce_jsonpath_subscript(pstate, subExpr, INT4OID);
}
else
{
@@ -161,10 +369,6 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
sbsref->refupperindexpr = upperIndexpr;
sbsref->reflowerindexpr = NIL;
- /* Determine the result type of the subscripting operation; always jsonb */
- sbsref->refrestype = JSONBOID;
- sbsref->reftypmod = -1;
-
/* Remove processed elements */
if (upperIndexpr)
*indirection = list_delete_first_n(*indirection, list_length(upperIndexpr));
@@ -219,7 +423,7 @@ jsonb_subscript_check_subscripts(ExprState *state,
* 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)
+ if (!workspace->jsonpath && workspace->indexOid[i] == INT4OID)
{
Datum datum = sbsrefstate->upperindex[i];
char *cs = DatumGetCString(DirectFunctionCall1(int4out, datum));
@@ -247,17 +451,44 @@ jsonb_subscript_fetch(ExprState *state,
{
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);
+ if (workspace->jsonpath)
+ {
+ bool empty = false;
+ bool error = false;
+ List *vars = &workspace->vars;
+ ListCell *lc;
+
+ /* copy computed variable values */
+ foreach(lc, vars)
+ {
+ JsonPathVariable *var = lfirst(lc);
+ int i = foreach_current_index(lc);
+
+ var->value = workspace->index[i];
+ var->isnull = false;
+ }
+
+ *op->resvalue = JsonPathQuery(*op->resvalue, workspace->jsonpath,
+ JSW_CONDITIONAL,
+ &empty, &error, vars,
+ NULL);
+
+ *op->resnull = empty || error;
+ }
+ else
+ {
+ Jsonb *jsonbSource = DatumGetJsonbP(*op->resvalue);
+
+ *op->resvalue = jsonb_get_element(jsonbSource,
+ workspace->index,
+ sbsrefstate->numupper,
+ op->resnull,
+ false);
+ }
}
/*
@@ -367,12 +598,57 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
ListCell *lc;
int nupper = sbsref->refupperindexpr->length;
char *ptr;
+ bool useJsonpath = sbsref->refprivate != NULL;
+ JsonPathVariable *vars;
+ int nvars = useJsonpath ? nupper : 0;
/* Allocate type-specific workspace with space for per-subscript data */
- workspace = palloc0(MAXALIGN(sizeof(JsonbSubWorkspace)) +
+ workspace = palloc0(MAXALIGN(offsetof(JsonbSubWorkspace, vars.initial_elements) + nvars * sizeof(ListCell)) +
+ MAXALIGN(nvars * sizeof(*vars) + nvars * 16) +
nupper * (sizeof(Datum) + sizeof(Oid)));
workspace->expectArray = false;
- ptr = ((char *) workspace) + MAXALIGN(sizeof(JsonbSubWorkspace));
+ ptr = ((char *) workspace) +
+ MAXALIGN(offsetof(JsonbSubWorkspace, vars.initial_elements) +
+ nvars * sizeof(ListCell));
+
+ if (!useJsonpath)
+ workspace->jsonpath = NULL;
+ else
+ {
+ workspace->jsonpath = DatumGetJsonPathP(castNode(Const, sbsref->refprivate)->constvalue);
+
+ vars = (JsonPathVariable *) ptr;
+ ptr += MAXALIGN(nvars * sizeof(*vars));
+
+ workspace->vars.type = T_List;
+ workspace->vars.length = nvars;
+ workspace->vars.max_length = nvars;
+ workspace->vars.elements = &workspace->vars.initial_elements[0];
+
+ for (int i = 0; i < nvars; i++)
+ {
+ Node *expr = list_nth(sbsref->refupperindexpr, i);
+
+ workspace->vars.elements[i].ptr_value = &vars[i];
+
+ if (expr && IsA(expr, String))
+ {
+ vars[i].typid = TEXTOID;
+ vars[i].typmod = -1;
+ }
+ else
+ {
+ vars[i].typid = exprType(expr);
+ vars[i].typmod = exprTypmod(expr);
+ }
+
+ vars[i].name = ptr;
+ snprintf(ptr, 16, "%d", i + 1);
+ vars[i].namelen = strlen(ptr);
+
+ ptr += 16;
+ }
+ }
/*
* This coding assumes sizeof(Datum) >= sizeof(Oid), else we might
@@ -390,7 +666,7 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
Node *expr = lfirst(lc);
int i = foreach_current_index(lc);
- workspace->indexOid[i] = exprType(expr);
+ workspace->indexOid[i] = jsonb_subscript_type(expr);
}
/*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 839e71d52f4..4b1e5de98e5 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -718,6 +718,8 @@ typedef struct SubscriptingRef
Expr *refexpr;
/* expression for the source value, or NULL if fetch */
Expr *refassgnexpr;
+ /* private expression */
+ Node *refprivate;
} SubscriptingRef;
/*
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 2baff931bf2..14123929475 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4939,6 +4939,12 @@ select ('123'::jsonb)['a'];
(1 row)
+select ('123'::jsonb).a;
+ a
+---
+
+(1 row)
+
select ('123'::jsonb)[0];
jsonb
-------
@@ -4957,6 +4963,12 @@ select ('{"a": 1}'::jsonb)['a'];
1
(1 row)
+select ('{"a": 1}'::jsonb).a;
+ a
+---
+ 1
+(1 row)
+
select ('{"a": 1}'::jsonb)[0];
jsonb
-------
@@ -4969,6 +4981,12 @@ select ('{"a": 1}'::jsonb)['not_exist'];
(1 row)
+select ('{"a": 1}'::jsonb)."not_exist";
+ not_exist
+-----------
+
+(1 row)
+
select ('{"a": 1}'::jsonb)[NULL];
jsonb
-------
@@ -4981,6 +4999,12 @@ select ('[1, "2", null]'::jsonb)['a'];
(1 row)
+select ('[1, "2", null]'::jsonb).a;
+ a
+---
+
+(1 row)
+
select ('[1, "2", null]'::jsonb)[0];
jsonb
-------
@@ -4993,6 +5017,12 @@ select ('[1, "2", null]'::jsonb)['1'];
"2"
(1 row)
+select ('[1, "2", null]'::jsonb)."1";
+ 1
+---
+
+(1 row)
+
select ('[1, "2", null]'::jsonb)[1.0];
ERROR: subscript type numeric is not supported
LINE 1: select ('[1, "2", null]'::jsonb)[1.0];
@@ -5022,6 +5052,12 @@ select ('[1, "2", null]'::jsonb)[1]['a'];
(1 row)
+select ('[1, "2", null]'::jsonb)[1].a;
+ a
+---
+
+(1 row)
+
select ('[1, "2", null]'::jsonb)[1][0];
jsonb
-------
@@ -5034,73 +5070,143 @@ select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b'];
"c"
(1 row)
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).b;
+ b
+-----
+ "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;
+ d
+-----------
+ [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[1];
+ d
+---
+ 2
+(1 row)
+
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a'];
jsonb
-------
(1 row)
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['a'];
+ d
+---
+
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d.a;
+ a
+---
+
+(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;
+ a1
+---------------
+ {"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;
+ a2
+-------
+ "aaa"
+(1 row)
+
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3'];
jsonb
-------
(1 row)
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2.a3;
+ a3
+----
+
+(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;
+ b1
+-----------------------
+ ["aaa", "bbb", "ccc"]
+(1 row)
+
select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2];
jsonb
-------
"ccc"
(1 row)
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2];
+ b1
+-------
+ "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'];
- ^
+ jsonb
+-------
+
+(1 row)
+
select ('[1, "2", null]'::jsonb)[1:2];
-ERROR: jsonb subscript does not support slices
-LINE 1: select ('[1, "2", null]'::jsonb)[1:2];
- ^
+ jsonb
+-------------
+ ["2", null]
+(1 row)
+
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:];
- ^
+ jsonb
+-------------
+ ["2", null]
+(1 row)
+
select ('[1, "2", null]'::jsonb)[:];
-ERROR: jsonb subscript does not support slices
+ jsonb
+----------------
+ [1, "2", null]
+(1 row)
+
create TEMP TABLE test_jsonb_subscript (
id int,
test_json jsonb
@@ -5781,3 +5887,142 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
12345
(1 row)
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+SELECT (jb).* FROM test_jsonb_dot_notation;
+ERROR: type jsonb is not composite
+SELECT (jb).* FROM test_jsonb_dot_notation t;
+ERROR: type jsonb is not composite
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+ERROR: type jsonb is not composite
+SELECT (jb).* FROM test_jsonb_dot_notation;
+ERROR: type jsonb is not composite
+SELECT (t.jb).* FROM test_jsonb_dot_notation;
+ERROR: missing FROM-clause entry for table "t"
+LINE 1: SELECT (t.jb).* FROM test_jsonb_dot_notation;
+ ^
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+ERROR: type jsonb is not composite
+SELECT (jb).a FROM test_jsonb_dot_notation;
+ a
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb).a FROM test_jsonb_dot_notation;
+ a
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb).b FROM test_jsonb_dot_notation;
+ b
+---------------------------------------------------
+ [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]
+(1 row)
+
+SELECT (jb).c FROM test_jsonb_dot_notation;
+ c
+---
+
+(1 row)
+
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+ b
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+ERROR: type jsonb is not composite
+SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.x FROM test_jsonb_dot_notation t;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation t;
+ ^
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ERROR: row expansion via "*" is not supported here
+LINE 1: SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ ^
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ERROR: row expansion via "*" is not supported here
+LINE 1: SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ ^
+SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
+ERROR: row expansion via "*" is not supported here
+LINE 1: SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
+ ^
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation;
+ERROR: type jsonb is not composite
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation;
+ERROR: type jsonb is not composite
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: jb.a
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: jb.a[1]
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_...
+ ^
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: ... (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_...
+ ^
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 544bb610e2d..4b49d59222b 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1286,30 +1286,46 @@ select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true);
-- jsonb subscript
select ('123'::jsonb)['a'];
+select ('123'::jsonb).a;
select ('123'::jsonb)[0];
select ('123'::jsonb)[NULL];
select ('{"a": 1}'::jsonb)['a'];
+select ('{"a": 1}'::jsonb).a;
select ('{"a": 1}'::jsonb)[0];
select ('{"a": 1}'::jsonb)['not_exist'];
+select ('{"a": 1}'::jsonb)."not_exist";
select ('{"a": 1}'::jsonb)[NULL];
select ('[1, "2", null]'::jsonb)['a'];
+select ('[1, "2", null]'::jsonb).a;
select ('[1, "2", null]'::jsonb)[0];
select ('[1, "2", null]'::jsonb)['1'];
+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].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).b;
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'];
+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[1];
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['a'];
+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;
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;
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3'];
+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;
select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2];
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2];
-- slices are not supported
select ('{"a": 1}'::jsonb)['a':'b'];
@@ -1572,3 +1588,43 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+
+SELECT (jb).* FROM test_jsonb_dot_notation;
+SELECT (jb).* FROM test_jsonb_dot_notation t;
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+SELECT (jb).* FROM test_jsonb_dot_notation;
+SELECT (t.jb).* FROM test_jsonb_dot_notation;
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+SELECT (jb).a FROM test_jsonb_dot_notation;
+SELECT (jb).a FROM test_jsonb_dot_notation;
+SELECT (jb).b FROM test_jsonb_dot_notation;
+SELECT (jb).c FROM test_jsonb_dot_notation;
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+SELECT (jb).*.x FROM test_jsonb_dot_notation t;
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
+SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
+SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
+SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
+SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
+SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
+SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation;
--
2.39.5 (Apple Git-154)
[application/octet-stream] v7-0005-Allow-processing-of-.-by-generic-subscripting.patch (18.0K, ../CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com/6-v7-0005-Allow-processing-of-.-by-generic-subscripting.patch)
download | inline diff:
From b376b023aed5b4769cb29daa7f55e6eb22b90afd Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:15:26 +0300
Subject: [PATCH v7 5/5] Allow processing of .* by generic subscripting
---
src/backend/parser/gram.y | 2 +
src/backend/parser/parse_expr.c | 34 +++--
src/backend/parser/parse_target.c | 60 ++++++---
src/backend/utils/adt/ruleutils.c | 6 +-
src/include/parser/parse_expr.h | 3 +
src/test/regress/expected/jsonb.out | 195 +++++++++++++++++++---------
6 files changed, 206 insertions(+), 94 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d7f9c00c409..5f69ac661bd 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -18959,6 +18959,7 @@ check_func_name(List *names, core_yyscan_t yyscanner)
static List *
check_indirection(List *indirection, core_yyscan_t yyscanner)
{
+#if 0
ListCell *l;
foreach(l, indirection)
@@ -18969,6 +18970,7 @@ check_indirection(List *indirection, core_yyscan_t yyscanner)
parser_yyerror("improper use of \"*\"");
}
}
+#endif
return indirection;
}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index afe953fdbea..b0c38529872 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -74,7 +74,6 @@ static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref);
static Node *transformWholeRowRef(ParseState *pstate,
ParseNamespaceItem *nsitem,
int sublevels_up, int location);
-static Node *transformIndirection(ParseState *pstate, A_Indirection *ind);
static Node *transformTypeCast(ParseState *pstate, TypeCast *tc);
static Node *transformCollateClause(ParseState *pstate, CollateClause *c);
static Node *transformJsonObjectConstructor(ParseState *pstate,
@@ -158,7 +157,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
break;
case T_A_Indirection:
- result = transformIndirection(pstate, (A_Indirection *) expr);
+ result = transformIndirection(pstate, (A_Indirection *) expr, NULL);
break;
case T_A_ArrayExpr:
@@ -432,8 +431,9 @@ unknown_attribute(ParseState *pstate, Node *relref, const char *attname,
}
}
-static Node *
-transformIndirection(ParseState *pstate, A_Indirection *ind)
+Node *
+transformIndirection(ParseState *pstate, A_Indirection *ind,
+ bool *trailing_star_expansion)
{
Node *last_srf = pstate->p_last_srf;
Node *result = transformExprRecurse(pstate, ind->arg);
@@ -453,12 +453,7 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
if (IsA(n, A_Indices))
subscripts = lappend(subscripts, n);
else if (IsA(n, A_Star))
- {
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("row expansion via \"*\" is not supported here"),
- parser_errposition(pstate, location)));
- }
+ subscripts = lappend(subscripts, n);
else
{
Assert(IsA(n, String));
@@ -487,7 +482,21 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
n = linitial(subscripts);
- if (!IsA(n, String))
+ if (IsA(n, A_Star))
+ {
+ /* Success, if trailing star expansion is allowed */
+ if (trailing_star_expansion && list_length(subscripts) == 1)
+ {
+ *trailing_star_expansion = true;
+ return result;
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("row expansion via \"*\" is not supported here"),
+ parser_errposition(pstate, location)));
+ }
+ else if (!IsA(n, String))
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("cannot subscript type %s because it does not support subscripting",
@@ -513,6 +522,9 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
result = newresult;
}
+ if (trailing_star_expansion)
+ *trailing_star_expansion = false;
+
return result;
}
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 5e126145ea5..965e2f06e7b 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -48,7 +48,7 @@ static Node *transformAssignmentSubscripts(ParseState *pstate,
static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref,
bool make_target_entry);
static List *ExpandAllTables(ParseState *pstate, int location);
-static List *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
+static Node *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
bool make_target_entry, ParseExprKind exprKind);
static List *ExpandSingleTable(ParseState *pstate, ParseNamespaceItem *nsitem,
int sublevels_up, int location,
@@ -134,6 +134,7 @@ transformTargetList(ParseState *pstate, List *targetlist,
foreach(o_target, targetlist)
{
ResTarget *res = (ResTarget *) lfirst(o_target);
+ Node *transformed = NULL;
/*
* Check for "something.*". Depending on the complexity of the
@@ -162,13 +163,19 @@ transformTargetList(ParseState *pstate, List *targetlist,
if (IsA(llast(ind->indirection), A_Star))
{
- /* It is something.*, expand into multiple items */
- p_target = list_concat(p_target,
- ExpandIndirectionStar(pstate,
- ind,
- true,
- exprKind));
- continue;
+ Node *columns = ExpandIndirectionStar(pstate,
+ ind,
+ true,
+ exprKind);
+
+ if (IsA(columns, List))
+ {
+ /* It is something.*, expand into multiple items */
+ p_target = list_concat(p_target, (List *) columns);
+ continue;
+ }
+
+ transformed = (Node *) columns;
}
}
}
@@ -180,7 +187,7 @@ transformTargetList(ParseState *pstate, List *targetlist,
p_target = lappend(p_target,
transformTargetEntry(pstate,
res->val,
- NULL,
+ transformed,
exprKind,
res->name,
false));
@@ -251,10 +258,15 @@ transformExpressionList(ParseState *pstate, List *exprlist,
if (IsA(llast(ind->indirection), A_Star))
{
- /* It is something.*, expand into multiple items */
- result = list_concat(result,
- ExpandIndirectionStar(pstate, ind,
- false, exprKind));
+ Node *cols = ExpandIndirectionStar(pstate, ind,
+ false, exprKind);
+
+ if (!cols || IsA(cols, List))
+ /* It is something.*, expand into multiple items */
+ result = list_concat(result, (List *) cols);
+ else
+ result = lappend(result, cols);
+
continue;
}
}
@@ -1348,22 +1360,30 @@ ExpandAllTables(ParseState *pstate, int location)
* For robustness, we use a separate "make_target_entry" flag to control
* this rather than relying on exprKind.
*/
-static List *
+static Node *
ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
bool make_target_entry, ParseExprKind exprKind)
{
Node *expr;
+ ParseExprKind sv_expr_kind;
+ bool trailing_star_expansion = false;
+
+ /* Save and restore identity of expression type we're parsing */
+ Assert(exprKind != EXPR_KIND_NONE);
+ sv_expr_kind = pstate->p_expr_kind;
+ pstate->p_expr_kind = exprKind;
/* Strip off the '*' to create a reference to the rowtype object */
- ind = copyObject(ind);
- ind->indirection = list_truncate(ind->indirection,
- list_length(ind->indirection) - 1);
+ expr = transformIndirection(pstate, ind, &trailing_star_expansion);
+
+ pstate->p_expr_kind = sv_expr_kind;
- /* And transform that */
- expr = transformExpr(pstate, (Node *) ind, exprKind);
+ /* '*' was consumed by generic type subscripting */
+ if (!trailing_star_expansion)
+ return expr;
/* Expand the rowtype expression into individual fields */
- return ExpandRowReference(pstate, expr, make_target_entry);
+ return (Node *) ExpandRowReference(pstate, expr, make_target_entry);
}
/*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index af5417d0859..10006b749dc 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -12919,7 +12919,11 @@ printSubscripts(SubscriptingRef *sbsref, deparse_context *context)
{
Node *up = (Node *) lfirst(uplist_item);
- if (IsA(up, String))
+ if (!up)
+ {
+ appendStringInfoString(buf, ".*");
+ }
+ else if (IsA(up, String))
{
appendStringInfoChar(buf, '.');
appendStringInfoString(buf, quote_identifier(strVal(up)));
diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h
index efbaff8e710..c9f6a7724c0 100644
--- a/src/include/parser/parse_expr.h
+++ b/src/include/parser/parse_expr.h
@@ -22,4 +22,7 @@ extern Node *transformExpr(ParseState *pstate, Node *expr, ParseExprKind exprKin
extern const char *ParseExprKindName(ParseExprKind exprKind);
+extern Node *transformIndirection(ParseState *pstate, A_Indirection *ind,
+ bool *trailing_star_expansion);
+
#endif /* PARSE_EXPR_H */
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 14123929475..9057186bdf3 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5891,19 +5891,39 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
CREATE TABLE test_jsonb_dot_notation AS
SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
SELECT (jb).* FROM test_jsonb_dot_notation;
-ERROR: type jsonb is not composite
+ jb
+------------------------------------------------------------------------------------------------------------------------------
+ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]]
+(1 row)
+
SELECT (jb).* FROM test_jsonb_dot_notation t;
-ERROR: type jsonb is not composite
+ jb
+------------------------------------------------------------------------------------------------------------------------------
+ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]]
+(1 row)
+
SELECT (t.jb).* FROM test_jsonb_dot_notation t;
-ERROR: type jsonb is not composite
+ jb
+------------------------------------------------------------------------------------------------------------------------------
+ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]]
+(1 row)
+
SELECT (jb).* FROM test_jsonb_dot_notation;
-ERROR: type jsonb is not composite
+ jb
+------------------------------------------------------------------------------------------------------------------------------
+ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]]
+(1 row)
+
SELECT (t.jb).* FROM test_jsonb_dot_notation;
ERROR: missing FROM-clause entry for table "t"
LINE 1: SELECT (t.jb).* FROM test_jsonb_dot_notation;
^
SELECT (t.jb).* FROM test_jsonb_dot_notation t;
-ERROR: type jsonb is not composite
+ jb
+------------------------------------------------------------------------------------------------------------------------------
+ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]]
+(1 row)
+
SELECT (jb).a FROM test_jsonb_dot_notation;
a
-------------------------------------------------------------------------
@@ -5935,75 +5955,120 @@ SELECT (jb).a.b FROM test_jsonb_dot_notation;
(1 row)
SELECT (jb).a.* FROM test_jsonb_dot_notation;
-ERROR: type jsonb is not composite
+ a
+-------------------------------------------
+ ["c", "d", "f", {"y": "yyy", "z": "zzz"}]
+(1 row)
+
SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
- ^
+ b
+---
+
+(1 row)
+
SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
- ^
+ x
+---
+
+(1 row)
+
SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
- ^
+ y
+-------
+ "yyy"
+(1 row)
+
SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
- ^
+ a
+----------------
+ ["yyy", "zzz"]
+(1 row)
+
SELECT (jb).*.x FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation;
- ^
+ x
+------------------------------------------------------
+ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}]
+(1 row)
+
SELECT (jb).*.x FROM test_jsonb_dot_notation t;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation t;
- ^
+ x
+------------------------------------------------------
+ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}]
+(1 row)
+
SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
-ERROR: row expansion via "*" is not supported here
-LINE 1: SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
- ^
+ x
+---
+
+(1 row)
+
SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
-ERROR: row expansion via "*" is not supported here
-LINE 1: SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
- ^
+ x
+---
+
+(1 row)
+
SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
-ERROR: row expansion via "*" is not supported here
-LINE 1: SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
- ^
+ x
+------------------------------------------------------
+ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}]
+(1 row)
+
SELECT (jb).*.x FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation;
- ^
+ x
+------------------------------------------------------
+ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}]
+(1 row)
+
SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
- ^
+ x
+------------------------------
+ ["yyy", "zzz", "YYY", "ZZZ"]
+(1 row)
+
SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
- ^
+ y
+----------------
+ ["yyy", "YYY"]
+(1 row)
+
SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
- ^
+ z
+----------------
+ ["zzz", "ZZZ"]
+(1 row)
+
SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
- ^
+ y
+----------------
+ ["yyy", "YYY"]
+(1 row)
+
SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
- ^
+ jb
+------------------------------
+ ["yyy", "zzz", "YYY", "ZZZ"]
+(1 row)
+
SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
- ^
+ jb
+----
+
+(1 row)
+
SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation;
-ERROR: type jsonb is not composite
+ c
+---
+
+(1 row)
+
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation;
-ERROR: type jsonb is not composite
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: jb.*
+(2 rows)
+
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation;
QUERY PLAN
--------------------------------------------
@@ -6019,10 +6084,16 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
(2 rows)
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_...
- ^
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: jb.a.*['b'::text]
+(2 rows)
+
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: ... (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_...
- ^
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: jb.a.*[1:2][:'b'::text].b
+(2 rows)
+
--
2.39.5 (Apple Git-154)
[application/octet-stream] v7-0003-Export-jsonPathFromParseResult.patch (2.4K, ../CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com/7-v7-0003-Export-jsonPathFromParseResult.patch)
download | inline diff:
From 015f01b44fa99fe1242b21cb83736e4d31fa5671 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:15:55 +0300
Subject: [PATCH v7 3/5] Export jsonPathFromParseResult()
---
src/backend/utils/adt/jsonpath.c | 24 ++++++++++++++++++------
src/include/utils/jsonpath.h | 4 ++++
2 files changed, 22 insertions(+), 6 deletions(-)
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 762f7e8a09d..a3270bdaba3 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -165,16 +165,12 @@ jsonpath_send(PG_FUNCTION_ARGS)
/*
* Converts C-string to a jsonpath value.
*
- * Uses jsonpath parser to turn string into an AST, then
- * flattenJsonPathParseItem() does second pass turning AST into binary
- * representation of jsonpath.
+ * Uses jsonpath parser to turn string into an AST.
*/
static Datum
jsonPathFromCstring(char *in, int len, struct Node *escontext)
{
JsonPathParseResult *jsonpath = parsejsonpath(in, len, escontext);
- JsonPath *res;
- StringInfoData buf;
if (SOFT_ERROR_OCCURRED(escontext))
return (Datum) 0;
@@ -185,8 +181,24 @@ jsonPathFromCstring(char *in, int len, struct Node *escontext)
errmsg("invalid input syntax for type %s: \"%s\"", "jsonpath",
in)));
+ return jsonPathFromParseResult(jsonpath, 4 * len, escontext);
+}
+
+/*
+ * Converts jsonpath AST to a jsonpath value.
+ *
+ * flattenJsonPathParseItem() does second pass turning AST into binary
+ * representation of jsonpath.
+ */
+Datum
+jsonPathFromParseResult(JsonPathParseResult *jsonpath, int estimated_len,
+ struct Node *escontext)
+{
+ JsonPath *res;
+ StringInfoData buf;
+
initStringInfo(&buf);
- enlargeStringInfo(&buf, 4 * len /* estimation */ );
+ enlargeStringInfo(&buf, estimated_len);
appendStringInfoSpaces(&buf, JSONPATH_HDRSZ);
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 23a76d233e9..e05941623e7 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -281,6 +281,10 @@ extern JsonPathParseResult *parsejsonpath(const char *str, int len,
extern bool jspConvertRegexFlags(uint32 xflags, int *result,
struct Node *escontext);
+extern Datum jsonPathFromParseResult(JsonPathParseResult *jsonpath,
+ int estimated_len,
+ struct Node *escontext);
+
/*
* Struct for details about external variables passed into jsonpath executor
*/
--
2.39.5 (Apple Git-154)
[application/octet-stream] v7-0002-Pass-field-accessors-to-generic-subscripting.patch (15.7K, ../CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com/8-v7-0002-Pass-field-accessors-to-generic-subscripting.patch)
download | inline diff:
From 29845033cce1de0e80b3c19d4e56b514d094e956 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 16:21:20 +0300
Subject: [PATCH v7 2/5] Pass field accessors to generic subscripting
---
src/backend/executor/execExpr.c | 24 +++++++---
src/backend/nodes/nodeFuncs.c | 73 ++++++++++++++++++++++++++----
src/backend/parser/parse_collate.c | 22 +++++++--
src/backend/parser/parse_expr.c | 58 ++++++++++++++++--------
src/backend/parser/parse_node.c | 39 ++++++++++++++--
src/backend/parser/parse_target.c | 3 +-
src/backend/utils/adt/arraysubs.c | 13 ++++--
src/backend/utils/adt/jsonbsubs.c | 11 ++++-
src/backend/utils/adt/ruleutils.c | 29 ++++++++----
src/include/parser/parse_node.h | 3 +-
10 files changed, 218 insertions(+), 57 deletions(-)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 03566c4d181..be4213455e5 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -3328,9 +3328,15 @@ ExecInitSubscriptingRef(ExprEvalStep *scratch, SubscriptingRef *sbsref,
{
sbsrefstate->upperprovided[i] = true;
/* Each subscript is evaluated into appropriate array entry */
- ExecInitExprRec(e, state,
- &sbsrefstate->upperindex[i],
- &sbsrefstate->upperindexnull[i]);
+ if (IsA(e, String))
+ {
+ sbsrefstate->upperindex[i] = CStringGetTextDatum(strVal(e));
+ sbsrefstate->upperindexnull[i] = false;
+ }
+ else
+ ExecInitExprRec(e, state,
+ &sbsrefstate->upperindex[i],
+ &sbsrefstate->upperindexnull[i]);
}
i++;
}
@@ -3351,9 +3357,15 @@ ExecInitSubscriptingRef(ExprEvalStep *scratch, SubscriptingRef *sbsref,
{
sbsrefstate->lowerprovided[i] = true;
/* Each subscript is evaluated into appropriate array entry */
- ExecInitExprRec(e, state,
- &sbsrefstate->lowerindex[i],
- &sbsrefstate->lowerindexnull[i]);
+ if (IsA(e, String))
+ {
+ sbsrefstate->lowerindex[i] = CStringGetTextDatum(strVal(e));
+ sbsrefstate->lowerindexnull[i] = false;
+ }
+ else
+ ExecInitExprRec(e, state,
+ &sbsrefstate->lowerindex[i],
+ &sbsrefstate->lowerindexnull[i]);
}
i++;
}
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 7bc823507f1..a9c29ab8f29 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2182,12 +2182,28 @@ expression_tree_walker_impl(Node *node,
case T_SubscriptingRef:
{
SubscriptingRef *sbsref = (SubscriptingRef *) node;
+ ListCell *lc;
+
+ /*
+ * Recurse directly for upper/lower container index lists,
+ * skipping String subscripts used for dot notation.
+ */
+ foreach(lc, sbsref->refupperindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && !IsA(expr, String) && WALK(expr))
+ return true;
+ }
+
+ foreach(lc, sbsref->reflowerindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && !IsA(expr, String) && WALK(expr))
+ return true;
+ }
- /* recurse directly for upper/lower container index lists */
- if (LIST_WALK(sbsref->refupperindexpr))
- return true;
- if (LIST_WALK(sbsref->reflowerindexpr))
- return true;
/* walker must see the refexpr and refassgnexpr, however */
if (WALK(sbsref->refexpr))
return true;
@@ -3082,12 +3098,51 @@ expression_tree_mutator_impl(Node *node,
{
SubscriptingRef *sbsref = (SubscriptingRef *) node;
SubscriptingRef *newnode;
+ ListCell *lc;
+ List *exprs = NIL;
FLATCOPY(newnode, sbsref, SubscriptingRef);
- MUTATE(newnode->refupperindexpr, sbsref->refupperindexpr,
- List *);
- MUTATE(newnode->reflowerindexpr, sbsref->reflowerindexpr,
- List *);
+
+ foreach(lc, sbsref->refupperindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && IsA(expr, String))
+ {
+ String *str;
+
+ FLATCOPY(str, expr, String);
+ expr = (Node *) str;
+ }
+ else
+ expr = mutator(expr, context);
+
+ exprs = lappend(exprs, expr);
+ }
+
+ newnode->refupperindexpr = exprs;
+
+ exprs = NIL;
+
+ foreach(lc, sbsref->reflowerindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && IsA(expr, String))
+ {
+ String *str;
+
+ FLATCOPY(str, expr, String);
+ expr = (Node *) str;
+ }
+ else
+ expr = mutator(expr, context);
+
+ exprs = lappend(exprs, expr);
+ }
+
+ newnode->reflowerindexpr = exprs;
+
MUTATE(newnode->refexpr, sbsref->refexpr,
Expr *);
MUTATE(newnode->refassgnexpr, sbsref->refassgnexpr,
diff --git a/src/backend/parser/parse_collate.c b/src/backend/parser/parse_collate.c
index d2e218353f3..be6dea6ffd2 100644
--- a/src/backend/parser/parse_collate.c
+++ b/src/backend/parser/parse_collate.c
@@ -680,11 +680,25 @@ assign_collations_walker(Node *node, assign_collations_context *context)
* contribute anything.)
*/
SubscriptingRef *sbsref = (SubscriptingRef *) node;
+ ListCell *lc;
+
+ /* skip String subscripts used for dot notation */
+ foreach(lc, sbsref->refupperindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && !IsA(expr, String))
+ assign_expr_collations(context->pstate, expr);
+ }
+
+ foreach(lc, sbsref->reflowerindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && !IsA(expr, String))
+ assign_expr_collations(context->pstate, expr);
+ }
- assign_expr_collations(context->pstate,
- (Node *) sbsref->refupperindexpr);
- assign_expr_collations(context->pstate,
- (Node *) sbsref->reflowerindexpr);
(void) assign_collations_walker((Node *) sbsref->refexpr,
&loccontext);
(void) assign_collations_walker((Node *) sbsref->refassgnexpr,
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 2c0f4a50b21..afe953fdbea 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -461,19 +461,40 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
}
else
{
- Node *newresult;
-
Assert(IsA(n, String));
+ subscripts = lappend(subscripts, n);
+ }
+ }
+
+ /* process trailing subscripts, if any */
+ while (subscripts)
+ {
+ Node *newresult = (Node *)
+ transformContainerSubscripts(pstate,
+ result,
+ exprType(result),
+ exprTypmod(result),
+ &subscripts,
+ false,
+ true);
+
+ if (!newresult)
+ {
+ /* generic subscripting failed */
+ Node *n;
+
+ Assert(subscripts);
+
+ n = linitial(subscripts);
- /* process subscripts before this field selection */
- while (subscripts)
- result = (Node *) transformContainerSubscripts(pstate,
- result,
- exprType(result),
- exprTypmod(result),
- &subscripts,
- false);
+ if (!IsA(n, String))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot subscript type %s because it does not support subscripting",
+ format_type_be(exprType(result))),
+ parser_errposition(pstate, exprLocation(result))));
+ /* try to find function for field selection */
newresult = ParseFuncOrColumn(pstate,
list_make1(n),
list_make1(result),
@@ -481,19 +502,16 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
NULL,
false,
location);
- if (newresult == NULL)
+
+ if (!newresult)
unknown_attribute(pstate, result, strVal(n), location);
- result = newresult;
+
+ /* consume field select */
+ subscripts = list_delete_first(subscripts);
}
+
+ result = newresult;
}
- /* process trailing subscripts, if any */
- while (subscripts)
- result = (Node *) transformContainerSubscripts(pstate,
- result,
- exprType(result),
- exprTypmod(result),
- &subscripts,
- false);
return result;
}
diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c
index 19a6b678e67..c1f8055564f 100644
--- a/src/backend/parser/parse_node.c
+++ b/src/backend/parser/parse_node.c
@@ -245,13 +245,15 @@ transformContainerSubscripts(ParseState *pstate,
Oid containerType,
int32 containerTypMod,
List **indirection,
- bool isAssignment)
+ bool isAssignment,
+ bool noError)
{
SubscriptingRef *sbsref;
const SubscriptRoutines *sbsroutines;
Oid elementType;
bool isSlice = false;
ListCell *idx;
+ int indirection_length = list_length(*indirection);
/*
* Determine the actual container type, smashing any domain. In the
@@ -267,11 +269,16 @@ transformContainerSubscripts(ParseState *pstate,
*/
sbsroutines = getSubscriptingRoutines(containerType, &elementType);
if (!sbsroutines)
+ {
+ if (noError)
+ return NULL;
+
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("cannot subscript type %s because it does not support subscripting",
format_type_be(containerType)),
parser_errposition(pstate, exprLocation(containerBase))));
+ }
/*
* Detect whether any of the indirection items are slice specifiers.
@@ -282,9 +289,9 @@ transformContainerSubscripts(ParseState *pstate,
*/
foreach(idx, *indirection)
{
- A_Indices *ai = lfirst_node(A_Indices, idx);
+ Node *ai = lfirst(idx);
- if (ai->is_slice)
+ if (IsA(ai, A_Indices) && castNode(A_Indices, ai)->is_slice)
{
isSlice = true;
break;
@@ -312,6 +319,32 @@ transformContainerSubscripts(ParseState *pstate,
sbsroutines->transform(sbsref, indirection, pstate,
isSlice, isAssignment);
+ /*
+ * Error out, if datatyoe falied to consume any indirection elements.
+ */
+ if (list_length(*indirection) == indirection_length)
+ {
+ Node *ind = linitial(*indirection);
+
+ if (noError)
+ return NULL;
+
+ if (IsA(ind, String))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("type %s does not support column notation",
+ format_type_be(containerType)),
+ parser_errposition(pstate, exprLocation(containerBase))));
+ else if (IsA(ind, A_Indices))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("type %s does not support array subscripting",
+ format_type_be(containerType)),
+ parser_errposition(pstate, exprLocation(containerBase))));
+ else
+ elog(ERROR, "invalid indirection operation: %d", nodeTag(ind));
+ }
+
/*
* Verify we got a valid type (this defends, for example, against someone
* using array_subscript_handler as typsubscript without setting typelem).
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 39fd82f8371..5e126145ea5 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -937,7 +937,8 @@ transformAssignmentSubscripts(ParseState *pstate,
containerType,
containerTypMod,
&subscripts,
- true);
+ true,
+ false);
if (subscripts)
elog(ERROR, "subscripting assignment is not supported");
diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c
index edd85f7ba67..fe18df86e45 100644
--- a/src/backend/utils/adt/arraysubs.c
+++ b/src/backend/utils/adt/arraysubs.c
@@ -61,6 +61,7 @@ array_subscript_transform(SubscriptingRef *sbsref,
List *upperIndexpr = NIL;
List *lowerIndexpr = NIL;
ListCell *idx;
+ int ndim;
/*
* Transform the subscript expressions, and separate upper and lower
@@ -72,9 +73,14 @@ array_subscript_transform(SubscriptingRef *sbsref,
*/
foreach(idx, *indirection)
{
- A_Indices *ai = lfirst_node(A_Indices, idx);
+ A_Indices *ai;
Node *subexpr;
+ if (!IsA(lfirst(idx), A_Indices))
+ break;
+
+ ai = lfirst_node(A_Indices, idx);
+
if (isSlice)
{
if (ai->lidx)
@@ -144,14 +150,15 @@ array_subscript_transform(SubscriptingRef *sbsref,
sbsref->reflowerindexpr = lowerIndexpr;
/* Verify subscript list lengths are within implementation limit */
- if (list_length(upperIndexpr) > MAXDIM)
+ ndim = list_length(upperIndexpr);
+ if (ndim > MAXDIM)
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("number of array dimensions (%d) exceeds the maximum allowed (%d)",
list_length(upperIndexpr), MAXDIM)));
/* We need not check lowerIndexpr separately */
- *indirection = NIL;
+ *indirection = list_delete_first_n(*indirection, ndim);
/*
* Determine the result type of the subscripting operation. It's the same
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index 8ad6aa1ad4f..a0d38a0fd80 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -55,9 +55,14 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
*/
foreach(idx, *indirection)
{
- A_Indices *ai = lfirst_node(A_Indices, idx);
+ A_Indices *ai;
Node *subExpr;
+ if (!IsA(lfirst(idx), A_Indices))
+ break;
+
+ ai = lfirst_node(A_Indices, idx);
+
if (isSlice)
{
Node *expr = ai->uidx ? ai->uidx : ai->lidx;
@@ -160,7 +165,9 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
sbsref->refrestype = JSONBOID;
sbsref->reftypmod = -1;
- *indirection = NIL;
+ /* Remove processed elements */
+ if (upperIndexpr)
+ *indirection = list_delete_first_n(*indirection, list_length(upperIndexpr));
}
/*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 54dad975553..af5417d0859 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -47,6 +47,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "nodes/pathnodes.h"
+#include "nodes/subscripting.h"
#include "optimizer/optimizer.h"
#include "parser/parse_agg.h"
#include "parser/parse_func.h"
@@ -12916,17 +12917,29 @@ printSubscripts(SubscriptingRef *sbsref, deparse_context *context)
lowlist_item = list_head(sbsref->reflowerindexpr); /* could be NULL */
foreach(uplist_item, sbsref->refupperindexpr)
{
- appendStringInfoChar(buf, '[');
- if (lowlist_item)
+ Node *up = (Node *) lfirst(uplist_item);
+
+ if (IsA(up, String))
+ {
+ appendStringInfoChar(buf, '.');
+ appendStringInfoString(buf, quote_identifier(strVal(up)));
+ }
+ else
{
+ appendStringInfoChar(buf, '[');
+ if (lowlist_item)
+ {
+ /* If subexpression is NULL, get_rule_expr prints nothing */
+ get_rule_expr((Node *) lfirst(lowlist_item), context, false);
+ appendStringInfoChar(buf, ':');
+ }
/* If subexpression is NULL, get_rule_expr prints nothing */
- get_rule_expr((Node *) lfirst(lowlist_item), context, false);
- appendStringInfoChar(buf, ':');
- lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
+ get_rule_expr((Node *) lfirst(uplist_item), context, false);
+ appendStringInfoChar(buf, ']');
}
- /* If subexpression is NULL, get_rule_expr prints nothing */
- get_rule_expr((Node *) lfirst(uplist_item), context, false);
- appendStringInfoChar(buf, ']');
+
+ if (lowlist_item)
+ lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
}
}
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 5ae11ccec33..71b04bd503c 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -378,7 +378,8 @@ extern SubscriptingRef *transformContainerSubscripts(ParseState *pstate,
Oid containerType,
int32 containerTypMod,
List **indirection,
- bool isAssignment);
+ bool isAssignment,
+ bool noError);
extern Const *make_const(ParseState *pstate, A_Const *aconst);
#endif /* PARSE_NODE_H */
--
2.39.5 (Apple Git-154)
view thread (67+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2023 JSON simplified accessor support
In-Reply-To: <CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox