Kai Data Storage Model

Column Name	Data Type	Description
`_id`	BSON NOT NULL	The `_id` field required in all MongoDB® documents.
`_more`	BSON NOT NULL	Contains all the fields in the document, except the `_id` field.
`$_id`	PERSISTED LONGBLOB	Used to implement SingleStore unique indexes on MongoDB® 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.

View the BSON Data

To view the BSON data, SingleStore recommends the following:

Use the Kai Shell 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

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"]

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'

To view the documents in exampleCollection using the Kai Shell, run the following command:

MongoDB

db.exampleCollection.find()

[ { _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;

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

Kai Data Storage Model

On this page

View the BSON Data

Example

Was this article helpful?

On this page

Was this article helpful?