JSONB filtering on numbers

json

(Max) #1

Hi there!

I’m trying to populate crdb with a timeseries dataset and I’m having some troubles with selection.

The table schema is the following:
create table test2(id int, data jsonb);

Inverted index:
CREATE INDEX test2_idx ON test2 USING GIN (data);

Then I insert couple of records:
insert into test2(id,data) values (1, '{"f": 1}'), (2, '{"f": 2}'), (11, '{"f": 11}'), (12, '{"f": 12}');

And so I try to retrieve those recrods where data.f > 10:

select * from test2 where data->'f' > '10'::JSONB order by id;
    pq: unsupported comparison operator: <jsonb> > <jsonb>

Casting to a string doesn’t produce an error but also doesn’t quite work as well:

root@cdb1:26257/c8> select * from test2 where data->>'f' > '10' order by id;
+----+-----------+
| id |   data    |
+----+-----------+
|  2 | {"f": 2}  |
| 11 | {"f": 11} |
| 12 | {"f": 12} |
+----+-----------+

So basically I just wanted to check if filtering by number json fileds is supported in any way?

CockroachDB version is 2.0.6

Thank you


(Ben Darnell) #2

You have to cast to a string (using the ->> operator) and then cast that string to an int for the comparison operator to work:

root@127.0.0.1:63535/defaultdb> select * from test2 where (data->>'f')::int > 10 order by id;

id | data

+----+-----------+

11 | {"f": 11}  

12 | {"f": 12}  

(2 rows)

Time: 1.5ms

This query is not able to take advantage of the inverted index you created. If you want to be able to use an index for this query, you need to create a computed column and add a regular index on that:

create table test3(id int, data jsonb, f int as ((data->>'f')::int) stored);
create index test3_idx on test3 (f);
insert into test3(id,data) values (1, '{"f": 1}'), (2, '{"f": 2}'), (11, '{"f": 11}'), (12, '{"f": 12}');
select * from test3 where f > 10;

(Max) #3

Thank you, Ben!

Does this mean that in general inverted indexes can be used only to query by equality, that is field=value, and for other filters like >,>=, <, <=, etc computed columns are required?


(Ben Darnell) #4

Correct, inverted indexes currently only support equality. I recommend using the @> operator when querying JSONB fields: where data @> '{"f": 10}'. Otherwise, the quoting gets confusing (if field f contains a number, you quote it like a string: where data->'f' = '10'. if field f contains a string, you put double quotes inside of single quotes: where data->'f' = '"xyz"'. The result of the -> operator is compared to a json-formatted string.

You can use the EXPLAIN command to verify that your queries are using the right index.