Generated Columns and JSON

Generated columns

The engine supports both VIRTUAL and STORED generated columns. A virtual column is computed by the server 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.

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 TidesDB tables, evaluated by the server.

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

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.