Sum and aggregation on nested documents field

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Sum and aggregation on nested documents field

jeebix
Hello everybody,

First, an example of a parent document with 2 nested documents :

{
        "id":"10891.10893.142693",
        "parent_i":10891,
        "asso_i":10893,
        "personne_i":142693,
        "etat_technique_s":"avec_documents",
        "zone_scolaire_s":"B",
        "territoire_s":"France Métropolitaine",
        "region_s":"PROVENCE-ALPES-COTE D'AZUR",
        "departement_s":"13 BOUCHES-DU-RHONE",
        "postal_country_s":"FR",
        "asso_country_s":"FRANCE",
        "object_type_s":"contact",
        "date_derni_re_commande_dt":"2017-11-21T00:00:00Z",
        "_version_":1605492468358316033,
        "_childDocuments_":[
        {
          "kit_sans_suite":["false"],
          "TTC_i":152,
          "type_cde_s":"KIT",
          "object_type":["order"],
          "statut_s":"V",
          "campagne_s":"G",
          "date_dt":"2017-09-28T00:00:00Z",
          "id":"G15608",
          "enseigne_s":"SAV",
          "gamme":["CATALOGUES > Catalogues Saveurs",
            "KITS > Kits Saveurs"]},
        {
          "object_type":["order"],
          "TTC_i":2793,
          "kit_sans_suite":["false"],
          "type_cde_s":"CDE",
          "statut_s":"V",
          "campagne_s":"G",
          "date_dt":"2017-11-21T00:00:00Z",
          "id":"G43625",
          "enseigne_s":"SAV",
         }
}

And i would like to get a sum of TTC_i field, by parent document... I tried
with stats, pivot, without any results...

Thanks for your help.

Best
JB



--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Reply | Threaded
Open this post in threaded view
|

Re: Sum and aggregation on nested documents field

Mikhail Khludnev-2
If you have  TTC_i as a function query on child level, and apply
score=total on the parent query.

On Mon, Jul 9, 2018 at 10:11 AM jeebix <[hidden email]> wrote:

> Hello everybody,
>
> First, an example of a parent document with 2 nested documents :
>
> {
>         "id":"10891.10893.142693",
>         "parent_i":10891,
>         "asso_i":10893,
>         "personne_i":142693,
>         "etat_technique_s":"avec_documents",
>         "zone_scolaire_s":"B",
>         "territoire_s":"France Métropolitaine",
>         "region_s":"PROVENCE-ALPES-COTE D'AZUR",
>         "departement_s":"13 BOUCHES-DU-RHONE",
>         "postal_country_s":"FR",
>         "asso_country_s":"FRANCE",
>         "object_type_s":"contact",
>         "date_derni_re_commande_dt":"2017-11-21T00:00:00Z",
>         "_version_":1605492468358316033,
>         "_childDocuments_":[
>         {
>           "kit_sans_suite":["false"],
>           "TTC_i":152,
>           "type_cde_s":"KIT",
>           "object_type":["order"],
>           "statut_s":"V",
>           "campagne_s":"G",
>           "date_dt":"2017-09-28T00:00:00Z",
>           "id":"G15608",
>           "enseigne_s":"SAV",
>           "gamme":["CATALOGUES > Catalogues Saveurs",
>             "KITS > Kits Saveurs"]},
>         {
>           "object_type":["order"],
>           "TTC_i":2793,
>           "kit_sans_suite":["false"],
>           "type_cde_s":"CDE",
>           "statut_s":"V",
>           "campagne_s":"G",
>           "date_dt":"2017-11-21T00:00:00Z",
>           "id":"G43625",
>           "enseigne_s":"SAV",
>          }
> }
>
> And i would like to get a sum of TTC_i field, by parent document... I tried
> with stats, pivot, without any results...
>
> Thanks for your help.
>
> Best
> JB
>
>
>
> --
> Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
>


--
Sincerely yours
Mikhail Khludnev
Reply | Threaded
Open this post in threaded view
|

Re: Sum and aggregation on nested documents field

jeebix
Thanks for your reply Mikhail, I tried like this :
"{!parent which=object_type_s:contact score=total v={!func}TTC_i}" in a
filter query, the same in the sort parameter, I also tried to put this query
"{!parent which=object_type_s:contact score=total}TTC_i" into the q
parameter, but no results...
I had a look to the dismax query parser, but I'm not sure it is a way to
solve my problem.

So what's wrong with my function query ? Did I explicite the child level
correctly ?

Best

JB



--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Reply | Threaded
Open this post in threaded view
|

Re: Sum and aggregation on nested documents field

jeebix
In reply to this post by Mikhail Khludnev-2
Thanks for your reply Mikhail, I tried like this :
"{!parent which=object_type_s:contact score=total v={!func}TTC_i}" in a
filter query, the same in the sort parameter, I also tried to put this query
"{!parent which=object_type_s:contact score=total}TTC_i" into the q
parameter, but no results...
I had a look to the dismax query parser, but I'm not sure it is a way to
solve my problem.

So what's wrong with my function query ? Did I explicite the child level
correctly ?

Best

JB



--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Reply | Threaded
Open this post in threaded view
|

Re: Sum and aggregation on nested documents field

Mikhail Khludnev-2
In reply to this post by jeebix
Hello, JB.
The rule of thumb if that you post what's you have whether it exception or
unexpected result with explain. Here I guess you need something like:
q={!parent which=object_type_s:contact score=total v=$chq}&chq=+
object_type:order {!func}TTC_i
Make sure that + isn't mangled by url ecoding ie you might need to make it
as %2b.

On Tue, Jul 10, 2018 at 11:13 AM jeebix <[hidden email]> wrote:

> Thanks for your reply Mikhail, I tried like this :
> "{!parent which=object_type_s:contact score=total v={!func}TTC_i}" in a
> filter query, the same in the sort parameter, I also tried to put this
> query
> "{!parent which=object_type_s:contact score=total}TTC_i" into the q
> parameter, but no results...
> I had a look to the dismax query parser, but I'm not sure it is a way to
> solve my problem.
>
> So what's wrong with my function query ? Did I explicite the child level
> correctly ?
>
> Best
>
> JB
>
>
>
> --
> Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
>


--
Sincerely yours
Mikhail Khludnev
Reply | Threaded
Open this post in threaded view
|

Re: Sum and aggregation on nested documents field

jeebix
Hello Mikhail,

First thanks a lot for your answers which are very useful for me... Then, I
tried the query with the '$' parameter, and I get some great result like
this :

"id":"693897",
        "asso_i":693897,
        "etat_technique_s":"avec_documents",
        "etat_marketing_s":"actif",
        "type_parent_s":"Société",
        "groupe_type_parent_s":"SOCIETE",
        "nombre_commandes_brut_i":121,
        "nombre_commandes_i":101,
        "nombre_kits_saveur_i":0,
        "ca_periode_i":60524,
        "ca_periode_fleur_i":0,
        "ca_periode_saveur_i":58148,
        "zone_scolaire_s":"B",
        "territoire_s":"France Métropolitaine",
        "region_s":"CENTRE VAL DE LOIRE",
        "departement_s":"45 LOIRET",
        "postal_country_s":"FR",
        "asso_country_s":"FRANCE",
        "object_type_s":"contact",
        "date_derni_re_commande_dt":"2016-04-21T00:00:00Z",
        "_version_":1605523881394177,
        *"score":308940.0*,
        "_childDocuments_":[
        {
          "fixe_facturation":["0238756400"],
          "object_type":["order"],
          "TTC_i":29120,
          "kit_sans_suite":["false"],
          "fixe_livraison":["0238756400"],
          "type_cde_s":"CDE",
          "statut_s":"V",
          "campagne_s":"A",
          "date_dt":"2016-04-19T00:00:00Z",
          "id":"A22058",
          "enseigne_s":"SAV",
          "gamme":["CHOCOLAT > Assortiment",
            "CHOCOLAT > Mono-produit",
            "EQUIPEMENT MAISON > Contenant pour liquide",
            "SAVEURS > Pâtisserie"]},
        {
          "fixe_facturation":["0238765400"],
          "object_type":["order"],
          "TTC_i":429,
          "kit_sans_suite":["false"],
          "fixe_livraison":["0238756400"],
          "type_cde_s":"CDE",
          "statut_s":"V",
          "campagne_s":"A",
          "date_dt":"2016-04-21T00:00:00Z",
          "id":"A22511",
          "enseigne_s":"BRI",
          "gamme":["SAVEURS > Pâtisserie"]}

The query looks like that :
/solr/<core>/select?chq=%2Bobject_type:order%20{!func}TTC_i&debugQuery=on&fl=*,score,[child%20parentFilter=object_type_s:contact]&indent=on&q={!parent%20which=object_type_s:contact%20score=total%20v=$chq}&wt=json

I also test the queries on the SOLR admin interface, and when I get what I
expect, I work on the development of the IHM which provides facet counting
and filtering for users.

The total score of TTC is a great advanced for me, but I need now to facet
the results or to be able to filter on the score returned.
I already tried using frange in filter query : {!frange l=0 u=10000
inclusive=true}$chq
I also tried : {!frange l=0 u=10000 inclusive=true}TTC_i
With inclusive = false... Without any result.

I know I'm not so far, could you put me on the right way if you have an idea
?

Thanks for your time,

Best

JB




--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Reply | Threaded
Open this post in threaded view
|

Re: Sum and aggregation on nested documents field

Mikhail Khludnev-2
  "to facet the results or to be able to filter on the score returned"
This is not clear, you need to clarify it to be replied.


On Wed, Jul 11, 2018 at 2:48 AM jeebix <[hidden email]> wrote:

> Hello Mikhail,
>
> First thanks a lot for your answers which are very useful for me... Then, I
> tried the query with the '$' parameter, and I get some great result like
> this :
>
> "id":"693897",
>         "asso_i":693897,
>         "etat_technique_s":"avec_documents",
>         "etat_marketing_s":"actif",
>         "type_parent_s":"Société",
>         "groupe_type_parent_s":"SOCIETE",
>         "nombre_commandes_brut_i":121,
>         "nombre_commandes_i":101,
>         "nombre_kits_saveur_i":0,
>         "ca_periode_i":60524,
>         "ca_periode_fleur_i":0,
>         "ca_periode_saveur_i":58148,
>         "zone_scolaire_s":"B",
>         "territoire_s":"France Métropolitaine",
>         "region_s":"CENTRE VAL DE LOIRE",
>         "departement_s":"45 LOIRET",
>         "postal_country_s":"FR",
>         "asso_country_s":"FRANCE",
>         "object_type_s":"contact",
>         "date_derni_re_commande_dt":"2016-04-21T00:00:00Z",
>         "_version_":1605523881394177,
>         *"score":308940.0*,
>         "_childDocuments_":[
>         {
>           "fixe_facturation":["0238756400"],
>           "object_type":["order"],
>           "TTC_i":29120,
>           "kit_sans_suite":["false"],
>           "fixe_livraison":["0238756400"],
>           "type_cde_s":"CDE",
>           "statut_s":"V",
>           "campagne_s":"A",
>           "date_dt":"2016-04-19T00:00:00Z",
>           "id":"A22058",
>           "enseigne_s":"SAV",
>           "gamme":["CHOCOLAT > Assortiment",
>             "CHOCOLAT > Mono-produit",
>             "EQUIPEMENT MAISON > Contenant pour liquide",
>             "SAVEURS > Pâtisserie"]},
>         {
>           "fixe_facturation":["0238765400"],
>           "object_type":["order"],
>           "TTC_i":429,
>           "kit_sans_suite":["false"],
>           "fixe_livraison":["0238756400"],
>           "type_cde_s":"CDE",
>           "statut_s":"V",
>           "campagne_s":"A",
>           "date_dt":"2016-04-21T00:00:00Z",
>           "id":"A22511",
>           "enseigne_s":"BRI",
>           "gamme":["SAVEURS > Pâtisserie"]}
>
> The query looks like that :
>
> /solr/<core>/select?chq=%2Bobject_type:order%20{!func}TTC_i&debugQuery=on&fl=*,score,[child%20parentFilter=object_type_s:contact]&indent=on&q={!parent%20which=object_type_s:contact%20score=total%20v=$chq}&wt=json
>
> I also test the queries on the SOLR admin interface, and when I get what I
> expect, I work on the development of the IHM which provides facet counting
> and filtering for users.
>
> The total score of TTC is a great advanced for me, but I need now to facet
> the results or to be able to filter on the score returned.
> I already tried using frange in filter query : {!frange l=0 u=10000
> inclusive=true}$chq
> I also tried : {!frange l=0 u=10000 inclusive=true}TTC_i
> With inclusive = false... Without any result.
>
> I know I'm not so far, could you put me on the right way if you have an
> idea
> ?
>
> Thanks for your time,
>
> Best
>
> JB
>
>
>
>
> --
> Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
>


--
Sincerely yours
Mikhail Khludnev
Reply | Threaded
Open this post in threaded view
|

Re: Sum and aggregation on nested documents field

jeebix
My apologize Mikhail, I try to explain it better :

This is actually what I get from SOLR with the query you helped me to build
:

"responseHeader":{
    "status":0,
    "QTime":15,
    "params":{
      "q":"{!parent which=object_type_s:contact score=max v=$chq}",
      "indent":"on",
      "fl":"*,score,[child parentFilter=object_type_s:contact]",
      "fq":["_query_:\"{!parent which=object_type_s:contact} (enseigne_s:SAV
AND (type_cde_s:CDE OR type_cde_s:REASSORT) AND campagne_s:G)\"",
        "-_query_:\"{!parent which=object_type_s:contact} (enseigne_s:SAV
AND type_cde_s:KIT AND campagne_s:I)\"",
        "{!frange l=30000}{!parent which=object_type_s:contact score=total
v=$chq}"],
      "chq":"+object_type:order +campagne_s:G +enseigne_s:SAV
+type_cde_s:(CDE OR REASSORT) {!func}TTC_i",
      "wt":"json",
      "debugQuery":"on",
      "_":"1531145722719"}},
  "response":{"numFound":2,"start":0,"maxScore":31640.16,"docs":[
      {
        "id":"94000.94001.1117636",
        "parent_i":94000,
        "asso_i":94001,
        "personne_i":1117636,
        "object_type_s":"contact",
        "date_derni_re_commande_dt":"2017-11-13T00:00:00Z",
        "_version_":1605583100313600002,
        *"score":31640.16*,
        "_childDocuments_":[
        {
          "object_type":["order"],
          "TTC_i":0,
          "type_cde_s":"KIT",
          "campagne_s":"G",
          "enseigne_s":"SAV"
         },
        {
          "object_type":["order"],
          "TTC_i":31636,
          "type_cde_s":"CDE",
          "campagne_s":"G",
          "enseigne_s":"SAV"
         }

I already get the TTC_i sum, by parent document with the "score" parameter.
If I correctly understand, the {!frange} allows me to filter on that
"score", so I can get the result of that question : "get the parent which
sum of TTC_i, with campagne_s:G and enseigne_s:SAV and type_cde_s:CDE or
type_cde_s:REASSORT, is higher than 30000".

If you think it's OK, I get the results I wanted. Then my goal is to build a
facet to get the number of parent docs which answer to the query, with
others constraints (like only one campagne_s, etc).
I think I have to use the json facet API, with facet.pivot for example.

Do you think I'm on the right way ?

Best

JB



--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html