# Kai Data Storage Model

By default, tables created using SingleStore Kai store data in the BSON format. The `_id` field of the document is added to the `_id` column, and the rest of the fields in the document are added to the `_more` column of the table in SingleStore.

Tables created using SingleStore Kai have the following columns:

| Column Name | Data Type          | Description                                                                                                                                                                                                                                                                                                                   |
| ----------- | ------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `_id`       | BSON NOT NULL      | The`_id`field required in allMongoDB®documents.                                                                                                                                                                                                                                                                               |
| `_more`     | BSON NOT NULL      | Contains all the fields in the document, except the`_id`field.                                                                                                                                                                                                                                                                |
| `$_id`      | PERSISTED LONGBLOB | Used to implementSingleStoreunique indexes onMongoDB®collections, similar to BSON unique indexes. This column contains the`_id`field normalized using the`BSON_NORMALIZE`or`BSON_NORMALIZE_NO_ARRAY`function, which transform the BSON data into a byte stream that preserves the BSON comparison when compared byte-by-byte. |

For example, run the following command on a Kai-enabled workspace to create a collection named `exampleCollection`: 

```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 } ] )
```

This `exampleCollection` is stored in SingleStore as:

```MongoDB
db.runCommand({sql:"SHOW CREATE TABLE dbTest.exampleCollection"}).cursor.firstBatch[0]["Create Table"]

```

```output

CREATE TABLE `exampleCollection` (
  `_id` bson NOT NULL,
  `_more` bson NOT NULL COMMENT 'KAI_MORE' ,
  `$_id` as BSON_NORMALIZE_NO_ARRAY(`_id`) PERSISTED longblob COMMENT 'KAI_AUTO' ,
  SHARD KEY `__SHARDKEY` (`$_id`),
  UNIQUE KEY `__PRIMARY` (`$_id`) USING HASH,
  SORT KEY `__UNORDERED` ()
) COMMENT 'KAI_CID:2QZxxxx1xxxxxxxx' AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES'
```

The `COMMENT` clauses shown in this `CREATE TABLE` SQL statement, for example, `COMMENT 'KAI_AUTO'`, `COMMENT 'KAI_MORE'`, or `COMMENT 'KAI_CID:2QZxxxx1xxxxxxxx'`, are reserved for internal use. Using these `COMMENT` clauses in a `CREATE TABLE` statement manually may lead to unexpected behavior.

When an index is created using the Kai API, SingleStore generates a computed column with the comment `KAI_AUTO` to indicate that this column was created using the Kai API and stores this information in the table metadata. This information is used during query generation for MongoDB® queries. SingleStore recommends creating MongoDB® collections and indexes using the SingleStore Kai endpoint (`mongodb://`) if you intend to query these collections using the Kai API.

## View the BSON Data

To view the BSON data, SingleStore recommends the following:

* Use the [Kai Shell](https://docs.singlestore.com/cloud/reference/singlestore-kai/getting-started-with-singlestore-kai/#section-idm4599119925816033938378931294.md) or other supported MongoDB® tools, such as MongoDB® Compass. 
* Cast the columns to JSON using the following SQL command:
  ```sql
  SELECT _id :> JSON , _more :> JSON FROM <table_name>;
  ```

## Example

To view the documents in a collection named `exampleCollection` using the Kai Shell, run the following command:

```mongodb
db.exampleCollection.find()

```

```output

[ { _id: 4, Code: 'hr3k', Qty: 15 },
  { _id: 3, Code: 'qoma', Qty: 20 },
  { _id: 1, Code: 'xv1f', Qty: 45 },
  { _id: 2, Code: 'nm3w', Qty: 30 } ]
```

To view the documents in `exampleCollection` using SQL commands, run the following command in the SQL command-line:

```sql
SELECT _id :> JSON AS _id, _more :> JSON AS _more FROM exampleCollection;

```

```output

+-----+--------------------------+
| _id | _more                    |
+-----+--------------------------+
| 4   | {"Code":"hr3k","Qty":15} |
| 3   | {"Code":"qoma","Qty":20} |
| 1   | {"Code":"xv1f","Qty":45} |
| 2   | {"Code":"nm3w","Qty":30} |
+-----+--------------------------+

```

***

Modified at: April 14, 2025

Source: [/cloud/reference/singlestore-kai/kai-data-storage-model/](https://docs.singlestore.com/cloud/reference/singlestore-kai/kai-data-storage-model/)

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