Indexing map columns

DSE Search indexes a CQL map column using an Apache Solr™ dynamic field. Dynamic fields apply the field definition using a wildcard match on the name. In the search index schema, DSE Search sets the dynamic field name to the CQL column name with an asterisk appended. DSE Search parses the data from a map using the key name and Solr will index only the keys that have the column name as the prefix. Keys that do not have the column name as a prefix are ignored.

For example, a search index with default settings on a map column named blist_ has the following definition:

<dynamicField indexed="true" multiValued="false" name="blist_*" type="StrField"/>

When DSE Search builds the index from the CQL rows, the key name is used, not the column name. Therefore, all keys that have the blist_ as the prefix in the example are indexed and the rest are ignored. Only blist_age and blist_nation are indexed when the following data is inserted:

INSERT INTO cycling.birthday_list (
  cyclist_name, blist
) VALUES (
  'Allan DAVIS', { 'age':'35', 'bday':'27/07/1980', 'nation':'AUSTRALIA' }
);

All key-value pairs in CQL maps have the same data type. The map in the example above sets all values to text (blist map<text,text>). Because DSE Search loads the data by mapping the key name to the Solr dynamic field name, you can customize field type for each key.

The following example creates a customized Search index for data that has the same three map keys in every record, blist_age, bday, and blist_nation. Only blist_age and blist_nation are indexed.

Create a keyspace named cycling:

CREATE KEYSPACE IF NOT EXISTS cycling
WITH REPLICATION = {
  'class' : 'SimpleStrategy',
  'replication_factor' : 1
};

Create a table called birthday in the cycling keyspace:

CREATE TABLE IF NOT EXISTS cycling.birthday_list (
  cyclist_name text PRIMARY KEY,
  blist map<text, text>
);

Create an index that excludes the blist_ map column:

CREATE SEARCH INDEX IF NOT EXISTS ON cycling.birthday_list
    WITH COLUMNS blist { excluded:true };

View the active schema:

DESC ACTIVE SEARCH INDEX SCHEMA ON cycling.birthday_list;

DSE Search sets CQL text to Solr StrField type:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<schema name="autoSolrSchema" version="1.5">
  <types>
    <fieldType class="org.apache.solr.schema.StrField" name="StrField"/>
  </types>
  <fields>
    <field indexed="true" multiValued="false" name="cyclist_name" type="StrField"/>
  </fields>
  <uniqueKey>cyclist_name</uniqueKey>
</schema>

To set blist_age to an integer, define the blist_age type and configure a field definition:

ALTER SEARCH INDEX SCHEMA ON cycling.birthday_list
ADD fields.field[ @indexed='true', @multiValued='false', @name='blist_age', @type='TrieIntField' ];

Define the blist_nation field as a string type, which has a corresponding type definition of @type='StrField':

ALTER SEARCH INDEX SCHEMA ON cycling.birthday_list
ADD fields.field[ @name='blist_nation', @indexed='true', @multiValued='false', @type='StrField' ];

View the pending changes to the schema to ensure that the syntax is correct:

DESC PENDING SEARCH INDEX SCHEMA ON cycling.birthday_list;

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<schema name="autoSolrSchema" version="1.5">
  <types>
    <fieldType class="org.apache.solr.schema.StrField" name="StrField"/>
    <fieldType class="org.apache.solr.schema.TrieIntField" name="TrieIntField"/>
  </types>
  <fields>
    <field indexed="true" multiValued="false" name="cyclist_name" type="StrField"/>
    <field indexed="true" multiValued="false" name="blist_age" type="TrieIntField"/>
    <field indexed="true" multiValued="false" name="blist_nation" type="StrField"/>
  </fields>
  <uniqueKey>cyclist_name</uniqueKey>
</schema>

Reload the index configuration and schema to push the changes live:
```
RELOAD SEARCH INDEX ON cycling.birthday_list;
```

Rebuild the index whenever fields are added:

REBUILD SEARCH INDEX ON cycling.birthday_list;

Use the map fields to filter queries:

Limit by age 23:

SELECT * FROM cycling.birthday_list WHERE solr_query = 'blist_age:23';

 cyclist_name     | blist
------------------+----------------------------------------------------------
   Claudio HEINEN | {'age': '23', 'bday': '27/07/1992', 'nation': 'GERMANY'}
 Laurence BOURQUE |  {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}

(2 rows)

Limit by nation GERMANY, which is case-sensitive because the type is a string:

SELECT * FROM cycling.birthday_list WHERE solr_query = 'blist_nation:GERMANY';

Indexing map columns

Was this helpful?

Give Feedback