Can you use solr_query in Cassandra to find map field containing some particular value?

Question

I would like to use solr_query to find results which field of map type contains given key-value pair.

I'm trying to create a query similar to this:

SELECT * FROM my_table WHERE pathId= 5 AND solr_query='validity: [1970-01-01T00:01:00 TO *] ’ AND metadata[1] = '2' LIMIT 1 ALLOW FILTERING;

or

SELECT * FROM my_table where metadata['1'] = '2' AND solr_query=$${ "q": "pathid:5", "fq": "validity:[1970-01-01T00:01:00 TO *]";

however each time I'm getting ServerError: java.lang.IllegalArgumentException: Search queries must have only one index expression. error.

I would like to be able to somehow query with those 3 conditions in 'where' clause - pathid validity and metadata. Is it possible to query for map containing given key-value pair inside solr_query, or is there any other way to do that?

I have created a search index on fields:

create SEARCH index on my_table with columns validity, pathId, metadata;

Answer 1

Cassandra Map fields are handled in DSE Search as dynamic fields. In order to work with these it is important that the Map keys follow the pattern <fieldname>_<key>. So, in your case, you would have to insert keys with the form metadata_key1, metadata_key2 ...

If you follow that naming convention, you can then query the data as follows:

SELECT * FROM my_table where solr_query=$${ "q": "pathid:5 AND metadata_key1:2", "fq": "validity:[1970-01-01T00:01:00 TO *]";

Use of dynamic fields in DSE Search is documented here.