Binding Variables

Once you build queries and aggregation pipelines, you may wish to open them up for others to use. JSON Studio includes an external gateway through which users can invoke a saved query/pipeline and get the results. This external gateway creates a Web service for each such query/pipeline and is invoked using HTTP.

When such queries/pipelines are turned into APIs, they often need to be parameterized. For example, a query might filter all documents that have a certain value for a field. Rather than hard-coding this value, the developer of the query/pipeline can define a variable that is mapped to a value by the caller of the API. These are called bind variables.

Bind variables can be used to define a query in the Finder or in a filter(match) stage in the Aggregation Builder.

Each bind variable has a name and a value. The query or stage are defined using the name of the bind variable. A value must be bound to the variable in order for the correct results to be produced. This binding can be done within JSON Studio (for example when you are developing queries/pipelines or even for running the same query with different values without modifying the query) or as part of a Gateway call.

NOTE: Do not start your bind variable name with LMRM_ - these are reserved for built-in bind variables.

Let’s look at an example of how bind variables are used in the Finder. In this example we will use documents describing properties of stocks listed on the exchange and each document has a field by the name of Sector. If we want a query that returns all the stocks in the Healthcare sector we would add a condition looking like:

"Sector" : "Healthcare"

However, it may be that we will want to run this query once for Healthcare, once for Utilities, once for Technology - etc. Therefore, instead, we place this condition:

"Sector" : "$$sec"

“$$sec” is a placeholder and sec is the name of the bind variable. Note that the syntax for the variable must be “$$<var name>” (including the double quotes) even if you later want to bind a value that is not a string.

If we just run this as the query the result set will be empty because there is no stock in the “$$sec” sector. To complete the example we need to bind a value to the variable. For now let’s look at how binding occurs within the GUI and we will look at how binding occurs through the Gateway in the next section.

On the top right of each application in the links area is a link called “Bind”. Click on this link to bind values to variables. If this is the first time you do this the table will be empty. Click on the + to add a new binding and enter sec as the name and “Healthcare” as the value as shown below. Don’t forget the double quotes - because the value you want to place is a string and the quotes allow the system to know this is a string. If you wanted to bind the number 7 for example, you would type in 7 without quotes.

_images/binder1.jpg

Click the save icon and close the pop-up. Now run the query again and you will get all the healthcare stocks. If you want to get all the Services stock re-open the binding editor and put in “Services”. You do not change the query.

Changing a bind value can be done on any application and all values are per-session. This allows you to bind values also in the Visualizer. For example, the query or pipeline that you use before navigating to the Visualizer may include a bind variable. As you are viewing the chart you may wish to view the data for a different value bound in the query. Click on Bind in the Visualizer itself and change the value, then re-run the query using the play button at the top-left and the chart will be updated. You never need to leave the Visualizer.

In addition to typing in bind values, you can also pick among a list - for example as shown below:

_images/binder2.jpg

When a variable has a list associated with it, binding a value to it is done by selecting from the drop down; no manual entry is possible.

Because for the most part such drop-downs are used when you need to pick from a list of possible values of a field, building bind variables is done through the “distinct” feature of the Schema Analyzer. For example, in the stocks collection shown below you would click on the flag icon to show all distinct values for this field in this collection (shown then on the right-hand-side in the Distinct values tab, and then click on the $$ icon. This adds a new bind variable (which you need to name) with the possible values being the distinct values.

_images/binder3.jpg

Query-based drop-downs

In the previous section you saw how to define a a pull down using a distinct command. The Studio also lets you define a pull down that will be based on a query of data in the database - useful for building larger pull downs and for controlling what exactly a pull down will show. This is based on a special collection in a designated database that you configure.

The collection that will be used to house values for populating pull downs is called group_members and it must reside in a database called sonargd that lives in an instance specified in the web.xml file as:

<context-param>
 <param-name>sonarg.monitoringURI</param-name>
 <param-value>mongodb://<user>:<pwd>@<host>:<port>/<auth db></param-value>
</context-param>

The group_members collection should have documents of the form:

{
   "Group Type": "..",
   "Group Description":".."
}

Each of the values is a string.

Then, within a query you can use a bind variable (e.g. assume it is called $$bv) and in the API use the string:

lmrmGroupBind.bv.<a string to match on the type>

Let’s look at an example. Assume that you have a flights collection and you wish to have a pull down for the carriers. Insert three documenst into the group_members collection in the sonargd database:

> db.group_members.find()
{ "_id" : ObjectId("5659f22c2ea79329943649b6"), "Group Type" : "bv_example", "Group Description" : "AA" }
{ "_id" : ObjectId("5659f22f2ea79329943649b7"), "Group Type" : "bv_example", "Group Description" : "DL" }
{ "_id" : ObjectId("5659f2362ea79329943649b8"), "Group Type" : "bv_example", "Group Description" : "UA" }

Now define the query using a bind variable by defining a condition:

"UniqueCarrier" : "$$bv1"

Save the query with a name and click the publish button. Check the “Based on Group Data” checkbox for bv1 and click the Publish URL button on the lower part of the popup. The API now includes data that will be retrieved from the group_members collection.

Built-in Date Bind Variables

There are special date-related bind variables that you can use without defining as shown below. These are useful when used with date comparison conditions and date aggregation operators:

  • $$LMRM_NOW. This is always replaced with the current datetime so that you can run queries with incremental selection by time. You can specify an offset (in milliseconds) - for example, for a time that is 60 seconds prior to now use $$LMRM_NOW-60000 and for a time that is 30 seconds from now use $$LMRM_NOW+30000. You can also specify an offset in days using $$LMRM_NOW-3DAYS or $$LMRM_NOW+5DAYS.
  • $$LMRM_DAYOFYEAR. Current date converted to a number between 1 and 366.
  • $$LMRM_DAYOFMONTH. Current date converted to a number between 1 and 31.
  • $$LMRM_DAYOFWEEK. Current date converted to a number between 1 and 7.
  • $$LMRM_YEAR. Current date converted to the full year.
  • $$LMRM_MONTH. Current date converted to a number between 1 and 12.
  • $$LMRM_WEEK. Current date converted to a number between 0 and 53
  • $$LMRM_HOUR. Current date converted to a number between 0 and 23.
  • $$LMRM_MINUTE. Current date converted to a number between 0 and 59.
  • $$LMRM_SECOND. Current date converted to a number between 0 and 59. May be 60 to account for leap seconds.
  • $$LMRM_MILLISECOND. Current date converted to a number between 0 and 999.
  • $$LMRM_TZOFFSET, Offset in milliseconds between the Studio/Gateway’s timezone and UTC
  • $$LMRM_USERNAME. Replaces the currently logged-in username.
  • $$LMRM_LASTRUN. Allows you to persist the last run time (in milliseconds since epoch) and use it within the query. This is useful when you have an incremental query that runs on a scheduled basis and at any point in time you want to process only records since the last time you ran. In this case add a condition in your query using the $$LMRM_LASTRUN placeholder and every time it runs it will lookup the value from the lmrm__last_run collection, and when it completes it persists the new “last time” so that next time it runs it continues from the same place. There is a single such value for a combination of collection name+query name+type (find or aggregation). Note that when you run such a query within the Studio the value used with be that of now.
  • $$LMRM_LASTDID. This is available only when connected to SonarW. Since SonarW has a sequential document ID mechanism you can iterate over documents in a collection and not miss any document (including when a document was updated). Use a condition such as _id: {$did: “$$LMRM_LASTDID”} and the system will replace this with a document ID range that includes exactly those documents added/changed since the last run. Note that this does not cover update-in-place columns - use the last update time column for this case. You can limit the size of a range of each iteration of the $did using a preference in the limit section of the aggregation builder in JSON Studio. For example, if the collection you iterate on has 10 Billion documents but you want to ensure that every sequential iteration will not run on more than 100 Million documents, set the limit in JSON Studio for the user that will be invoking the query via the gateway (e.g. the user configured for the dispatcher).

Table Of Contents

Previous topic

Visualization Geo-Spatial Data with the Mapper

Next topic

Using the Gateway (External API)

Copyright © 2013-2016 jSonar, Inc
MongoDB is a registered trademark of MongoDB Inc. Excel is a trademark of Microsoft Inc. JSON Studio is a registered trademark of jSonar Inc. All trademarks and service marks are the property of their respective owners.