Queries on databases

MongoDB

The Mongo DB query creation window contains 3 tabs:

• Data Selection: visual designer allowing to build step by step the query.

• Results: execute the query configured in the "Data selection" tab and display the result.

• Cache: allows to activate the cache on this query.

Mongo DB queries are created by selecting a collection, then adding different steps to the query.

Once you have selected the collection from the list box, this collection will go through a series of operations. The output of each step will be the input for the next operation.

The different Mongo aggregation steps available are as follows:

Step	Description
$addFields	Adds new fields to documents. Similar to$project, $addFieldsremodels each document in the stream; specifically, adding new fields to the output documents that contain both the existing fields from the input documents and the newly added fields. $setis an anlias for$addFields.
$bucket	Categorizes incoming documents into groups, called "buckets", based on an expression and specified "bucket" boundaries.
$bucketAuto	Categorizes incoming documents into a specific number of groups, called "buckets", based on a specified expression. The "buckets"' boundaries are automatically determined in an attempt to evenly distribute the documents into the specified number of "buckets".
$count	Returns a count of the number of documents at this point in the aggregation pipeline.
$facet	Processes multiple aggregation pipelines in a single step on the same set of input documents. Allows the creation of multi-faceted aggregations capable of characterizing data on multiple dimensions, or facets, in a single step.
$graphLookup	Performs a recursive search on a collection. At each output document, adds a new array field that contains the recursive search results for that document.
$group	Groups input documents by a specified identifier expression and applies the accumulator expression(s), if specified, to each group. Consumes all input documents and produces one document for each separate dashboard item group. The output documents contain only the identifier field and, if specified, the accumulator fields.
$limit	Passes the first n unmodified documents to the pipeline where n is the specified limit. For each input document, outputs one document (for the first n documents) or zero documents (after the first n documents).
$lookup	Performs a left outer join to another collection in the same database to filter the documents in the "joined" collection for processing.
$match	Filters the document stream to allow only matching documents to pass unchanged to the next stage of the pipeline.$matchuses standard MongoDB queries. For each input document, generates one document (a match) or zero documents (no match).
$merge	Writes the resulting documents from the aggregation pipeline into a collection. The step can incorporate (insert new documents, merge documents, replace documents, keep existing documents, fail the operation, process documents with a custom update pipeline) the results into an output collection. To use the$mergescene, it must be the last step in the pipeline.
$out	Writes the resulting documents from the aggregation pipeline into a collection. To use the$outscene, this must be the last step in the pipeline.
$project	Remodels each document in the pipeline, for example by adding new fields or deleting existing ones. For each input document, generates a document. See also$unsetto delete existing fields.
$replaceRoot	Replaces a document with the specified embedded document. The operation replaces all existing fields in the input document, including the_id field. Specify an embedded document in the input document to promote the embedded document to the next level.
$replaceWith	Replaces a document with the specified embedded document. The operation replaces all existing fields in the input document, including the_idfield. Specify an embedded document in the input document to promote the embedded document to the top level.
$skip	Ignores the first n documents where n is the specified skip number and passes the remaining unmodified documents to the pipeline. For each input document, outputs zero documents (for the first n documents) or one document (if after the first n documents).
$sort	Rearranges the document stream according to a specified sort key. Only the order changes; the documents remain unchanged. For each input document, generates a document.
$sortByCount	Groups incoming documents according to the value of a specified expression, then calculates the number of documents in each separate dashboard item group.
$unionWith	Performs a union of two collections; that is, combines the pipeline results from two collections into a single result set. New in version 4.4.
$unwind	Deconstructs an array field from the input documents to generate a document for each element. Each output document replaces the array with an element value. For each input document, generates n documents where n is the number of elements in the array and can be zero for an empty array.

SQL

• Query creation: visual designer allowing you to build your query step by step.

• SQL Query: allows you to have the output of your query in SQL language.

• Results: executes the query configured in the "Query creation" tab and displays the result.

• Cache: allows you to activate the cache on this query.

• History: allows you to use queries that have already been used for this database.