marklogicmarklogic-10marklogic-optic-api

MarkLogic Optic API, existsJoin with data selection using empty qualifier resulting in 'Ambiguous column reference' error


I came across following situation - I use Optic API, I have first view filtered by second one using existsJoin to get only rowset I want. Then I need to include property which was used in op.on definition (the same column exists in both views) in objects which I return as a result, additionally that property must be present without any qualifiers etc.

Below is the simplest possible code that mimics what I need to do in mine application (using views created by TDE).

const op = require('/MarkLogic/optic');
const testViewToFilter = op.fromLiterals([
  {id:1000, val: 10},
  {id:1001, val: 11},
  {id:1002, val: 12}
], 'testViewToFilter');
const filteringView = op.fromLiterals([{id:1000}], 'filteringView')
testViewToFilter
  .existsJoin(
    filteringView,
    op.on(
      testViewToFilter.col('id'),
      filteringView.col('id')
    )
  )
  .select([testViewToFilter.col('id')], '')
  .result()

What I expect:

[{id: 1000}]

What I get:

[javascript] SQL-AMBCOLUMN: return plan.execute(query); -- Ambiguous column reference: found filteringView.id and id

Am I doing something wrong? How can I achieve the result I expect? I don't understand why do I get Ambiguous column reference for id column.

I get the same results for MarkLogic versions: 10.0-9.2 and 10.0-9.5


Solution

  • This is one of the funny optic API nuances. Once a column has been used with a name more than once (even when using a qualifier), then in the future, you cannot create any column with the unqualified name.

    If I have: foo.id and bar.id I cannot then create id

    The error is the creation of id in the line .select([testViewToFilter.col('id')], '')

    You have two options:

    1. A new qualifier:

      .select([testViewToFilter.col('id')], 'filleMeIN')

      Resulting in {"filleMeIN.id": 1000}

    2. Redefine the column name:

      .select([op.as("newId", testViewToFilter.col('id'))], '')

      Resulting in {"newId": 1000}

    Small note: this is language-independent. The same nuance exists when using xQuery