MySQL JSON

Learning Focus

MySQL's JSON column type validates JSON at insert time and provides rich functions for extraction and modification using JSONPath expressions.

Create and Insert

CREATE TABLE users (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    profile JSON
);

INSERT INTO users (name, profile) VALUES
('Alice', '{"age": 30, "city": "Singapore", "tags": ["dev", "admin"]}'),
('Bob',   '{"age": 25, "city": "Jakarta",   "tags": ["editor"]}');

Extract Values

-- JSON_EXTRACT: returns JSON-typed value (quoted strings)
SELECT JSON_EXTRACT(profile, '$.city') FROM users;    -- "Singapore"

-- ->> shorthand: returns unquoted text
SELECT profile->>'$.city' FROM users;                 -- Singapore

-- Nested path
SELECT profile->>'$.address.postal' FROM users;

Key Functions

Function	Purpose
JSON_EXTRACT(col, path)	Extract value
JSON_UNQUOTE(val)	Remove surrounding quotes
JSON_SET(col, path, val)	Insert or update
JSON_INSERT(col, path, val)	Insert only if missing
JSON_REPLACE(col, path, val)	Replace only if exists
JSON_REMOVE(col, path)	Delete value
JSON_CONTAINS(col, val)	Containment check
JSON_LENGTH(col)	Array or object length
JSON_KEYS(col)	All top-level keys

Filtering

-- Filter by JSON value
SELECT name FROM users WHERE profile->>'$.city' = 'Singapore';

-- Array contains value
SELECT name FROM users
WHERE JSON_CONTAINS(profile->'$.tags', '"admin"');

-- Count tag array length
SELECT name, JSON_LENGTH(profile->'$.tags') AS tag_count FROM users;

Updating

UPDATE users SET profile = JSON_SET(profile, '$.city', 'Kuala Lumpur')
WHERE name = 'Bob';

UPDATE users SET profile = JSON_SET(profile, '$.verified', TRUE)
WHERE name = 'Alice';

UPDATE users SET profile = JSON_REMOVE(profile, '$.tags')
WHERE name = 'Bob';

Generated Columns for Indexing

MySQL cannot index a raw JSON column — use a generated column:

ALTER TABLE users
ADD COLUMN city VARCHAR(100) GENERATED ALWAYS AS (profile->>'$.city') STORED,
ADD INDEX idx_city (city);

SELECT name FROM users WHERE city = 'Singapore';  -- uses index

Concept Map

Concept Flow

MySQL JSON Column
  ├── JSON_EXTRACT / ->>           →  Query and Filter
  ├── JSON_SET / JSON_INSERT       →  Modify Values
  └── Generated Column + Index    →  B-tree Index on JSON path

Common Pitfalls

Pitfall	Consequence	Prevention
Direct index on JSON column	Error — not supported	Use generated column + index
JSON_EXTRACT for text comparison	Quoted string mismatch	Use ->> for unquoted text
JSON_INSERT when key exists	Old value kept	Use JSON_SET for upsert

What's Next

• Previous: PostgreSQL JSONB — Compare PostgreSQL approach.
• Next: Configuration Files and JSONC — JSON in config files.
• Section Overview — Return to the Databases module index.