Mailing-List: contact user-help@couchdb.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@couchdb.apache.org
Received-SPF: pass (athena.apache.org: domain of kinleyd@gmail.com designates
 209.85.212.52 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type:content-transfer-encoding;
        b=av/nqA+Jr+UPjNH9NkY93ylSEX00ygo1waKKRdKxgcERUZzBvC2AZdSuh4sR9IaX2V
         SnF4JWB6Aba+gkAa6/Z35LATBqkgdIv1XVtRW86ZEF0THv3177BMAm0TdguEiBzbJ6+P
         iPCd5Kmvm9j2ERCLUj0jU2Gc6tY2e72krNfYo=
MIME-Version: 1.0
In-Reply-To: <AANLkTi=3j+cVOJSbg76BHuJ==PjEWev9FjMhfeeamSWm@mail.gmail.com>
References: <AANLkTinaA5fQCW0hc-u0UWCPnWRE3wSkUZe25xPvoxkO@mail.gmail.com>
	<AANLkTimPnh40ALL4mS22c=KQRZXWCeShKz3OHXiKFe5c@mail.gmail.com>
	<AANLkTi=3j+cVOJSbg76BHuJ==PjEWev9FjMhfeeamSWm@mail.gmail.com>
Date: Mon, 14 Mar 2011 22:17:55 +0600
Message-ID: <AANLkTik_X8b=Rpt+ozo9+NmJYZqxO-xymiMHL=upqu2i@mail.gmail.com>
Subject: Re: filtering on timestamp + aggregation on another field
From: Kinley Dorji <kinleyd@gmail.com>
To: user@couchdb.apache.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Aroj,

Unlike in SQL, CouchDB views cannot be filtered by value. They can be
only filtered by key for performance reasons as the key is where the
indexing action takes place.

So the solution for you would be to have a view of this type:

emit (doc.timestamp, {country:doc.country, city:doc.city,
hospital:doc.hospital, rooms:doc.rooms}); // this representing the
output of the map stage

This would create a view that you could filter based on your start and
end time stamp keys for the desired period.

The aggregation would take place in your code in the reduce stage,
which would manipulate the json that you created on the value side of
your key/value pair for a particular time stamp to get whatever
aggregation you need.

Hope that helps.

On Mon, Mar 14, 2011 at 10:02 PM, Aroj George <arojis@gmail.com> wrote:
> Thanks Kinley. But just emitting the timestamp does not solve the problem
> for us.
>
> We want to be able to emit something like,
>
> [India,Mahrashtra,Pune,timestamp] =A0(where Pune is a city in state
> Maharashtra which is in country India)
>
> and then we would like to filter using the timestamp and group by locatio=
n
> on say India as,
>
> *startkey=3DIndia,%,%,timestamp1 & endkey=3DIndia,%,%,timestamp2 & group_=
level =3D
> 1*
>
> This should give me all results grouped by location India, but filtered o=
ut
> between the two timestamps.
>
> But ofcourse there is no % filter possible it seems.
>
> A SQL equivalent would be,
>
> select * from table where timestamp > timestamp1 and timestamp < timestam=
p2
> group by country.
>
> What's the right thing to do when faced with such a requirement?
>
> we could do a temp view like below?
>
> if (doc.timestamp > T1 & doc.timestamp < T2) emit(doc.location_path,doc)
>
> But aren't temp views considered bad for performance.
>
> Please do suggest the best way to solve this problem in couchdb.
>
>
> Rgds,
> Aroj
>
>
> On 14 Mar 2011 18:59, "Kinley Dorji" <kinleyd@gmail.com> wrote:
>