2

Hi we have a solr index with diff fields in it like business,businessType, regionName, StateName, .....

Now I need a solr query to get the number of business of type businessType ='event' group by regionName.

if I want to write a sql query for this it would be select region_name , Count(business) from solr where businessType='event' group by region_name

Any pointer would be helpful

Uttkarsh Jain
  • 228
  • 1
  • 12
  • Use facets. Your solr query will look like, q=*:*&fq=businessType:event&facet=true&facet.field=region_name&rows=0. – raghu777 Feb 03 '20 at 10:56
  • No it dint work well.. what I nee is like say for e.g. we have region A, 23 events .. region B, 21 events.. – Uttkarsh Jain Feb 03 '20 at 11:02
  • What is the difference between what was returned in the facet response from the query above and what you expected? It should give you the same result as what your SQL statement does, _provided_ that `region_name` field is a pure string field (and not tokenized or processed further). – MatsLindh Feb 03 '20 at 11:09
  • Its not returning me the count of businesses in that region. what I need is count of region for any region – Uttkarsh Jain Feb 03 '20 at 11:22
  • Considering the SQL query you have given the results should be matching. Just to Clarify, You need count of businesses in a every region whose business type is event. Considering this query I have given should work. – raghu777 Feb 03 '20 at 11:54
  • It was my mistake, it is working but its partially working .. we are getting the region names for which we have businesses. for the regions which are not having events it is not returning those events in the list – Uttkarsh Jain Feb 03 '20 at 16:18
  • is there a way to get a list of regions too where we don't have any business in that – Uttkarsh Jain Feb 03 '20 at 16:23
  • @raghu777 how about if I want to group by multiple fields say region, state and get count based on two group by – Uttkarsh Jain Feb 20 '20 at 04:19
  • Check pivots in facet. – raghu777 Feb 20 '20 at 04:47

2 Answers2

1

I finally figured out how to do this. Note, if you need to query on a field with a space or a special character, you need to put the search term in quotes, e.g. businessType:"(fun) event".

curl http://localhost:8983/solr/yourCollection/query -d 
{ "query"="*:*",
  "fq"="businessType:event",
  "rows"=0,
  "json.facet"= { "category" : {
    "type": "terms",
    "field" : "region_name",
    "limit" : -1 }}
}

One more Note: if you want to count over 2 fields, you have to do a nested facet.

curl http://localhost:8983/solr/yourCollection/query -d 
{ "query"="*:*",
  "fq"="businessType:event",
  "rows"=0,
  "json.facet"= { "category1" : {
    "type": "terms",
    "field" : "regionName",
    "limit" : -1,
      "facet" : { "category2" : {
        "type": "terms",
        "field" : "stateName",
        "limit" : -1
  }}}}
}

Add another facet chunk after the "limit":-1 item if you need to group by a third dimension. I tried this on my company's Solr and it hung, never returning anything but a timeout error. In general, working with Solr isn't very easy... and the documentation, IMO, is pretty terrible. And absolutely nothing about the syntax or names of the commands seem intuitive at all...

quickreaction
  • 675
  • 5
  • 17
0

Use facets. Your solr query will look like, q=:&fq=businessType:event&facet=true&facet.field=region_name&rows=0

if want to group by on multiple fields then we need to do facet.pivot=state,region_name

Uttkarsh Jain
  • 228
  • 1
  • 12
  • I know this has been awhile, but if grouping by multiple fields, does there need to be the removal of the facet.field instead using facet.pivot? Or is the facet.pivot just an addendum to the query? This is the problem with Solr documentation and examples... I've spent 3 days trying to understand a simple interpretation of SQL: COUNT, x, y GROUP BY x, y :: SOLR: ??? – quickreaction Jun 29 '22 at 15:47
  • @quickreaction check this commit https://github.com/Ukjain/solr-scala-client/commit/24f16dc951c9650ca1bd91191d5b315ac94257af out. We were using scala and we added facetpivotlist to make sure we get grouping on multiple fields. – Uttkarsh Jain Jul 07 '22 at 13:55