JSON data type in MySQL 8 for flexible data

JSON fields natively in MySQL

Since version 5.7, MySQL has a column type JSON which is automatically validated and stored in an internal binary format, making it more efficient than saving JSON as text.

CREATE TABLE preferensi (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pengguna_id BIGINT UNSIGNED NOT NULL,
    pengaturan JSON NOT NULL
);

INSERT INTO preferensi (pengguna_id, pengaturan)
VALUES (1, '{"tema": "gelap", "bahasa": "id"}');

Fetching values from JSON field

SELECT
    pengguna_id,
    pengaturan->>'$.tema' AS tema
FROM preferensi
WHERE pengaturan->>'$.bahasa' = 'id';

Operator ->> takes a value and converts it directly to text, so it can be directly compared to a regular string.

Generated column to speed up searches

ALTER TABLE preferensi
    ADD COLUMN tema VARCHAR(20)
    GENERATED ALWAYS AS (pengaturan->>'$.tema') STORED,
    ADD INDEX idx_preferensi_tema (tema);

Generated columns copy values from JSON to regular indexable columns, as indexes directly on JSON expressions are not always efficiently supported in all MySQL versions.

Structure validation on the database side

ALTER TABLE preferensi
    ADD CONSTRAINT chk_pengaturan
    CHECK (JSON_VALID(pengaturan));

While JSON-type columns are automatically validated, additional constraints are useful if you limit certain structures that are mandatory.

practice;

Add new generated column for field bahasa, then create a composite index between tema and bahasa.