JSON in MySQL

MySQL's native JSON type (5.7.8+) validates documents on insert and stores them in an optimized binary format with fast key lookups.

Concept

MySQL's native JSON type (5.7.8+) validates documents on insert and stores them in an optimized binary format with fast key lookups. MySQL provides JSON path expressions ($.key, $.array[0], $.*), the operators -> and ->>, a large JSON function library, JSON_TABLE to convert JSON to rows (8.0.4+), multi-valued indexes on arrays (8.0.17+), and JSON schema validation (8.0.17+).

Why It Matters

Modern applications and engineering tools exchange JSON (API payloads, test configurations, ECU parameter sets, device metadata). Storing variable attributes as JSON avoids dozens of sparse columns while keeping core fields relational.

Syntax

col JSON
col->'$.path'              -- JSON_EXTRACT (returns JSON)
col->>'$.path'             -- JSON_UNQUOTE(JSON_EXTRACT(...)) (returns text)
JSON_OBJECT('k', v, ...), JSON_ARRAY(v, ...)
JSON_SET / JSON_INSERT / JSON_REPLACE / JSON_REMOVE (doc, path, value ...)
JSON_CONTAINS(doc, candidate[, path]), JSON_CONTAINS_PATH(doc, 'one'|'all', path, ...)
MEMBER OF(json_array), JSON_OVERLAPS(a, b)
JSON_KEYS, JSON_LENGTH, JSON_TYPE, JSON_VALID, JSON_PRETTY, JSON_VALUE (8.0.21+)
JSON_TABLE(doc, path COLUMNS (...)) AS alias

Example

23.1 Storing test configurations 23.2 Querying JSON -> returns JSON (strings keep quotes: "BMS"); ->> returns unquoted text (BMS). Compare numbers with CAST(config->'$.timeout_s' AS UNSIGNED) or JSON_VALUE(config, '$.timeout_s' RETURNING UNSIGNED).

23.3 Modifying JSON MySQL 8 performs partial in-place updates for JSON_SET/JSON_REPLACE/JSON_REMOVE on the same column, reducing write and binlog volume.

23.4 JSON_TABLE: JSON to rows (After the JSON_ARRAY_APPEND in 23.3.) 23.5 Indexing JSON

JSON columns cannot be indexed directly. Options:

23.6 JSON schema validation (8.0.17+) 23.7 When to use JSON Keep relational columns for Variable, optional attributes

Fields used in joins, frequent filters, FKs API payload snapshots

Values needing strong types and constraints Reporting dimensions and aggregates Sparse, evolving metadata

Anything updated very frequently in small parts

CREATE TABLE test_configs (
    config_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    test_case    VARCHAR(20) NOT NULL,
    config       JSON        NOT NULL,
    CONSTRAINT chk_config_obj CHECK (JSON_TYPE(config) = 'OBJECT')
);

INSERT INTO test_configs (test_case, config) VALUES
('TC-BMS-010', '{"ecu": "BMS", "bench": "HIL-02", "timeout_s": 300,
                 "signals": ["PackVoltage", "PackCurrent"], "limits": {"cell_v_max": 4.25}}'),
('TC-VCU-002', '{"ecu": "VCU", "bench": "HIL-01", "timeout_s": 120,
                 "signals": ["VehicleSpeed"], "limits": {"regen_nm_max": 250}}'),
('TC-ADAS-100', JSON_OBJECT('ecu', 'ADAS', 'bench', 'VEH-03', 'timeout_s', 600,
                            'signals', JSON_ARRAY('RadarTargetDist', 'VehicleSpeed')));

INSERT INTO test_configs (test_case, config) VALUES ('BAD', '{"ecu": "BMS",}');
-- ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." ...

SELECT test_case,
       config->>'$.ecu'                 AS ecu,
       config->'$.timeout_s'            AS timeout_s,
       JSON_LENGTH(config, '$.signals') AS n_signals,
       config->>'$.limits.cell_v_max'   AS cell_v_max
FROM test_configs
ORDER BY config_id;

+-------------+------+-----------+-----------+------------+
| test_case   | ecu  | timeout_s | n_signals | cell_v_max |
+-------------+------+-----------+-----------+------------+
| TC-BMS-010  | BMS  | 300       |         2 | 4.25       |
| TC-VCU-002  | VCU  | 120       |         1 | NULL       |
| TC-ADAS-100 | ADAS | 600       |         2 | NULL       |
+-------------+------+-----------+-----------+------------+

-- Which configs use the VehicleSpeed signal?
SELECT test_case FROM test_configs WHERE 'VehicleSpeed' MEMBER OF (config->'$.signals');   -- TC-VCU-002, TC-ADAS-100
SELECT test_case FROM test_configs WHERE JSON_CONTAINS(config->'$.signals', '"VehicleSpeed"');  -- same
SELECT test_case FROM test_configs WHERE config->>'$.bench' LIKE 'HIL%';                     -- TC-BMS-010, TC-VCU-002

UPDATE test_configs
SET config = JSON_SET(config, '$.timeout_s', 360, '$.retries', 2)   -- replace or add
WHERE test_case = 'TC-BMS-010';

UPDATE test_configs SET config = JSON_ARRAY_APPEND(config, '$.signals', 'StateOfCharge') WHERE test_case = 'TC-BMS-010';
UPDATE test_configs SET config = JSON_REMOVE(config, '$.retries') WHERE test_case = 'TC-BMS-010';

SELECT tc.test_case, jt.seq, jt.signal_name
FROM test_configs tc,
     JSON_TABLE(tc.config, '$.signals[*]'
         COLUMNS (seq FOR ORDINALITY, signal_name VARCHAR(40) PATH '$')) AS jt
ORDER BY tc.config_id, jt.seq;

+-------------+-----+-----------------+
| test_case   | seq | signal_name     |
+-------------+-----+-----------------+
| TC-BMS-010  |   1 | PackVoltage     |
| TC-BMS-010  |   2 | PackCurrent     |
| TC-BMS-010  |   3 | StateOfCharge   |
| TC-VCU-002  |   1 | VehicleSpeed    |
| TC-ADAS-100 |   1 | RadarTargetDist |
| TC-ADAS-100 |   2 | VehicleSpeed    |
+-------------+-----+-----------------+

-- 1. Generated column + index
ALTER TABLE test_configs
    ADD COLUMN ecu_code VARCHAR(10) GENERATED ALWAYS AS (config->>'$.ecu') VIRTUAL,
    ADD INDEX ix_test_configs_ecu (ecu_code);
SELECT test_case FROM test_configs WHERE ecu_code = 'BMS';

-- 2. Functional index (8.0.13+)
CREATE INDEX ix_test_configs_bench ON test_configs ((CAST(config->>'$.bench' AS CHAR(10)) COLLATE utf8mb4_bin));

-- 3. Multi-valued index on an array (8.0.17+) — used by MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS
ALTER TABLE test_configs ADD INDEX ix_test_configs_signals ((CAST(config->'$.signals' AS CHAR(40) ARRAY)));

ALTER TABLE test_configs ADD CONSTRAINT chk_config_schema CHECK (
    JSON_SCHEMA_VALID('{
        "type": "object",
        "required": ["ecu", "bench", "timeout_s"],
        "properties": {
            "ecu": {"type": "string"},
            "timeout_s": {"type": "integer", "minimum": 1}
        }
    }', config)
);

Expected Result

(MySQL normalizes JSON objects: keys are stored sorted by length and then alphabetically, and duplicate keys are removed.)

SELECT JSON_PRETTY(config) FROM test_configs WHERE test_case = 'TC-VCU-002'\G

JSON_PRETTY(config): {
  "ecu": "VCU",
  "bench": "HIL-01",
  "limits": {
    "regen_nm_max": 250
  },
  "signals": [
    "VehicleSpeed"
  ],
  "timeout_s": 120
}

Common Mistakes

Using -> and comparing with a string (quotes included) instead of ->>.

Putting core relational fields only in JSON (no FKs, weak typing, slow filters).

Comparing JSON numbers as strings.

Expecting key order to be preserved.

Best Practices

Hybrid design: relational core + JSON extensions.

Index frequently filtered JSON paths via generated columns or functional/multi-valued indexes.

Validate with JSON_SCHEMA_VALID CHECK constraints.

Interview Relevance

Medium-high: "-> vs ->>?", "How do you index JSON in MySQL?", "What is JSON_TABLE?", "When should you use JSON columns?".

Practice Questions

Store ECU parameter sets as JSON and query all ECUs with "can_fd": true.

Use JSON_TABLE to list each test's limits as key/value rows (hint: JSON_KEYS).

Create a multi-valued index and verify with EXPLAIN that MEMBER OF uses it.