Simple Digest Example

Imagine that each history with labelc="binOperationsTask" represents one case where waste bins had to be delivered, repaired or replaced, and that an example history looks like this.

{
    "labela": "Bin Operations Task",
    "labelb": "3226-5911-2348-4316",
    "labelc": "binOperationsTask"
    "events": [{
            "pos": [1,5],
            "event": {
                "binTypes": {
                    "quantity": 1,
                    "charge": 35,
                    "binType": "Garden Waste 240L",
                    "serviceChannel": "online",
                    "whatPartLost": "",
                    "whyReplacing": "Stolen",
                    "whatPartStolen": "",
                    "binNameWithQty": "1x Garden Waste 240L",
                    "howDamaged": "",
                    "reviewed": true,
                    "typeRequired": "Replacement",
                    "displayCharge": "35",
                    "formattedBinType": "Garden waste"
                }
            }
        }, {
            "pos": [2,5],
            "event": {
                "binTypes": {
                    "quantity": 1,
                    "charge": 35,
                    "binType": "Residual 240L",
                    "serviceChannel": "online",
                    "whatPartLost": "",
                    "whyReplacing": "Stolen",
                    "whatPartStolen": "",
                    "binNameWithQty": "1x Residual 240L",
                    "howDamaged": "",
                    "reviewed": true,
                    "typeRequired": "Replacement",
                    "displayCharge": "35",
                    "formattedBinType": "Household waste"
                }
            }
        }, {
            "pos": [3,5],
            "event": {
                "binTypes": {
                    "quantity": 1,
                    "charge": 25,
                    "binType": "Recycling Blue With Inner Caddy 240L",
                    "serviceChannel": "online",
                    "whatPartLost": "The main bin and caddy",
                    "whyReplacing": "Lost",
                    "whatPartStolen": "",
                    "binNameWithQty": "1x Recycling Blue With Inner Caddy 240L",
                    "howDamaged": "",
                    "reviewed": true,
                    "typeRequired": "Replacement",
                    "displayCharge": "25",
                    "formattedBinType": "Recycling"
                }
            }
        }, {
            "pos": [4,5],
            "event": {
                "uprn": "100000057135",
                "private": false,
                "loc_location": {
                    "coordinates": [
                        "-1.590066",
                        "54.955294"
                    ],
                    "type": "Point"
                },
                "address": "5 St Edmunds Court, Sunderland Road, Gateshead",
                "slotSize": 1,
                "slotType": "Replacement",
                "serviceChannel": "online",
                "loc_lon": -1.590066,
                "loc_area": "Central",
                "assignedToCrew": "true",
                "postcode": "NE8 3PF",
                "description": "Form: [Order a replacement bin] submitted.",
                "loc_lat": 54.955294,
                "loc_ward": "BRIDGES",
                "taskDate": "2021-01-12",
                "caseSubType": "Replacement",
                "processType": "Replacement bins",
                "event": "Form Submission",
                "userRole": "PUBLIC"
            }
        }, {
            "pos": [5,5],
            "event": {
                "caseClosed": "true"
            }
            "timestamp": 1611916728263
        }
    ]
}

Events 1, 2, and 3 show the different bins that were replaced. Event 4 shows what was done with the bins and at which address. Event 5 shows the case being closed.

This simple example digester collects the final value of address and description. In the history above both of those fields are present in Event 4.

{
    "id": 1001,
    "method": "registerdigest",
    "params": {
        "name": "binaddress",
        "filter": {
            "AND": [
                {
                    "key": "labela",
                    "EQ": "Bin Operations Task"
                },
                {
                    "key": "labelc",
                    "EQ": "binOperationsTask"
                }
            ]
        },
        "columns": [
            {
                "name": "BusinessKey",
                "type": "varchar",
                "size": 50,
                "operation": "label",
                "key": "labelb"
            },
            {
                "name": "Description",
                "type": "varchar",
                "size": 50,
                "operation": "lastvalue",
                "key": "description"
            },
            {
                "name": "Address",
                "type": "varchar",
                "size": 250,
                "operation": "lastvalue",
                "key": "address"
            }
        ]
    }
}

The digest is called "binaddress" and it only considers histories that have labela="Bin Operations Task" and labelc="binOperationsTask". It keeps the business key from labelb in a column called "BusinessKey" and the last value from any fields called "description" and "address" in columns called "Description" and "Address". Registering the digester would create a new view in the database called "dh_binaddress_vw" and querying the view would get data like this.

-- have a look at the (simplified) bin addresses digest
SELECT *
FROM dh_binaddress_vw

Bear in mind that this is test data so the repeated and clustered addresses are not a concern.