Distinct-lik behaviour in query

[Vincent Van Mulders]

Using 4.0 nightly

If I have index entries like:

{ pos: 4, season: 1, lane: 3 }, 

{ pos: 3, season: 2, lane: 4 }, 

{ pos: 2, season: 1, lane: 3 }, 

{ pos: 2, season: 3, lane: 1 },

{ pos: 3, season: 2, lane: 2 }

I expect output like

{

        pos : [2,3,4],

season : [1,2,3],

lane : [1,2,3,4]

}

I'm looking for mysql distinct-like behaviour at query-time

Query would be something like :

from index 'index-name' as i

where i.pos == 1 && i.lane == 3

select distinct(i.pos) as pos,distinct(i.lane) as lane

Index is fanout that exposes the fields to query on

Is something like this possible wih just query syntax in 4.0 or do I need more, like facets?

[Oren Eini (Ayende Rahien)]

Distinct is applied to the whole resultset.

from index 'index-name' as i

where i.pos == 1 && i.lane == 3

select distinct i.pos as pos, i.lane as lane

Hibernating Rhinos Ltd  

Oren Eini l CEO l Mobile: + 972-52-548-6969

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

 

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Vincent Van Mulders]

Must have done something wrong on earlier syntax  tries i found via the python client

This approach works for now but since it outputs every possible entry,i'll probably need to change it later ( some result sets are 3k+ entries)

I have a map-reduce that outputs index entries like this

{

    "season": 6,

    "osQue": 1,

    "osPatch": 601,

    "summonerId": 44951859,

    "c": 18,

    "pos": 4,

    "count": 2,

    "k": 16,

    "d": 6,

    "a": 12,

    "@metadata": {

        "@collection": "",

        "@flags": "Artificial, FromIndex"

    }

}

And i'm trying to query on this index via 

from index "" as i

group by i.c

where i.summonerId  == 44951859 and i.osPatch == 722

order by i.count desc

select i.c

Trying to group on the c field but I seem to be getting duplicates in the result set

From what I've read this should be correct syntax with the needed order by added

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.

[Oren Eini (Ayende Rahien)]

group by is case sensitive, it might be that

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[Vincent Van Mulders]

Am I wrong to think that I can use the output of a map/reduce to reduce further in the query?

If I reduce on lets say

group r by new {

       r.season,

      r.summonerId,

  r.c

} into g

And in my query

group by c as long

It will take the map reduce entries where my current <where> clause matches and group them by r.c?

I'm looking to use a map/reduce so I dont have to create the index entries as documents myself

Does the 'as long' work on group by clauses?

[Oren Eini (Ayende Rahien)]

No, group by cannot work on the output of the index.

The projection (on a single document) can allow you to do group by inside the same result, though.

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[Oren Eini (Ayende Rahien)]

from index 'foo' as s

select {

   Sum: s.reduce( (a, b) => a+b, 0)

}

[Vincent Van Mulders]

This data structure is really annoying me

I tried out the "output as collection"

Documents like this

{

    "season": 7,

    "summonerId": 45174200,

    "c": 62,

    "count": 1,

    "@metadata": {

        "@collection": "lol_game_player_stats",

        "@flags": "Artificial, FromIndex"

    }

}{

    "season": 7,

    "summonerId": 31661247,

    "c": 115,

    "count": 5,

    "@metadata": {

        "@collection": "lol_game_player_stats",

        "@flags": "Artificial, FromIndex"

    }

}

With an added index on top of these

from d in docs.lol_game_player_stats

select new {

    d.season,

    d.summonerId,

    d.c,

    d.count

}

With query

from index test as i

group by i.c

where i.summonerId == 44951859 and i.c == 51

order by i.count as long desc

And still not getting grouped data

I'm considering moving this part to an sql-like solution (since it's just rows and rows of data i wonna perform calculations on essentially and I can't leverage the precompute aspect of raven here anyways)

[Oren Eini (Ayende Rahien)]

Please create a full test case, as I'm not following

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[Vincent Van Mulders]

Trying to get one together,haven't used c# in years

I think I got my misunderstanding down to an easy example though

I added in a test db with the needed docs and index

Reading through this 

https://ravendb.net/docs/article-page/3.5/java/indexes/querying/dynamic-aggregation
The Orders/All index used in the query is a simple map-only index.

Tells me that I can combine a map-index and dynamic aggregation ( unless this is changed in 4.0)

Querying on the collection in the test db 

from test

group by FieldToGroupOn

select FieldToGroupOn ,sum(CountIWantSumOf)

Works as expected but creates an automatic map-reduce index

Creating my own index via

from d in docs.test

select new {

    d.FieldToGroupOn,

    d.CountIWantSumOf

}

And quering like so

from index 'manual-index' 

group by FieldToGroupOn 

select FieldToGroupOn,sum(CountIWantSumOf)

Does not group at all

Am I not supposed to be able to aggregate in query on a map-index?

What does this 

FacetResults facetResults = session .query(Order.class, Orders_All.class) .where(o.total.gt(500)) .aggregateBy(o.product) .sumOn(o.total) .toList();

Translate to in 4.0 RQL?

[Grisha Kotler]

from index 'manual-index'

select facet(FieldToGroupOn, sum(CountIWantSumOf))

Hibernating Rhinos Ltd  

Grisha Kotler l RavenDB Core Team Developer l Mobile: +972-54-586-8647

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

RavenDB paving the way to "Data Made Simple" http://ravendb.net/

[Vincent Van Mulders]

I'm digging deeper into facets

Index

from d in docs.lol_game

select new

{

    d.season,

    d.osQue,

    d.osPatch,

}

Facet doc ( "facets/test" )

{

    "Facets": [

        {

            "FieldName": "season",

            "Mode": "Default",

            "Ranges": []

        },

        {

            "FieldName": "osQue",

            "Mode": "Default",

            "Ranges": []

        },

        {

            "FieldName": "osPatch",

            "Mode": "Default",

            "Ranges": []

        }

    ],

    "@metadata": {

        "@collection": "Facets"

    }

}

And the url I'm using

/dbname/query=from index 'test' & facetSetupDoc=facets/test

Based this of old docs and from what I've found in the python client

Tried with different syntaxes like "facetSetupDoc", "facetDoc", adding "op=facets&" to the url

But I keep getting the documents indexed as results 

Missing something on the url part?

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.

[Oren Eini (Ayende Rahien)]

from index test

select facet(season) , facet(osQue) , facet(osPatch)

Hibernating Rhinos Ltd  

Oren Eini l CEO l Mobile: + 972-52-548-6969

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

 

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[Vincent Van Mulders]

This works great

Thank you for the solution