How to perform this aggregation efficiently?

Imagine a schema for a golf course. I have a course which contains multiple holes, holes contain tees, and tees contain pins (in this case pins are called baskets). A hole can have multiple tee and pin locations so the distance from tee to pin is stored on a facet. Here’s an example query:

{
  q(func: eq(CourseName, "Cool Golf Course")) {
    CourseName 
    CourseLocation
    Hole @filter(eq(HoleNumber, "1")) {
      HoleNumber
      Tee @filter(eq(TeeColor, "Red")) {
        TeeColor
          Basket @filter(eq(BasketDesignation, "A")) @facets(distance) {
            BasketDesignation
          }
      }
    }
  }
}

Which returns the following:

"data": {
    "q": [
      {
        "CourseName": "Cool Golf Course",
        "Hole": [
          {
            "HoleNumber": "1",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": "A",
                    "Basket|distance": 68
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  }

What I want to do is a query like this which will give me the total course distance by adding up all of the distance facet values in a query:

{
  q(func: eq(CourseName, "Cool Golf Course")) {
    CourseName 
    CourseLocation
    Hole {
      HoleNumber
      Tee @filter(eq(TeeColor, "Red")) {
        TeeColor
          Basket @filter(eq(BasketDesignation, "A")) @facets(d as distance) {
            BasketDesignation
          }
      }
    }
    Distance: sum(val(d))
  }
}

…but I cannot aggregate the values higher than one block in the query as it fails with a “Check the levels” error.

This query works:

{
  var(func: eq(CourseName, "Cool Golf Course")) {
    CourseName 
    CourseLocation
    Hole {
      HoleNumber
      Tee @filter(eq(TeeColor, "Red")) {
        TeeColor
          Basket @filter(eq(BasketDesignation, "A")) @facets(d as distance) {
            BasketDesignation
          }
      }
    }
  }
  q() {
    sum(val(d))
  }
}

…and I could go on and pass every little bit of information from the var block to the query results but that would be tedious. What I want is the value from sum(val(d)) where d is the distance of every basket returned by the query at the “root” of the course node.

How do I aggregate a set of values several levels removed from the block the value is returned in?

Not sure I follow everything, but will @normalize directive help here? Could you give example of a query that is correct but tedious for you to write?

See the following query:

  q(func: eq(CourseName, "Cool Golf Course")) {
    CourseName 
    GeoLocation
    Hole {
      HoleNumber
      Tee @filter(eq(TeeColor, "Red")) {
        TeeColor
          Basket @filter(eq(BasketDesignation, "A")) @facets(Distance: d as distance) {
            BasketDesignation
          }
          teedistance as sum(val(d))
      }
      holedistance as sum(val(teedistance))
    }
    CourseDistance: sum(val(holedistance))
  }
}

…which produces the following response:

"q": [
      {
        "CourseName": "Cool Golf Course",
        "Hole": [
          {
            "HoleNumber": "1",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "B",
                      "A"
                    ],
                    "Distance": 68
                  }
                ],
                "sum(val(d))": 68
              }
            ],
            "sum(val(teedistance))": 68
          },
          {
            "HoleNumber": "2",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 79
                  }
                ],
                "sum(val(d))": 79
              }
            ],
            "sum(val(teedistance))": 79
          },
          {
            "HoleNumber": "3",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 69
                  }
                ],
                "sum(val(d))": 69
              }
            ],
            "sum(val(teedistance))": 69
          },
          {
            "HoleNumber": "4",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 57
                  }
                ],
                "sum(val(d))": 57
              }
            ],
            "sum(val(teedistance))": 57
          },
          {
            "HoleNumber": "5",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 63
                  }
                ],
                "sum(val(d))": 63
              }
            ],
            "sum(val(teedistance))": 63
          },
          {
            "HoleNumber": "6",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 54
                  }
                ],
                "sum(val(d))": 54
              }
            ],
            "sum(val(teedistance))": 54
          },
          {
            "HoleNumber": "7",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 77
                  }
                ],
                "sum(val(d))": 77
              }
            ],
            "sum(val(teedistance))": 77
          },
          {
            "HoleNumber": "8",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 84
                  }
                ],
                "sum(val(d))": 84
              }
            ],
            "sum(val(teedistance))": 84
          },
          {
            "HoleNumber": "9",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 66
                  }
                ],
                "sum(val(d))": 66
              }
            ],
            "sum(val(teedistance))": 66
          },
          {
            "HoleNumber": "10",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 69
                  }
                ],
                "sum(val(d))": 69
              }
            ],
            "sum(val(teedistance))": 69
          },
          {
            "HoleNumber": "11",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 66
                  }
                ],
                "sum(val(d))": 66
              }
            ],
            "sum(val(teedistance))": 66
          },
          {
            "HoleNumber": "12",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 69
                  }
                ],
                "sum(val(d))": 69
              }
            ],
            "sum(val(teedistance))": 69
          },
          {
            "HoleNumber": "13",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 49
                  }
                ],
                "sum(val(d))": 49
              }
            ],
            "sum(val(teedistance))": 49
          },
          {
            "HoleNumber": "14",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 72
                  }
                ],
                "sum(val(d))": 72
              }
            ],
            "sum(val(teedistance))": 72
          },
          {
            "HoleNumber": "15",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 72
                  }
                ],
                "sum(val(d))": 72
              }
            ],
            "sum(val(teedistance))": 72
          },
          {
            "HoleNumber": "16",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 48
                  }
                ],
                "sum(val(d))": 48
              }
            ],
            "sum(val(teedistance))": 48
          },
          {
            "HoleNumber": "17",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 61
                  }
                ],
                "sum(val(d))": 61
              }
            ],
            "sum(val(teedistance))": 61
          },
          {
            "HoleNumber": "18",
            "Tee": [
              {
                "TeeColor": "Red",
                "Basket": [
                  {
                    "BasketDesignation": [
                      "A"
                    ],
                    "Distance": 69
                  }
                ],
                "sum(val(d))": 69
              }
            ],
            "sum(val(teedistance))": 69
          }
        ],
        "CourseDistance": 1192
      }
    ]
  }

In the Basket @filter(eq(BasketDesignation, "A")) @facets(Distance: d as distance) level of the query we grab d as distance from the facet between the tee and the basket. For this response we have 18 holes each with a distance value stored in the variable d. For all the distances returned by the query I want them added up to produce what can be see in CourseDistance value.

To do this I have to run sum() on the variable at every level of the query starting at the parent of Basket all the way up to the Course level where I want it. The way it’s constructed works but it runs the sum() function three times before actually getting to the level where we can get the value we want and it causes the response to return the distance value multiple times as can be see by all the sum(val(varName)) lines.

The query I want to make is:

{
  q(func: eq(CourseName, "Cool Golf Course")) {
    CourseName 
    GeoLocation
    Hole {
      HoleNumber
      Tee @filter(eq(TeeColor, "Red")) {
        TeeColor
          Basket @filter(eq(BasketDesignation, "A")) @facets(Distance: d as distance) {
            BasketDesignation
          }
      }
    }
    CourseDistance: sum(val(d))
  }
}

…but this returns:

Message: : Invalid variable aggregation. Check the levels.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.