Adapting NoSQL Syntax to SQL Databases
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. Read more