TimescaleDB: PostgreSQL for Time-Series Data — Hypertables, Compression, and Continuous Aggregates

-- Create a table with a JSON column
CREATE TABLE products (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sku         VARCHAR(64)  NOT NULL,
  attributes  JSON         NOT NULL,
  created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

-- Insert a valid document — MySQL validates and converts to binary
INSERT INTO products (sku, attributes) VALUES (
  'SHOE-001',
  '{"color": "navy", "sizes": [7, 8, 9, 10], "material": "leather", "weight_kg": 0.6}'
);

-- This will FAIL with ER_INVALID_JSON_TEXT — TEXT columns would silently accept it
INSERT INTO products (sku, attributes) VALUES (
  'BAD-002',
  '{"color": "red", sizes: [8, 9]}'  -- missing quotes around key
);

-- Full function form — returns JSON type (quoted strings, JSON arrays, etc.)
SELECT
  sku,
  JSON_EXTRACT(attributes, '$.color')       AS color,
  JSON_EXTRACT(attributes, '$.sizes[0]')    AS first_size,
  JSON_EXTRACT(attributes, '$.weight_kg')   AS weight
FROM products;

-- Path expression syntax:
--   $.key          — top-level key
--   $.key.nested   — nested key
--   $.array[0]     — first array element
--   $.array[last]  — last element (MySQL 8.0.27+)
--   $.**           — wildcard: all descendants

-- -> is syntactic sugar for JSON_EXTRACT(); returns quoted JSON string
SELECT sku, attributes->'$.color' AS color FROM products;
-- Result: "navy"  (with quotes — still a JSON string type)

-- ->> is JSON_UNQUOTE(JSON_EXTRACT()); returns unquoted plain string
SELECT sku, attributes->>'$.color' AS color FROM products;
-- Result: navy  (no quotes — a plain VARCHAR-like value)

-- Use ->> when you want to compare with a string literal directly
SELECT * FROM products WHERE attributes->>'$.color' = 'navy';

-- Nested and array paths work the same way
SELECT
  sku,
  attributes->>'$.material'   AS material,
  attributes->'$.sizes[1]'    AS second_size
FROM products;

-- All supported path expression forms
SELECT
  -- Scalar key
  attributes->>'$.color'                          AS color,
  -- Nested object
  attributes->>'$.shipping.carrier'               AS carrier,
  -- Array element by index
  attributes->'$.sizes[0]'                        AS size_0,
  -- Array element from end (8.0.27+)
  attributes->'$.sizes[last]'                     AS last_size,
  -- All values under a key (returns JSON array)
  JSON_EXTRACT(attributes, '$.tags[*]')           AS all_tags,
  -- Double wildcard: all values at any depth
  JSON_EXTRACT(attributes, '$**.name')            AS all_names
FROM products;

-- JSON_SET: INSERT if path does not exist, REPLACE if it does
UPDATE products
SET attributes = JSON_SET(attributes,
  '$.color',      'midnight-blue',   -- replaces existing key
  '$.season',     'winter'           -- inserts new key
)
WHERE sku = 'SHOE-001';

-- JSON_INSERT: INSERT only — silently ignores if path already exists
UPDATE products
SET attributes = JSON_INSERT(attributes,
  '$.season',    'spring',   -- ignored if $.season already set
  '$.new_field', 'hello'     -- inserted if missing
)
WHERE sku = 'SHOE-001';

-- JSON_REPLACE: REPLACE only — silently ignores if path does NOT exist
UPDATE products
SET attributes = JSON_REPLACE(attributes,
  '$.color', 'charcoal'   -- only updates if $.color already present
)
WHERE sku = 'SHOE-001';

-- JSON_REMOVE: delete one or more paths
UPDATE products
SET attributes = JSON_REMOVE(attributes,
  '$.season',
  '$.new_field'
)
WHERE sku = 'SHOE-001';

-- JSON_MERGE_PATCH: RFC 7396 merge — great for partial updates from API payloads
-- Keys in the patch overwrite the target; keys set to null are removed
UPDATE products
SET attributes = JSON_MERGE_PATCH(attributes,
  '{"color": "black", "weight_kg": null}'   -- updates color, removes weight_kg
)
WHERE sku = 'SHOE-001';

-- JSON_CONTAINS: check if a value exists anywhere in a document
-- Returns 1 (true) or 0 (false)
SELECT sku FROM products
WHERE JSON_CONTAINS(attributes, '"leather"', '$.material');

-- JSON_OVERLAPS: true if two JSON documents share at least one value (8.0.17+)
-- Useful for checking array intersection
SELECT sku FROM products
WHERE JSON_OVERLAPS(attributes->'$.sizes', '[8, 9]');

-- JSON_SEARCH: find the path(s) where a string value appears
-- 'one' = first match, 'all' = all matches (returns JSON array of paths)
SELECT sku,
  JSON_SEARCH(attributes, 'one', 'leather')  AS path_to_leather,
  JSON_SEARCH(attributes, 'all', 'cotton')   AS all_cotton_paths
FROM products;

-- Option 1: Virtual generated column + index
ALTER TABLE products
  ADD COLUMN color_gen VARCHAR(64)
    GENERATED ALWAYS AS (attributes->>'$.color') VIRTUAL,
  ADD INDEX idx_color (color_gen);

-- Now this query uses the index:
SELECT sku FROM products WHERE color_gen = 'navy';

-- Option 2: Functional index directly on the JSON path (MySQL 8.0.13+)
-- No separate column needed; the index stores the expression result
ALTER TABLE products
  ADD INDEX idx_color_functional ((attributes->>'$.color'));

-- MySQL optimiser picks up the functional index automatically:
SELECT sku FROM products WHERE attributes->>'$.color' = 'navy';
-- EXPLAIN will show: key = idx_color_functional

-- Verify with EXPLAIN
EXPLAIN SELECT sku FROM products WHERE attributes->>'$.color' = 'navy'\G

-- Create a multi-valued index on the sizes array
-- CAST(... AS UNSIGNED ARRAY) tells MySQL each element is an unsigned integer
ALTER TABLE products
  ADD INDEX idx_sizes ((CAST(attributes->'$.sizes' AS UNSIGNED ARRAY)));

-- Query using MEMBER OF operator — uses the multi-valued index
SELECT sku FROM products WHERE 9 MEMBER OF (attributes->'$.sizes');

-- Query using JSON_CONTAINS — also uses the multi-valued index
SELECT sku FROM products
WHERE JSON_CONTAINS(attributes->'$.sizes', '9');

-- Query using JSON_OVERLAPS — also index-eligible
SELECT sku FROM products
WHERE JSON_OVERLAPS(attributes->'$.sizes', '[8, 9, 10]');

-- Verify the index is used:
EXPLAIN SELECT sku FROM products WHERE 9 MEMBER OF (attributes->'$.sizes')\G
-- type: ref, key: idx_sizes, Extra: Using where

-- Multi-valued index on a string array (tag slugs, category IDs, etc.)
ALTER TABLE products
  ADD INDEX idx_tags ((CAST(attributes->'$.tags' AS CHAR(64) ARRAY)));

SELECT sku FROM products
WHERE 'waterproof' MEMBER OF (attributes->'$.tags');