%sql does not parse JSON operators appropriately

[maciejb]

What happens?

It seems that the -> operator which is used to access JSON fields, is incompatible with %sql and is being interpreted as an argument switch instead. I am unable to locate any methods for escaping this in the documentation, although it can be worked around by using %%sql blocks.

To Reproduce

Connect to a Postgres database, and issue the following:

%sql select '{"key": 1}'::jsonb -> 'key'

The resulting error

UsageError: unrecognized arguments: -> 'key'

OS:

macOS

JupySQL Version:

0.10.2

Full Name:

Maciej Bukczynski

Affiliation:

Darkhorse Analytics

[edublancas]

hi @maciejb, thanks for reporting this!

We've had similar issues to this one in the past. We inherited the %sql parsing logic from ipython-sql and we decided to keep it since changing it too much would make the ipython-sql -> jupysql transition more difficult, but that means we keep encountering these issues from time to time.

for now, using %%sql is the way to go. but we'll keep this open and tackle it so %sql doesn't think -> is an argument.

[maciejb]

Thanks, @edublancas! And thank you for your work on this important project :)

I'm not sure about this being inherited logic, though. We encountered this only upon transitioning from ipython-sql to jupysql, where this had been working without issue.

Also, in case it's useful for anyone else, if %%sql were undesirable in some situation, the Python variable approach also works around this successfully, e.g.:

query = "select '{\"key\": 1}'::jsonb -> 'key';"
%sql {{query}}

[edublancas]

ah, thanks for sharing this. I think we modified the parser recently so I guess we introduced the bug there. alright, we'll get to it!

[bryannho]

Should have a fix for this out soon, but as I was testing I wanted to document a few more errors/details I found.

The parsing bug occurs because the operator begins with -. The same thing happens with ->>. All other JSON operators seem to operate fine using %sql and %%sql.

Interestingly, removing any whitespace before the occurrence of -> or --> avoids the parsing bug. e.g:

%sql select '{"key": 1}'::jsonb -> 'key' returns an error, but

%sql select '{"key": 1}'::jsonb-> 'key' works fine.

[maciejb]

Not sure whether to open a new issue or not, but I found another case of this issue which #938 did not address: operations with negative numbers. For example:

[edublancas]

@maciejb: please open another issue!