op:sql-condition

op:sql-condition(
   $operand as item()
) as map:map

Summary

This function returns a filter definition as input for a WHERE operation. As with a cts:query or sem:store, the filter definition cannot be used in an Optic Boolean expression but, instead, must be the only argument to the WHERE call. Add a separate WHERE call to filter based on an Optic Boolean expression. The condition must be a valid simple SQL Boolean expression expressed as a string.

The expression may use literals, column references, SQL operators, and core SQL functions but to limit exposure to injection attacks, may not use nested SELECT statements or arbitrary builtin function calls.

Usage Notes

A boolean SQL expression that supports subqueries is vulnerable to an exploit where a deliberately false filter is made true by an OR-related subquery, making it possible to discover information about other views. Similarly, any function, such as cts:doc, that reads the database or its configuration could also disclose unintended information.

Example

(: Return row with an 'id' value between .5 and 1.5. :)

xquery version "1.0-ml";

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

op:from-literals((
	    map:entry("id", 1) => map:with("name", "Master 1") => map:with("date", "2015-12-01"),
        map:entry("id", 2) => map:with("name", "Master 2") => map:with("date", "2015-12-02")))

   => op:where(op:sql-condition("id BETWEEN 0.5 AND 1.5"))
   => op:result()