duckdb / duckdb/pg_duckdb

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

Open
#502 4 comments 0 reactions 0 assignees View on GitHub
JSON
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.