> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/server-usage/storage-engines/tidesql/tidesql-generated-columns-and-json.md).

# TideSQL Generated Columns and JSON

Virtual and stored generated columns in TideSQL, and the pattern for indexing JSON paths through stored generated columns.

## Generated Columns

The engine supports both `VIRTUAL` and `STORED` generated columns. The server recomputes a virtual column each time the row is read, so the value it returns never comes from the stored row. A query answered from an index on that column alone takes the value from the index entry instead. A stored column is computed on write and persisted with the row, so it reads back without recomputation. Either kind can carry a secondary index, including a `UNIQUE` one, and the engine re-keys the index entry when an `UPDATE` to a base column changes the generated value. The JSON section below uses stored columns for this pattern.

```sql
CREATE TABLE orders (
  id       INT PRIMARY KEY,
  price    DECIMAL(10,2),
  qty      INT,
  total    DECIMAL(10,2) AS (price * qty) VIRTUAL,
  category VARCHAR(10) AS (CASE WHEN price >= 100 THEN 'premium' ELSE 'standard' END) VIRTUAL
) ENGINE=TIDESDB;

INSERT INTO orders (id, price, qty) VALUES (1, 49.99, 3);
SELECT * FROM orders;   -- total = 149.97, category = 'standard'
```

## JSON

MariaDB's `JSON` type is an alias for a text type, so JSON storage and the JSON functions such as `JSON_VALUE()`, `JSON_EXTRACT()`, `JSON_SET()`, and `JSON_CONTAINS()` work normally on TideSQL tables, evaluated by the server.

For efficient filtering on JSON paths, extract the paths you care about into stored generated columns and index those:

```sql
CREATE TABLE docs (
  id   INT NOT NULL PRIMARY KEY,
  data LONGTEXT,
  name VARCHAR(100) AS (JSON_VALUE(data, '$.name')) PERSISTENT,
  age  INT          AS (JSON_VALUE(data, '$.age'))  PERSISTENT,
  KEY idx_name (name),
  KEY idx_age (age)
) ENGINE=TIDESDB;

INSERT INTO docs (id, data) VALUES
  (1, '{"name":"Alice","age":30,"tags":["admin","dev"]}'),
  (2, '{"name":"Bob","age":25,"tags":["dev"]}');

SELECT * FROM docs WHERE name = 'Alice';   -- uses idx_name
SELECT * FROM docs WHERE age >= 30;        -- uses idx_age
```

This gives engine-native indexing through ordinary secondary indexes while keeping JSON manipulation in standard SQL.

<sub>*This page is licensed: GPLv2*</sub>