Over a million developers have joined DZone.

Adapting NoSQL Syntax to SQL Databases

Let's take a look at how Backand adapts a MongoDB-inspired NoSQL query language into SQL statements, allowing you to use whichever idiom lets you feel the most productive.

· Database Zone

Sign up for the Couchbase Community Newsletter to stay ahead of the curve on the latest NoSQL news, events, and webinars. Brought to you in partnership with Coucbase.

One of the greatest strengths of NoSQL databases is the query language used. While SQL databases are essentially required to use SQL queries, NoSQL databases give you the chance to build and manipulate queries as JSON, allowing for far more readable, easier to understand queries in your application. Below we’ll look at how Backand adapts a MongoDB-inspired NoSQL query language into SQL statements, allowing you to use whichever idiom lets you feel the most productive.

Components of a Query

NoSQL queries in Backand consist of the following components:

  • object – the object to query against
  • q (query) – the query to perform
  • fields – the fields to return from the query
  • groupBy – the field to group the results by
  • aggregate – functions applied to columns and fields in the query
  • orderBy – determines how to order the returned data
  • limit – limits the number of records returned

Each of these components has a number of intended uses and can prove flexibility across a large number of queries. Review the documentation for more information on each parameter.

Translating the Query Into SQL

It’s important to note that once a NoSQL query is received by Backand, it is converted into SQL using the following pattern:

SELECT {fields with aggregation}
FROM {object}
WHERE {q}
GROUP BY {groupBy}
ORDER BY {orderBy}
LIMIT {limit}

This can create some strange behavior if you are not familiar with how SQL queries operate. Namely, all fields must be present in the tables of their respective objects, and that aggregation is applied at the field level. You can test your JSON query using the Backand function transformJson, which is documented here.

Conditional Expressions

Conditional expressions for queries are defined in JSON in one of four ways. The first is through a constant comparison against a field’s value. Below, we query for all cities named “New York”:

{ 'city': 'New York' }

The second makes use of operators to perform a more complex comparison. Below, we pull back all objects with a population value greater than 1000:

{ 'population': { '$gt': 1000 } }

The third allows you to specify inclusion within the results of a subquery. For example, the following subquery looks for all records where the product ID is present in a distribution center in Chicago:

{
 'productID': {
  '$in': {
   'object': 'distributions',
   'fields': 'productID',
   'q': {
    'city': 'Chicago'
        }
      }
    }
  }

The fourth example allows you to perform an operation of any of the above queries. This can be a combination using $and or $or, or a negation using $not. For example, to find all cities that are not named “New York” or that have a population of at least $1,000, use the following:

{
  '$or': [
    {
     '$not': { 'location': 'New York'}
    },
    {
     'population': { '$gt': 1000 }
    }
  ]
}

Notice the use of the Array operator ([]) above – this tells the query builder which clauses to combine in the “OR” conditional.

Filters

You also have the capability to specify a NoSQL query as a filter. This allows you to use variables in your queries, giving you more flexibility to fetch exactly the data that you need. Variables are populated with values once they hit the Backand servers. The values populated are either pulled directly from a related table, or they can be provided as an argument in the HTTP request executing the filter. Specify a variable using the following syntax:

'{{ <variable_name> }}'

Simply replace <variable_name> with the variable you wish to use. Please note that the entire variable structure needs to be encased in quotes, so that the final request represents valid JSON. This variable name is populated with the correct value prior to execution of the query.

It is important to note that while Backand will escape constants sent as part of a NoSQL query, values of variables cannot be escaped. This means that you need to be certain that the possible values for the variable being used do not contain unsafe SQL syntax, as that will cause the query to fail (or, worse, have unexpected side effects).

Conclusion

NoSQL queries are familiar to many web developers–particularly those with experience in the MEAN stack. While Backand is built on top of SQL, our NoSQL Query Language allows you to build queries using whichever tool you find most useful. You can build your queries in NoSQL JSON, or write straight SQL syntax as a custom query. In either case, Backand is glad to provide the tools you need to build out your application in the manner that you desire.

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
nosql ,sql ,json ,mongodb ,database

Published at DZone with permission of Itay Herskovits, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}