Marklogic - Optic API: Joining views by value (op:on does not support values, only column-references)

Question

I'm trying to join two views "A" and "B" using the op:join-left-outer function.

I have two "ON-conditions" for the JOIN:

1. The first one is a simple op:on function. (And not a part of my problem)
2. The second one should be an ON-condition joining a column by value ($myValue). But op:on does not support values, only column-references. So the following code doesn't work:

let $aView := op:from-view("foobar", "A")
let $bView := op:from-view("foobar", "B")

let $myValue := "42"

let $opticQuery := op:join-left-outer(
  $aView, 
  $bView, 
  (
    op:on(op:view-col("A", "SOME_COLUMN"), op:view-col("B", "SOME_COLUMN")),
    
    (: Not working pseudo code following :)
    op:on(op:view-col("B", "SOME_OTHER_COLUMN"), $myValue)
  )
)

In SQL I would write something like this:

SELECT * FROM A
LEFT JOIN B
ON A.SOME_COLUMN = B.SOME_COLUMN
AND B.SOME_OTHER_COLUMN = '42'

My question: Is there a way to do the same in Optic API or am I doing something wrong?

Answer 1

Interestingly enough, when trying to use a second op:on() with column references on left and right (by binding 42 as a new column on aView), I also did not get expected results.

However, the Optic API does seem to allow you to do what you want if expressed in a different way:

• The op:join-left-outer() documentation shows a fourth parameter - condition.
• op:eq() allows for a mix of column references and constants.

Therefore, I would expect that you can remove your second op:on() and use the following as the 4th param: op:eq(op:view-col("B", "SOME_OTHER_COLUMN"), $myValue)

A free-standing sample is below -where the resulting second row has null values for the outer joined table

xquery version "1.0-ml";
import module namespace op="http://marklogic.com/optic"
 at "/MarkLogic/optic.xqy";

let $myVal := 42

let $plan-table-1 := op:from-literals((
        map:entry("col1", 1) => map:with("val", "a"),
        map:entry("col1", 2) => map:with("val", "b")
        ), "table1")     

let $plan-table-2 := op:from-literals((
        map:entry("col1", 1) => map:with("val2", "c") => map:with("someOtherCol", 42),
        map:entry("col1", 2) => map:with("val2", "d") => map:with("someOtherCol", 8)
        ), "table2")


return op:join-left-outer(
  $plan-table-1,
  $plan-table-2, 
  op:on(op:view-col("table1", "col1"), op:view-col("table2", "col1")), 
  op:eq(op:view-col("table2", "someOtherCol"), $myVal)
)=>op:result()