Left join on doc-uri in MarkLogic Optic query

Question

I try to do a join with fragmentColId to obtain the document uri, but in such a way that it won't drop the row if there is no actual fragment.

I am using op:join-doc-uri, which works fine against TDEs, but not against built-in tables like sys.sys_tables. It would be great if there is a way to write one query that works for both cases.

Answer 1

The information in the sys.sys_tables table does not come from any document in the database, so it doesn't have a fragment associated with any of it's rows.

If you want to alter your query based on the presence or absence of columns, you probably want to use op:column-info() to look at the Optic plan built so far and figure out what columns it returns.

Answer 2

I tried the left-join first. It looked like this:

xquery version '1.0-ml';

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

declare option xdmp:mapping 'false';

let $fragId := "fragmentId"
let $fragCol := op:fragment-id-col($fragId)
let $schema := "MySchema"
let $view := "MyView"
return
  op:from-view($schema, $view, '', $fragCol)
  =>op:offset-limit(1,10)
  =>op:join-left-outer(
    op:from-search(cts:true-query(), $fragId)
    =>op:join-doc-uri('_dburi', $fragCol)
    =>op:select(($fragId, '_dburi'))
  )
  =>op:result()

It is (much) slower than doing a straight op:join-doc-uri, and unfortunately, it never completed for schema sys and view sys_tables.

However, you can analyse the situation first, and either optionally blend in the op:join-doc-uri, or write the optic query twice, once with and once without the op:join-doc-uri.

I already added an if for schema equal to sys and sysspatial, but resolving this fully dynamically is more elegant. The op:column-info suggested by John helps a lot!

xquery version '1.0-ml';

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

declare option xdmp:mapping 'false';

let $fragId := "fragmentId"
let $fragCol := op:fragment-id-col($fragId)
let $schema := "sys"
let $view := "sys_tables"

let $hasFragments :=
  (
    op:from-view($schema, $view, '', $fragCol)
    =>op:column-info()
  ) ! map:get(., 'column') = $fragId

return
  if ($hasFragments) then
    op:from-view($schema, $view, '', $fragCol)
    =>op:offset-limit(1,10)
    =>op:join-doc-uri('_dburi', $fragCol)
    =>op:result()
  else
    op:from-view($schema, $view, '', $fragCol)
    =>op:offset-limit(1,10)
    =>op:result()