public inbox for [email protected]  
help / color / mirror / Atom feed
Re: Extract numeric filed in JSONB more effectively
8+ messages / 4 participants
[nested] [flat]

* Re: Extract numeric filed in JSONB more effectively
@ 2023-08-02 06:01  jian he <[email protected]>
  0 siblings, 1 reply; 8+ messages in thread

From: jian he @ 2023-08-02 06:01 UTC (permalink / raw)
  To: Andy Fan <[email protected]>; +Cc: pgsql-hackers

On Tue, Aug 1, 2023 at 12:39 PM Andy Fan <[email protected]> wrote:
>
> Hi:
>
> Currently if we want to extract a numeric field in jsonb, we need to use
> the following expression:  cast (a->>'a' as numeric). It will turn a numeric
> to text first and then turn the text to numeric again. See
> jsonb_object_field_text and JsonbValueAsText.  However the binary format
> of numeric in JSONB is compatible with the numeric in SQL, so I think we
> can have an operator to extract the numeric directly. If the value of a given
> field is not a numeric data type, an error will be raised, this can be
> documented.
>
> In this patch, I added a new operator for this purpose, here is the
> performance gain because of this.
>
> create table tb (a jsonb);
> insert into tb select '{"a": 1}'::jsonb from generate_series(1, 100000)i;
>
> current method:
> select count(*) from tb where cast (a->>'a' as numeric) = 2;
> 167ms.
>
> new method:
> select count(*) from tb where a@->'a' = 2;
> 65ms.
>
> Is this the right way to go? Testcase, document and catalog version are
> updated.
>
>
> --
> Best Regards
> Andy Fan


return PointerGetDatum(v->val.numeric);
should be something like
PG_RETURN_NUMERIC(v->val.numeric);
?






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Extract numeric filed in JSONB more effectively
@ 2023-08-03 00:50  Andy Fan <[email protected]>
  parent: jian he <[email protected]>
  0 siblings, 1 reply; 8+ messages in thread

From: Andy Fan @ 2023-08-03 00:50 UTC (permalink / raw)
  To: jian he <[email protected]>; +Cc: pgsql-hackers

Hi Jian:


> return PointerGetDatum(v->val.numeric);
> should be something like
> PG_RETURN_NUMERIC(v->val.numeric);
> ?
>

Thanks for this reminder, a new patch is attached.  and commitfest
entry is added as well[1]. For recording purposes,  I compared the
new operator with all the existing operators.

select 1 from tb where (a->'a')::numeric = 2;   30.56ms
select 1 from tb where (a->>'a')::numeric = 2; 29.43ms
select 1 from tb where (a@->'a') = 2;              14.80ms

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

-- 
Best Regards
Andy Fan


Attachments:

  [application/octet-stream] v2-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch (5.7K, 3-v2-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch)
  download | inline diff:
From 1415e09d6ca860d466939229e6d6a9012bf2bbcf Mon Sep 17 00:00:00 2001
From: Andy Fan <[email protected]>
Date: Tue, 1 Aug 2023 10:38:29 +0800
Subject: [PATCH v1] Add jsonb operator to return a numeric directly.

The binary format of numeric in JOSNB is compatible with the numeric
in SQL, so we can get the numeric more effectively.
---
 doc/src/sgml/func.sgml                       | 14 +++++++++++
 src/backend/utils/adt/jsonfuncs.c            | 26 ++++++++++++++++++++
 src/include/catalog/catversion.h             |  2 +-
 src/include/catalog/pg_operator.dat          |  3 +++
 src/include/catalog/pg_proc.dat              |  4 +++
 src/test/regress/expected/jsonb_jsonpath.out | 20 +++++++++++++++
 src/test/regress/sql/jsonb_jsonpath.sql      |  4 +++
 7 files changed, 72 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index be2f54c9141..7f4b8970475 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15699,6 +15699,20 @@ table2-mapping
         <returnvalue>t</returnvalue>
        </para></entry>
       </row>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <type>jsonb</type> <literal>@-></literal> <type>jsonpath</type>
+        <returnvalue>numeric</returnvalue>
+       </para>
+       <para>
+        Returns the result of a JSON value at the specified path as numeric.
+        Raise error if the JSON value is not a numeric.
+       </para>
+       <para>
+        <literal>'{"a":1}'::jsonb @-> 'a' </literal>
+        <returnvalue>1</returnvalue>
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index a4bfa5e4040..5393df0ed7f 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -886,6 +886,32 @@ json_object_field_text(PG_FUNCTION_ARGS)
 		PG_RETURN_NULL();
 }
 
+Datum
+jsonb_object_field_numeric(PG_FUNCTION_ARGS)
+{
+	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();
+
+	v = getKeyJsonValueFromContainer(&jb->root,
+									 VARDATA_ANY(key),
+									 VARSIZE_ANY_EXHDR(key),
+									 &vbuf);
+
+	if (v == NULL || v->type == jbvNull)
+		PG_RETURN_NULL();
+
+	if (v->type != jbvNumeric)
+		elog(ERROR, "field '%s' has non-numeric value.", text_to_cstring(key));
+
+	return PointerGetDatum(v->val.numeric);
+};
+
+
 Datum
 jsonb_object_field_text(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index f507b49bb28..5a534771edb 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202307261
+#define CATALOG_VERSION_NO	202308011
 
 #endif
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index b2cdea66c4b..d63b9f5188d 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3178,6 +3178,9 @@
 { oid => '3477', descr => 'get jsonb object field as text',
   oprname => '->>', oprleft => 'jsonb', oprright => 'text', oprresult => 'text',
   oprcode => 'jsonb_object_field_text' },
+{ oid => '3814', descr => 'get jsonb object field as numeric',
+  oprname => '@->', oprleft => 'jsonb', oprright => 'text', oprresult => 'numeric',
+  oprcode => 'jsonb_object_field_numeric' },
 { oid => '3212', descr => 'get jsonb array element',
   oprname => '->', oprleft => 'jsonb', oprright => 'int4', oprresult => 'jsonb',
   oprcode => 'jsonb_array_element' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989a..e93303f3be7 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9928,6 +9928,10 @@
   proname => 'jsonb_object_field_text', prorettype => 'text',
   proargtypes => 'jsonb text', proargnames => '{from_json, field_name}',
   prosrc => 'jsonb_object_field_text' },
+{ oid => '3813',
+  proname => 'jsonb_object_field_numeric', prorettype => 'numeric',
+  proargtypes => 'jsonb text', proargnames => '{from_json, field_name}',
+  prosrc => 'jsonb_object_field_numeric' },
 { oid => '3215',
   proname => 'jsonb_array_element', prorettype => 'jsonb',
   proargtypes => 'jsonb int4', proargnames => '{from_json, element_index}',
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 6659bc9091a..bf2351cf9cf 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -34,6 +34,26 @@ select jsonb '{"a": 12}' @? '$.b + 2';
  
 (1 row)
 
+select jsonb '{"a": 12}' @-> 'a';
+ ?column? 
+----------
+       12
+(1 row)
+
+select pg_typeof(jsonb '{"a": 12}' @-> 'a');
+ pg_typeof 
+-----------
+ numeric
+(1 row)
+
+select jsonb '{"a": 12}' @-> 'b';
+ ?column? 
+----------
+         
+(1 row)
+
+select jsonb '{"a": "12a"}' @-> 'a';
+ERROR:  field 'a' has non-numeric value.
 select jsonb '{"a": {"a": 12}}' @? '$.a.a';
  ?column? 
 ----------
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index e0ce509264a..32576566f11 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -4,6 +4,10 @@ select jsonb '{"a": 12}' @? '$.a.b';
 select jsonb '{"a": 12}' @? '$.b';
 select jsonb '{"a": 12}' @? '$.a + 2';
 select jsonb '{"a": 12}' @? '$.b + 2';
+select jsonb '{"a": 12}' @-> 'a';
+select pg_typeof(jsonb '{"a": 12}' @-> 'a');
+select jsonb '{"a": 12}' @-> 'b';
+select jsonb '{"a": "12a"}' @-> 'a';
 select jsonb '{"a": {"a": 12}}' @? '$.a.a';
 select jsonb '{"a": {"a": 12}}' @? '$.*.a';
 select jsonb '{"b": {"a": 12}}' @? '$.*.a';
-- 
2.21.0



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Extract numeric filed in JSONB more effectively
@ 2023-08-03 04:59  Pavel Stehule <[email protected]>
  parent: Andy Fan <[email protected]>
  0 siblings, 1 reply; 8+ messages in thread

From: Pavel Stehule @ 2023-08-03 04:59 UTC (permalink / raw)
  To: Andy Fan <[email protected]>; +Cc: jian he <[email protected]>; pgsql-hackers

Hi

čt 3. 8. 2023 v 2:51 odesílatel Andy Fan <[email protected]> napsal:

> Hi Jian:
>
>
>> return PointerGetDatum(v->val.numeric);
>> should be something like
>> PG_RETURN_NUMERIC(v->val.numeric);
>> ?
>>
>
> Thanks for this reminder, a new patch is attached.  and commitfest
> entry is added as well[1]. For recording purposes,  I compared the
> new operator with all the existing operators.
>
> select 1 from tb where (a->'a')::numeric = 2;   30.56ms
> select 1 from tb where (a->>'a')::numeric = 2; 29.43ms
> select 1 from tb where (a@->'a') = 2;              14.80ms
>
> [1] https://commitfest.postgresql.org/44/4476/
>
>
I don't like this solution because it is bloating  operators and it is not
extra readable. For completeness you should implement cast for date, int,
boolean too. Next, the same problem is with XML or hstore type (probably
with any types that are containers).

It is strange so only casting is 2x slower. I don't like the idea so using
a special operator is 2x faster than common syntax for casting. It is a
signal, so there is a space for optimization. Black magic with special
operators is not user friendly for relatively common problems.

Maybe we can introduce some *internal operator* "extract to type", and in
rewrite stage we can the pattern (x->'field')::type transform to OP(x,
'field', typid)

Regards

Pavel


-- 
> Best Regards
> Andy Fan
>


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Extract numeric filed in JSONB more effectively
@ 2023-08-03 07:53  Andy Fan <[email protected]>
  parent: Pavel Stehule <[email protected]>
  0 siblings, 2 replies; 8+ messages in thread

From: Andy Fan @ 2023-08-03 07:53 UTC (permalink / raw)
  To: Pavel Stehule <[email protected]>; +Cc: jian he <[email protected]>; pgsql-hackers

Hi Pavel:

Thanks for the feedback.

I don't like this solution because it is bloating  operators and it is not
> extra readable.
>

If we support it with cast, could we say we are bloating CAST?  It is true
that it is not extra readable, if so how about  a->>'a'  return text?
Actually
I can't guess any meaning of the existing jsonb operations without
documentation.

For completeness you should implement cast for date, int, boolean too.
> Next, the same problem is with XML or hstore type (probably with any types
> that are containers).
>

I am not sure completeness is a gold rule we should obey anytime,
like we have some function like int24le to avoid the unnecessary
cast, but we just avoid casting for special types for performance
reason, but not for all. At the same time,  `int2/int4/int8` doesn't
have a binary compatibility type in jsonb. and the serialization
/deserialization for boolean is pretty cheap.

I didn't realize timetime types are binary compatible with SQL,
so maybe we can have some similar optimization as well.
(It is a pity that timestamp(tz) are not binary, or else we may
just need one operator).


>
> I don't like the idea so using a special operator is 2x faster than common
> syntax for casting. It is a signal, so there is a space for optimization.
> Black magic with special operators is not user friendly for relatively
> common problems.
>

I don't think "Black magic" is a proper word here, since it is not much
different from ->> return a text.  If you argue text can be cast to
most-of-types,  that would be a reason, but I doubt this difference
should generate a "black magic".


>
> Maybe we can introduce some *internal operator* "extract to type", and in
> rewrite stage we can the pattern (x->'field')::type transform to OP(x,
> 'field', typid)
>

Not sure what the OP should be?  If it is a function, what is the
return value?  It looks to me like it is hard to do in c language?

After all,  if we really care about the number of operators, I'm OK
with just let users use the function directly, like

jsonb_field_as_numeric(jsonb, 'filedname')
jsonb_field_as_timestamp(jsonb, 'filedname');
jsonb_field_as_timestamptz(jsonb, 'filedname');
jsonb_field_as_date(jsonb, 'filedname');

it can save an operator and sloves the readable issue.

-- 
Best Regards
Andy Fan


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Extract numeric filed in JSONB more effectively
@ 2023-08-03 09:47  Pavel Stehule <[email protected]>
  parent: Andy Fan <[email protected]>
  1 sibling, 1 reply; 8+ messages in thread

From: Pavel Stehule @ 2023-08-03 09:47 UTC (permalink / raw)
  To: Andy Fan <[email protected]>; +Cc: jian he <[email protected]>; pgsql-hackers

Hi

čt 3. 8. 2023 v 9:53 odesílatel Andy Fan <[email protected]> napsal:

> Hi Pavel:
>
> Thanks for the feedback.
>
> I don't like this solution because it is bloating  operators and it is not
>> extra readable.
>>
>
> If we support it with cast, could we say we are bloating CAST?  It is true
> that it is not extra readable, if so how about  a->>'a'  return text?
> Actually
> I can't guess any meaning of the existing jsonb operations without
> documentation.
>

yes, it can bloat CAST, but for usage we have already used syntax, and
these casts are cooked already:

(2023-08-03 11:04:51) postgres=# select castfunc::regprocedure from pg_cast
where castsource = 'jsonb'::regtype;
┌──────────────────┐
│     castfunc     │
╞══════════════════╡
│ -                │
│ bool(jsonb)      │
│ "numeric"(jsonb) │
│ int2(jsonb)      │
│ int4(jsonb)      │
│ int8(jsonb)      │
│ float4(jsonb)    │
│ float8(jsonb)    │
└──────────────────┘
(8 rows)

the operator ->> was a special case, the text type is special in postgres
as the most convertible type. And when you want to visualise a value or
display the value, you should convert value to text.

I can live with that because it is just one, but with your proposal opening
the doors for implementing tens of similar operators, I think it is bad.
Using ::target_type is common syntax and doesn't require reading
documentation.

More, I believe so lot of people uses more common syntax, and then this
syntax should to have good performance - for jsonb - (val->'op')::numeric
works, and then there should not be performance penalty, because this
syntax will be used in 99%.

Usage of cast is self documented.


> For completeness you should implement cast for date, int, boolean too.
>> Next, the same problem is with XML or hstore type (probably with any types
>> that are containers).
>>
>
> I am not sure completeness is a gold rule we should obey anytime,
> like we have some function like int24le to avoid the unnecessary
> cast, but we just avoid casting for special types for performance
> reason, but not for all. At the same time,  `int2/int4/int8` doesn't
> have a binary compatibility type in jsonb. and the serialization
> /deserialization for boolean is pretty cheap.
>
> I didn't realize timetime types are binary compatible with SQL,
> so maybe we can have some similar optimization as well.
> (It is a pity that timestamp(tz) are not binary, or else we may
> just need one operator).
>
>
>>
>> I don't like the idea so using a special operator is 2x faster than
>> common syntax for casting. It is a signal, so there is a space for
>> optimization. Black magic with special operators is not user friendly for
>> relatively common problems.
>>
>
> I don't think "Black magic" is a proper word here, since it is not much
> different from ->> return a text.  If you argue text can be cast to
> most-of-types,  that would be a reason, but I doubt this difference
> should generate a "black magic".
>

I used the term black magic, because nobody without reading documentation
can find this operator. It is used just for this special case, and the
functionality is the same as using cast (only with different performance).

The operator ->> is more widely used. But if we have some possibility to
work without it, then the usage for a lot of users will be more simple.
More if the target types can be based on context

Can be nice to use some like `EXTRACT(YEAR FROM val->'field')` instead
`EXTRACT(YEAR FROM (val->>'field')::date)`


>
>>
>> Maybe we can introduce some *internal operator* "extract to type", and in
>> rewrite stage we can the pattern (x->'field')::type transform to OP(x,
>> 'field', typid)
>>
>
> Not sure what the OP should be?  If it is a function, what is the
> return value?  It looks to me like it is hard to do in c language?
>

It should be internal structure - it can be similar like COALESCE or IS
operator


>
> After all,  if we really care about the number of operators, I'm OK
> with just let users use the function directly, like
>
> jsonb_field_as_numeric(jsonb, 'filedname')
> jsonb_field_as_timestamp(jsonb, 'filedname');
> jsonb_field_as_timestamptz(jsonb, 'filedname');
> jsonb_field_as_date(jsonb, 'filedname');
>
> it can save an operator and sloves the readable issue.
>

I don't like it too much, but it is better than introduction new operator

We already have the jsonb_extract_path and jsonb_extract_path_text
function.

I can imagine to usage "anyelement" type too. some like
`jsonb_extract_path_type(jsonb, anyelement, variadic text[] )`






> --
> Best Regards
> Andy Fan
>


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Extract numeric filed in JSONB more effectively
@ 2023-08-03 12:34  Chapman Flack <[email protected]>
  parent: Andy Fan <[email protected]>
  1 sibling, 1 reply; 8+ messages in thread

From: Chapman Flack @ 2023-08-03 12:34 UTC (permalink / raw)
  To: Andy Fan <[email protected]>; +Cc: Pavel Stehule <[email protected]>; jian he <[email protected]>; pgsql-hackers

On 2023-08-03 03:53, Andy Fan wrote:
> I didn't realize timetime types are binary compatible with SQL,
> so maybe we can have some similar optimization as well.
> (It is a pity that timestamp(tz) are not binary, or else we may
> just need one operator).

Not to veer from the thread, but something about that paragraph
has been hard for me to parse/follow.

>> Maybe we can introduce some *internal operator* "extract to type", and 
>> in
>> rewrite stage we can the pattern (x->'field')::type transform to OP(x,
>> 'field', typid)
> 
> Not sure what the OP should be?  If it is a function, what is the
> return value?  It looks to me like it is hard to do in c language?

Now I am wondering about the 'planner support function' available
in CREATE FUNCTION since PG 12. I've never played with that yet.
Would that make it possible to have some, rather generic, extract
from JSON operator that can look at the surrounding expression
and replace itself sometimes with something more efficient?

Regards,
-Chap






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Extract numeric filed in JSONB more effectively
@ 2023-08-03 13:22  Andy Fan <[email protected]>
  parent: Pavel Stehule <[email protected]>
  0 siblings, 0 replies; 8+ messages in thread

From: Andy Fan @ 2023-08-03 13:22 UTC (permalink / raw)
  To: Pavel Stehule <[email protected]>; +Cc: jian he <[email protected]>; pgsql-hackers

Hi:


> More, I believe so lot of people uses more common syntax, and then this
> syntax should to have good performance - for jsonb - (val->'op')::numeric
> works, and then there should not be performance penalty, because this
> syntax will be used in 99%.
>

This looks like a valid opinion IMO,  but to rescue it, we have to do
something like "internal structure" and remove the existing cast.
But even we pay the effort, it still breaks some common knowledge,
since xx:numeric is not a cast.  It is an "internal structure"!


I don't think "Black magic" is a proper word here, since it is not much
>> different from ->> return a text.  If you argue text can be cast to
>> most-of-types,  that would be a reason, but I doubt this difference
>> should generate a "black magic".
>>
>
> I used the term black magic, because nobody without reading documentation
> can find this operator.
>

I think this is what document is used for..


> It is used just for this special case, and the functionality is the same
> as using cast (only with different performance).
>

This is not good, but I didn't see a better choice so far,  see my first
graph.


>
> The operator ->> is more widely used. But if we have some possibility to
> work without it, then the usage for a lot of users will be more simple.
> More if the target types can be based on context
>

It would be cool but still I didn't see a way to do that without making
something else complex.


>>> Maybe we can introduce some *internal operator* "extract to type", and
>>> in rewrite stage we can the pattern (x->'field')::type transform to OP(x,
>>> 'field', typid)
>>>
>>
>> Not sure what the OP should be?  If it is a function, what is the
>> return value?  It looks to me like it is hard to do in c language?
>>
>
> It should be internal structure - it can be similar like COALESCE or IS
> operator
>

It may work, but see my answer in the first graph.


>
>
>>
>> After all,  if we really care about the number of operators, I'm OK
>> with just let users use the function directly, like
>>
>> jsonb_field_as_numeric(jsonb, 'filedname')
>> jsonb_field_as_timestamp(jsonb, 'filedname');
>> jsonb_field_as_timestamptz(jsonb, 'filedname');
>> jsonb_field_as_date(jsonb, 'filedname');
>>
>> it can save an operator and sloves the readable issue.
>>
>
> I don't like it too much, but it is better than introduction new operator
>

Good to know it.  Naming operators is a complex task  if we add four.


> We already have the jsonb_extract_path and jsonb_extract_path_text
> function.
>

I can't follow this.  jsonb_extract_path returns a jsonb, which is  far
away from
our goal: return a numeric effectively?

I can imagine to usage "anyelement" type too. some like
> `jsonb_extract_path_type(jsonb, anyelement, variadic text[] )`
>

Can you elaborate this please?

-- 
Best Regards
Andy Fan


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Extract numeric filed in JSONB more effectively
@ 2023-08-03 13:50  Andy Fan <[email protected]>
  parent: Chapman Flack <[email protected]>
  0 siblings, 0 replies; 8+ messages in thread

From: Andy Fan @ 2023-08-03 13:50 UTC (permalink / raw)
  To: Chapman Flack <[email protected]>; +Cc: Pavel Stehule <[email protected]>; jian he <[email protected]>; pgsql-hackers

Hi:

On Thu, Aug 3, 2023 at 8:34 PM Chapman Flack <[email protected]> wrote:

> On 2023-08-03 03:53, Andy Fan wrote:
> > I didn't realize timetime types are binary compatible with SQL,
> > so maybe we can have some similar optimization as well.
> > (It is a pity that timestamp(tz) are not binary, or else we may
> > just need one operator).
>
> Not to veer from the thread, but something about that paragraph
> has been hard for me to parse/follow.
>

I don't think this is a key conflict so far. but I'd explain this in more
detail. If timestamp -> timestamptz or timestamptz -> timestamp is
binary compatible,  we can only have 1 operator to return a timestamp.
then when we cast it to timestamptz, it will be a no-op during runtime.
however cast between timestamp and timestamptz is not binary
compatible. whose castmethod is 'f';



>
> >> Maybe we can introduce some *internal operator* "extract to type", and
> >> in
> >> rewrite stage we can the pattern (x->'field')::type transform to OP(x,
> >> 'field', typid)
> >
> > Not sure what the OP should be?  If it is a function, what is the
> > return value?  It looks to me like it is hard to do in c language?
>
> Now I am wondering about the 'planner support function' available
> in CREATE FUNCTION since PG 12. I've never played with that yet.
> Would that make it possible to have some, rather generic, extract
> from JSON operator that can look at the surrounding expression
> and replace itself sometimes with something  efficient?
>

I didn't realize this before,  'planner support function' looks
amazing and SupportRequestSimplify looks promising, I will check it
more.

-- 
Best Regards
Andy Fan


^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2023-08-03 13:50 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-02 06:01 Re: Extract numeric filed in JSONB more effectively jian he <[email protected]>
2023-08-03 00:50 ` Andy Fan <[email protected]>
2023-08-03 04:59   ` Pavel Stehule <[email protected]>
2023-08-03 07:53     ` Andy Fan <[email protected]>
2023-08-03 09:47       ` Pavel Stehule <[email protected]>
2023-08-03 13:22         ` Andy Fan <[email protected]>
2023-08-03 12:34       ` Chapman Flack <[email protected]>
2023-08-03 13:50         ` Andy Fan <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox