DSE search: how to find a row where a column whic is part of Search-index doesn’t have any value

38 views Asked by At

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.

0

There are 0 answers