0

I have the following table

CREATE TABLE shipment (
    shipment_id TIMEUUID,
    dropoff_contact person,
    job_ids list<TIMEUUID>,
    deleted boolean,
    PRIMARY KEY(shipment_id)
);
CREATE INDEX IF NOT EXISTS job_ids ON loadex.shipment (job_ids);

Is there any way to query this table for empty job_ids:

I have tried these, they are not working:

SELECT * FROM shipment where job_ids=[]

SELECT * FROM shipment where job_ids=null

Hussain Wali
  • 308
  • 3
  • 12
  • No, there is not any way to query by an empty/null collection. – Aaron Apr 01 '21 at 17:49
  • @Aaron Then how can I do this? what is the work around what do you suggest? – Hussain Wali Apr 02 '21 at 05:31
  • workaround: I have added another boolean column called `assigned` and created an index on that. I am handling if list is empty set assigned to false on the application level. – Hussain Wali Apr 02 '21 at 06:28
  • It’s a by-product of Cassandra’s storage design. Finding a value of null/empty requires an exhaustive scan of all data. It’s something that’s easier for a RDBMS to handle. Also, creating a secondary index on a low cardinality column (ex: Boolean) is not going to perform well at all. – Aaron Apr 02 '21 at 11:19
  • should I add a primary composite primary key then? – Hussain Wali Apr 02 '21 at 11:49
  • also should i use materialized views in a production app? cassandra documentation say MVs are experimental in the latest 4.0 release. – Hussain Wali Apr 02 '21 at 11:55

0 Answers0