Written Tutorial

Problem

Your data has properties for names and counts, and you want the sums of the counts grouped by name.

Solution

Applies to MarkLogic versions 9+

Assuming you want sums of a property called “amount” grouped by a property called “name”:

const op = require('/MarkLogic/optic');
​
op.fromLexicons({
  name: cts.jsonPropertyReference('name'),
  amount: cts.jsonPropertyReference('amount', ["type=int"])
})
  .groupBy('name', [
    op.sum('totalamount', 'amount')
  ])
  .result();

import module namespace op="https://marklogic.com/optic"
  at "/MarkLogic/optic.xqy";

op:from-lexicons(
  map:new((
    map:entry("name",   cts:json-property-reference("name")),
    map:entry("amount", cts:json-property-reference("amount", "type=int"))
  ))
)
  => op:group-by('name', (
    op:sum('totalamount', 'amount')
  ))
  => op:result()

To see this run, you can create some sample docs to play with:

declareUpdate();

for (let index=0; index < 1000; index++) {
  xdmp.documentInsert(
    '/values' + index + '.json',
    {
      "name": "val" + (xdmp.random(2) + 1),
      "amount": xdmp.random(2) + 1
    }
  )
}

The response I got is below. Yours might be different due to the xdmp.random calls.

{
  "name": "val1", 
  "totalamount": 712
},
{
  "name": "val2", 
  "totalamount": 659
},
{
  "name": "val3", 
  "totalamount": 649
}

Required Indexes:

• range index on “name”
• range index on “amount”

Discussion

MarkLogic provides group-by with the Optic API, which performs relational operations over data extracted from documents.

Optic can work with range indexes, using op.fromLexicons as shown above, but it can also calculate group-bys using op.fromTriples or op.fromView, to work with triples or rows respectively.

Optic’s groupBy takes a field that you want to group by, along with instructions on how to aggregate the related values. For the sum aggregator, we provide a new name for the aggregated values, along with the source field.

The groupBy function documentation lists the aggregation functions you can use. If you need an aggregation function other than the ones provided, op:uda allows you call a User-Defined Function.