Re: Extract numeric filed in JSONB more effectively - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Extract numeric filed in JSONB more effectively
Date
Msg-id CAFj8pRA9tNRMpfpZoUwgS1Wdu0yT2YEZ9jO2AZjsgD15gzVuPw@mail.gmail.com
Whole thread Raw
In response to Re: Extract numeric filed in JSONB more effectively  (Andy Fan <zhihui.fan1213@gmail.com>)
List pgsql-hackers


čt 3. 8. 2023 v 16:27 odesílatel Andy Fan <zhihui.fan1213@gmail.com> napsal:
Hi: 


 If you use explicit cast, then the code should not be hard, in the rewrite stage all information should be known.

Can you point to me where the code is for the XML stuff?  I thought
this is a bad idea but I may accept it if some existing code does
such a thing already.   "such thing"  is  typeA:typeB is
converted something else but user can't find out an entry in
pg_cast for typeA to typeB. 

in XML there is src/backend/utils/adt/xml.c, the XmlTableGetValue routine. It is not an internal transformation - and from XML type to some else.

you can look at parser - parse_expr, parse_func. You can watch the lifecycle of :: operator. There are transformations of nodes to different nodes

you can look to patches related to SQL/JSON (not fully committed yet) and json_table


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

 The custom @-> operator you can implement in your own custom extension. Builtin solutions should be generic as it is possible.

I agree, but actually I think there is no clean way to do it, at least I
dislike the conversion of typeA to typeB in a cast syntax but there
is no entry in pg_cast for it.  Are you saying something like this 
or I misunderstood you? 

There is not any possibility of user level space.  The conversions should be supported by cast from pg_cast, where it is possible. When it is impossible, then you can raise an exception in some strict mode, or you can do IO cast. But this is not hard part

You should to teach parser to push type info deeper to some nodes about expected result

(2023-08-04 05:28:36) postgres=# select ('{"a":2, "b":"nazdar"}'::jsonb)['a']::numeric;
┌─────────┐
│ numeric │
╞═════════╡
│       2 │
└─────────┘
(1 row)

(2023-08-04 05:28:36) postgres=# select ('{"a":2, "b":"nazdar"}'::jsonb)['a']::numeric;
┌─────────┐
│ numeric │
╞═════════╡
│       2 │
└─────────┘
(1 row)

(2023-08-04 05:28:41) postgres=# select ('{"a":2, "b":"nazdar"}'::jsonb)['a']::int;
┌──────┐
│ int4 │
╞══════╡
│    2 │
└──────┘
(1 row)

when the parser iterates over the expression, it crosses ::type node first, so you have information about the target type. Currently this information is used when the parser is going back and when the source type is the same as the target type, the cast can be ignored. Probably it needs to add some flag to the operator if they are able to use this. Maybe it can be a new third argument with an expected type. So new kinds of op functions can look like opfx("any", "any", anyelement) returns anyelement. Maybe you find another possibility. It can be invisible for me (or for you) now.

It is much more work, but the benefits will be generic. I think this is an important part for container types, so partial fix is not good, and it requires a system solution. The performance is important, but without generic solutions, the complexity increases, and this is a much bigger problem.

Regards

Pavel





--
Best Regards
Andy Fan

pgsql-hackers by date:

Previous
From: jian he
Date:
Subject: Re: Extract numeric filed in JSONB more effectively
Next
From: Andy Fan
Date:
Subject: Re: Extract numeric filed in JSONB more effectively