Important

New database features are no longer connected to engine versions; features are now enabled independently during scheduled update windows, and “major” and “minor” releases no longer exist in Helios. Please visit the release notes to view the latest features available in your database clusters.

createView

Syntax

MongoDB

db.createView (
    <view_name>,
    <view_source>,
    <aggregation_pipeline, { $view: { json:true, inferred:true }}>,
    <collation>
 )

Arguments

view_name: Name of the view.
view_source: Name of the source collection or view.
aggregation_pipeline: An array that contains the aggregation pipelines.
$view: {json:true, inferred:true}: Creates structures (columns) that are optimal for SQL queries and improves the performance of SQL queries that reference the view.
- json: Converts the documents or array fields added to the view to JSON type instead of BSON type.
- inferred: Infers the data type of each field added to the view and creates a top-level column with the inferred type in the view.
collation (Optional): The default collation for the view.

Example

The following example creates a view using the $view: {json:true, inferred:true} option and then uses the view in SQL statements.

Create a view via the MongoDB® endpoint. The following commands create a collection and then use the collection as the source to create a view.

MongoDB

db.exampleCollection.insertMany( [
    { _id: 1, Code: "xv1f", Qty: 45 },
    { _id: 2, Code: "nm3w", Qty: 30 },
    { _id: 3, Code: "qoma", Qty: 20 },
    { _id: 4, Code: "hr3k", Qty: 15 } ] )

db.createView(
    "exampleView",
    "exampleCollection",
    [{ $match: { Qty: { $gt: 25 } } }, {$view:{json:true, inferred:true}}]
  )

db.exampleView.find()

[ { _id: 1, Code: 'xv1f', Qty: 45 },
  { _id: 2, Code: 'nm3w', Qty: 30 } ]

MongoDB

db.exampleView.find({Qty:{$gt:40}})

[ { _id: 1, Code: 'xv1f', Qty: 45 } ]

Run the following commands via the SQL endpoint.

SQL

DESC exampleView;

+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| _more_1  | bson       | YES  |     | NULL    |       |
| $_id     | longblob   | YES  |     | NULL    |       |
| _id      | bson       | NO   |     | NULL    |       |
| doc      | JSON       | YES  |     | NULL    |       |
| doc.Code | longtext   | YES  |     | NULL    |       |
| doc.Qty  | bigint(21) | YES  |     | NULL    |       |
| doc._id  | bigint(21) | YES  |     | NULL    |       |
+----------+------------+------+-----+---------+-------+

This command shows that a top-level column is added for each field in the view.

The following command queries the view using SQL:

SQL

SELECT `doc._id` '_id', `doc.Code` 'Code', `doc.Qty` 'Qty'
FROM exampleView
WHERE `doc.Qty` > 40;

+-----+------+-----+
| _id | Code | Qty |
+-----+------+-----+
|   1 | xv1f |  45 |
+-----+------+-----+

The following SQL statement shows information on how this query is executed:

SQL

EXPLAIN SELECT `doc._id` '_id', `doc.Code` 'Code', `doc.Qty` 'Qty'
FROM exampleView
WHERE `doc.Qty` > 40;

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather partitions:all alias:remote_0 parallelism_level:segment                                                                                                    |
| Project [BSON_EXTRACT_BIGINT(exampleView._id) AS _id, BSON_EXTRACT_STRING(exampleView._more,'Code') AS Code, BSON_EXTRACT_BIGINT(exampleView._more,'Qty') AS Qty] |
| ColumnStoreFilter [BSON_EXTRACT_BIGINT(exampleView._more,'Qty') > 40 AND BSON_EXTRACT_BIGINT(exampleView._more,'Qty') > 25]                                       |
| ColumnStoreScan dbTest.exampleCollection AS exampleView, SORT KEY __UNORDERED () table_type:sharded_columnstore                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

createView

On this page

Syntax

Arguments

Example

Was this article helpful?

On this page

Was this article helpful?