Question on json index

I have list of movies json data that are stored in a table, and I have enabled inverted index on the json column. I have 800k rows in my table. With the following query its super fast,

select count(*) from movies where movie @> ‘{“run_time”:4882}’;


select count(*) from movies where movie->>‘run_time’ = ‘4882’; is dead slow. I just wonder why cockroachdb wont automatically infer there is an index available, and perform the computation based on the index? If cockroachdb does then, it will pave the way for more efficient solutions with complex operations. For example, I can retrieve an attribute from a row, and then do a join against that. Without that, json operations in cockroachdb is not really that great for json data.

I’m just wondering what are your thoughts on this.

Thanks for writing in. There’s no strong reason why this doesn’t work yet - we just haven’t gotten around to it. I filed an issue here:

Thank you for the quick turnaround.

with movies_ids as (select movie->>‘cluster_id’ cid, movie->>‘reference_id’ rid from movies) select cid, count(*) from movies_ids group by cid;

In the above query, can we expect index being used for the group by operation if the inverted index is enabled?