Escaping string double quote in JSON Path Not Working

[liveuser123]

What happens?

I’m encountering an issue with DuckDB when trying to extract a key from a JSON object using the json_extract function. The key contains special characters, specifically the string quote (") and the square bracket ([),
As per the DuckDB documentation, I am using double quotes in the JSON path (e.g., $.) to escape the special characters. However, my key itself contains a double quote, which is causing the issue. The query fails with a JSON path error. I’ve tried escaping the double quote in the key with a backslash (), but it doesn’t seem to work
Any help or guidance on how to correctly escape special characters in JSON paths, especially double quotes within the key, or a potential fix for this issue would be greatly appreciated. Thank you!

To Reproduce

Here’s an example of the query that’s causing the problem:

select json_extract_string(json('{"j[so]n_\"key": 67}'), '$."j[so]n_\"key"');

or

select json('{"j[so]n_\"key": 67}')->>'$."j[so]n_\"key"';

gives error

Binder Error: JSON path error near 'key"'

OS:

Ubuntu 20.04.5 LTS

DuckDB Version:

0.10.2

DuckDB Client:

python

Full Name:

Sam

Affiliation:

Freelancing

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

I have tested with a stable release

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

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

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

• Yes, I have

[gitccl]

json_extract_string(json('{"j[so]n_\"key": 67}'), '/j[so]n_"key') works.

[liveuser123]

What if the key has another forward slash with double quotes
eg select json_extract_string(json('{"j[so/]n_\"key": 67}'), '/j[so/]n_"key')