Search using CQL

Tutorial steps to get started using CQL to search the database.

Procedure

  1. Start cqlsh.
  2. Search the family_size field to find the ids of families of 6 or more.
    SELECT id FROM nhanes_ks.nhanes WHERE solr_query='family_size:6' LIMIT 3;
     id
    -------
     13322
     36213
      8856
    
    (3 rows)
  3. Perform a search for the ids of subjects in a Federal Information Processing Standards (fips) region that starts with the letters “Il” and whose ethnicity starts with the letters “Me”.
    SELECT id FROM nhanes_ks.nhanes WHERE solr_query='fips:Il* AND ethnicity:Mex*' LIMIT 5;
     id
    -------
     48654
     11298
     36653
     35025
     35344
    
    (5 rows)
  4. Perform a fuzzy search for subjects are non-Hispanic.
    select id, ethnicity FROM nhanes_ks.nhanes WHERE solr_query='ethnicity:"~Hispanic"' LIMIT 10;
    id    | ethnicity
    -------+----------------
     38875 |   Not Hispanic
      7789 |   Not Hispanic
     50309 |   Not Hispanic
     38721 |   Not Hispanic
     48797 |   Not Hispanic
     46146 |   Not Hispanic
     49842 | Other Hispanic
     47675 |   Not Hispanic
     13861 |   Not Hispanic
     13014 |   Not Hispanic
    
    (10 rows)
  5. Perform a range search for ids of subjects who are from 551 to 590 months old.
    SELECT id FROM nhanes_ks.nhanes WHERE solr_query='age_months:[551 TO 590}' LIMIT 3;
     id
    -------
    50309
    40371
    32907
                        
    (3 rows)
  6. Perform a JSON-based query that searches for the ids of subjects whose ethnicity is Mexican-American. Sort the results by id in descending order.
    SELECT id FROM nhanes_ks.nhanes WHERE solr_query='{"q":"ethnicity:Mexi*", "sort":"id asc"}' LIMIT 3;
     id
    -------
     53582
     53592
     53595
     
    (3 rows)