Subject: Re: Extract numeric filed in JSONB more effectively

From: jian he <[email-masked]>
Date: Thu, 17 Aug 2023 00:32:08 +0800

On Wed, Aug 16, 2023 at 2:28 PM Andy Fan <[email-masked]> wrote:
>
> update with the correct patch..

regression=# select proname, pg_catalog.pg_get_function_arguments(oid)
from pg_proc
where proname =  'jsonb_extract_path_type';
         proname         |                     pg_get_function_arguments
-------------------------+--------------------------------------------------------------------
 jsonb_extract_path_type | from_json jsonb, VARIADIC path_elems
text[], target_oid anyelement
(1 row)

VARIADIC should be the last argument?

select jsonb_array_element_type(jsonb'[1231]',0, null::int);
now return null.
Should it return 1231?

regression=# select jsonb_array_element_type(jsonb'1231',0, 1::int);
 jsonb_array_element_type
--------------------------
                     1231
(1 row)

not sure if it's ok. if you think it's not ok then:
+ if (!JB_ROOT_IS_ARRAY(jb))
+PG_RETURN_NULL();
change to
+if (JB_ROOT_IS_SCALAR(jb) || !JB_ROOT_IS_ARRAY(jb))
+PG_RETURN_NULL();

select jsonb_array_element_type(jsonb'[1231]',0, '1'::jsonb);
will crash, because jsonb_array_element_type call
cast_jsonbvalue_to_type then in switch case, it will go to
default part. in default part you have Assert(false);
also in cast_jsonbvalue_to_type, PG_RETURN_POINTER(NULL) code won't be reached.

in jsonb_cast_support function. you already have
!jsonb_cast_is_optimized(fexpr->funcresulttype)). then in the default
branch of cast_jsonbvalue_to_type, you can just elog(error, "can only
cast to xxx type"). jsonb_array_element_type, jsonb_object_field_type,
 third argument is anyelement. so targetOid can be any datatype's oid.

---

From: Andy Fan <[email-masked]>
Date: Thu, 17 Aug 2023 17:07:31 +0800

Hi jian:

>          proname         |                     pg_get_function_arguments

Currently if users call this function directly(usually I don't  think
so), they will get something wrong.   This issue is fixed in the
v9 version.  To keep the consistency among all the functions,
I moved the 'target_type anyelement' to the 1st argument.
Thanks for the report!

> select jsonb_array_element_type(jsonb'[1231]',0, null::int);

No, this is by design. the function is declared as strict, so
any NULL inputs yield a NULL output.  That's just what we
talked above (the markDummyConst section).  I don't
think this should be addressed.

> will crash

OK,  looks I didn't pay enough attention to the 'user directly call
jsonb_xx_type' function, so I changed the code in v9 based on
your suggestion.

Thanks for the review,  v9 attached!

-- 
Best Regards
Andy Fan

---

From: Chapman Flack <[email-masked]>
Date: Thu, 17 Aug 2023 16:30:41 -0400

> Thanks for the review,  v9 attached!

 From the earliest iterations of this patch, I seem to recall
a couple of designs being considered:

In one, the type-specific cast function would only be internally
usable, would take a type oid as an extra parameter (supplied in
the SupportRequestSimplify rewriting), and would have to be
declared with some nonspecific return type; 'internal' was
mentioned.

The idea of an 'internal' return type with no 'internal' parameter
was quickly and rightly shot down. But it would have seemed to me
enough to address that objection by using 'internal' also in its
parameter list. I could imagine a function declared with two
'internal' parameters, one understood to be a JsonbValue and one
understood to be a type oid, and an 'internal' result, treated in
the rewritten expression tree as binary-coercible to the desired
result.

Admittedly, I have not tried to implement that myself to see
what unexpected roadblocks might exist on that path. Perhaps
there are parts of that rewriting that no existing node type
can represent? Someone more familiar with those corners of
PostgreSQL may immediately see other difficulties I do not.

But I have the sense that that approach was abandoned early, in
favor of the current approach using user-visible polymorphic
types, and supplying typed dummy constants for use in the
resolution of those types, with a new function introduced to create
said dummy constants, including allocation and input conversion
in the case of numeric, just so said dummy constants can be
passed into functions that have no use for them other than to
call get_fn_expr_argtype to recover the type oid, which was the
only thing needed in the first place.

Compared to the initial direction I thought this was going,
none of that strikes me as better.

Nothing makes my opinion authoritative here, and there may
indeed be reasons it is better, known to others more familiar
with that code than I am. But it bugs me.

If the obstacles to the earlier approach came down to needing
a new type of expression node, something like "assertion of
'internal'-to-foo binary coercibility, vouched by a prosupport
function", would that be a bad thing?

Regards,
-Chap

---

From: Andy Fan <[email-masked]>
Date: Fri, 18 Aug 2023 09:14:27 +0800

Hi Chapman,

Thanks for the review!

> was quickly and rightly shot down.

Yes, it mainly breaks the type-safety system.  Parser need to know
the result type, so PG defines the rule like this:

anyelement fn(anyment in);

if the exprType(in) in the query tree is X, then PG would think fn
return type X.  that's why we have to have an anyelement in the
input.

> But it would have seemed to me

I have some trouble understanding this.  are you saying something
like:

internal fn(internal jsonValue,  internal typeOid)?

If so, would it break the type-safety system?  And I'm not pretty
sure the 'binary-coercible' here.  is it same as the 'binary-coercible'
in "timestamp is not binary coercible with timestamptz since..."?
I have a strong feeling that I think I misunderstood you here.

> Perhaps there are parts of that rewriting that no existing node type
> can represent?
>

I didn't understand this as well:(:(

> only thing needed in the first place.

Yes,  but if we follow the type-safety system, we can't simply input
a Oid targetOid, then there are some more considerations here:
a).  we can't use the makeNullConst because jsonb_xxx_type is
strict,  so if we have NULL constant input here,  the PG system
will return NULL directly.  b).  Not only the type oid is the thing
We are interested in the  const.constvalue is as well since
'explain select xxxx'  to access it to show it as a string.
Datum(0) as the constvalue will crash in this sense.  That's why
makeDummyConst was introduced.

> something like "assertion of

I can't follow this as well.  Could you provide the function prototype
here?

-- 
Best Regards
Andy Fan

---

From: Chapman Flack <[email-masked]>
Date: Thu, 17 Aug 2023 22:55:56 -0400

> the result type, so PG defines the rule like this:

Well, the reason "internal return type with no internal parameter type"
was shot down was more specific: if there is such a function in the
catalog, an SQL user can call it, and then its return type is a value
typed 'internal', and with that the SQL user could call other
functions with 'internal' parameters, and that's what breaks type
safety. The specific problem is not having at least one 'internal'
input parameter.

There are lots of functions in the catalog with internal return type
(I count 111). They are not inherently bad; the rule is simply that
each one also needs at least one IN parameter typed internal, to
make sure it can't be directly called from SQL.

> return type X.  that's why we have to have an anyelement in the

That's a consequence of the choice to have anyelement as the return
type, though. A different choice wouldn't have that consequence.

> If so, would it break the type-safety system?

That is what I'm saying, and it doesn't break type safety at the
SQL level, because as long as it has parameters declared internal,
no SQL can ever call it. So it can only appear in an expression
tree because your SupportRequestSimplify put it there properly
typed, after the SQL query was parsed but before evaluation.

The thing about 'internal' is it doesn't represent any specific
type, it doesn't necessarily represent the same type every time it
is mentioned, and it often means something that isn't a cataloged
type at all, such as a pointer to some kind of struct. There must be
documentation explaining what it has to be. For example, your
jsonb_cast_support function has an 'internal' parameter and
'internal' return type. From the specification for support
functions, you know the 'internal' for the parameter type means
"one of the Node structs in supportnodes.h", and the 'internal'
for the return type means "an expression tree semantically
equivalent to the FuncExpr".

So, in addition to declaring
internal fn(internal jsonValue,  internal typeOid), you would
have to write a clear spec that jsonValue has to be a JsonbValue,
typeOid has to be something you can call DatumGetObjectId on,
and the return value should be a Datum in proper form
corresponding to typeOid. And, of course, generate the expression
tree so all of that is true when it's evaluated.

>> Perhaps there are parts of that rewriting that no existing node type

The description above was in broad strokes. Because I haven't
tried to implement this, I don't know whether some roadblock would
appear, such as, is it hard to make a Const node of type internal
and containing an oid? Or, what sort of node must be inserted to
clarify that the 'internal' return is actually a Datum of the
expected type? By construction, we know that it is, but how to
make that explicit in the expression tree?

> makeDummyConst was introduced.

Again, all of that complication stems from the choice to use the
anyelement return type and rely on polymorphic type resolution
to figure the oid out, when we already have the oid to begin with
and the oid is all we want.

> I can't follow this as well.

That was just another way of saying what I was getting at above
about what's needed in the expression tree to indicate that the
'internal' produced by this function is, in fact, really a bool
(or whatever). We know that it is, but perhaps the expression
tree will be considered ill-formed without a node that says so.
A node representing a no-op, binary conversion would suffice,
but is there already a node that's allowed to represent an
internal-to-bool no-op cast?

If there isn't, one might have to be invented. So it might be that
if we go down the "use polymorphic resolution" road, we have to
invent dummy Consts, and down the "internal" road we also have to
invent something, like the "no-op cast considered correct because
a SupportRequestSimplify function put it here" node.

If it came down to having to invent one of those things or the
other, I'd think the latter more directly captures what we really
want to do.

(And I'm not even sure anything has to be invented. If there's an
existing node for no-op binary casts, I think I'd first try
putting that there and see if anything complains.)

If this thread is being followed by others more familiar with
the relevant code or who see obvious problems I'm missing,
please chime in!

Regards,
-Chap

---

From: jian he <[email-masked]>
Date: Fri, 18 Aug 2023 13:02:55 +0800

> and the oid is all we want.

you want jsonb_object_field_type(internal, jsonb, text)? because on
sql level, it's safe.

The return data type is determined when we are in jsonb_cast_support.
we just need to pass the {return data type} information to the next
function: jsonb_object_field_type.

---

From: Andy Fan <[email-masked]>
Date: Fri, 18 Aug 2023 15:41:13 +0800

> because as long as it has parameters declared internal,
> no SQL can ever call it.

I was confused about the difference between anyelement and
internal, and I want to know a way to create a function which
is disallowed to be called by the user.  Your above words
resolved two questions of mine!

So it can only appear in an expression
> type at all, such as a pointer to some kind of struct.

I should have noticed this during the study planner support function,
but highlighting this is pretty amazing.

> invent something.

I think you might already feel that putting an internal function
into an expression would cause something wrong.  I just have
a quick hack on this, and crash happens at the simplest case.
If something already exists to fix this, I am inclined
to use 'internal', but I didn't find the way.  I'm thinking if we
should clarify "internal" should only be used internally and
should never be used in expression by design?

> the relevant code or who see obvious problems I'm missing,

Thank you wise & modest gentleman,  I would really hope Tom can
chime in at this time.

In general,  the current decision we need to make is shall we use
'internal' or 'anyelement' to present the target OID.  the internal way
would be more straight but have troubles to be in the expression tree.
the 'anyelement'  way is compatible with expression, but it introduces
the makeDummyConst overhead and I'm not pretty sure it is a correct
implementation in makeDummyConst. see the XXX part.

+/*
+ * makeDummyConst
+ *      create a Const node with the specified type/typmod.
+ *
+ * This is a convenience routine to create a Const which only the
+ * type is interested but make sure the value is accessible.
+ */
+Const *
+makeDummyConst(Oid consttype, int32 consttypmod, Oid constcollid)
+{
+       int16           typLen;
+       bool            typByVal;
+       Const           *c;
+       Datum           val = 0;
+
+
+       get_typlenbyval(consttype, &typLen, &typByVal);
+
+       if (consttype == NUMERICOID)
+               val = DirectFunctionCall1(numeric_in, CStringGetDatum("0"));
+       else if (!typByVal)
+               elog(ERROR, "create dummy const for type %u is not
supported.", consttype);
+
+       /* XXX: here I assume constvalue=0 is accessible for const by value
type.*/
+       c = makeConst(consttype, consttypmod, 0, (int) typLen, val, false,
typByVal);
+
+       return c;
+}

-- 
Best Regards
Andy Fan

---

From: Chapman Flack <[email-masked]>
Date: Fri, 18 Aug 2023 14:50:15 -0400

On 2023-08-18 03:41, Andy Fan wrote:
> I just have

If I build from this patch, this test:

SELECT (test_json -> 0)::int4, test_json -> 0 FROM test_jsonb WHERE 
json_type = 'scalarint';

fails like this:

Program received signal SIGSEGV, Segmentation fault.
convert_saop_to_hashed_saop_walker (node=0x17, context=0x0)
     at 
/var/tmp/nohome/pgbuildh/../postgresql/src/backend/optimizer/util/clauses.c:2215
2215		if (IsA(node, ScalarArrayOpExpr))

(gdb) p node
$1 = (Node *) 0x17

So the optimizer is looking at some node to see if it is a
ScalarArrayOpExpr, but the node has some rather weird address.

Or maybe it's not that weird. 0x17 is 23, and so is:

select 'int4'::regtype::oid;
  oid
-----
   23

See what happened?

+ int64 target_typ = fexpr->funcresulttype;
...
+ fexpr->args = list_insert_nth(fexpr->args, 0, (void *) target_typ);

This is inserting the desired result type oid directly as the first
thing in the list of fexpr's args.

But at the time your support function is called, nothing is being
evaluated yet. You are just manipulating a tree of expressions to
be evaluated later, and you want fexpr's first arg to be an
expression that will produce this type oid later, when it is
evaluated.

A constant would do nicely:

+ Const	*target  = makeConst(
	INTERNALOID, -1, InvalidOid, SIZEOF_DATUM,
	ObjectIdGetDatum(fexpr->funcresulttype), false, true);
+ fexpr->args = list_insert_nth(fexpr->args, 0, target);

With that change, it doesn't segfault, but it does do this:

ERROR:  cast jsonb to type 0 is not allowed

and that's because of this:

+ Oid			targetOid = DatumGetObjectId(0);

The DatumGetFoo(x) macros are for when you already have the Datum
(it's x) and you know it's a Foo. So this is just setting targetOid
to zero. When you want to get something from function argument 0 and
you know that's a Foo, you use a PG_GETARG_FOO(argno) macro (which
amounts to PG_GETARG_DATUM(argno) followed by DatumGetFoo.

So, with

+ Oid			targetOid = PG_GETARG_OID(0);

SELECT (test_json -> 0)::int4, test_json -> 0 FROM test_jsonb WHERE 
json_type = 'scalarint';
  int4 | ?column?
------+----------
     2 | 2

However, EXPLAIN is sad:

ERROR:  cannot display a value of type internal

and that may be where this idea runs aground.

Now, I was expecting something to complain about the result of
jsonb_array_element_type, and that didn't happen. We rewrote
a function that was supposed to be a cast to int4, and
replaced it with a function returning internal, and evaluation
happily just took that as the int4 that the next node expected.

If something had complained about that, it might have been
necessary to insert some new node above the internal-returning
function to say the result was really int4. Notice there is a
makeRelabelType() for that. (I had figured there probably was,
but didn't know its exact name.)

So it doesn't seem strictly necessary to do that, but it might
make the EXPLAIN result look better (if EXPLAIN were made to work,
of course).

Now, my guess is EXPLAIN is complaining when it sees the Const
of type internal, and doesn't know how to show that value.
Perhaps makeRelabelType is the answer there, too: what if the
Const has Oid type, so EXPLAIN can show it, and what's inserted
as the function argument is a relabel node saying it's internal?

Haven't tried that yet.

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Fri, 18 Aug 2023 15:08:57 -0400

> as the function argument is a relabel node saying it's internal?

Simply changing the Const to be of type Oid makes EXPLAIN happy,
and nothing ever says "hey, why are you passing this oid for an
arg that wants internal?". This is without adding any relabel
nodes anywhere.

  Seq Scan on pg_temp.test_jsonb
    Output: pg_catalog.jsonb_array_element_type('23'::oid, test_json, 0), 
(test_json -> 0)
    Filter: (test_jsonb.json_type = 'scalarint'::text)

Nothing in that EXPLAIN output to make you think anything weird
was going on, unless you went and looked up jsonb_array_element_type
and saw that its arg0 isn't oid and its return type isn't int4.

But I don't know that adding relabel nodes wouldn't still be
the civilized thing to do.

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Fri, 18 Aug 2023 17:02:52 -0400

> the civilized thing to do.

Interestingly, when I relabel both places, like this:

     Oid   targetOid = fexpr->funcresulttype;
     Const *target  = makeConst(
       OIDOID, -1, InvalidOid, sizeof(Oid),
       ObjectIdGetDatum(targetOid), false, true);
     RelabelType *rTarget = makeRelabelType((Expr *)target,
       INTERNALOID, -1, InvalidOid, COERCE_IMPLICIT_CAST);
     fexpr->funcid = new_func_id;
     fexpr->args = opexpr->args;
     fexpr->args = list_insert_nth(fexpr->args, 0, rTarget);
     expr = (Expr *)makeRelabelType((Expr *)fexpr,
       targetOid, -1, InvalidOid, COERCE_IMPLICIT_CAST);
   }
   PG_RETURN_POINTER(expr);

EXPLAIN looks like this:

  Seq Scan on pg_temp.test_jsonb
    Output: jsonb_array_element_type(('23'::oid)::internal, test_json, 
0), (test_json -> 0)
    Filter: (test_jsonb.json_type = 'scalarint'::text)

With COERCE_IMPLICIT_CAST both places, the relabeling of the
function result is invisible, but the relabeling of the argument
is visible.

With the second one changed to COERCE_EXPLICIT_CAST:

  Seq Scan on pg_temp.test_jsonb
    Output: (jsonb_array_element_type(('23'::oid)::internal, test_json, 
0))::integer, (test_json -> 0)
    Filter: (test_jsonb.json_type = 'scalarint'::text)

then both relabelings are visible.

I'm not sure whether one way is better than the other, or whether
it is even important to add the relabel nodes at all, as nothing
raises an error without them. As a matter of taste, it seems like
a good idea though.

Regards,
-Chap

---

From: Andy Fan <[email-masked]>
Date: Mon, 21 Aug 2023 09:31:56 +0800

> arg that wants internal?". This is without adding any relabel

Highlighting the user case of makeRelableType is interesting! But using
the Oid directly looks more promising for this question IMO, it looks like:
"you said we can put anything in this arg,  so I put an OID const here",
seems nothing is wrong.  Compared with the makeRelableType method,
I think the current method is more straightforward.  Compared with
anyelement, it avoids the creation of makeDummyConst which I'm not
sure the implementation is alway correct.  So I am pretty inclined to this
way!

v10 attached.

-- 
Best Regards
Andy Fan

---

From: Chapman Flack <[email-masked]>
Date: Sun, 20 Aug 2023 23:19:47 -0400

On 2023-08-20 21:31, Andy Fan wrote:
> seems nothing is wrong.

Perhaps one of the more senior developers will chime in, but to me,
leaving out the relabel nodes looks more like "all of PostgreSQL's
type checking happened before the SupportRequestSimplify, so nothing
has noticed that we rewrote the tree with mismatched types, and as
long as nothing crashes we sort of got away with it."

Suppose somebody writes an extension to double-check that plan
trees are correctly typed. Or improves EXPLAIN to check a little more
carefully than it seems to. Omitting the relabel nodes could spell
trouble then.

Or, someone more familiar with the code than I am might say "oh,
mismatches like that are common in rewritten trees, we live with it."
But unless somebody tells me that, I'm not believing it.

But I would say we have proved the concept of SupportRequestSimplify
for this task. :)

Now, it would make me happy to further reduce some of the code
duplication between the original and the _type versions of these
functions. I see that you noticed the duplication in the case of
jsonb_extract_path, and you factored out jsonb_get_jsonbvalue so
it could be reused. There is also some duplication with object_field
and array_element. (Also, we may have overlooked jsonb_path_query
and jsonb_path_query_first as candidates for the source of the
cast. Two more candidates; five total.)

Here is one way this could be structured. Observe that every one
of those five source candidates operates in two stages:

Start: All of the processing until a JsonbValue has been obtained.
Finish: Converting the JsonbValue to some form for return.

Before this patch, there were two choices for Finish:
JsonbValueToJsonb or JsonbValueAsText.

With this patch, there are four Finish choices: those two, plus
PG_RETURN_BOOL(v->val.boolean), PG_RETURN_NUMERIC(v->val.numeric).

Clearly, with rewriting, we can avoid 5✕4 = 20 distinct
functions. The five candidate functions only differ in Start.
Suppose each of those had a _start version, like
jsonb_object_field_start, that only proceeds as far as
obtaining the JsonbValue, and returns that directly (an
'internal' return type). Naturally, each _start function would
need an 'internal' parameter also, even if it isn't used,
just to make sure it is not SQL-callable.

Now consider four Finish functions: jsonb_finish_jsonb,
jsonb_finish_text, jsonb_finish_boolean, jsonb_finish_numeric.

Each would have one 'internal' parameter (a JsonbValue), and
its return type declared normally. There is no need to pass
a type oid to any of these, and they need not contain any
switch to select a return type. The correct finisher to use
is simply chosen once at the time of rewriting.

So cast(jsonb_array_element(jb, 0) as numeric) would just get
rewritten as jsonb_finish_numeric(jsonb_array_element_start(jb,0)).

The other (int and float) types don't need new code; just have
the rewriter add a cast-from-numeric node on top. That's all
those other switch cases in cast_jsonbvalue_to_type are doing,
anyway.

Notice in this structure, less relabeling is needed. The
final return does not need relabeling, because each finish
function has the expected return type. Each finish function's
parameter is typed 'internal' (a JsonbValue), but that's just
what each start function returns, so no relabeling needed
there either.

The rewriter will have to supply some 'internal' constant
as a start-function parameter (because of the necessary
'internal' parameter). It might still be civilized to relabel
that.

Regards,
-Chap

---

From: Andy Fan <[email-masked]>
Date: Mon, 21 Aug 2023 18:58:05 +0800

> function result is invisible, but the relabeling of the argument
I think this is because get_rule_expr's showimplicit is always
true for args in this case, checking the implementation of
get_rule_expr, I found PG behavior like this in many places.

-- 
Best Regards
Andy Fan

---

From: Andy Fan <[email-masked]>
Date: Tue, 22 Aug 2023 11:14:48 +0800

（Just relalized this was sent to chap in private, resent it again).

On Mon, Aug 21, 2023 at 6:50 PM Andy Fan <[email-masked]> wrote:

>
>
> On Mon, Aug 21, 2023 at 11:19 AM Chapman Flack <[email-masked]>
> wrote:
>
> Well, this sounds long-lived.  I kind of prefer to label it now.  Adding
> the 3rd commit to relabel the arg and return value.
>
>
> Yes,  this is great!
>> and array_element.
Yes, compared with jsonb_extract_path,  object_field and array_element
just have much less duplication, which are 2 lines and 6 lines separately.

> (Also, we may have overlooked jsonb_path_query
I can try to add them at the same time when we talk about the
infrastruct,  thanks for the hint!

> I'm not very excited with this manner, reasons are: a).  It will have
> to emit more steps in ExprState->steps which will be harmful for
> execution. The overhead  is something I'm not willing to afford.
> b). this manner requires more *internal*, which is kind of similar
> to "void *"  in C.  Could you explain more about the benefits of this?
>
> --
> Best Regards
> Andy Fan
>

-- 
Best Regards
Andy Fan

---

From: Andy Fan <[email-masked]>
Date: Tue, 22 Aug 2023 13:54:27 +0800

>> the 3rd commit to relabel the arg and return value.
After we label it, we will get error like this:

select (a->'a')::int4 from m;
ERROR:  cannot display a value of type internal

However the following statement can work well.

 select ('{"a": 12345}'::jsonb->'a')::numeric;
 numeric
---------
   12345

That's mainly because the later query doesn't go through the planner
support function. I didn't realize this before so the test case doesn't
catch it.  Will add the test case  in the next version.  The reason why
we get the error for the first query is because the query tree says
we should output  an "internal"  result at last and then pg doesn't
know how to output an internal data type. This is kind of in conflict
with our goal.

So currently the only choices are:  PATCH 001 or PATCH 001 + 002.

https://www.postgresql.org/message-id/CAKU4AWrs4Pzajm2_tgtUTf%3DCWfDJEx%3D3h45Lhqg7tNOVZw5YxA%40mail.gmail.com

-- 
Best Regards
Andy Fan

---

From: Chapman Flack <[email-masked]>
Date: Tue, 22 Aug 2023 08:16:02 -0400

> ERROR:  cannot display a value of type internal

Without looking in depth right now, I would double-check
what relabel node is being applied at the result. The idea,
of course, was to relabel the result as the expected result
type, not internal.

(Or, as in the restructuring suggested earlier, to use a
finish function whose return type is already as expected,
and needs no relabeling.)

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Sat, 26 Aug 2023 18:28:17 -0400

> of course, was to relabel the result as the expected result

as I suspected, looking at v10-0003, there's this:

+   fexpr = (FuncExpr *)makeRelabelType((Expr *) fexpr, INTERNALOID,
+                                       0, InvalidOid, 
COERCE_IMPLICIT_CAST);

compared to the example I had sent earlier:

>       targetOid, -1, InvalidOid, COERCE_IMPLICIT_CAST);

The key difference: this is the label going on the result type
of the function we are swapping in. The function already has
return type declared internal; we want to relabel it as
returning the type identified by targetOid. A relabel node
to type internal is the reverse of what's needed (and also
superfluous, as the function's return type is internal already).

Two more minor differences: (1) the node you get from
makeRelabelType is an Expr, but not really a FuncExpr. Casting
it to FuncExpr is a bit bogus. Also, the third argument to
makeRelabelType is a typmod, and I believe the "not-modified"
typmod is -1, not 0.

In the example I had sent earlier, there were two relabel nodes,
serving different purposes. In one, we have a function that wants
internal for an argument, but we've made a Const with type OIDOID,
so we want to say "this is internal, the thing the function wants."

The situation is reversed at the return type of the function: the
function declares its return type internal, but the surrounding
query is expecting an int4 (or whatever targetOid identifies),
so any relabel node there needs to say it's an int4 (or whatever).

So, that approach involves two relabelings. In the one idea for
restructuring that I suggested earlier, that's reduced: the
..._int function produces a JsonbValue (typed internal) and
the selected ..._finish function expects that, so those types
match and no relabeling is called for. And with the correct
..._finish function selected at rewrite time, it already has
the same return type the surrounding query expects, so no
relabeling is called for there either.

However, simply to ensure the ..._int function cannot be
casually called, it needs an internal parameter, even an
unused one, and the rewriter must supply a value for that,
which may call for one relabel node.

> execution. The overhead  is something I'm not willing to afford.

I would be open to a performance comparison, but offhand I am not
sure whether the overhead of another step or two in an ExprState
is appreciably more than some of the overhead in the present patch,
such as the every-time-through fcinfo initialization buried in
DirectFunctionCall1 where you don't necessarily see it. I bet
the fcinfo in an ExprState step gets set up once, and just has
new argument values slammed into it each time through.

(Also, I know very little about how the JIT compiler is used in PG,
but I suspect that a step you bury inside your function is a step
it may not get to see.)

> to "void *"  in C.

I'm not sure in what sense you mean "more". The present patch
has to deal with two places where some other type must be
relabeled internal or something internal relabeled to another
type. The approach I suggested does involve two families of
function, one returning internal (a JsonbValue) and one
expecting internal (a JsonbValue), where the rewriter would
compose one over the other, no relabeling needed. There's
also an internal parameter needed for whatever returns
internal, and that's just the protocol for how such things
are done. To me, that seems like a fairly principled use of
the type.

I would not underestimate the benefit of reducing the code
duplication and keeping the patch as clear as possible.
The key contributions of the patch are getting a numeric or
boolean efficiently out of the JSON operation. Getting from
numeric to int or float are things the system already does
well. A patch that focuses on what it contributes, and avoids
redoing things the system already can do--unless the duplication
can be shown to have a strong performance benefit--is easier to
review and probably to get integrated.

Regards,
-Chap

---

From: Andy Fan <[email-masked]>
Date: Wed, 30 Aug 2023 12:47:35 +0800

(Sorry for leaving this discussion for such a long time,  how times fly!)

On Sun, Aug 27, 2023 at 6:28 AM Chapman Flack <[email-masked]> wrote:

> of the function we are swapping in.

I'm feeling we have some understanding gap in this area, let's
see what it is.  Suppose the original query is:

numeric(jsonb_object_field(v_jsonb, text)) -> numeric.

without the patch 003,  the rewritten query is:
jsonb_object_field_type(NUMERICOID,  v_jsonb, text) -> NUMERIC.

However the declared type of jsonb_object_field_type is:

jsonb_object_field_type(internal, jsonb, text) -> internal.

So the situation is:  a).  We input a CONST(type=OIDOID, ..) for an
internal argument.  b).  We return a NUMERIC type which matches
the original query c).  result type NUMERIC doesn't match the declared
type  'internal'  d).  it doesn't match the  run-time type of internal
argument which is OID.

case a) is fixed by RelableType.  case b) shouldn't be treat as an
issue.  I thought you wanted to address the case c), and patch
003 tries to fix it, then the ERROR above.  At last I realized case
c) isn't the one you want to fix.  case d) shouldn't be requirement
at the first place IIUC.

So your new method is:
1. jsonb_{op}_start() ->  internal  (internal actually JsonbValue).
2. jsonb_finish_{type}(internal, ..) -> type.   (internal is JsonbValue ).

This avoids the case a) at the very beginning.  I'd like to provides
patches for both solutions for comparison.  Any other benefits of
this method I am missing?

> makeRelabelType is a typmod, and I believe the "not-modified"

My fault, you are right.

> new argument values slammed into it each time through.

fcinfo initialization in DirectFunctionCall1 is an interesting point!
so  I am persuaded the extra steps in  ExprState may not be
worse than the current way due to the "every-time-through
fcinfo initialization" (in which case the memory is allocated
once in heap rather than every time in stack).   I can do a
comparison at last to see if we can find some other interesting
findings.

> well.

True, reusing the casting system should be better than hard-code
the casting function manually.  I'd apply this on both methods.

> A patch that focuses on what it contributes, and avoids

Agreed.

At last, thanks for the great insights and patience!

-- 
Best Regards
Andy Fan

---

From: Chapman Flack <[email-masked]>
Date: Wed, 30 Aug 2023 09:47:53 -0400

On 2023-08-30 00:47, Andy Fan wrote:
> So the situation is: b).  We return a NUMERIC type which matches
> the original query ...
>  case b) shouldn't be treat as an issue.

*We* may know we are returning a NUMERIC type which matches the
original query, but nothing else knows that. Anything that
examined the complete tree after our rewriting would see some
expression that wants a numeric type, but supplied with a
subexpression that returns internal. Without a relabel node
there to promise that we know this internal is really numeric,
any type checker would reject the tree.

The fact that it even works at all without a relabel node there
seems to indicate that all of PostgreSQL's type checking was
done before calling the support function, and that there is not
much sanity checking of what the support function returns,
which I guess is efficient, if a little scary. Seems like
writing a support function is a bit like trapeze performing
without a net.

> patches for both solutions for comparison.

I think, unavoidably, there is still a case a) at the very beginning,
just because of the rule that if json_{op}_start is going to have an
internal return type, it needs to have at least one internal parameter
to prevent casual calls from SQL, even if that parameter is not used
for anything.

It would be ok to write in a Const for that parameter, just zero or
42 or anything besides null (in case the function is strict), but
again if the Const has type internal then EXPLAIN will be sad, so
it has to be some type that makes EXPLAIN cheerful, and relabeled
internal.

But with this approach there is no longer a type mismatch of the
end result.

> once in heap rather than every time in stack).

Stack allocation is super cheap, just by emitting the function
entry to reserve n+m bytes instead of just m, so it there's any
measurable cost to the DirectFunctionCall I would think it more
likely to be in the initialization after allocation ... but I
haven't looked at that code closely to see how much there is.
I just wanted to make the point that another step or two in
ExprState might not be a priori worse. We might be talking
about negligible effects in either direction.

> I can do a

That would be the way to find out. I think I would still lean
toward the approach with less code duplication, unless there
is a strong timing benefit the other way.

> the casting function manually.  I'd apply this on both methods.

I noticed there is another patch registered in this CF: [1]
It adds new operations within jsonpath like .bigint .time
and so on.

I was wondering whether that work would be conflicting or
complementary with this. It looks to be complementary. The
operations being added there are within jsonpath evaluation.
Here we are working on faster ways to get those results out.

It does not seem that [1] will add any new choices in
JsonbValue. All of its (.bigint .integer .number) seem to
verify the requested form and then put the result as a
numeric in ->val.numeric. So that doesn't add any new
cases for this patch to handle. (Too bad, in a way: if that
other patch added ->val.bigint, this patch could add a case
to retrieve that value without going through the work of
making a numeric. But that would complicate other things
touching JsonbValue, and be a matter for that other patch.)

It may be expanding the choices for what we might one day
find in ->val.datetime though.

Regards,
-Chap

[1] https://commitfest.postgresql.org/44/4526/

---

From: Andy Fan <[email-masked]>
Date: Thu, 31 Aug 2023 17:10:39 +0800

Hi Chap,

The v11 attached, mainly changes are:
1.  use the jsonb_xx_start and jsonb_finish_numeric style.
2.  improve the test case a bit.

It doesn't include:
1.  the jsonb_finish_text function, since we have a operator ->> for text
already and the performance for it is OK and there is no cast entry for
jsonb to text.
2.  the jsonb_finish_jsonb since I can't see a clear user case for now.
Rewriting jsonb_object_field with 2 DirectFunctionCall looks not pretty
reasonable as we paid 2 DirectFunctionCall overhead to reduce ~10 lines
code duplication.

An incompatible issue at error message level is found during test:
create table jb(a jsonb);
insert into jb select '{"a": "a"}'::jsonb;
select (a->'a')::int4 from jb;

master:   ERROR:  cannot cast jsonb string to type *integer*
patch:  ERROR:  cannot cast jsonb string to type *numeric*

That's mainly because we first extract the field to numeric and
then cast it to int4 and the error raised at the first step and it
doesn't know the final type.  One way to fix it is adding a 2nd
argument for jsonb_finish_numeric for the real type, but
it looks weird and more suggestions on this would be good.

Performance comparison between v10 and v11.

create table tb (a jsonb);
insert into tb select '{"a": 1}'::jsonb from generate_series(1, 100000)i;
select 1 from tb where (a->'a')::int2 = 2;   (pgbench 5 times)

v11:  16.273 ms
v10:  15.986 ms
master: 32.530ms

So I think the performance would not be an issue.

> Thanks for this information. I tried the  jsonb_xx_start and
jsonb_finish_numeric style, and it looks like a good experience
and it may not make things too complicated even if the above
things happen IMO.

Any feedback is welcome.

-- 
Best Regards
Andy Fan

---

From: Andy Fan <[email-masked]>
Date: Fri, 1 Sep 2023 11:09:22 +0800

> it looks weird and more suggestions on this would be good.
v12 is attached to address the above issue, I added a new argument
named target_oid for jsonb_finish_numeric so that it can raise a
correct error message.  I also fixed the issue reported by opr_sanity
in this version.

Chap, do you still think we should refactor the code for the previous
existing functions like jsonb_object_field for less code duplication
purpose?  I think we should not do it because a). The code duplication
is just ~10 rows.  b).  If we do the refactor, we have to implement
two DirectFunctionCall1.   Point b) is the key reason I am not willing
to do it.  Or do I miss other important reasons?

-- 
Best Regards
Andy Fan

---

From: jian he <[email-masked]>
Date: Sat, 2 Sep 2023 09:25:37 +0800

I think the last patch failed. I am not 100% sure.
https://cirrus-ci.com/task/5464366154252288
says "Created 21 hours ago", I assume the latest patch.

the diff in Artifacts section. you can go to
testrun/build/testrun/regress/regress/regression.diffs

diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/jsonb.out
/tmp/cirrus-ci-build/build/testrun/regress/regress/results/jsonb.out
--- /tmp/cirrus-ci-build/src/test/regress/expected/jsonb.out
2023-09-01 03:34:43.585036700 +0000
+++ /tmp/cirrus-ci-build/build/testrun/regress/regress/results/jsonb.out
2023-09-01 03:39:05.800452844 +0000
@@ -528,7 +528,7 @@
 (3 rows)

 SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type
= 'object';
-ERROR:  cannot cast jsonb string to type integer
+ERROR:  unknown jsonb type: 1125096840
 SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type
= 'object';
 ERROR:  cannot cast jsonb string to type boolean
 \pset null ''

---

From: Andy Fan <[email-masked]>
Date: Mon, 4 Sep 2023 19:43:50 +0800

Hi Jian,

> +ERROR:  unknown jsonb type: 1125096840

Thanks for the report!  The reason is I return the address of a local
variable.

jsonb_object_field_start(PG_FUNCTION_ARGS)
{

    JsonbValue  *v;
    JsonbValue  vbuf;
    v = getKeyJsonValueFromContainer(&jb->root,
                                     VARDATA_ANY(key),\
                                     VARSIZE_ANY_EXHDR(key),
                                     &vbuf);
    PG_RETURN_POINTER(v);
}

Here the v points to vbuf which is a local variable in stack.  I'm confused
that why it works on my local machine and also works in the most queries
in cfbot, the fix is below

    v = getKeyJsonValueFromContainer(&jb->root,
                                     VARDATA_ANY(key),\
                                     VARSIZE_ANY_EXHDR(key),
                                     NULL);

I will send an updated version soon.

-- 
Best Regards
Andy Fan

---

From: Andy Fan <[email-masked]>
Date: Mon, 4 Sep 2023 22:35:16 +0800

Hi,

  v13 attached.  Changes includes:

1.  fix the bug Jian provides.
2.  reduce more code duplication without DirectFunctionCall.
3.  add the overlooked  jsonb_path_query and jsonb_path_query_first as
candidates

--
Best Regards
Andy Fan

---

From: jian he <[email-masked]>
Date: Tue, 5 Sep 2023 20:51:01 +0800

On Mon, Sep 4, 2023 at 10:35 PM Andy Fan <[email-masked]> wrote:
> 3.  add the overlooked  jsonb_path_query and jsonb_path_query_first as candidates
>
>
> --
> Best Regards
> Andy Fan

based on v13.
IMHO, it might be a good idea to write some comments on
jsonb_object_field_internal. especially the second boolean argument.
something like "some case, we just want return JsonbValue rather than
Jsonb. to return JsonbValue, make as_jsonb be false".

I am not sure "jsonb_object_field_start" is a good name, so far I only
come up with "jsonb_object_field_to_jsonbvalues".

linitial(jsonb_start_func->args) =
makeRelabelType(linitial(jsonb_start_func->args),
   INTERNALOID, 0,
   InvalidOid,
   COERCE_IMPLICIT_CAST);

if no need, output typmod (usually -1), so here should be -1 rather than 0?

list_make2(jsonb_start_func, makeConst(.....). you can just combine
two different types then make a list, seems pretty cool...

---

From: Andy Fan <[email-masked]>
Date: Wed, 6 Sep 2023 15:00:00 +0800

> Jsonb. to return JsonbValue, make as_jsonb be false".

OK,  I will proposal  "return a JsonbValue when as_jsonb is false".

> come up with "jsonb_object_field_to_jsonbvalues".

Yes, I think it is a good idea.  Puting the jsonbvalue in the name can
compensate for the imprecision of "internal" as a return type.  I am
thinking
if we should rename jsonb_finish_numeric to jsonbvalue_to_numeric as
well.

> if no need, output typmod (usually -1), so here should be -1 rather than 0?

I agree. -1 is better than 0.

Thanks for the code level review again! I want to wait for some longer time
to gather more feedback.  I'm willing to name it better,  but hope I didn't
rename it to A and rename it back shortly.

-- 
Best Regards
Andy Fan

---

From: Chapman Flack <[email-masked]>
Date: Wed, 13 Sep 2023 17:18:16 -0400

> 3.  add the overlooked  jsonb_path_query and jsonb_path_query_first as

Apologies for the delay. I like the way this is shaping up.

My first comment will be one that may be too large for this patch
(and too large to rest on my opinion alone); that's why I'm making
it first.

It seems at first a minor point: to me it feels like a wart to have
to pass jsonb_finish_numeric (and *only* jsonb_finish_numeric) a type
oid reflecting the target type of a cast that's going to be applied
*after jsonb_finish_numeric has done its work*, and only for the
purpose of generating a message if the jsonb type *isn't numeric*,
but saying "cannot cast to" (that later target type) instead.

I understand this is done to exactly match the existing behavior,
so what makes this a larger issue is I'm not convinced the existing
behavior is good. Therefore I'm not convinced that bending over
backward to preserve it is good.

What's not good: the places a message from cannotCastJsonbValue
are produced, there has been no attempt yet to cast anything.
The message purely tells you about whether you have the kind
of jsonb node you think you have (and array, bool, null, numeric,
object, string are the only kinds of those). If you're wrong
about what kind of jsonb node it is, you get this message.
If you're right about the kind of node, you don't get this
message, regardless of whether its value can be cast to the
later target type. For example, '32768'::jsonb::int2 produces
ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE "smallint out of range"
but that message comes from the actual int2 cast.

IMV, what the "cannot cast jsonb foo to type %s" message really
means is "jsonb foo where jsonb bar is required" and that's what
it should say, and that message depends on nothing about any
future plans for what will be done to the jsonb bar, so it can
be produced without needing any extra information to be passed.

I'm also not convinced ERRCODE_INVALID_PARAMETER_VALUE is a
good errcode for that message (whatever the wording). I do not
see much precedent elsewhere in the code for using
INVALID_PARAMETER_VALUE to signal this kind of "data value
isn't what you think it is" condition. Mostly it is used
when checking the kinds of parameters passed to a function to
indicate what it should do.

There seem to be several more likely choices for an errcode
there in the 2203x range.

But I understand that issue is not with this patch so much
as with preexisting behavior, and because it's preexisting,
there can be sound arguments against changing it.

But if that preexisting message could be changed, it would
eliminate the need for an unpleasing wart here.

Other notes are more minor:

+		else
+			/* not the desired pattern. */
+			PG_RETURN_POINTER(fexpr);
...
+
+		if (!OidIsValid(new_func_id))
+			PG_RETURN_POINTER(fexpr);
...
+			default:
+				PG_RETURN_POINTER(fexpr);

If I am reading supportnodes.h right, returning NULL is
sufficient to say no transformation is needed.

+		FuncExpr	*fexpr = palloc0(sizeof(FuncExpr));
...
+		memcpy(fexpr, req->fcall, sizeof(FuncExpr));

Is the shallow copy necessary? If so, a comment explaining why
might be apropos. Because the copy is shallow, if there is any
concern about the lifespan of req->fcall, would there not be a
concern about its children?

Is there a reason not to transform the _tz flavors of
jsonb_path_query and jsonb_path-query_first?

-	JsonbValue *v;
-	JsonbValue	vbuf;
+	JsonbValue	*v;
...
+	int i;
  	JsonbValue *jbvp = NULL;
-	int			i;

Sometimes it's worth looking over a patch for changes like these,
and reverting such whitespace or position changes, if they aren't
things you want a reviewer to be squinting at. :)

Regards,
-Chap

---

From: Andy Fan <[email-masked]>
Date: Thu, 14 Sep 2023 14:41:36 +0800

> Apologies for the delay. I like the way this is shaping up.

This is a great signal:)

> backward to preserve it is good.

I hesitated to do so, but I'm thinking if any postgresql user uses
some code like   if (errMsg.equals('old-error-message')),  and if we
change the error message, the application will be broken. I agree
with the place for the error message,  IIUC,  you intend to choose
not compatible with the old error message?

> sufficient to say no transformation is needed.

I double confirmed you are right here.
Changed it to PG_RETURN_POINT(null);   here in the next version.

> concern about its children?

All the interesting parts in the input FuncExpr are heap based,
but the FuncExpr itself is stack based (I'm not sure why the fact
works like this),  Also based on my previously understanding, I
need to return a FuncExpr original even if the function can't be
simplified, so I made a shallow copy.  There will be no copy at
all in the following version since I returned NULL in such a case.

> jsonb_path_query and jsonb_path-query_first?

I misunderstood the _tz flavors return timestamp,  after some deep
reading of these functions, they just work at the comparisons part.
so I will add them in the following version.

> things you want a reviewer to be squinting at. :)

Yes, I  look over my patch carefully before sending it out usually,
but this is an oversight.

Lastly,  do you have any idea about the function naming as Jian & I
discussed at [1]?

[1]
https://www.postgresql.org/message-id/CAKU4AWqQ0hed%3DZmpT%2B7Vxnp4H9ZxQqFz30%3Dk%3DvvrmNe57X4dKQ%40mail.gmail.com

-- 
Best Regards
Andy Fan

---

From: Andy Fan <[email-masked]>
Date: Fri, 15 Sep 2023 09:53:20 +0800

> so I will add them in the following version.

_tz favors did return timestamp..  the reason is stated in the commit
 messge of patch 2.

try to apply jsonb extraction optimization to  _tz functions.

both jsonb_path_query_tz and jsonb_path_query_tz_first returns
the elements which are timestamp comparable, but such elements
are impossible to be cast to numeric or boolean IIUC. Just provides
this commit for communication purpose only.

so v14 is attached, changes include:
1. Change the typmod for internal type from 0 to -1.
2. return NULL for non-simplify expressions from the planner
support function, hence shallow copy is removed as well.

Things are not addressed yet:
1.  the error message handling.
2.  if we have chances to optimize _tz functions, I guess no.
3.  function naming issue. I think I can get it modified once after
all the other issues are addressed.

-- 
Best Regards
Andy Fan

---

From: Andy Fan <[email-masked]>
Date: Thu, 5 Oct 2023 14:53:05 +0800

Hi,

I am feeling this topic has been well discussed and the only pending
issues are below,  it would be great that any committer can have a
look at these,  so I mark this entry as "Ready for Committer".

> 1.  the error message handling.

You can check [1] for more background of this,  I think blocking this
feature at an error message level is not pretty reasonable.

> 2.  if we have chances to optimize _tz functions, I guess no.

patch 002 is dedicated  for this,  I think it should not be committed,
the reason is described in the commit message.

> all the other issues are addressed.
[1]
https://www.postgresql.org/message-id/d70280648894e56f9f0d12c75090c3d8%40anastigmatix.net

-- 
Best Regards
Andy Fan

---

From: Chapman Flack <[email-masked]>
Date: Wed, 01 Nov 2023 02:17:24 +0000

Adding this comment via the CF app so it isn't lost, while an improperly-interpreted-DKIM-headers issue is still preventing me from mailing directly to -hackers.

It was my view that the patch was getting close by the end of the last commitfest, but still contained a bit of a logic wart made necessary by a questionable choice of error message wording, such that in my view it would be better to determine whether a different error message would better conform to ISO SQL in the first place, and obviate the need for the logic wart.

There seemed to be some progress possible on that when petere had time to weigh in on the standard shortly after the last CF ended.

So, it would not have been my choice to assign RfC status before getting to a resolution on that.

Also, it is possible for a JsonbValue to hold a timestamp (as a result of a jsonpath evaluation, I don't think that can happen any other way), and if such a jsonpath evaluation were to be the source expression of a cast to SQL timestamp, that situation seems exactly analogous to the other situations being optimized here and would require only a few more lines in the exact pattern here introduced. While that could be called out of scope when this patch's title refers to "numeric field" specifically, it might be worth considering for completeness. The patch does, after all, handle boolean already, as well as numeric.

---

From: [email-masked]
Date: Mon, 06 Nov 2023 11:26:28 +0800

Chapman Flack <[email-masked]> writes:

(This is Andy Fan and I just switch to my new email address).

Hi Chap,

Thanks for alway keep an eye on this!

> Adding this comment via the CF app so it isn't lost, while an
> improperly-interpreted-DKIM-headers issue is still preventing me from
> mailing directly to -hackers.
>
> It was my view that the patch was getting close by the end of the last
> commitfest, but still contained a bit of a logic wart made necessary by
> a questionable choice of error message wording, such that in my view it
> would be better to determine whether a different error message would
> better conform to ISO SQL in the first place, and obviate the need for
> the logic wart.
>
> There seemed to be some progress possible on that when petere had time
> to weigh in on the standard shortly after the last CF ended.
>

I agree with this.

>
> Also, it is possible for a JsonbValue to hold a timestamp (as a result
> of a jsonpath evaluation, I don't think that can happen any other
> way),

I believe this is where our disagreement lies.

CREATE TABLE employees (                                                                                                                          
   id serial PRIMARY KEY,
   data jsonb
);

INSERT INTO employees (data) VALUES (
   '{
      "employees":[
         {
            "firstName":"John",
            "lastName":"Doe",
            "hireDate":"2022-01-01T09:00:00Z",
            "age": 30
         },
         {
            "firstName":"Jane",
            "lastName":"Smith",
            "hireDate":"2022-02-01T10:00:00Z",
            "age": 25
         }
      ]
   }'
);

select
jsonb_path_query_tz(data, '$.employees[*] ? (@.hireDate >=
"2022-02-01T00:00:00Z" && @.hireDate < "2022-03-01T00:00:00Z")')
from employees;

select jsonb_path_query_tz(data, '$.employees[*].hireDate ? (@ >=
"2022-02-01T00:00:00Z" && @ < "2022-03-01T00:00:00Z")') from employees;
select pg_typeof(jsonb_path_query_tz(data, '$.employees[*].hireDate ? (@
>= "2022-02-01T00:00:00Z" && @ < "2022-03-01T00:00:00Z")')) from
employees;

select jsonb_path_query_tz(data, '$.employees[*].hireDate ? (@
>= "2022-02-01T00:00:00Z" && @ < "2022-03-01T00:00:00Z")')::timestamp
from employees;
select jsonb_path_query_tz(data, '$.employees[*].hireDate ? (@
>= "2022-02-01T00:00:00Z" && @ < "2022-03-01T00:00:00Z")')::timestamptz
from employees;

I tried all of the above queires and can't find a place where this
optimization would apply. am I miss something? 

> and if such a jsonpath evaluation were to be the source expression of a
> cast to SQL timestamp, that situation seems exactly analogous to the
> other situations being optimized here and would require only a few more
> lines in the exact pattern here introduced.

Could you provide an example of this? 

> While that could be called
> out of scope when this patch's title refers to "numeric field"
> specifically, it might be worth considering for completeness. The patch
> does, after all, handle boolean already, as well as numeric.

I'd never arugment for this, at this point at least. 

v15 is provides without any fundamental changes.  Just rebase to the
lastest code and prepared a better commit message.

>From 4d53fda4974fa18827350a5f42482f0eec29d6ba Mon Sep 17 00:00:00 2001
From: "yizhi.fzh" <[email-masked]>
Date: Mon, 6 Nov 2023 11:09:14 +0800
Subject: [PATCH v15 1/1] Improve the performance of Jsonb extraction.

In the past, when we needed to extract a numeric value from a field in a
JSONB object, even though the JSONB object already contained a binary
matching numeric type, we would first find the corresponding JsonbValue,
then convert the JsonbValue to Jsonb, and finally use the cast system to
convert the Jsonb to a Numeric-like data type. This approach was very
inefficient in terms of performance.

In the current patch, if we encounter a function or operator that needs
to extract a JSONB field and cast it to a numeric-like data type, the
request will be automatically converted into extracting the field as a
JsonbValue data type from the JSONB field, then, convert the JsonbValue
to a numeric data type. If necessary, the final conversion from the
numeric data type to another numeric-like data type is done through the
casting system. This series of conversions is implemented through the
planner support function.  By utilizing these methods, the cumbersome
JSONB-related operations are avoided. Because the boolean type and
numeric type share certain similarities in their attributes, we have
implemented the same optimization approach for both.

At the implementation level, considering that we have multiple operators
and various target data types, and to avoid an excessive number of
functions, we have deconstructed the two steps mentioned earlier into
two categories of functions. The first category of functions extracts
the data as a JsonbValue type, while the second category of functions
converts the JsonbValue type into the desired data type. In specific
scenarios, we utilize planner support functions to automatically
assemble these functions.

The optimized functions and operators includes:
1. jsonb_object_field / ->
2. jsonb_array_element / ->
3. jsonb_extract_path / #>
4. jsonb_path_query
5. jsonb_path_query_first
---
 src/backend/utils/adt/jsonb.c         | 166 ++++++++++++++++++++++++++
 src/backend/utils/adt/jsonbsubs.c     |   4 +-
 src/backend/utils/adt/jsonfuncs.c     | 118 ++++++++++++------
 src/backend/utils/adt/jsonpath_exec.c |  32 +++--
 src/include/catalog/catversion.h      |   2 +-
 src/include/catalog/pg_proc.dat       |  46 +++++--
 src/include/utils/jsonb.h             |  11 +-
 src/test/regress/expected/jsonb.out   | 112 ++++++++++++++++-
 src/test/regress/sql/jsonb.sql        |  66 +++++++++-
 src/tools/pgindent/typedefs.list      |   1 +
 10 files changed, 498 insertions(+), 60 deletions(-)

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 6f445f5c2b..685f4e3e6b 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -17,11 +17,15 @@
 #include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
 #include "funcapi.h"
+#include "nodes/makefuncs.h"
+#include "nodes/supportnodes.h"
+#include "parser/parse_coerce.h"
 #include "libpq/pqformat.h"
 #include "miscadmin.h"
 #include "utils/builtins.h"
 #include "utils/date.h"
 #include "utils/datetime.h"
+#include "utils/fmgroids.h"
 #include "utils/json.h"
 #include "utils/jsonb.h"
 #include "utils/jsonfuncs.h"
@@ -2039,6 +2043,168 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype)
 	elog(ERROR, "unknown jsonb type: %d", (int) type);
 }
 
+
+/*
+ * jsonb_cast_support()
+ *
+ * Planner support function for casting a jsonb extraction to a numeric
+ * or bool data type. Instead of converting a jsonbvalue to jsonb, the new
+ * method will cast the jsonbvalue to the desired data type directly.
+ */
+Datum
+jsonb_cast_support(PG_FUNCTION_ARGS)
+{
+	Node	   *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+	if (IsA(rawreq, SupportRequestSimplify))
+	{
+		SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+		FuncExpr   *fexpr = req->fcall;
+		FuncExpr   *jsonb_start_func = NULL,
+				   *jsonb_finish_func = NULL,
+				   *final_func = NULL;
+		Node	   *input;
+		Oid			new_func_id = InvalidOid;
+		List	   *args;
+		Oid			input_func_id,
+					collid,
+					inputcollid;
+		bool		retset = false,
+					variadic = false;
+
+		Assert(list_length(fexpr->args) == 1);
+		input = (Node *) linitial(fexpr->args);
+
+		if (IsA(input, OpExpr))
+		{
+			OpExpr	   *opExpr = castNode(OpExpr, input);
+
+			input_func_id = opExpr->opfuncid;
+			collid = opExpr->opcollid;
+			inputcollid = opExpr->inputcollid;
+			args = opExpr->args;
+		}
+		else if (IsA(input, FuncExpr))
+		{
+			FuncExpr   *funcExpr = castNode(FuncExpr, input);
+
+			input_func_id = funcExpr->funcid;
+			collid = funcExpr->funccollid;
+			inputcollid = funcExpr->inputcollid;
+			args = funcExpr->args;
+		}
+		else
+			/* not the desired pattern. */
+			PG_RETURN_POINTER(NULL);
+
+		/* build a function to return the JsonbValue directly. */
+		switch (input_func_id)
+		{
+			case F_JSONB_OBJECT_FIELD:
+				new_func_id = F_JSONB_OBJECT_FIELD_START;
+				break;
+			case F_JSONB_ARRAY_ELEMENT:
+				new_func_id = F_JSONB_ARRAY_ELEMENT_START;
+				break;
+			case F_JSONB_EXTRACT_PATH:
+				new_func_id = F_JSONB_EXTRACT_PATH_START;
+				variadic = true;
+				break;
+			case F_JSONB_PATH_QUERY:
+				new_func_id = F_JSONB_PATH_QUERY_START;
+				retset = true;
+				break;
+			case F_JSONB_PATH_QUERY_FIRST:
+				new_func_id = F_JSONB_PATH_QUERY_FIRST_START;
+				break;
+			default:
+				new_func_id = InvalidOid;
+				break;
+		}
+
+		if (!OidIsValid(new_func_id))
+			PG_RETURN_POINTER(NULL);
+
+		jsonb_start_func = makeFuncExpr(new_func_id, INTERNALOID, args,
+										collid, inputcollid,
+										COERCE_EXPLICIT_CALL);
+		jsonb_start_func->funcretset = retset;
+		jsonb_start_func->funcvariadic = variadic;
+
+		/* relabel the first argument as 'internal'. */
+		linitial(jsonb_start_func->args) = makeRelabelType(linitial(jsonb_start_func->args),
+														   INTERNALOID, -1,
+														   InvalidOid,
+														   COERCE_IMPLICIT_CAST);
+		switch (fexpr->funcresulttype)
+		{
+			case INT2OID:
+			case INT4OID:
+			case INT8OID:
+			case FLOAT4OID:
+			case FLOAT8OID:
+			case NUMERICOID:
+				/* build the function to turn the JsonbValue into numeric */
+				jsonb_finish_func = makeFuncExpr(F_JSONB_FINISH_NUMERIC, NUMERICOID,
+												 list_make2(jsonb_start_func,
+															makeConst(OIDOID,
+																	  -1,
+																	  InvalidOid,
+																	  sizeof(Oid),
+																	  ObjectIdGetDatum(fexpr->funcresulttype),
+																	  false,
+																	  true)),
+												 collid, inputcollid, COERCE_EXPLICIT_CALL);
+
+				if (fexpr->funcresulttype != NUMERICOID)
+				{
+					/*
+					 * leverage the casting system to turn the numeric to
+					 * desired type.
+					 */
+					final_func = (FuncExpr *) coerce_type(NULL, (Node *) jsonb_finish_func, NUMERICOID,
+														  fexpr->funcresulttype, 0, COERCION_EXPLICIT,
+														  COERCE_EXPLICIT_CAST, fexpr->location);
+				}
+				else
+					final_func = jsonb_finish_func;
+
+				PG_RETURN_POINTER(final_func);
+			case BOOLOID:
+				final_func = makeFuncExpr(F_JSONB_FINISH_BOOL, BOOLOID,
+										  list_make1(jsonb_start_func), collid,
+										  inputcollid, COERCE_EXPLICIT_CALL);
+				PG_RETURN_POINTER(final_func);
+			default:
+				PG_RETURN_POINTER(NULL);
+		}
+	}
+
+	PG_RETURN_POINTER(NULL);
+}
+
+
+Datum
+jsonb_finish_numeric(PG_FUNCTION_ARGS)
+{
+	JsonbValue *v = (JsonbValue *) PG_GETARG_POINTER(0);
+	Oid			final_oid = PG_GETARG_OID(1);
+
+	if (v->type != jbvNumeric)
+		cannotCastJsonbValue(v->type, format_type_be(final_oid));
+	PG_RETURN_NUMERIC(v->val.numeric);
+}
+
+Datum
+jsonb_finish_bool(PG_FUNCTION_ARGS)
+{
+	JsonbValue *v = (JsonbValue *) PG_GETARG_POINTER(0);
+
+	if (v->type != jbvBool)
+		cannotCastJsonbValue(v->type, "boolean");
+	PG_RETURN_BOOL(v->val.boolean);
+}
+
 Datum
 jsonb_bool(PG_FUNCTION_ARGS)
 {
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index de0ae3604f..cb050c65ef 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -251,7 +251,7 @@ jsonb_subscript_fetch(ExprState *state,
 									  workspace->index,
 									  sbsrefstate->numupper,
 									  op->resnull,
-									  false);
+									  JsonbValue_AsJsonb);
 }
 
 /*
@@ -343,7 +343,7 @@ jsonb_subscript_fetch_old(ExprState *state,
 												   sbsrefstate->upperindex,
 												   sbsrefstate->numupper,
 												   &sbsrefstate->prevnull,
-												   false);
+												   JsonbValue_AsJsonb);
 	}
 }
 
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index aa37c401e5..4e73c0f5c7 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -357,7 +357,7 @@ static JsonParseErrorType get_scalar(void *state, char *token, JsonTokenType tok
 static Datum get_path_all(FunctionCallInfo fcinfo, bool as_text);
 static text *get_worker(text *json, char **tpath, int *ipath, int npath,
 						bool normalize_results);
-static Datum get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text);
+static Datum get_jsonb_path_all(FunctionCallInfo fcinfo, JsonbValueTarget target);
 static text *JsonbValueAsText(JsonbValue *v);
 
 /* semantic action functions for json_array_length */
@@ -492,6 +492,20 @@ static JsonParseErrorType transform_string_values_object_field_start(void *state
 static JsonParseErrorType transform_string_values_array_element_start(void *state, bool isnull);
 static JsonParseErrorType transform_string_values_scalar(void *state, char *token, JsonTokenType tokentype);
 
+Datum
+jsonbvalue_covert(JsonbValue *jbv, JsonbValueTarget target)
+{
+	switch (target)
+	{
+		case JsonbValue_AsJsonbValue:
+			PG_RETURN_POINTER(jbv);
+		case JsonbValue_AsJsonb:
+			PG_RETURN_JSONB_P(JsonbValueToJsonb(jbv));
+		case JsonbValue_AsText:
+			PG_RETURN_TEXT_P(JsonbValueAsText(jbv));
+	}
+	PG_RETURN_POINTER(NULL);
+}
 
 /*
  * pg_parse_json_or_errsave
@@ -847,13 +861,12 @@ json_object_field(PG_FUNCTION_ARGS)
 		PG_RETURN_NULL();
 }
 
-Datum
-jsonb_object_field(PG_FUNCTION_ARGS)
+static Datum
+jsonb_object_field_internal(FunctionCallInfo fcinfo, JsonbValueTarget target)
 {
 	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
 	text	   *key = PG_GETARG_TEXT_PP(1);
 	JsonbValue *v;
-	JsonbValue	vbuf;
 
 	if (!JB_ROOT_IS_OBJECT(jb))
 		PG_RETURN_NULL();
@@ -861,14 +874,26 @@ jsonb_object_field(PG_FUNCTION_ARGS)
 	v = getKeyJsonValueFromContainer(&jb->root,
 									 VARDATA_ANY(key),
 									 VARSIZE_ANY_EXHDR(key),
-									 &vbuf);
+									 NULL);
 
 	if (v != NULL)
-		PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
+		return jsonbvalue_covert(v, target);
 
 	PG_RETURN_NULL();
 }
 
+Datum
+jsonb_object_field(PG_FUNCTION_ARGS)
+{
+	return jsonb_object_field_internal(fcinfo, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_object_field_start(PG_FUNCTION_ARGS)
+{
+	return jsonb_object_field_internal(fcinfo, JsonbValue_AsJsonbValue);
+}
+
 Datum
 json_object_field_text(PG_FUNCTION_ARGS)
 {
@@ -922,8 +947,8 @@ json_array_element(PG_FUNCTION_ARGS)
 		PG_RETURN_NULL();
 }
 
-Datum
-jsonb_array_element(PG_FUNCTION_ARGS)
+static Datum
+jsonb_array_element_internal(FunctionCallInfo fcinfo, JsonbValueTarget target)
 {
 	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
 	int			element = PG_GETARG_INT32(1);
@@ -945,11 +970,23 @@ jsonb_array_element(PG_FUNCTION_ARGS)
 
 	v = getIthJsonbValueFromContainer(&jb->root, element);
 	if (v != NULL)
-		PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
+		return jsonbvalue_covert(v, target);
 
 	PG_RETURN_NULL();
 }
 
+Datum
+jsonb_array_element(PG_FUNCTION_ARGS)
+{
+	return jsonb_array_element_internal(fcinfo, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_array_element_start(PG_FUNCTION_ARGS)
+{
+	return jsonb_array_element_internal(fcinfo, JsonbValue_AsJsonbValue);
+}
+
 Datum
 json_array_element_text(PG_FUNCTION_ARGS)
 {
@@ -1476,17 +1513,23 @@ get_scalar(void *state, char *token, JsonTokenType tokentype)
 Datum
 jsonb_extract_path(PG_FUNCTION_ARGS)
 {
-	return get_jsonb_path_all(fcinfo, false);
+	return get_jsonb_path_all(fcinfo, JsonbValue_AsJsonb);
 }
 
 Datum
 jsonb_extract_path_text(PG_FUNCTION_ARGS)
 {
-	return get_jsonb_path_all(fcinfo, true);
+	return get_jsonb_path_all(fcinfo, JsonbValue_AsText);
+}
+
+Datum
+jsonb_extract_path_start(PG_FUNCTION_ARGS)
+{
+	return get_jsonb_path_all(fcinfo, JsonbValue_AsJsonbValue);
 }
 
 static Datum
-get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
+get_jsonb_path_all(FunctionCallInfo fcinfo, JsonbValueTarget target)
 {
 	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
 	ArrayType  *path = PG_GETARG_ARRAYTYPE_P(1);
@@ -1508,7 +1551,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
 
 	deconstruct_array_builtin(path, TEXTOID, &pathtext, &pathnulls, &npath);
 
-	res = jsonb_get_element(jb, pathtext, npath, &isnull, as_text);
+	res = jsonb_get_element(jb, pathtext, npath, &isnull, target);
 
 	if (isnull)
 		PG_RETURN_NULL();
@@ -1517,7 +1560,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
 }
 
 Datum
-jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
+jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, JsonbValueTarget target)
 {
 	JsonbContainer *container = &jb->root;
 	JsonbValue *jbvp = NULL;
@@ -1550,16 +1593,26 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
 	 */
 	if (npath <= 0 && jbvp == NULL)
 	{
-		if (as_text)
-		{
-			return PointerGetDatum(cstring_to_text(JsonbToCString(NULL,
-																  container,
-																  VARSIZE(jb))));
-		}
-		else
+		switch (target)
 		{
-			/* not text mode - just hand back the jsonb */
-			PG_RETURN_JSONB_P(jb);
+			case JsonbValue_AsText:
+				return PointerGetDatum(cstring_to_text(JsonbToCString(NULL,
+																	  container,
+																	  VARSIZE(jb))));
+				/* not text mode - just hand back the jsonb */
+			case JsonbValue_AsJsonb:
+				PG_RETURN_JSONB_P(jb);
+			case JsonbValue_AsJsonbValue:
+				{
+					JsonbValue *jbv = NULL;
+
+					if (JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb))
+						PG_RETURN_POINTER(getIthJsonbValueFromContainer(container, 0));
+
+					jbv = palloc0(sizeof(JsonbValue));
+					JsonbToJsonbValue(jb, jbv);
+					PG_RETURN_POINTER(jbv);
+				}
 		}
 	}
 
@@ -1645,23 +1698,14 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
 		}
 	}
 
-	if (as_text)
-	{
-		if (jbvp->type == jbvNull)
-		{
-			*isnull = true;
-			return PointerGetDatum(NULL);
-		}
 
-		return PointerGetDatum(JsonbValueAsText(jbvp));
-	}
-	else
+	if (target == JsonbValue_AsText && jbvp->type == jbvNull)
 	{
-		Jsonb	   *res = JsonbValueToJsonb(jbvp);
-
-		/* not text mode - just hand back the jsonb */
-		PG_RETURN_JSONB_P(res);
+		*isnull = true;
+		return PointerGetDatum(NULL);
 	}
+
+	return jsonbvalue_covert(jbvp, target);
 }
 
 Datum
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 2d0599b4aa..03045c7ba9 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -393,7 +393,7 @@ jsonb_path_match_opr(PG_FUNCTION_ARGS)
  *		rowset.
  */
 static Datum
-jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz)
+jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz, JsonbValueTarget target)
 {
 	FuncCallContext *funcctx;
 	List	   *found;
@@ -435,19 +435,25 @@ jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz)
 	v = lfirst(c);
 	funcctx->user_fctx = list_delete_first(found);
 
-	SRF_RETURN_NEXT(funcctx, JsonbPGetDatum(JsonbValueToJsonb(v)));
+	SRF_RETURN_NEXT(funcctx, jsonbvalue_covert(v, target));
 }
 
 Datum
 jsonb_path_query(PG_FUNCTION_ARGS)
 {
-	return jsonb_path_query_internal(fcinfo, false);
+	return jsonb_path_query_internal(fcinfo, false, JsonbValue_AsJsonb);
 }
 
 Datum
 jsonb_path_query_tz(PG_FUNCTION_ARGS)
 {
-	return jsonb_path_query_internal(fcinfo, true);
+	return jsonb_path_query_internal(fcinfo, true, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_path_query_start(PG_FUNCTION_ARGS)
+{
+	return jsonb_path_query_internal(fcinfo, false, JsonbValue_AsJsonbValue);
 }
 
 /*
@@ -487,7 +493,7 @@ jsonb_path_query_array_tz(PG_FUNCTION_ARGS)
  *		item.  If there are no items, NULL returned.
  */
 static Datum
-jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
+jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz, JsonbValueTarget target)
 {
 	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
 	JsonPath   *jp = PG_GETARG_JSONPATH_P(1);
@@ -498,7 +504,11 @@ jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
 	(void) executeJsonPath(jp, vars, jb, !silent, &found, tz);
 
 	if (JsonValueListLength(&found) >= 1)
-		PG_RETURN_JSONB_P(JsonbValueToJsonb(JsonValueListHead(&found)));
+	{
+		JsonbValue *jbv = JsonValueListHead(&found);
+
+		return jsonbvalue_covert(jbv, target);
+	}
 	else
 		PG_RETURN_NULL();
 }
@@ -506,13 +516,19 @@ jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
 Datum
 jsonb_path_query_first(PG_FUNCTION_ARGS)
 {
-	return jsonb_path_query_first_internal(fcinfo, false);
+	return jsonb_path_query_first_internal(fcinfo, false, JsonbValue_AsJsonb);
 }
 
 Datum
 jsonb_path_query_first_tz(PG_FUNCTION_ARGS)
 {
-	return jsonb_path_query_first_internal(fcinfo, true);
+	return jsonb_path_query_first_internal(fcinfo, true, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_path_query_first_start(PG_FUNCTION_ARGS)
+{
+	return jsonb_path_query_first_internal(fcinfo, false, JsonbValue_AsJsonbValue);
 }
 
 /********************Execute functions for JsonPath**************************/
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index bee21befda..802c6f1925 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202310301
+#define CATALOG_VERSION_NO	202311061
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 091f7e343c..6934d0894d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4590,25 +4590,25 @@
   proname => 'pg_lsn', prorettype => 'pg_lsn', proargtypes => 'numeric',
   prosrc => 'numeric_pg_lsn' },
 
-{ oid => '3556', descr => 'convert jsonb to boolean',
+{ oid => '3556', descr => 'convert jsonb to boolean', prosupport => 'jsonb_cast_support',
   proname => 'bool', prorettype => 'bool', proargtypes => 'jsonb',
   prosrc => 'jsonb_bool' },
 { oid => '3449', descr => 'convert jsonb to numeric',
-  proname => 'numeric', prorettype => 'numeric', proargtypes => 'jsonb',
+  proname => 'numeric', prorettype => 'numeric', proargtypes => 'jsonb', prosupport => 'jsonb_cast_support',
   prosrc => 'jsonb_numeric' },
-{ oid => '3450', descr => 'convert jsonb to int2',
+{ oid => '3450', descr => 'convert jsonb to int2', prosupport => 'jsonb_cast_support',
   proname => 'int2', prorettype => 'int2', proargtypes => 'jsonb',
   prosrc => 'jsonb_int2' },
-{ oid => '3451', descr => 'convert jsonb to int4',
+{ oid => '3451', descr => 'convert jsonb to int4', prosupport => 'jsonb_cast_support',
   proname => 'int4', prorettype => 'int4', proargtypes => 'jsonb',
   prosrc => 'jsonb_int4' },
-{ oid => '3452', descr => 'convert jsonb to int8',
+{ oid => '3452', descr => 'convert jsonb to int8', prosupport => 'jsonb_cast_support',
   proname => 'int8', prorettype => 'int8', proargtypes => 'jsonb',
   prosrc => 'jsonb_int8' },
-{ oid => '3453', descr => 'convert jsonb to float4',
+{ oid => '3453', descr => 'convert jsonb to float4', prosupport => 'jsonb_cast_support',
   proname => 'float4', prorettype => 'float4', proargtypes => 'jsonb',
   prosrc => 'jsonb_float4' },
-{ oid => '2580', descr => 'convert jsonb to float8',
+{ oid => '2580', descr => 'convert jsonb to float8', prosupport => 'jsonb_cast_support',
   proname => 'float8', prorettype => 'float8', proargtypes => 'jsonb',
   prosrc => 'jsonb_float8' },
 
@@ -9951,6 +9951,30 @@
   proname => 'jsonb_object_field_text', prorettype => 'text',
   proargtypes => 'jsonb text', proargnames => '{from_json, field_name}',
   prosrc => 'jsonb_object_field_text' },
+{ oid => '4552', descr => 'extract jsonbvalue from jsonb for the given field',
+  proname => 'jsonb_object_field_start', prorettype => 'internal',
+  proargtypes => 'internal text', proargnames => '{from_json, field_name}',
+  prosrc => 'jsonb_object_field_start' },
+{ oid => '3813', descr => 'extract josnbvalue from jsonb array for the given index',
+  proname => 'jsonb_array_element_start', prorettype => 'internal',
+  proargtypes => 'internal int4', proargnames => '{from_jsonb, element_index}',
+  prosrc => 'jsonb_array_element_start' },
+{ oid => '4551', descr => 'extract jsonbvalue from jsonb for the given paths',
+  proname => 'jsonb_extract_path_start', provariadic => 'text', prorettype => 'internal',
+  proargtypes => 'internal _text', proallargtypes => '{internal,_text}',
+  proargmodes => '{i,v}', proargnames => '{from_jsonb,path_elems}',
+  prosrc => 'jsonb_extract_path_start'},
+{ oid => '4553', descr => 'convert a jsonbvalue to numeric',
+  proname => 'jsonb_finish_numeric', prorettype => 'numeric',
+  proargtypes => 'internal oid', proargnames => '{from_jsonvalue,target_oid}',
+  prosrc => 'jsonb_finish_numeric' },
+{ oid => '4554', descr => 'convert a jsonbvalue to boolean',
+  proname => 'jsonb_finish_bool', prorettype => 'bool',
+  proargtypes => 'internal', proargnames => '{jsonvalue}',
+  prosrc => 'jsonb_finish_bool' },
+{ oid => '3814', descr => 'planner support for numeric(jsonb)',
+  proname => 'jsonb_cast_support', prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'jsonb_cast_support' },
 { oid => '3215',
   proname => 'jsonb_array_element', prorettype => 'jsonb',
   proargtypes => 'jsonb int4', proargnames => '{from_json, element_index}',
@@ -10143,6 +10167,10 @@
   proname => 'jsonb_path_query', prorows => '1000', proretset => 't',
   prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
   prosrc => 'jsonb_path_query' },
+{ oid => '4557', descr => 'jsonpath query as jsonbvalue',
+  proname => 'jsonb_path_query_start', prorows => '1000', proretset => 't',
+  prorettype => 'internal', proargtypes => 'internal jsonpath jsonb bool',
+  prosrc => 'jsonb_path_query_start' },
 { oid => '4007', descr => 'jsonpath query wrapped into array',
   proname => 'jsonb_path_query_array', prorettype => 'jsonb',
   proargtypes => 'jsonb jsonpath jsonb bool',
@@ -10151,6 +10179,10 @@
   proname => 'jsonb_path_query_first', prorettype => 'jsonb',
   proargtypes => 'jsonb jsonpath jsonb bool',
   prosrc => 'jsonb_path_query_first' },
+{ oid => '4555', descr => 'jsonpath query first item as jsonbvalue',
+  proname => 'jsonb_path_query_first_start', prorettype => 'internal',
+  proargtypes => 'internal jsonpath jsonb bool',
+  prosrc => 'jsonb_path_query_first_start' },
 { oid => '4009', descr => 'jsonpath match',
   proname => 'jsonb_path_match', prorettype => 'bool',
   proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match' },
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index addc9b608e..5e0014f040 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -294,6 +294,13 @@ struct JsonbValue
 	}			val;
 };
 
+typedef enum JsonbValueTarget
+{
+	JsonbValue_AsJsonbValue,
+	JsonbValue_AsJsonb,
+	JsonbValue_AsText
+} JsonbValueTarget;
+
 #define IsAJsonbScalar(jsonbval)	(((jsonbval)->type >= jbvNull && \
 									  (jsonbval)->type <= jbvBool) || \
 									  (jsonbval)->type == jbvDatetime)
@@ -428,12 +435,12 @@ extern const char *JsonbTypeName(JsonbValue *val);
 extern Datum jsonb_set_element(Jsonb *jb, Datum *path, int path_len,
 							   JsonbValue *newval);
 extern Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath,
-							   bool *isnull, bool as_text);
+							   bool *isnull, JsonbValueTarget target);
 extern bool to_jsonb_is_immutable(Oid typoid);
 extern Datum jsonb_build_object_worker(int nargs, const Datum *args, const bool *nulls,
 									   const Oid *types, bool absent_on_null,
 									   bool unique_keys);
 extern Datum jsonb_build_array_worker(int nargs, const Datum *args, const bool *nulls,
 									  const Oid *types, bool absent_on_null);
-
+extern Datum jsonbvalue_covert(JsonbValue *jbv, JsonbValueTarget target);
 #endif							/* __JSONB_H__ */
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4a16d0dbaf..c6af7a59cc 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -457,8 +457,114 @@ CREATE TEMP TABLE test_jsonb (
 );
 INSERT INTO test_jsonb VALUES
 ('scalar','"a scalar"'),
+('scalarint','2'),
 ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'),
-('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}');
+('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}, "field7": true, "field8": [1,2,3,4,5]}');
+\pset null NULL
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json->'field5' -> 0)::numeric,
+(test_json->'field5' -> 10)::numeric,
+(test_json#>'{"field6", "f1"}')::numeric,
+(test_json#>'{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '1700'::oid), (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '21'::oid))::smallint, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '23'::oid))::integer, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '20'::oid))::bigint, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '700'::oid))::real, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '701'::oid))::double precision, jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal, 0), '1700'::oid), jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal, 10), '1700'::oid), jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field6,f1}'::text[]), '1700'::oid), jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field6,f2}'::text[]), '1700'::oid), jsonb_finish_bool(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field7}'::text[]))
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json -> 'field5' -> 0)::numeric,
+(test_json -> 'field5' -> 10)::numeric,
+(test_json #> '{"field6", "f1"}')::numeric,
+(test_json #> '{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+ numeric | int2 | int4 | int8 | float4 | float8 | numeric | numeric | numeric | numeric | bool 
+---------+------+------+------+--------+--------+---------+---------+---------+---------+------
+       4 |    4 |    4 |    4 |      4 |      4 |       1 |    NULL |       9 |    NULL | t
+(1 row)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+                                                                                                                                                                                                                                                                                                                             QUERY PLAN                                                                                                                                                                                                                                                                                                                             
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Result
+   Output: (jsonb_path_query(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)), jsonb_path_query_first(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), (jsonb_finish_numeric((jsonb_path_query_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)), '21'::oid))::smallint, (jsonb_finish_numeric(jsonb_path_query_first_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), '21'::oid))::smallint
+   ->  ProjectSet
+         Output: jsonb_path_query(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), jsonb_path_query_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), test_json
+         ->  Seq Scan on pg_temp.test_jsonb
+               Output: json_type, test_json
+               Filter: (test_jsonb.json_type = 'object'::text)
+(7 rows)
+
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+ jsonb_path_query | jsonb_path_query_first | jsonb_path_query | jsonb_path_query_first 
+------------------+------------------------+------------------+------------------------
+ 2                | 2                      |                2 |                      2
+ 3                | 2                      |                3 |                      2
+ 4                | 2                      |                4 |                      2
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+                                                     QUERY PLAN                                                      
+---------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{}'::text[]), '1700'::oid)
+   Filter: (test_jsonb.json_type = 'scalarint'::text)
+(3 rows)
+
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+ numeric 
+---------
+       2
+(1 row)
+
+-- let raise some errors.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+                                                      QUERY PLAN                                                       
+-----------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field1'::text), '23'::oid))::integer
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+ERROR:  cannot cast jsonb string to type integer
+SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type = 'object';
+ERROR:  cannot cast jsonb string to type boolean
+\pset null ''
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
  ?column? 
 ----------
@@ -586,7 +692,9 @@ SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'object';
  field4
  field5
  field6
-(6 rows)
+ field7
+ field8
+(8 rows)
 
 -- nulls
 SELECT (test_json->'field3') IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'object';
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index e4b7cdf703..500d04936d 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -154,8 +154,72 @@ CREATE TEMP TABLE test_jsonb (
 
 INSERT INTO test_jsonb VALUES
 ('scalar','"a scalar"'),
+('scalarint','2'),
 ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'),
-('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}');
+('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}, "field7": true, "field8": [1,2,3,4,5]}');
+
+\pset null NULL
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json->'field5' -> 0)::numeric,
+(test_json->'field5' -> 10)::numeric,
+(test_json#>'{"field6", "f1"}')::numeric,
+(test_json#>'{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json -> 'field5' -> 0)::numeric,
+(test_json -> 'field5' -> 10)::numeric,
+(test_json #> '{"field6", "f1"}')::numeric,
+(test_json #> '{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+
+-- let raise some errors.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+
+SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type = 'object';
+
+\pset null ''
 
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array';
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 87c1aee379..b0f66589e8 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1256,6 +1256,7 @@ JsonArrayAgg
 JsonArrayConstructor
 JsonArrayQueryConstructor
 JsonBaseObjectInfo
+JsonbValueTarget
 JsonConstructorExpr
 JsonConstructorExprState
 JsonConstructorType
-- 
2.34.1

-- 
Best Regards
Andy Fan

---

From: jian he <[email-masked]>
Date: Tue, 2 Jan 2024 16:18:00 +0800

hi.
you don't need to change src/include/catalog/catversion.h
as mentioned in https://wiki.postgresql.org/wiki/Committing_checklist
Otherwise, cfbot will fail many times.

change to

+ JsonbValue_AsText,
+} JsonbValueTarget;

currently cannot do `git apply`.

---

From: Andy Fan <[email-masked]>
Date: Sun, 07 Jan 2024 15:17:16 +0800

Hi,

> Otherwise, cfbot will fail many times.

Thanks for the wiki.

I checked the wiki and search "catversion", the only message I got is:

"Consider the need for a catversion bump."

How could this be explained as "no need to change ../catversion.h"? 

> currently cannot do `git apply`.

OK, I guess it's something about whitespaces, my git-commit hook has
been configured to capture this during commit. After we reach an
agreement about the 'catversion.h' stuff, the next version of patch
should fix this issue. 

-- 
Best Regards
Andy Fan

---

From: jian he <[email-masked]>
Date: Mon, 8 Jan 2024 08:00:00 +0800

> How could this be explained as "no need to change ../catversion.h"?

that means catversion.h changes is the committer's responsibility, IMHO.

IMHO, main reason is every time the catversion.h change, cfbot
http://cfbot.cputube.org will fail.
one patch took very long time to be committable.
you don't need update your patch for the every catversion.h changes.

> > + JsonbValue_AsJsonbValue,

reason: https://git.postgresql.org/cgit/postgresql.git/commit/?id=611806cd726fc92989ac918eac48fd8d684869c7

> agreement about the 'catversion.h' stuff, the next version of patch

Anyway, I made the following change:
remove catversion.h changes.
refactored the tests. Some of the explain(costs off, verbose) output
is very very long.
it's unreadable on the web browser. so I cut them into small pieces.
resolve duplicate OID issues.
slight refactored jsonbvalue_covert function, for the switch
statement, add a default branch.
see file v16-0001-Improve-the-performance-of-Jsonb-extraction.patch

you made a lot of changes, that might not be easy to get committed, i think.
Maybe we can split the patch into several pieces.
The first part is the original idea that:  pattern:  (jsonb(object) ->
'key')::numerica_data_type can be optimized.
The second part:  is other cases where cast jsonb to scalar data type
can also be optimized.

So, I refactor your patch. only have optimized casts for:
(jsonb(object) -> 'key')::numerica_data_type.
We can optimize more cast cases, but IMHO,
make it as minimal as possible, easier to review, easier to understand.
If people think this performance gain is good, then later we can add
more on top of it.

summary: 2 files attached.
v16-0001-Improve-the-performance-of-Jsonb-extraction.patch
refactored of your patch, that covers all the cast optimization cases,
this file will run the CI test.

v1-0001-Improve-performance-of-Jsonb-extract-via-key-and-c.no-cfbot
this one also based on your patch. but as a minimum patch to optimize
(jsonb(object) -> 'key')::numerica_data_type case only. (this one will
not run CI test).
From 5ac139d3eb213beaa53e6cd39c25b2ecb334ba56 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Sun, 7 Jan 2024 14:14:11 +0800
Subject: [PATCH v16 1/1] Improve the performance of Jsonb extraction.

At the implementation level, considering that we have multiple operators
and various target data types, and to avoid an excessive number of
functions, we have deconstructed the two steps mentioned earlier into
two categories of functions. The first category of functions extracts
the data as a JsonbValue type, while the second category of functions
converts the JsonbValue type into the desired data type. In specific
scenarios, we utilize planner support functions to automatically
assemble these functions.
---
 src/backend/utils/adt/jsonb.c         | 166 ++++++++++++++++++
 src/backend/utils/adt/jsonbsubs.c     |   4 +-
 src/backend/utils/adt/jsonfuncs.c     | 180 ++++++++++++-------
 src/backend/utils/adt/jsonpath_exec.c |  32 +++-
 src/include/catalog/pg_proc.dat       |  46 ++++-
 src/include/utils/jsonb.h             |  11 +-
 src/test/regress/expected/jsonb.out   | 240 +++++++++++++++++++++++++-
 src/test/regress/sql/jsonb.sql        | 116 ++++++++++++-
 src/tools/pgindent/typedefs.list      |   1 +
 9 files changed, 707 insertions(+), 89 deletions(-)

index c10b3fbe..b5a09b61 100644
 
+
+/*
+ * jsonb_cast_support()
+ *
index 79c5d16f..37be80d1 100644
 
 /*
 
index caaafb72..baeefabc 100644
 
 /* semantic action functions for json_array_length */
@@ -492,6 +492,22 @@ static JsonParseErrorType transform_string_values_object_field_start(void *state
 static JsonParseErrorType transform_string_values_array_element_start(void *state, bool isnull);
 static JsonParseErrorType transform_string_values_scalar(void *state, char *token, JsonTokenType tokentype);
 
+Datum
+			elog(ERROR, "invalid jsonbvalue cast target type");
+	}
+	pg_unreachable();
+}
 
 /*
  * pg_parse_json_or_errsave
@@ -847,26 +863,37 @@ json_object_field(PG_FUNCTION_ARGS)
 		PG_RETURN_NULL();
 }
 
+static Datum
+jsonb_object_field_internal(FunctionCallInfo fcinfo, JsonbValueTarget target)
+{
+	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
+	text	   *key = PG_GETARG_TEXT_PP(1);
+	JsonbValue *v;
+
+	if (!JB_ROOT_IS_OBJECT(jb))
+		PG_RETURN_NULL();
+
+	v = getKeyJsonValueFromContainer(&jb->root,
+									 VARDATA_ANY(key),
+									 VARSIZE_ANY_EXHDR(key),
+									 NULL);
+
+	if (v != NULL)
+		return jsonbvalue_covert(v, target);
+
+	PG_RETURN_NULL();
+}
+
 Datum
 jsonb_object_field(PG_FUNCTION_ARGS)
 {
-	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
-	text	   *key = PG_GETARG_TEXT_PP(1);
-	JsonbValue *v;
-	JsonbValue	vbuf;
+	return jsonb_object_field_internal(fcinfo, JsonbValue_AsJsonb);
+}
 
-	if (!JB_ROOT_IS_OBJECT(jb))
-		PG_RETURN_NULL();
-
-	v = getKeyJsonValueFromContainer(&jb->root,
-									 VARDATA_ANY(key),
-									 VARSIZE_ANY_EXHDR(key),
-									 &vbuf);
-
-	if (v != NULL)
-		PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
-
-	PG_RETURN_NULL();
+Datum
+jsonb_object_field_start(PG_FUNCTION_ARGS)
+{
+	return jsonb_object_field_internal(fcinfo, JsonbValue_AsJsonbValue);
 }
 
 Datum
@@ -922,32 +949,44 @@ json_array_element(PG_FUNCTION_ARGS)
 		PG_RETURN_NULL();
 }
 
+static Datum
+jsonb_array_element_internal(FunctionCallInfo fcinfo, JsonbValueTarget target)
+{
+	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
+	int			element = PG_GETARG_INT32(1);
+	JsonbValue *v;
+
+	if (!JB_ROOT_IS_ARRAY(jb))
+		PG_RETURN_NULL();
+
+	/* Handle negative subscript */
+	if (element < 0)
+	{
+		uint32		nelements = JB_ROOT_COUNT(jb);
+
+		if (-element > nelements)
+			PG_RETURN_NULL();
+		else
+			element += nelements;
+	}
+
+	v = getIthJsonbValueFromContainer(&jb->root, element);
+	if (v != NULL)
+		return jsonbvalue_covert(v, target);
+
+	PG_RETURN_NULL();
+}
+
 Datum
 jsonb_array_element(PG_FUNCTION_ARGS)
 {
-	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
-	int			element = PG_GETARG_INT32(1);
-	JsonbValue *v;
+	return jsonb_array_element_internal(fcinfo, JsonbValue_AsJsonb);
+}
 
-	if (!JB_ROOT_IS_ARRAY(jb))
-		PG_RETURN_NULL();
-
-	/* Handle negative subscript */
-	if (element < 0)
-	{
-		uint32		nelements = JB_ROOT_COUNT(jb);
-
-		if (-element > nelements)
-			PG_RETURN_NULL();
-		else
-			element += nelements;
-	}
-
-	v = getIthJsonbValueFromContainer(&jb->root, element);
-	if (v != NULL)
-		PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
-
-	PG_RETURN_NULL();
+Datum
+jsonb_array_element_start(PG_FUNCTION_ARGS)
+{
+	return jsonb_array_element_internal(fcinfo, JsonbValue_AsJsonbValue);
 }
 
 Datum
@@ -1476,17 +1515,23 @@ get_scalar(void *state, char *token, JsonTokenType tokentype)
 Datum
 jsonb_extract_path(PG_FUNCTION_ARGS)
 {
-	return get_jsonb_path_all(fcinfo, false);
+	return get_jsonb_path_all(fcinfo, JsonbValue_AsJsonb);
 }
 
 Datum
 
 static Datum
@@ -1508,7 +1553,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)

 
 	if (isnull)
 		PG_RETURN_NULL();
@@ -1517,7 +1562,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
 }
 
 Datum
@@ -1550,16 +1595,26 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
 
@@ -1645,23 +1700,14 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
 		}
 	}
 
-	if (as_text)
-	{

 Datum
index ac16f5c8..b121a6a3 100644

 Datum
 
 Datum
 
 /*

 Datum
 
index 79793927..3922ac4d 100644

@@ -9983,6 +9983,30 @@
+{ oid => '9303', descr => 'extract josnbvalue from jsonb array for the given index',
@@ -10175,6 +10199,10 @@
@@ -10183,6 +10211,10 @@
index e38dfd49..53e06a20 100644
 
+typedef enum JsonbValueTarget
+{
+	JsonbValue_AsJsonbValue,
+	JsonbValue_AsJsonb,
+	JsonbValue_AsText,
+} JsonbValueTarget;
+
 #define IsAJsonbScalar(jsonbval)	(((jsonbval)->type >= jbvNull && \
 									  (jsonbval)->type <= jbvBool) || \
 									  (jsonbval)->type == jbvDatetime)
@@ -428,12 +435,12 @@ extern const char *JsonbTypeName(JsonbValue *val);
 extern Datum jsonb_set_element(Jsonb *jb, Datum *path, int path_len,
 							   JsonbValue *newval);
 extern Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath,
-							   bool *isnull, bool as_text);
+							   bool *isnull, JsonbValueTarget target);
 extern bool to_jsonb_is_immutable(Oid typoid);
 extern Datum jsonb_build_object_worker(int nargs, const Datum *args, const bool *nulls,
 									   const Oid *types, bool absent_on_null,
 									   bool unique_keys);
 extern Datum jsonb_build_array_worker(int nargs, const Datum *args, const bool *nulls,
 									  const Oid *types, bool absent_on_null);
-
+extern Datum jsonbvalue_covert(JsonbValue *jbv, JsonbValueTarget target);
 #endif							/* __JSONB_H__ */
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index b597d01a..0167b0e2 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -457,8 +457,242 @@ CREATE TEMP TABLE test_jsonb (
 );
 INSERT INTO test_jsonb VALUES
 ('scalar','"a scalar"'),
+('scalarint','2'),
 ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'),
-('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}');
+('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4
+			,"field5": [1,2,3], "field6": {"f1":9}
+			,"field7": true, "field8": [1,2,3,4,5]}');
+\pset null NULL
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	(test_json -> 'field4')::numeric
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+                                                  QUERY PLAN                                                  
+--------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '1700'::oid)
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	(test_json -> 'field4')::int2
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+                                                       QUERY PLAN                                                       
+------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '21'::oid))::smallint
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::int4
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+                                                      QUERY PLAN                                                       
+-----------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '23'::oid))::integer
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::int8
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+                                                      QUERY PLAN                                                      
+----------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '20'::oid))::bigint
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN	(COSTS OFF, VERBOSE)
+SELECT	(test_json -> 'field4')::float4
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+                                                     QUERY PLAN                                                      
+---------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '700'::oid))::real
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::float8
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+                                                           QUERY PLAN                                                            
+---------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '701'::oid))::double precision
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json->'field5' -> 0)::numeric
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+                                                      QUERY PLAN                                                      
+----------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal, 0), '1700'::oid)
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json->'field5' -> 10)::numeric
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+                                                      QUERY PLAN                                                       
+-----------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal, 10), '1700'::oid)
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json#>'{"field6", "f1"}')::numeric
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+                                                          QUERY PLAN                                                          
+------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field6,f1}'::text[]), '1700'::oid)
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json#>'{"field6", "f2"}')::numeric
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+                                                          QUERY PLAN                                                          
+------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field6,f2}'::text[]), '1700'::oid)
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	(test_json#>'{"field7"}')::bool
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+                                                QUERY PLAN                                                 
+-----------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_finish_bool(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field7}'::text[]))
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json -> 'field5' -> 0)::numeric,
+(test_json -> 'field5' -> 10)::numeric,
+(test_json #> '{"field6", "f1"}')::numeric,
+(test_json #> '{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+ numeric | int2 | int4 | int8 | float4 | float8 | numeric | numeric | numeric | numeric | bool 
+---------+------+------+------+--------+--------+---------+---------+---------+---------+------
+       4 |    4 |    4 |    4 |      4 |      4 |       1 |    NULL |       9 |    NULL | t
+(1 row)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
+FROM test_jsonb	WHERE json_type = 'object';
+                                                             QUERY PLAN                                                              
+-------------------------------------------------------------------------------------------------------------------------------------
+ ProjectSet
+   Output: jsonb_path_query(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)
+   ->  Seq Scan on pg_temp.test_jsonb
+         Output: json_type, test_json
+         Filter: (test_jsonb.json_type = 'object'::text)
+(5 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
+FROM test_jsonb	WHERE json_type = 'object';
+                                                                QUERY PLAN                                                                 
+-------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_path_query_first(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb	WHERE json_type = 'object';
+                                                                                              QUERY PLAN                                                                                              
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Result
+   Output: (jsonb_finish_numeric((jsonb_path_query_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)), '21'::oid))::smallint
+   ->  ProjectSet
+         Output: jsonb_path_query_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)
+         ->  Seq Scan on pg_temp.test_jsonb
+               Output: json_type, test_json
+               Filter: (test_jsonb.json_type = 'object'::text)
+(7 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM 	test_jsonb	WHERE json_type = 'object';
+                                                                                                QUERY PLAN                                                                                                
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: (jsonb_finish_numeric(jsonb_path_query_first_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), '21'::oid))::smallint
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+ jsonb_path_query | jsonb_path_query_first | jsonb_path_query | jsonb_path_query_first 
+------------------+------------------------+------------------+------------------------
+ 2                | 2                      |                2 |                      2
+ 3                | 2                      |                3 |                      2
+ 4                | 2                      |                4 |                      2
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+                                                     QUERY PLAN                                                      
+---------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{}'::text[]), '1700'::oid)
+   Filter: (test_jsonb.json_type = 'scalarint'::text)
+(3 rows)
+
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+ numeric 
+---------
+       2
+(1 row)
+
+-- let raise some errors.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+                                                      QUERY PLAN                                                       
+-----------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field1'::text), '23'::oid))::integer
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+ERROR:  cannot cast jsonb string to type integer
+SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type = 'object';
+ERROR:  cannot cast jsonb string to type boolean
+\pset null ''
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
  ?column? 
 ----------
@@ -586,7 +820,9 @@ SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'object';
  field4
  field5
  field6
-(6 rows)
+ field7
+ field8
+(8 rows)
 
 -- nulls
 SELECT (test_json->'field3') IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'object';
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 6dae715a..1b9959f9 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -154,8 +154,122 @@ CREATE TEMP TABLE test_jsonb (
 
 INSERT INTO test_jsonb VALUES
 ('scalar','"a scalar"'),
+('scalarint','2'),
 ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'),
-('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}');
+('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4
+			,"field5": [1,2,3], "field6": {"f1":9}
+			,"field7": true, "field8": [1,2,3,4,5]}');
+
+\pset null NULL
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	(test_json -> 'field4')::numeric
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	(test_json -> 'field4')::int2
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::int4
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::int8
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+
+EXPLAIN	(COSTS OFF, VERBOSE)
+SELECT	(test_json -> 'field4')::float4
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::float8
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json->'field5' -> 0)::numeric
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json->'field5' -> 10)::numeric
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json#>'{"field6", "f1"}')::numeric
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json#>'{"field6", "f2"}')::numeric
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	(test_json#>'{"field7"}')::bool
+FROM 	test_jsonb
+WHERE 	json_type = 'object';
+
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json -> 'field5' -> 0)::numeric,
+(test_json -> 'field5' -> 10)::numeric,
+(test_json #> '{"field6", "f1"}')::numeric,
+(test_json #> '{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
+FROM test_jsonb	WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
+FROM test_jsonb	WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb	WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT	jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM 	test_jsonb	WHERE json_type = 'object';
+
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+
+-- let raise some errors.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+
+SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type = 'object';
+
+\pset null ''
 
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array';
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 5fd46b7b..4ca6679e 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1265,6 +1265,7 @@ JsonArrayAgg
 JsonArrayConstructor
 JsonArrayQueryConstructor
 JsonBaseObjectInfo
+JsonbValueTarget
 JsonConstructorExpr
 JsonConstructorExprState
 JsonConstructorType
-- 
2.34.1