Hash arrow (#>, #>>) JSON operators unsupported
- Dominant language
- C++
- Stars
- 3.2k
- Forks
- 204
- PR merge metrics
- No merged PRs in 30d
Description
### 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?
- [X] Yes, I have
### Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?
- [X] Yes, I have
Contributor guide
Research direction
Start by reproducing the PostgreSQL query in the issue against a JSON column and trace the PGDuckDB/CreatePlan path named in the error. Compare how the #> and #>> operators are handled there, then verify both examples execute successfully without syntax errors.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- cpp, postgres
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 38/100