Exploring op.select()

Introduction

The op.select() function in the Optic API allows you to extract specific columns from a plan or set of plans.

When to Use `op.select()`

Use op.select() when:

You need only certain fields from your data.
You want to reduce the size of your result set.
You want to improve query performance by limiting the data processed.
You are preparing data for display in a user interface.
You are working with multiple data sources and want to normalize the output.
You are preparing data for downstream operations like sorting or aggregation.

Parameters

Name	Datatype	Required	Notes
`plan`	Object	Yes	The input plan or set of plans from which to select columns.
`columns`	Array of Strings/op.col()/opviewCol() references	Yes	An array of column names or expressions to select.
`qualifier`	String	No	An optional qualifier for the selected columns, useful for disambiguating column names in joins or complex queries.

Simple Example

Simple usage of op.select() can be seen in the following example, which is where we left off on th op.fromView() article. Although the results are a bit noise with the schema, vie and column names, we can clearly see the results.

const op = require('/MarkLogic/optic');
const llamaPlan = op.fromView('llamaverse', 'llamas')
  .select([op.viewCol("llamas", "name"), op.viewCol("llamas", "placeOfBirth")])
  .orderBy('name')
  .limit(3)
llamaPlan.result()


{"llamaverse.llamas.name":"Aaron", "llamaverse.llamas.placeOfBirth":"Cusco, Peru"}
{"llamaverse.llamas.name":"Angela", "llamaverse.llamas.placeOfBirth":"Cusco, Peru"}
{"llamaverse.llamas.name":"Anthony", "llamaverse.llamas.placeOfBirth":"Santiago, Chile"}

Two Views:

This sample shows what happens if the columns are from two different views. We will also touch on an optic join in the saple. however, we will not go into detail on joins in this article. We will cover joins in a future article.

Below we now have content from two views, llamas and secretPowers. We still need the exact scope by view here to avoid ambiguity in the column naed "ID" which exists in both views. However, we also need to refer to the proper view per column when consumung the data. That can be cumbersome. It can also be confusing because the accessor for a single result would be row["llamaverse.llamas.id"] and not row.llamaverse.llamas.id since the name is just a string representaive of [schema].[view].[coumn]S.

const op = require('/MarkLogic/optic');
const llamaPlan = op.fromView('llamaverse', 'llamas')
const secretPowersPlan = op.fromView('llamaverse', 'secretPowers')
llamaPlan
.joinLeftOuter(secretPowersPlan, op.on(op.viewCol("llamas", "secretPowerId"), op.viewCol("secretPowers", "id")))
.select([op.viewCol("llamas", "id"), op.viewCol("llamas", "name"), op.viewCol("secretPowers", "id"), op.viewCol("secretPowers", "name"), op.viewCol("secretPowers", "description")])
.limit(3)
.result()


{"llamaverse.llamas.id":"a57cfd3f-b4b6-47f5-9227-81ddae645f41", "llamaverse.llamas.name":"James", "llamaverse.secretPowers.id":"d8839ba6-2b77-4bcc-9927-b86cdfecb9fb", "llamaverse.secretPowers.name":"Fog Walker", "llamaverse.secretPowers.description":"Can vanish into fog like a mystical meme. Especially effective when someone says 'who took my snack?'"}
{"llamaverse.llamas.id":"57623d51-ff33-450c-98df-a288e8a263d6", "llamaverse.llamas.name":"Ashley", "llamaverse.secretPowers.id":"f391c275-158f-44da-9884-4dd3b004d0ef", "llamaverse.secretPowers.name":"Druid's Sight", "llamaverse.secretPowers.description":"Sees snippets of the future, especially while meditating under trees. Particularly accurate when there's a full moon and snacks involved."}
{"llamaverse.llamas.id":"c7e16d34-60db-4fd7-90f2-e6999c0518cf", "llamaverse.llamas.name":"Richard", "llamaverse.secretPowers.id":"1bba3ab4-77b8-4807-8aa3-99b47aa29587", "llamaverse.secretPowers.name":"Potion Sniffer", "llamaverse.secretPowers.description":"Can sniff out any magical concoction, from ancient elixirs to freshly brewed llama lattes."}

Using `op.select()` to normalize column and view names

We can use the last parameter of op.select() to normalize the column names. This is especially useful when working with multiple views or complex joins. However, we must first address the ambiguity in the column names.

In this example, we took care of collisions xxx.A and yyy.A by renaming one or the other using op.as() (to be discussed inn a separate article). You will note that the way we referenced the new columns varied. This was not only to show different approaches, but also because for the secretPowersId column, we had to be specific because the llamas view already has a column called secretPowers - and even though it is not selected, it is still in the plan. This is a good example of why we prefer to be specific with our column names.

Finally, we used the qualifier parameter to normalize the column names. In this case to a new vire called result.

const op = require('/MarkLogic/optic');
const llamaPlan = op.fromView('llamaverse', 'llamas')
const secretPowersPlan = op.fromView('llamaverse', 'secretPowers')
llamaPlan
.joinLeftOuter(secretPowersPlan, op.on(op.viewCol("llamas", "secretPowerId"), op.viewCol("secretPowers", "id")))
.select(
   [
    op.viewCol("llamas", "id"), 
    op.as("llamaName", op.viewCol("llamas", "name")), 
    op.as(op.viewCol("secretPowers", "secretPowerId"), op.viewCol("secretPowers", "id")),
    op.as("secretPowerName", op.viewCol("secretPowers", "name")), 
    op.as("secretPowerdescription", op.viewCol("secretPowers", "description"))
   ], "result")
.limit(3)
.result()

{"result.id":"a57cfd3f-b4b6-47f5-9227-81ddae645f41","result.llamaName":"James","result.secretPowerId":"d8839ba6-2b77-4bcc-9927-b86cdfecb9fb","result.secretPowerName":"Fog Walker","result.secretPowerdescription":"Can vanish into fog like a mystical meme. Especially effective when someone says 'who took my snack?'"}
{"result.id":"57623d51-ff33-450c-98df-a288e8a263d6","result.llamaName":"Ashley","result.secretPowerId":"f391c275-158f-44da-9884-4dd3b004d0ef","result.secretPowerName":"Druid's Sight","result.secretPowerdescription":"Sees snippets of the future, especially while meditating under trees. Particularly accurate when there's a full moon and snacks involved."}
{"result.id":"c7e16d34-60db-4fd7-90f2-e6999c0518cf","result.llamaName":"Richard","result.secretPowerId":"1bba3ab4-77b8-4807-8aa3-99b47aa29587","result.secretPowerName":"Potion Sniffer","result.secretPowerdescription":"Can sniff out any magical concoction, from ancient elixirs to freshly brewed llama lattes."}

Removing the schema and view names

If you want to remove the schema and view names from the results, you can use the qualifier parameter with anempty sring. This will return the columns without the schema and view prefixes.

const op = require('/MarkLogic/optic');
const llamaPlan = op.fromView('llamaverse', 'llamas')
const secretPowersPlan = op.fromView('llamaverse', 'secretPowers')
llamaPlan
.joinLeftOuter(secretPowersPlan, op.on(op.viewCol("llamas", "secretPowerId"), op.viewCol("secretPowers", "id")))
.select(
   [
    op.viewCol("llamas", "id"), 
    op.as("llamaName", op.viewCol("llamas", "name")), 
    op.as(op.viewCol("secretPowers", "secretPowerId"), op.viewCol("secretPowers", "id")),
    op.as("secretPowerName", op.viewCol("secretPowers", "name")), 
    op.as("secretPowerdescription", op.viewCol("secretPowers", "description"))
   ], "")
.limit(3)
.result()

{"id":"a57cfd3f-b4b6-47f5-9227-81ddae645f41","llamaName":"James","secretPowerId":"d8839ba6-2b77-4bcc-9927-b86cdfecb9fb","secretPowerName":"Fog Walker","secretPowerdescription":"Can vanish into fog like a mystical meme. Especially effective when someone says 'who took my snack?'"}
{"id":"57623d51-ff33-450c-98df-a288e8a263d6","llamaName":"Ashley","secretPowerId":"f391c275-158f-44da-9884-4dd3b004d0ef","secretPowerName":"Druid's Sight","secretPowerdescription":"Sees snippets of the future, especially while meditating under trees. Particularly accurate when there's a full moon and snacks involved."}
{"id":"c7e16d34-60db-4fd7-90f2-e6999c0518cf","llamaName":"Richard","secretPowerId":"1bba3ab4-77b8-4807-8aa3-99b47aa29587","secretPowerName":"Potion Sniffer","secretPowerdescription":"Can sniff out any magical concoction, from ancient elixirs to freshly brewed llama lattes."}

Tips

You can use op.as() to rename columns in your selection.
Combine op.select() with other operations like op.where() or op.orderBy() for more complex queries.
Use the qualifier parameter to disambiguate columns when working with multiple sources.

# Introduction

# When to Use op.select()

# Parameters

# Simple Example

# Two Views:

# Using op.select() to normalize column and view names

# Removing the schema and view names

# Tips