The purpose of the Optic API has some overlap with xdmp:sql. Are there any guidelines on when I should use Optic versus SQL?
Optic provides an idiomatic, language-integrated interface to the capabilities of the MarkLogic engine that underlie SQL and SPARQL as well as Optic. (Optic doesn't generate SQL or SPARQL.)
Examples of MarkLogic idiomatic operations include
fn.reverse()The language-integrated aspects are similar to those of SQL Alchemy or JOOQ. In SQL, other than parameterizing literals, the programmer has to work by string concatenation. Besides the inconvenience of string concatenation, it requires extra care by the developer to prevent injection attacks.
By contrast, in Optic, you can capture an intermediate stage of building a plan in a variable, pass a plan into a function that adds operations and returns the modified plan, and so on.
You can work more naturally by chaining operations that express the plan you need to execute in the expected order of operations (by contrast with the imperative sentence constraints of the SQL grammar). The Jooq lead has an interesting blog post on that issue:
https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/
That said, if a team is deeply versed in SQL and doesn’t need the idiomatic capabilities or see the value in language integration provided by Optic, they should not hesitate to use SQL.
In other words, the choice of which interface to use (SQL or Optic) is likely to be a decision per team or project instead of a decision per query.