SET @doc = '{
  "name": "Widget",
  "price": {"usd": 9.99, "cad": 13.49},
  "sizes": ["S","M","L"],
  "tags": ["sale","new"],
  "variants": [
    {"color": "blue", "stock": 10},
    {"color": "red",  "stock": 5}
  ]
}';

SELECT JSON_EXTRACT(@doc, '$.price.usd');          -- 9.99
SELECT JSON_EXTRACT(@doc, '$.sizes[0]');            -- "S"
SELECT JSON_EXTRACT(@doc, '$.sizes[*]');            -- ["S","M","L"]
SELECT JSON_EXTRACT(@doc, '$.variants[*].color');   -- ["blue","red"]
SELECT JSON_EXTRACT(@doc, '$.variants[last].stock');-- 5

SELECT JSON_OBJECT('name', 'Alice', 'age', 30);
-- {"name": "Alice", "age": 30}

SELECT JSON_ARRAY(1, 'two', TRUE, NULL);
-- [1, "two", true, null]

-- Build a JSON response from relational data
SELECT JSON_OBJECT(
  'id',    id,
  'name',  name,
  'email', email,
  'tags',  JSON_ARRAY('mysql', 'developer')
) AS user_json
FROM users
WHERE id = 1;

CREATE TABLE products (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(255),
  meta  JSON
);

INSERT INTO products (name, meta) VALUES
('Widget', '{"color": "blue", "sizes": ["S","M","L"], "price": {"usd": 9.99}}');

-- Three equivalent ways to extract
SELECT JSON_EXTRACT(meta, '$.color') FROM products;   -- "blue" (quoted)
SELECT meta -> '$.color'             FROM products;   -- "blue" (quoted)
SELECT meta ->> '$.color'            FROM products;   -- blue   (unquoted string)

-- Filtering: must use ->> (unquoted) for string comparison
SELECT name FROM products WHERE meta ->> '$.color' = 'blue';   -- works
SELECT name FROM products WHERE meta ->  '$.color' = 'blue';   -- no match (quotes differ)

-- Extract nested value
SELECT meta -> '$.price.usd' FROM products;   -- 9.99

-- Extract array element (0-indexed)
SELECT meta -> '$.sizes[0]' FROM products;    -- "S"
SELECT meta -> '$.sizes[1]' FROM products;    -- "M"

SET @doc = '{"name": "Alice", "age": 30}';

-- JSON_SET: update if exists, insert if not
SELECT JSON_SET(@doc, '$.age', 31, '$.city', 'Toronto');
-- {"name": "Alice", "age": 31, "city": "Toronto"}

-- JSON_INSERT: insert only if path does NOT exist
SELECT JSON_INSERT(@doc, '$.age', 99, '$.city', 'Toronto');
-- {"name": "Alice", "age": 30, "city": "Toronto"}  (age unchanged)

-- JSON_REPLACE: update only if path DOES exist
SELECT JSON_REPLACE(@doc, '$.age', 31, '$.city', 'Toronto');
-- {"name": "Alice", "age": 31}  (city not added)

-- JSON_REMOVE: remove a key
SELECT JSON_REMOVE(@doc, '$.age');
-- {"name": "Alice"}

-- Update JSON column in-place
UPDATE products
SET meta = JSON_SET(meta, '$.price.cad', 13.49)
WHERE id = 1;

-- Aggregate related rows into a JSON array per category
SELECT
  c.name                    AS category,
  JSON_ARRAYAGG(
    JSON_OBJECT('id', p.id, 'name', p.name, 'price', p.price)
    ORDER BY p.price
  )                         AS products_json
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name;
-- Returns one row per category, products column is a JSON array of objects

-- Build a key-value object from rows
SELECT JSON_OBJECTAGG(setting_key, setting_value) AS config
FROM user_settings
WHERE user_id = 42;
-- Returns: {"theme": "dark", "language": "en", "notifications": "1"}

-- Nested aggregation: users with their orders as a JSON array
SELECT
  u.id,
  u.name,
  JSON_ARRAYAGG(
    JSON_OBJECT('order_id', o.id, 'total', o.total, 'date', DATE(o.created_at))
    ORDER BY o.created_at DESC
  ) AS recent_orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY u.id, u.name;

-- Sample: orders with line items stored as a JSON array
CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  items_json  JSON NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO orders (customer_id, items_json) VALUES
(1, '[{"name":"Laptop","qty":1,"price":999.00},{"name":"Mouse","qty":2,"price":25.00}]'),
(2, '[{"name":"Keyboard","qty":1,"price":79.00}]');

-- Expand JSON array into relational rows
SELECT
  o.id                  AS order_id,
  o.customer_id,
  items.product_name,
  items.qty,
  items.unit_price,
  items.qty * items.unit_price AS line_total
FROM orders o,
JSON_TABLE(
  o.items_json,
  '$[*]' COLUMNS (
    product_name VARCHAR(100)   PATH '$.name'  DEFAULT 'Unknown' ON ERROR,
    qty          INT             PATH '$.qty',
    unit_price   DECIMAL(10,2)  PATH '$.price'
  )
) AS items;

-- Result: one row per line item, fully queryable
-- order_id | customer_id | product_name | qty | unit_price | line_total
-- 1        | 1           | Laptop       | 1   | 999.00     | 999.00
-- 1        | 1           | Mouse        | 2   |  25.00     |  50.00
-- 2        | 2           | Keyboard     | 1   |  79.00     |  79.00

-- Aggregate across JSON items directly
SELECT
  o.customer_id,
  SUM(items.qty * items.unit_price) AS order_total,
  SUM(items.qty)                    AS total_items
FROM orders o,
JSON_TABLE(o.items_json, '$[*]' COLUMNS (
  qty        INT            PATH '$.qty',
  unit_price DECIMAL(10,2) PATH '$.price'
)) AS items
GROUP BY o.customer_id;

-- Step 1: add a virtual generated column for the JSON path
ALTER TABLE products
  ADD COLUMN color_gen VARCHAR(50)
    GENERATED ALWAYS AS (meta ->> '$.color') VIRTUAL;

-- Step 2: create an index on the generated column
CREATE INDEX idx_product_color ON products (color_gen);

-- Step 3: queries that filter on color_gen now use the index
SELECT name FROM products WHERE color_gen = 'blue';

-- Verify with EXPLAIN
EXPLAIN SELECT name FROM products WHERE color_gen = 'blue';
-- type: ref, key: idx_product_color  <-- index is used

-- For numeric JSON paths, use a STORED generated column (can be indexed directly)
ALTER TABLE products
  ADD COLUMN price_usd DECIMAL(10,2)
    GENERATED ALWAYS AS (CAST(meta ->> '$.price.usd' AS DECIMAL(10,2))) STORED;

CREATE INDEX idx_product_price ON products (price_usd);

-- Range query now uses the index
SELECT name, price_usd FROM products WHERE price_usd BETWEEN 5 AND 15;

-- Define a schema: name (string, required), age (integer, min 0), email (string)
SET @schema = '{
  "type": "object",
  "required": ["name", "age"],
  "properties": {
    "name":  {"type": "string"},
    "age":   {"type": "integer", "minimum": 0},
    "email": {"type": "string",  "format": "email"}
  },
  "additionalProperties": false
}';

-- Validate a document inline
SELECT JSON_SCHEMA_VALID(@schema, '{"name": "Alice", "age": 30}');    -- 1 (valid)
SELECT JSON_SCHEMA_VALID(@schema, '{"name": "Bob",   "age": -5}');    -- 0 (invalid: age < 0)
SELECT JSON_SCHEMA_VALID(@schema, '{"age": 25}');                      -- 0 (name missing)

-- Use as a CHECK constraint on a table
CREATE TABLE user_profiles (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  profile JSON NOT NULL,
  CONSTRAINT chk_profile_schema
    CHECK (JSON_SCHEMA_VALID(
      '{"type":"object","required":["name","age"],"properties":{"name":{"type":"string"},"age":{"type":"integer","minimum":0}}}',
      profile
    ))
);

-- This INSERT will fail — age is negative
INSERT INTO user_profiles (profile) VALUES ('{"name":"Alice","age":-1}');
-- ERROR 3819: Check constraint violated

SELECT JSON_PRETTY('{"name":"Alice","address":{"city":"Toronto","zip":"M5V 0A1"},"tags":["admin","user"]}');
-- Output:
-- {
--   "name": "Alice",
--   "address": {
--     "city": "Toronto",
--     "zip": "M5V 0A1"
--   },
--   "tags": [
--     "admin",
--     "user"
--   ]
-- }

-- Useful when debugging JSON stored in columns
SELECT id, name, JSON_PRETTY(meta) AS meta_formatted
FROM products
WHERE id = 1G

-- Cache computed API responses in a JSON column
CREATE TABLE api_cache (
  cache_key    VARCHAR(255) NOT NULL PRIMARY KEY,
  payload      JSON         NOT NULL,
  cached_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at   DATETIME     NOT NULL,
  INDEX idx_expires (expires_at)
);

-- Store a cached API response
INSERT INTO api_cache (cache_key, payload, expires_at)
VALUES (
  'product_catalog_v2',
  (
    SELECT JSON_ARRAYAGG(
      JSON_OBJECT('id', p.id, 'name', p.name, 'price', p.price,
                  'category', c.name)
      ORDER BY p.name
    )
    FROM products p
    JOIN categories c ON c.id = p.category_id
    WHERE p.is_active = 1
  ),
  DATE_ADD(NOW(), INTERVAL 1 HOUR)
)
ON DUPLICATE KEY UPDATE
  payload    = VALUES(payload),
  cached_at  = NOW(),
  expires_at = VALUES(expires_at);

-- Read from cache (check expiry first)
SELECT payload
FROM api_cache
WHERE cache_key = 'product_catalog_v2'
  AND expires_at > NOW();

-- Purge expired entries
DELETE FROM api_cache WHERE expires_at < NOW() LIMIT 500;

-- JSON_CONTAINS: check if a value exists anywhere in the document
SELECT JSON_CONTAINS('{"a": 1, "b": 2}', '1', '$.a');    -- 1 (TRUE)
SELECT JSON_CONTAINS('{"a": 1}', '2', '$.a');              -- 0 (FALSE)
SELECT JSON_CONTAINS('[1,2,3]', '2');                       -- 1 (any array element)

-- Check if a JSON array column contains a specific value
SELECT name FROM products
WHERE JSON_CONTAINS(meta -> '$.tags', '"sale"');

-- JSON_CONTAINS_PATH: check if a path exists
SELECT JSON_CONTAINS_PATH('{"a":{"b":1}}', 'one', '$.a.b');     -- 1
SELECT JSON_CONTAINS_PATH('{"a":{"b":1}}', 'one', '$.a.c');     -- 0
SELECT JSON_CONTAINS_PATH('{"a":1}', 'all', '$.a', '$.b');      -- 0 (both must exist)
SELECT JSON_CONTAINS_PATH('{"a":1,"b":2}', 'all', '$.a', '$.b');-- 1

-- JSON_SEARCH: find the path(s) where a string value appears
SET @doc = '{"tags": ["mysql","database","sql"],"category": "mysql"}';

SELECT JSON_SEARCH(@doc, 'one', 'mysql');
-- "$.category"  (first match)

SELECT JSON_SEARCH(@doc, 'all', 'mysql');
-- ["$.category", "$.tags[0]"]  (all matches)

SELECT JSON_SEARCH(@doc, 'all', 'sq%');  -- LIKE-style wildcards
-- ["$.tags[2]", "$.category"]

SELECT JSON_KEYS('{"a": 1, "b": 2, "c": 3}');        -- ["a", "b", "c"]
SELECT JSON_KEYS('{"a": {"b": 1, "c": 2}}', '$.a');  -- ["b", "c"]

SELECT JSON_LENGTH('{"a":1,"b":2}');          -- 2 (number of keys)
SELECT JSON_LENGTH('[1,2,3,4,5]');             -- 5 (array length)
SELECT JSON_LENGTH('{"a":{"b":1}}', '$.a');   -- 1 (nested object key count)

-- Find products with more than 3 attributes in their meta JSON
SELECT name FROM products WHERE JSON_LENGTH(meta) > 3;

-- JSON_DEPTH: maximum nesting depth
SELECT JSON_DEPTH('{"a":{"b":{"c":1}}}');  -- 3
SELECT JSON_DEPTH('[1,[2,[3]]]');            -- 3
SELECT JSON_DEPTH('"flat string"');          -- 1