How to select different field values ​​using Solr?

Question

I would like to make the equivalent of this SQL, but with Solr as the data store.

SELECT DISTINCT txt FROM my_table; 

What syntax will make Solr give me only different meanings?

 http://localhost:8983/solr/select?q=txt:?????&fl=txt 

EDIT: Such a grandiose search seems appropriate, but when I examined it, I realized that I had only a detailed part of the problem.

My SQL query must be read ...

 SELECT DISTINCT SUBSTR(txt,0,3) FROM my_table; 

Any possibility of this with Solr?

Answer 1

Faceting will provide you with a result set containing various values ​​for the field.

eg.

 http://localhost:8983/solr/select/?q=*%3A*&rows=0&facet=on&facet.field=txt 

You should get something like this:

 <response> <responseHeader><status>0</status><QTime>2</QTime></responseHeader> <result numFound="4" start="0"/> <lst name="facet_counts"> <lst name="facet_queries"/> <lst name="facet_fields"> <lst name="txt"> <int name="value">100</int> <int name="value1">80</int> <int name="value2">5</int> <int name="value3">2</int> <int name="value4">1</int> </lst> </lst> </lst> </response> 

Check out the wiki for more information. Bounding is really the coolest part of solr. Enjoy :)

http://wiki.apache.org/solr/SimpleFacetParameters#Facet_Fields

Note. The boundary designation displays the indexed value, Ie, after all filters have been applied. One way around this is to use the copyfield method so that you can create a faceted version of the txt field. Thus, your results will show the original value.

Hope this helps. Most of the cutting documentation is available on the wiki. Or I wrote some screenshots that you can check here:

http://www.craftyfella.com/2010/01/faceting-and-multifaceting-syntax-in.html

Answer 2

For the DISTINCT part of your question, I think you can look for Solr functions to collapse / group fields . This will allow you to specify the field in which you want to get unique results, create a group for these unique values ​​and show you how many documents are in this group.

Then you can use the same substr , which is stored in a separate field, and collapse it.

Answer 3

I would save the substring in another field (call txt_substring ) and then the border on txt_substring , as CraftyFella showed.

Usually I used an n-gram tokenizer , but I don’t think you can do it.

Answer 4

Use StatsComponent with the stats.calcdistinct parameter to get a list of different values ​​for a specific field:

https://cwiki.apache.org/confluence/display/solr/The+Stats+Component

It will also give you a number of different values. (In the case of facets, you need to know the counter to request everything, or you set facet.limit to something really high and calculate the result yourself. In addition, you will need a string field to create the faces that you will need here.)

http://wiki.apache.org/solr/StatsComponent is deprecated as it does not cover stats.calcdistinct

stats.calcdistinct is probably available since 4.7.

Example:

 /select?stats=on&stats.field=region&rows=0&stats.calcdistinct=true "stats":{ "stats_fields":{ "region":{ "min":"GB", "max":"GB", "count":20276, "missing":0, "distinctValues":["GB"], "countDistinct":1}}}} 

Answer 5

take a look at faceted search

Answer 6

Solr 5.1 and later have the new Facet Module, which has built-in support for finding the number of unique values ​​in a field. You can even find the number of unique values ​​in the field for each bucket of the facet and sort by that value to find the highest or fewer unique values.

The number of unique values ​​in "myfield": json.facet = {x: 'unique (MyField)'}

Face in the "category" field, and for each category - the number of unique values ​​in the "color":

 json.facet={ cat_breakdown : { terms : { // group results by unique values of "category" field : category, facet : { x : "unique(color)", // for each category, find the number of unique colors y : "avg(price)" // for each category, find the average price } }} } 

This is in Solr 5.1 and later. More facet features such as "unique" are shown at http://yonik.com/solr-facet-functions/