How to Group Results in WOQL

To use this HowTo, first clone the Star Wars demo into your team on DFRNT TerminusDB cloud. You will then have full access to the data needed for this tutorial.

How to use Group By

If we need to group variables according to some criteria, we can create an aggregate of solutions using group_by.

A group by is composed of a focus, a template, and a group together with a query.

Single Variable Template (Flat Array)

When grouping a single variable, the template produces a flat array of values:

let v = Vars("person", "label", "eyes", "group", "member");
limit(1)
.group_by(
  "eyes",
  ["label"],
  v.group,
  and(triple(v.person, "rdf:type", "@schema:People"),
      triple(v.person, "label", v.label),
      triple(v.person, "eye_color", v.eyes)))

The first argument, here "eyes" refers to the eyes variable, and is the variable around which to form the group, the focus.

The second ["label"] is the template, which refers to the variable "label". Since the template contains only one variable, the results will be collected as a flat array.

The third variable v.group, is the group variable, which will include groups of templates for each set of solutions which shares a focus.

This raw query output will be:

{
    "eyes": {
        "@type": "xsd:string",
        "@value": "black"
    },
    "group": [
        {
            "@type": "xsd:string",
            "@value": "Greedo"
        },
        {
            "@type": "xsd:string",
            "@value": "Nien Nunb"
        },
        {
            "@type": "xsd:string",
            "@value": "Gasgano"
        },
        {
            "@type": "xsd:string",
            "@value": "Kit Fisto"
        },
        {
            "@type": "xsd:string",
            "@value": "Plo Koon"
        },
        {
            "@type": "xsd:string",
            "@value": "Lama Su"
        },
        {
            "@type": "xsd:string",
            "@value": "Taun We"
        },
        {
            "@type": "xsd:string",
            "@value": "Shaak Ti"
        },
        {
            "@type": "xsd:string",
            "@value": "Tion Medon"
        },
        {
            "@type": "xsd:string",
            "@value": "BB8"
        }
    ],
    "label": null,
    "person": null
}

Notice how each result is a flat array of values, not nested arrays. To make this into individual solutions, use the member operator: member(v.member, v.group), which will expand the group array into individual elements in the v.member variable.

Multiple Variable Template (Array of Tuples)

When grouping multiple variables, the template produces an array of tuples:

let v = Vars("person", "label", "eyes", "group");
limit(1)
.group_by(
  "eyes",
  ["label", "eyes"],
  v.group,
  and(triple(v.person, "rdf:type", "@schema:People"),
      triple(v.person, "label", v.label),
      triple(v.person, "eye_color", v.eyes)))

Now the template ["label", "eyes"] includes two variables, so each result will be a tuple (array) containing both values:

{
    "eyes": {
        "@type": "xsd:string",
        "@value": "black"
    },
    "group": [
        [
            {
                "@type": "xsd:string",
                "@value": "Greedo"
            },
            {
                "@type": "xsd:string",
                "@value": "black"
            }
        ],
        [
            {
                "@type": "xsd:string",
                "@value": "Nien Nunb"
            },
            {
                "@type": "xsd:string",
                "@value": "black"
            }
        ],
        [
            {
                "@type": "xsd:string",
                "@value": "Gasgano"
            },
            {
                "@type": "xsd:string",
                "@value": "black"
            }
        ],
        [
            {
                "@type": "xsd:string",
                "@value": "Kit Fisto"
            },
            {
                "@type": "xsd:string",
                "@value": "black"
            }
        ],
        [
            {
                "@type": "xsd:string",
                "@value": "Plo Koon"
            },
            {
                "@type": "xsd:string",
                "@value": "black"
            }
        ]
    ],
    "label": null,
    "person": null
}

Each element in the group array is now a tuple [label, eyes] containing both values.