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?