# createView

Creates a non-materialized view from the result of the specified aggregation pipeline. Views in SingleStore are non-writable and they act as read-only collections (tables). Refer to [CREATE VIEW](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/create-view.md) for more information.

This command is an extension of the MongoDB® [createView](https://www.mongodb.com/docs/manual/reference/method/db.createView/) command.

## 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()

```

```output

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

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

```

```output

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

Run the following commands via the SQL endpoint.

```sql
DESC exampleView;

```

```output

+----------+------------+------+-----+---------+-------+
| 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;

```

```output

+-----+------+-----+
| _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;

```

```output

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

***

Modified at: August 9, 2024

Source: [/cloud/reference/singlestore-kai/singlestore-extension-commands/createview/](https://docs.singlestore.com/cloud/reference/singlestore-kai/singlestore-extension-commands/createview/)

(An index of the documentation is available at /llms.txt)