Aggregation Pipeline

The pipeline allows very complex operations to modify the structure of the result. The resulting row docs don’t have to conform to the tables column settings. See the example below for a minimal Get Rows call that utilizes a aggregation pipeline.

The example tables col_settings

{
    "c0": {
        "name": "Column 0 Text",
        "display_type": "string"
    },
    "c1": {
        "name": "Column 1 Number",
        "display_type": "integer"
    },
    "c1": {
        "name": "Column 2 Number",
        "display_type": "integer"
    }
}

Data in the table

[
    { "c0": "Frank", "c1": 12, "c2": 100 },
    { "c0": "Niels", "c1": 34, "c2": 100 },
    { "c0": "Heye", "c1": 56, "c2": 100 },
    { "c0": "Richard", "c1": 78, "c2": 100 },
]

a get_rows call with a small pipeline that uses $project to return only some of the columns of the table

{
    "table_id": "...",
    "pipeline": [
        {"$project": {"keep": ["c0", "c1"]}}
    ]
}

Resulting rows

[
    { "c0": "Frank",   "c1": 12},
    { "c0": "Niels",   "c1": 34},
    { "c0": "Heye",    "c1": 56},
    { "c0": "Richard", "c1": 78},
]

Warning

Aggregation pipelines are not available for SQL backed datatables.

Extensive Stage Example

Here is a big example that uses multiple stages:

{
    "pipeline": [
        {
            "$group":{
                "group_cxs":[
                    "c0"
                ],
                "c1":"sum",
                "c3":"merge",
                "c4":"merge"
            }
        },
        {
            "$filter_subtable":{
                "subtable_cx":"c3",
                "filter":{
                    "c3.c2":"tableware"
                }
            }
        },
        {
            "$project":{
                "keep":[
                    "c0",
                    "c1",
                    "c2",
                    "c3",
                    "c4"
                ]
            }
        },
        {
            "$group_subtable":{
                "subtable_cx":"c3",
                "group_cxs":[
                    "c0",
                    "c3"
                ],
                "c1":"sum"
            }
        },
        {
            "$final_compute":{
                "c6":"$sum($col($filter(c3 + c4, {'c0':'spoon'}), c1))"
            }
        },
        {
            "$final_compute":{
                "c3":"$format(c3, c0-c3 (c1))"
            }
        },
        {
            "$final_compute":{
                "c4":"$format(c4, c0-c3 (c1))"
            }
        }
    ]
}

Example Stages Explained:

Operating on the example data above, it would return a result like this:

• First $group merges the main rows that have the same area_code.

  • the total_amount will be the sum

  • Box1 and Box2 will be merged individually.

• The $filter_subtable removes all entries from Box1 that don’t have type tableware

• The $project doesn’t do anything, since all columns are kept.

• The $group_subtable combines positions in Box1, so e.g. we don’t have ‘plate - white’ twice

• The First $final_compute* builds a string from Box1

• The Second $final_compute* builds a string from Box2

• The Third $final_compute* counts the amount of spoons in both boxes