I have a SOLR search index on one of my Cassandra table.
To depict the problem here is a hypothetical table and query.
"Please pardon me for bad design, this is just hypothetical table, concentrating on describing the problem I’m facing”
Lets assume this is the table
my_keyspace.cust_items(
cust_name text,
order_date date,
oder_id uuid,
item_id uuid,
item_name text,
item_desc text
item_props map<text,text>,
primary key((cust_name),order_date,order_id, item_id));
I’m using DSE (datastax enterprise) 6.x. So to query the data I’m leveraging DSE SEARCH For the sake of simplicity, lets assume all the calumn in above table are part of SOLR index.
CREATE SEARCH INDEX ON my_keyspace.cust_items;
So now when I want to find an item with name as ITM1 for a customer named CUST1 between date1 & date2, I use following query.
SELECT *
FROM my_keyspace.cust_items
WHERE solr_query = ‘{“q": “cust_name:CUST1 && item_name:ITM1
&& order_date:[date1 TO date2]”}'
Until here, everything works great.
What I’m unable to do..
for a given customer, date-range and order-id,I want to find Items, which don’t have a specific item property.
I store these properties under a map type column item_props.
So in this case lets say I want to fetch the items where item_props_prop1 has no value,
below CQL should return data but it doesn’t.
SELECT *
FROM my_keyspace.cust_items
WHERE solr_query = ‘{“q": “cust_name:CUST1 && order_id:123
&& item_props_prop1:'' && order_date:[date1 TO date2]”}'
All rows have key item_props_prop1 in item_props map column, but in DS Studio even when the value is shown “” the above query doesn’t fetch the data.