SOLR Group BY and SUM

Question

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':

XML

<fields>

   <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"/>

</fields>

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

SQL

SELECT sum(TransCount), sum(NetSales)
FROM Core
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:

?wt=json
&indent=true
&q=*:*
&rows=0
&facet=true
&facet.date=BusinessDateTime
&facet.date.start=2012-02-01T00:00:01Z
&facet.date.end=2013-02-01T23:59:59Z
&facet.date.gap=%2B1MONTH
&group=true
&group.field=BusinessDateTime
&group.facet=true
&group.field=NetSales

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.

Thanks,
Adam

Posted 18-Mar-13 6:23am

Adam R Harris

Updated 19-Mar-13 7:05am

v3

Add a Solution

Comments

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Adam R Harris · Accepted Answer · 2013-03-19T07:03:00

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.

wt=json
&indent=true
&q=*:*
&fq=BusinessDateTime:[2012-03-01T00:00:0Z TO 2013-03-01T23:59:5Z]
&rows=0
&stats=on
&stats.field=NetSales
&stats.field=TransCount
&stats.facet=BusinessDateTime

Then in JSONiq i transform the data using this function:

import module namespace functx = "http://www.functx.com/";

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.