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
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:
A new qualifier:
.select([testViewToFilter.col('id')], 'filleMeIN')
Resulting in {"filleMeIN.id": 1000}
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