Using Query Builder in NoSQL DB

You have several features in Studio that make it easy to build apps and provide a vast range of functionalities to make the best of data from your different sources including NoSQL databases like MongoDB, DynamoDB, Redis, and so on.

Now let us consider an example to get data based on defined queries from the MongoDB database. You are aware that the MongoDB data is available in the key-value form. Now customized run-time querying of such data can be needed at times like providing an option to the user to choose a key to further base the search on and display it in a table grid control.

So in such cases, you can make use of the Query Builder control and make use of the pre-defined operators like equal to, greater than, smaller than, $in and so on as well as add Custom Operators.

The Query Builder property Dynamic Operators enables you to provide Custom operators that can be used for customized querying of NoSQL type data. It is important to remember that whenever you add the dynamic operators, the parameter values that you pass should be as per the data type format applicable. For example for an operator like $All which is used to filter records that contain All the objects from the array, you should provide the value in an array form.

Now let us consider an example to bind data from a MongoDB Connector to a table grid control and use a Query Builder Control to further customize the query.

You would need a query configured in the Connector configuration to pass the Query for a Dynamic filter. The Action will be a find action and you would be passing the Filter variable which is a JSON type field. You will be passing the Query generated from the Query builder to this variable when you configure the connector.

For the sake of this example, in the Query Builder let us add the Dynamic Operator to filter to match all elements specified in the query. We would be comparing the data from a database that provides reviews and details about different types of tourist accommodations. In the example, we have added the Schema which provides all the fields that we want to provide to build the query.

For the Amenities, you can see that we have provided enum which enables you to provide or limit the different options available. So for the available operators like equal to, in, not in, not equal to and so on, there are three options available: “Internet, Wifi, and Kitchen.” However for the other fields, namely, Number of reviews and Superhost?, you do not have the same options available. We have simply defined the fields with the respective data types as integer and boolean.

So now for the three fields, we will have the default operators to build the query.

If you consider the Dynamic Operators property of the Query Builder we are providing the custom operators that can be used in addition to the default operators available.

So in this case the Dynamic Operator for filtering records matching All objects from the array will be as follows:

{

"label": "$All",

"value": "$all"

}

Where Label is the operator name as it appears in the builder and the value is the actual Operator as it is defined in NoSQLs like MongoDB.

There would be others too. In this example, we have also added a custom one as well as one to check the Type.

So now if you run this form you can see that in the Query Builder there are rules that you can add and the available operators. In this list of Operators, you can see the Dynamic Operators.

So whenever you select the Operator your Query Builder will generate the query. You need to use the QueryBuilder.Query value and pass it to the connector to generate the query to fetch data from the NoSQL.

In this example let us also add a text control that shows the Query generated with the Query Builder.

When you run the form you can see the query that is passed and the table grid contains the data as per the filter used. In this example we would be filtering data where Amenities available are Cable TV and Television. Note that we have added them as an array as the respective operator needs it to be provided as an array.

Also you can see the Query generated which is reflected in the Text input control.

Thus you can use the Query builder to generate Queries with Dynamic Operators that provide you the flexibility to further enhance your Connector actions

.