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.