When JSON belongs in a relational database
MySQL provides a native JSON data type for storing JSON documents. It validates that inserted values contain valid JSON and provides functions for extracting and modifying document values. JSON can be useful when an event payload has a well-defined but evolving set of optional fields, when data is naturally document-shaped, or when an application must retain an external JSON structure. It should not be used to avoid modeling stable entities, relationships, and constraints that the database can represent directly.
Frequently filtered, joined, sorted, or constrained attributes are often better as ordinary columns. Relational columns have explicit types, can participate in foreign keys, and make common predicates straightforward. A JSON column allows flexible structure but shifts some validation into the application and can make analytics and indexing more complex. A hybrid design is common: stable identifiers and search dimensions are relational columns, while genuinely variable properties are retained in a JSON document.
Create a JSON column and read a path
CREATE TABLE events (
event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
event_type VARCHAR(80) NOT NULL,
payload JSON NOT NULL,
received_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (event_id)
);
INSERT INTO events (event_type, payload)
VALUES ('signup', '{"source": "web", "campaign": "spring"}');The JSON type rejects malformed JSON documents. Paths use a syntax such as $.source to address a top-level property. JSON_EXTRACT returns a JSON value, while JSON_UNQUOTE can produce a text representation for scalar values. The arrow operators are shorthand available in modern MySQL versions, but explicit functions can be clearer and version compatibility should be checked. Missing paths and JSON null have distinct semantics, so test how each function represents them.
SELECT event_id,
JSON_UNQUOTE(JSON_EXTRACT(payload, '$.source')) AS source
FROM events
WHERE event_type = 'signup';Update documents carefully
MySQL provides functions such as JSON_SET, JSON_INSERT, and JSON_REPLACE to modify document paths. Their behavior differs: one can add or replace a path, another inserts only when absent, and another replaces only an existing path. Choose intentionally and test nested paths, arrays, missing keys, and SQL NULL. Updating a JSON value is still a database write and participates in transaction and locking behavior for its row.
Avoid constructing JSON text by concatenating untrusted input. Use application JSON serializers and parameterized SQL values so quoting and escaping are handled correctly. Validate allowed keys and value types in the application; syntactically valid JSON is not automatically a valid business document. Consider schema versioning for payloads so readers can handle older records and migrations can be planned.
Indexing JSON properties
A JSON column is not automatically indexed for every property. For an important access path, MySQL can use generated columns or functional index features depending on version and expression rules. A generated column extracts a property into a typed relational value that can be indexed. This improves some lookups but adds schema coupling: the path, cast, and index must match the data contract. Verify exact syntax and limitations for the deployed version, and test whether the optimizer uses the index with EXPLAIN.
Do not create a generated index for every possible key. Each index consumes disk and adds write work; data type and collation decisions affect comparisons. If a JSON property becomes central to reporting or joins, promote it to a normal column and migrate clients when that makes the model clearer.
Security, privacy, and operations
JSON payloads often contain fields supplied by other systems. Treat them as untrusted: validate structure, limit document size, and avoid storing secrets or unnecessary personal data. Apply authorization to the row and its fields rather than assuming that hiding a JSON path in the UI is sufficient. Logs and backups can retain the complete document, so data retention and access controls must include JSON content. Use prepared statements for SQL values and never interpolate JSON text into a query.
Large documents can increase row size, storage, and network transfer, especially when an application retrieves the full column but needs only a few properties. Select only required data where possible. Consider whether frequently updated document properties cause unnecessary row writes, and benchmark realistic payload sizes. Review the application client's JSON mapping to ensure precision-sensitive numbers and null/missing distinctions are preserved.
A balanced modeling decision
Before choosing JSON, list which fields are stable, required, queried, joined, or constrained. Put those in typed columns. Keep variable or source-shaped attributes in JSON when flexibility provides real value. Define validation, indexing, schema evolution, and privacy rules for the document. Test valid data, malformed input, missing paths, JSON nulls, and queries against representative payloads.
MySQL JSON supports flexible documents within a relational system, but flexibility does not eliminate the need for a data contract. Use JSON intentionally and move stable access patterns into typed, indexed schema elements. Continue with data type selection, index design, and security practices.