Are you tired of remembering different database commands or scouring the internet for a complete source?
Well, you’ve come to the right place! This blog series, The Database Cheatsheet, contains everything you need to know about database commands, covering everything developers and database professionals need to know to use and operate their databases.
First, here is a quick disclaimer: these commands are specifically tailored for SingleStore. If you plan to use them on other database platforms, ensure you check the documentation to confirm compatibility or required syntax changes. Need a database to get started with? Start free with SingleStore Helios® Cloud to get up and running in minutes.
SingleStore Database Cheat Sheet
- SingleStore core concepts
- SQL + Kai commands
- Vector operations tips
In this post, we’ll cover JSON operations, partitioning, full-text search and cutting-edge vector functionality. These capabilities help you address a broad range of use cases — from real-time analytics to AI-driven applications — all within a single, unified database.
JSON operations
JSON (JavaScript Object Notation) is a lightweight, human-readable format used for storing and exchanging data. In relational databases, JSON columns allow you to store semi-structured data, meaning data that doesn’t always fit neatly into fixed table schemas.
This is useful when dealing with dynamic attributes, nested objects or lists where the structure may change over time — like user profiles, event metadata, API responses or log data. Instead of creating many new columns, you can store this information in a JSON field while still being able to query it like structured data.
Creating a table with a JSON column
To store JSON data in SingleStore, you can simply define a table with a JSON column:
CREATE TABLE table_name (id BIGINT AUTO_INCREMENT PRIMARY KEY,data JSON,SHARD KEY (id));
Inserting JSON data
Once the table is created with a JSON field, you can insert JSON objects directly into the table:
INSERT INTO table_name (json_column) VALUES ('{"key": "value"}');
For example, if we had a json_posts table and wanted to insert a JSON object into it (with multiple fields within the JSON object), we could do it like so:
INSERT INTO json_posts (data)VALUES ('{"title": "Post One", "tags": ["news", "events"]}');
Querying JSON data
You’ll likely also need to be able to query the JSON data as well. To extract specific fields from JSON data, you can use the JSON_EXTRACT_JSON function:
SELECT JSON_EXTRACT_JSON(json_column, '$.key') FROM table_name;
To get a more concrete example for the json_posts table we mentioned earlier, we could extract the title field from the JSON object like this:
SELECT JSON_EXTRACT_JSON(data, '$.title') AS title FROM json_posts;
Running this query, the output would look similar to this:
title |
Post One |
Using JSON columns is a great way to flexibly store unstructured data while still being able to query key fields within the JSON data.
Partitioning
Partitioning is a data management strategy that divides a table’s data into smaller, more manageable chunks. In SingleStore, range-based partitioning is commonly used to optimize performance for time-series data, event logs and datasets that grow over time.
Instead of scanning an entire table when running a query, SingleStore only looks at the relevant partition, improving query efficiency and reducing resource usage.
Creating a partitioned table
Depending on the data you are storing within your tables, there are various ways to create a partitioned table. For example, if we had a table that contained a field for event_date, we could create a partitioned table based on date ranges:
CREATE TABLE table_name (id BIGINT,event_date DATE,data JSON,SHARD KEY (id),KEY (event_date))PARTITION BY RANGE (event_date) (PARTITION p_2023 VALUES LESS THAN ('2024-01-01'),PARTITION p_2024 VALUES LESS THAN ('2025-01-01'));
This ensures queries on specific date ranges only scan the relevant partition, improving query speed and efficiency.
Full-text search
Traditional SQL queries are not optimized for searching large amounts of text efficiently. Full-text search allows databases to quickly find words and phrases in text columns — making it ideal for search engines, documentation systems, user-generated content and chat applications.
Creating a table with full-text indexing
To enable text search, create a table with a FULLTEXT INDEX:
CREATE TABLE articles (id BIGINT AUTO_INCREMENT PRIMARY KEY,content TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,FULLTEXT INDEX ft_idx (content),SHARD KEY (id));
SELECT * FROM articles WHERE MATCH(content) AGAINST ('database performance');
The results of this query would perform a full-text search and return a response that would look like this:
id | content |
2 | “Database performance tips and optimizations” |
This makes SingleStore ideal for real-time text search applications.
Vector operations
Vector embeddings are used in AI and machine learning applications to represent complex data (like text, images and audio) in numerical form. SingleStore’s native vector support makes it easy to store and query embeddings for semantic search, recommendations, anomaly detection and similarity matching.
Creating a table with a vector column
To store vectors in SingleStore, you’ll need to define a VECTOR(n) column. For example, this could be done with this syntax:
CREATE TABLE embeddings (id BIGINT AUTO_INCREMENT PRIMARY KEY,description TEXT,embedding VECTOR(1536),SHARD KEY (id));
Vector search operations
Once the vector data is in the database, you’ll also want to be able to query it using various vector operations. Here are a few ways you can do this in SingleStore:
To compare vectors using dot product:
SELECT DOT_PRODUCT(embedding1, embedding2) FROM embeddings;
To compute Euclidean distance:
SELECT EUCLIDEAN_DISTANCE(embedding1, embedding2) FROM embeddings;
To normalize vectors for cosine similarity:
SELECT DOT_PRODUCT(NORMALIZE_VECTOR(embedding1),NORMALIZE_VECTOR(embedding2)) AS cosine_similarity FROM embeddings;
CREATE INDEX idx_embedding ON embeddings(embedding)USING VECTORMETRIC DOT_PRODUCT;
By indexing vectors, you can dramatically speed up AI-driven search queries — making real-time recommendation systems and similarity searches highly efficient.
Try SingleStore free
In this blog, we explored SingleStore’s unique capabilities, including JSON support, partitioning, full-text search and vector operations. These features allow you to handle structured, semi-structured and AI-driven workloads — all within a single, high-performance database. Check out our other Database Cheatsheet articles or head to the notebook in SingleStore Spaces for easy reference.
Disclaimer
These commands are specifically tailored for SingleStore. While the general SQL syntax for sorting and limiting is often similar across databases, it’s always a good idea to double-check the documentation if you’re using another platform. Need a database to get started with? Sign up for a free SingleStore Helios® trial to get up and running in minutes.
