Click here to Skip to main content
15,744,500 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
Hello All,

*** Updates:
1) To clarify this is an Apache SOLR project that will be consumed from JavaScript MVC using JSONiq as the middle man.

2) I have found a solution which i added and accepted.

Pretty stuck here and I am hoping you might be the person to help me out. I am working with SOLR and JSONiq which are totally new to me and doing even the simplest of things is just escaping me. I know SQL pretty well however this simple requirement seems escape me. I'll jump right into it.

Here is the schema of my 'Core':

   <field name="BuID" type="int" indexed="true" stored="true" required="true"/>
   <field name="BusinessDate" type="text_general" indexed="true" stored="true" required="true"/>
   <field name="BusinessDateTime" type="date" indexed="true" stored="true" />
   <field name="Name" type="text_general" indexed="true" stored="true" required="true"/>
   <field name="BeginTime" type="text_general" indexed="true" stored="true" required="true"/>
   <field name="BeginDateTime" type="date" indexed="true" stored="true" />
   <field name="TransCount" type="int" indexed="true" stored="true" required="true"/>
   <field name="NetSales" type="float" indexed="true" stored="true" required="true"/>


I need to group by the month of BusinessDateTime and sum up NetSales and TransCount for a given date range. Now if this were SQL i would just right

SELECT sum(TransCount), sum(NetSales)
WHERE BusinessDateTime BETWEEN '2012/04/01' AND '2013/04/01'
GROUP BY MONTH(BusinessDateTime)

But ofcourse nothing is this simple with SOLR and/or JSONiq. I have tried messing around with Facet and Group but they never seem to work the way i want them to. For example here is a query i am currently playing with:


Now the facet is working properly however it is returning the count of the documents however i need the sum of the NetSales and the TransCount fields instead.

Any help or suggestions would be greatly appreciated.
Also, if i left anything out please feel free to post a comment so i can provide more detail.

Updated 19-Mar-13 7:05am
Prasad Khandekar 18-Mar-13 14:49pm    
Are you referring to Apache SOLR project?
Adam R Harris 18-Mar-13 15:04pm    
Yes, i should have indicated that. I will update the question to reflect this comment.
Prasad Khandekar 18-Mar-13 15:51pm    
Hello Adam,

Apache SOLR to my best knowledge is a Text Search Engine and Function Query (Which You are using) allows one to use the actual value of a field and functions of those fields in a relevancy score. So it's always going to return you the matching documents and won't function as regular SQL.
Adam R Harris 19-Mar-13 12:53pm    
You are 100% correct. Turns out i need to use stats to get the data i need. Check my 'solution' for more details.

1 solution

Well after searching like crazy and with the help of my boss and the SOLR mailing list i have managed to come up with a solution that i would like to post in case someone in the future needs to accomplish a similar thing.

What I ended up doing is using SOLR stats combined with facets to get the NetSales and TransCount for each day.

&fq=BusinessDateTime:[2012-03-01T00:00:0Z TO 2013-03-01T23:59:5Z]

Then in JSONiq i transform the data using this function:
import module namespace functx = "";

variable $t := [data];

let $stats := $t("stats")
let $statFields := $stats("stats_fields")
let $transCount := $statFields("TransCount")

let $netSales := $statFields("NetSales")
let $netSalesFacets := $netSales("facets")("BusinessDateTime")
let $dates := jn:keys($netSalesFacets) 

let $concat := for $sales in $netSalesFacets, $d in $dates, $trans in $netSalesFacets
                let $dets := jn:value($sales, $d)
                let $tranDet := jn:value($trans, $d)
                return {
                    'Date' : $d,
                    'sum' : $dets("sum"),
                    'Tsum' : $tranDet("sum")

let $results := for $stat in jn:keys($statFields)
                return $stat

return $concat

I still need to break out the actual data from the string however i already have that worked out just not implemented in the sample above. If you have stumbled across this and are in need of the additional code to convert the date to an actual date object and then get the month and year for grouping just throw a comment on here and ill update my solution or reply to your comment.
Share this answer
Prasad Khandekar 19-Mar-13 13:52pm    
Hello Adam,

Thank's for sharing the solution. My vote of 5.
Adam R Harris 19-Mar-13 14:34pm    
Thank you Prasad.
wenky_t 25-Jun-14 3:03am    
Im also wandering for this solution.but this solution is not acceptable in my application.My requirement si to calculate the average value of a column from database and save it in solr Document without saving the details.
Can anyone help me this regard.I have been trying using mysql aggregate methods,but it is not working.
Does Solr have any restictions in executing aggrgate functions..please let me know.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900