Re: SQL:2023 JSON simplified accessor support

On 2024-09-26 Th 11:45 AM, Alexandra Wang wrote:
> Hi,
>
> I didn’t run pgindent earlier, so here’s the updated version with the
> correct indentation. Hope this helps!

This is a really nice feature, and provides a lot of expressive power
for such a small piece of code.

I notice this doesn't seem to work for domains over json and jsonb.

andrew(at)~=# create domain json_d as json;
CREATE DOMAIN
andrew(at)~=# create table test_json_dot(id int, test_json json_d);
CREATE TABLE
andrew(at)~=# insert into test_json_dot select 1, '{"a": 1, "b": 42}'::json;
INSERT 0 1      |          |
andrew(at)~=# select (test_json_dot.test_json).b, json_query(test_json,
'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as
expected from test_json_dot;
ERROR:  column notation .b applied to type json_d, which is not a
composite type
LINE 1: select (test_json_dot.test_json).b, json_query(test_json, 'l...

I'm not sure that's a terribly important use case, but we should
probably make it work. If it's a domain we should get the basetype of
the domain. There's some example code in src/backend/utils/adt/jsonfuncs.c

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com