marklogicmarklogic-optic-api

MarkLogic Optic API aggregate arithmetics


I am trying to get Arithmetics with MarkLogics Optic API to work with aggregate functions, though I get the following error:

message=OPTIC-INVALARGS: fn.error(null, 'OPTIC-INVALARGS', -- Invalid arguments: right argument at 1 of op.multiply value must have xs.numeric datatype: op.count(op.col('count_42'), null)

I am requesting against the rows endpoint:

POST http://marklogic:8000/LATEST/rows?database=SQLData HTTP/1.1

I am trying a fairly basic query, please see the following:

{
"$optic": {
    "ns": "op",
    "fn": "operators",
    "args": [{
            "ns": "op",
            "fn": "from-view",
            "args": [null, "employees", "e", null]
        }, {
            "ns": "op",
            "fn": "select",
            "args": [[{
                        "ns": "op",
                        "fn": "col",
                        "args": ["firstname"]
                    }, {
                        "ns": "op",
                        "fn": "col",
                        "args": ["lastname"]
                    }
                ], null]
        }, {
            "ns": "op",
            "fn": "group-by",
            "args": [[{
                        "ns": "op",
                        "fn": "col",
                        "args": ["firstname"]
                    }
                ], [{
                        "ns": "op",
                        "fn": "as",
                        "args": ["es", {
                                "ns": "op",
                                "fn": "multiply",
                                "args": [10, {
                                        "ns": "op",
                                        "fn": "count",
                                        "args": ["count_42"]
                                    }
                                ]
                            }
                        ]
                    }
                ]]
        }
    ]
  }
}

I am not sure if I'm doing something incorrectly or the endpoint does not support aggregate arithmetics. Would appreciate if anyone had a clue.

Thanks in advance.


Solution

  • The op.count() aggregate is not an expression function. Like other aggregates, it populates a column instead of returning a value. The function requires a name for the populated column and must appear at the top level in the value list for the groupBy() operation:

    http://docs.marklogic.com/op.count

    It should work to move the multiplication expression into a subsequent select() operation that takes the populated count column as one of the arguments. In other words, after the grouping operation, you can use the aggregate column in expressions in the same way as any other column.

    In general, debugging may be easier by testing Optic queries with the SJS or XQuery builder in QueryConsole (using limit() to produce a small result set). Once the query is working as desired, using op.export() to produce the JSON AST.

    Hoping that helps,