sql: jsonb comparator operators are not implemented

[timgraham]

Describe the problem

A clause like ("jsontest"."value" -> 'c') > '2' crashes (but works on PostgreSQL).

To Reproduce

CREATE TABLE jsontest (
    value jsonb
);
INSERT INTO jsontest VALUES ('{"c": 2}');
INSERT INTO jsontest VALUES ('{"c": 2.5}');
INSERT INTO jsontest VALUES ('{"c": 3}');
SELECT * FROM jsontest WHERE ("jsontest"."value" -> 'c') > '2';
SELECT * FROM jsontest WHERE ("jsontest"."value" -> 'c') > '2.33';

Expected behavior

Expected result (for both select queries):

{"c": 2.5}
{"c": 3}

Actual results:

ERROR: unsupported comparison operator: <jsonb> > <string>
SQLSTATE: 22023

Environment:

• CockroachDB version 20.1.0

Django 3.1 (to be released in August 2020) adds support for JSONField and this issue came up in a failing test.

Jira issue: CRDB-4253

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @rafiss (found keywords: Django)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[jordanlewis]

This would be fixed by moving toward a Postgres-style implicit cast model. We could also add a quick fix for it. @timgraham do you think this is a really useful thing, or is it one of those things that comes up just in tests?

[timgraham]

I don't think this case deserves special priority over similar issues (cockroachdb/django-cockroachdb#19, cockroachdb/django-cockroachdb#20, etc) that may be fixed by the more general solution you described.

[otan]

EDIT: nvm, misread this one!

[otan]

ah this is slightly more than i expected: we don't support jsonb comparison!

[email protected]:5436/json_path_multi_filtering_pg_json> select '{"a": 1}'::jsonb < '{"b": 2}'::jsonb;
ERROR: unsupported comparison operator: <jsonb> < <jsonb>
SQLSTATE: 22023

Prisma relies on this too, but I am skipping those tests.

[pbiggar]

I'm evaluating cockroachDB as a new DB for darklang. We use jsonb to store all user data, and then compile users' queries -- written in darklang -- to SQL. (Blog post on that here: https://blog.darklang.com/compiling-dark-to-sql/, and the implementation is here https://github.com/darklang/dark/blob/3e6fd9959e0ec9e87bce1f6033386e9c44d81c53/fsharp-backend/src/LibBackend/SqlCompiler.fs).

To be able to switch over, we would need to support < and > operators in jsonb, as numerical comparisons are a pretty common use case in queries.

[saurik]

@pbiggar I would expect you are extracting a specific field and comparing that--to perform a "numerical comparison"--not comparing the JSON object values themselves, and so you should not need this feature?

[pbiggar]

I would expect you are extracting a specific field and comparing that--to perform a "numerical comparison"--not comparing the JSON object values themselves, and so you should not need this feature?

We need to make the sort of query listed in this issues description:

SELECT * FROM jsontest WHERE ("jsontest"."value" -> 'c') > '2';

We also allow this sort of query:

SELECT * FROM jsontest WHERE ("jsontest"."value" -> 'c') > ("jsontest"."value" -> 'd');

We do not support (or intend to support) this sort of query:

select '{"a": 1}'::jsonb < '{"b": 2}'::jsonb;

[saurik]

@pbiggar But you said you wanted to do a "numerical comparison". The reason those queries don't work is because, from a type perspective, jsontest.value->'c' might return anything, including an object or an array, and due to this issue there is no way to compare arbitrary jsonb values.

But, if you want to just extract fields whose type you know, this works fine (though it would be preferable, to me at least, to be able to directly cast a jsonb to a decimal instead of having to first get its value as a string, but that's a different issue from being unable to compare jsonb values):

xxx@:26257/defaultdb> SELECT * FROM jsontest WHERE ("jsontest"."value" ->> 'c')::decimal > 2;
    value
--------------
  {"c": 2.5}
  {"c": 3}
(2 rows)

[pbiggar]

@saurik Ah, I see! Yes, that's exactly what I need. Sounds like it's already supported. I always know the types and the sql I generate already has appropriate type-casts. Thanks!

[timgraham]

The case in the issue description works in CockroachDB 23.2 nightly. (Not sure if it was fixed in a duplicate issue or as a side effect of another change, possibly 5cdb625.)