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 thoughtthis is a bad idea but I may accept it if some existing code doessuch a thing already. "such thing" is typeA:typeB isconverted something else but user can't find out an entry inpg_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 makingsomething 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 Idislike the conversion of typeA to typeB in a cast syntax but thereis no entry in pg_cast for it. Are you saying something like thisor 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)
┌─────────┐
│ 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)
┌─────────┐
│ 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 RegardsAndy Fan
pgsql-hackers by date: