Search code examples
marklogicmarklogic-9marklogic-optic-api

MarkLogic Optic API - Group By

Can we use "case-sensitive" or "case-insensitive" in op:group-by in XQuery?

I want to group the values irrespective of case. If there is any solution, please give an example.

Thanks in advance!

Solution

  • One way to solve the problem is to make the string values of the grouping key all upper or lower case before grouping.

    For a small data set, an op:select() statement could list the indexed columns needed for the output and also construct a dynamic grouping key column with:

    op:as("upperCaseGroupingKey", ofn:upper-case(op:col("variableCaseGroupingKey")))

    For larger data sets, however, it will perform better to construct the grouping key using the fn:upper-case() function in the TDE that projects the rows out of the document into the index.

    Hoping that helps,