There is an Optic query in MarkLogic 10 I'm trying to build that will display all rows of a certain template view, the issue is some rows need to do joins to other tables based on if it has values in other columns, which not all rows have.
TEMPLATE VIEW: CHILDREN
Example document:
{
id: "11"
childName: "Mark"
}
Example document2:
{
id: "22"
childName: "Jacob"
hasParent: "33"
}
TEMPLATE VIEW: PARENT
Example document3:
{
id: "33"
name: "Tom"
role: "Dad"
}
I need to return both document 1 and 2 in the response, linked with its fields from the Parent view and columns from Parent documents (if the document has the hasParent
key present).
In my current optic query, if I have an op:inner-join()
linking the example documents to other tables to pull all the columns I need, the first example document is not returned in the response, as it does not have the hasParent
field.
Query:
let $children := op:from-view("Test", "Children")
let $parent := op:from-view("Test", "Parent")
return $children
=>op:join-inner($parent, op:on(
op:view-col("Children", "hasParent"),
op:view-col("Parent", "id")))
=>op:where(op:eq(op:view-col('Children', 'hasParent'), op:view-col('Parent', 'id'))
=>op:select((op:view-col("Children", "id"),
op:view-col("Children", "name"),
op:view-col("Parent", "id"),
op:view-col("Parent", "name"),
op:view-col("Parent", "role")
))
=>op:result()
The above query will only return rows for "Children" documents that have a "hasParent" field that will allow them to execute the joins in my query. The output I'm looking for is to return all rows in the "Children" table view. And if there is no "hasParent" field to link to other tables, to leave those columns blank that are pulled from the "Parent" table in my op:select()
function.
Is there a way using conditionals or something similar to return all the documents I need in this query?
My understanding is that you want the NoSQL equivalent of the SQL left outer join
MarkLogic Optic join-left-outer
let $children := op:from-view("Test", "Children")
let $parent := op:from-view("Test", "Parent")
return $children
=> op:join-left-outer($parent, op:on(
op:view-col("Children", "hasParent"),
op:view-col("Parent", "id")))
=> op:select((
op:view-col("Children", "id"),
op:view-col("Children", "name"),
op:view-col("Parent", "id"),
op:view-col("Parent", "name"),
op:view-col("Parent", "role")
))
=> op:result()