Pokemon Solr with CQL

docker-compose.yaml

version: '3.7'
volumes: { }
services:

  dse:
    image: datastax/dse-server:6.8.26
    environment:
      - DS_LICENSE=accept
      - SSL_VALIDATE=false
    volumes:
      - ./data:/var/lib/cassandra/data
    expose:
      - 9042
      - 8983
    ports:
      - "9042:9042"
      - "8983:8983"
    container_name: dse
    command:
      - -s
    networks:
      ds:
        aliases:
          - dse

networks:
  ds: {}

PokemonSolr.cql

create keyspace pokedex with replication = {
  'class': 'NetworkTopologyStrategy',
  'dc1': 1
};

drop table if exists pokedex.pokemons;

create table pokedex.pokemons(
  name text,
  primary_type text,
  secondary_type text,
  base_stat int,
  location text,
  legendary boolean,
  weakness frozen<set<text>>,
  height decimal,
  weight decimal,
  primary key (name)
);

insert into pokedex.pokemons(name, primary_type, base_stat, location, weakness, height, weight)
values('pikachu', 'electric', 320, '25.749537,-80.258957', {'ground'}, 0.4, 6.0);
insert into pokedex.pokemons(name, primary_type, secondary_type, base_stat, location, legendary, weakness, height, weight)
values('articuno', 'ice', 'flying', 550, '-34.915440,138.595520', true, {'electric', 'rock', 'fire', 'steel'}, 1.7, 55.4);
insert into pokedex.pokemons(name, primary_type, base_stat, location, weakness, height, weight)
values('charmeleon', 'fire', 405, '30.494345,-90.916779', {'rock', 'water', 'ground'}, 1.1, 19.0);
insert into pokedex.pokemons(name, primary_type, secondary_type, base_stat, location, weakness, height, weight)
values('dragonite', 'dragon', 'flying', 600, '33.799973,-117.934158', {'rock', 'fairy', 'ice', 'dragon'}, 2.2, 210.0);
insert into pokedex.pokemons(name, primary_type, secondary_type, base_stat, location, weakness, height, weight)
values('baxcalibur', 'dragon', 'ice', 600, '38.937969,-77.282272', {'fairy', 'steel', 'dragon', 'fighting', 'rock'}, 2.1, 210.0);
insert into pokedex.pokemons(name, primary_type, secondary_type, base_stat, location, legendary, weakness, height, weight)
values('chien-pao', 'dark', 'ice', 570, '35.658455,139.745026', true, {'fire', 'fighting', 'bug', 'rock', 'steel', 'fairy'}, 1.9, 152.2);
insert into pokedex.pokemons(name, primary_type, secondary_type, base_stat, location, legendary, weakness, height, weight)
values('koraidon', 'fighting', 'dragon', 670, '43.130077,-80.758316', true, {'fairy', 'ice', 'dragon', 'psychic', 'flying'}, 2.5, 303.0);
insert into pokedex.pokemons(name, primary_type, base_stat, location, weakness, height, weight)
values('arcanine', 'fire', 555, '-6.129323,28.986500', {'ground', 'rock', 'water'}, 1.9, 155.0);

DROP SEARCH INDEX on pokedex.pokemons;

CREATE SEARCH INDEX
ON pokedex.pokemons
WITH COLUMNS 
  name { indexed:true, docValues:true }, 
  primary_type { indexed:true, excluded:false },
  secondary_type { indexed:true, excluded:false },
  base_stat {indexed:true, docValues:true },
  location {indexed:true},
  weight {indexed:true},
  height {indexed:true},
  legendary {indexed:true},
  weakness {indexed:true, docValues: true}
AND CONFIG { realtime:true }
AND PROFILES spaceSavingNoJoin;

ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
ADD types.fieldType[@class='org.apache.solr.schema.TextField', @name='TextField'];


ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
ADD types.fieldType[@class='org.apache.solr.schema.TrieDoubleField', 
@name='points', @precisionStep='8', @omitNorms='true', @positionIncrementGap='0'];

ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
ADD types.fieldType[@class='org.apache.solr.schema.LatLonType', @name='geo_location', @subFieldSuffix='_point'];

ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
ADD types.fieldType[@class='org.apache.solr.schema.BoolField', @name='boolean'];


ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
ADD fields.field[ @name='_type', 
                  @type='TextField', 
                  @multiValued='true'];

ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
ADD fields.dynamicField[ @name='*_point', 
                  @type='points', 
                  @indexed='true',
                  @stored='false'];

ALTER SEARCH INDEX SCHEMA on pokedex.pokemons ADD copyField[@source='primary_type', @dest='_type'];
ALTER SEARCH INDEX SCHEMA on pokedex.pokemons ADD copyField[@source='secondary_type', @dest='_type'];


ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
SET field[ @name='location']@type='geo_location';

ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
SET field[ @name='legendary']@type='boolean';

ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
SET field[ @name='weakness']@type='StrField';
ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
SET field[ @name='weakness']@multiValued='true';


ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
SET field[ @name='height']@type='DecimalStrField';

ALTER SEARCH INDEX SCHEMA on pokedex.pokemons 
SET field[ @name='weight']@type='DecimalStrField';



describe pending search index schema on pokedex.pokemons;
describe pending search index config on pokedex.pokemons;

RELOAD SEARCH INDEX ON pokedex.pokemons;
REBUILD SEARCH INDEX on pokedex.pokemons;

### list all pokemons
select name, base_stat, primary_type, secondary_type, location, height, weight, weakness from pokedex.pokemons 
where solr_query='{"q":"*:*"}';
#http://localhost:8983/solr/pokedex.pokemons/select?q=*%3A*&wt=json&indent=true&omitHeader=true

### search for articuno
select name, base_stat, primary_type, secondary_type, height, weight, weakness from pokedex.pokemons 
where solr_query='name:articuno';
#http://localhost:8983/solr/pokedex.pokemons/select?q=*%3A*&fq=name%3Aarticuno&wt=json&indent=true

### find pokemons whose base_stat is at least 570 or greater
select name, base_stat, primary_type, secondary_type from pokedex.pokemons 
where solr_query='{"q":"*:*", "fq":"base_stat:[570 TO 700]", "sort": "base_stat desc"}';
#http://localhost:8983/solr/pokedex.pokemons/select?q=*%3A*&fq=base_stat%3A%5B570+TO+700%5D&sort=base_stat+desc&wt=json&indent=true

### find dragon type
select name, base_stat, primary_type, secondary_type, height, weight, weakness from pokedex.pokemons 
where solr_query='{"q":"_type:dragon", "sort":"name asc"}';
#http://localhost:8983/solr/pokedex.pokemons/select?q=*%3A*&fq=_type%3Adragon&sort=name+asc&wt=json&indent=true

### weak against fire or water
select name, base_stat, primary_type, secondary_type, height, weight, weakness from pokedex.pokemons 
where solr_query='{"q":"weakness:(fire OR water)", "sort":"name asc"}';
#http://localhost:8983/solr/pokedex.pokemons/select?q=*%3A*&fq=weakness%3A(fire+OR+water)&sort=name+asc&wt=json&indent=true

### taller than 1.5m
select name, base_stat, primary_type, secondary_type, height, weight, weakness from pokedex.pokemons 
where solr_query='{"q":"*:*", "fq":"height: [1.5 TO 5]", "sort":"name asc"}';
#http://localhost:8983/solr/pokedex.pokemons/select?q=*%3A*&fq=height%3A%5B1.5+TO+5%5D&sort=name+asc&wt=json&indent=true

### within 500km of Kyoto
35.011665,135.768326
select name, base_stat, primary_type, secondary_type,location, height, weight, weakness from pokedex.pokemons 
where solr_query='{"q":"*:*", "fq":"{!geofilt sfield=location pt=35.011665,135.768326 d=500}", "sort":"name asc"}';

#http://localhost:8983/solr/pokedex.pokemons/select?q=*%3A*&fq=%7B!geofilt+sfield%3Dlocation+pt%3D35.011665%2C135.768326+d%3D500%7D&wt=json&indent=true