Hash arrow (#>, #>>) JSON operators unsupported

[naoyak]

What happens?

Attempting to query a JSON column with the hash arrow (#> or #>>) operators with pg_duckdb results in a syntax error:

"ERROR: (PGDuckDB/CreatePlan) Prepared query returned an error: 'Parser Error: syntax error at or near \"#\"\nLINE 1: SELECT ((dim_calibrations #>> ARRAY['v1'::text, 'c2h4'::text, 'en...\n                                  ^",

To Reproduce

postgres=# create table dummy(col json) USING duckdb;
CREATE TABLE
postgres=# insert into dummy values ('{"a": {"b": ["foo","bar"]}}'::json);
INSERT 0 0
postgres=# SELECT col #>> '{a,b,1}' from dummy;
ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: 'Parser Error: syntax error at or near "#"
LINE 1: SELECT (col #>> '{a,b,1}'::text[]) AS "?column?" FR...
                    ^

OS:

macOS

pg_duckdb Version (if built from source use commit hash):

0.2.0 nightly

Postgres Version (if built from source use commit hash):

17

Hardware:

No response

Full Name:

Naoya Kanai

Affiliation:

Strella

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a nightly build

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

[Leo-XM-Zeng]

It appears that duckdb does not support the "#>>" operator.