Using MySQL JSON Data Type for Flexible Schema Design and Efficient Indexing

Relational structured storage has limitations because columns and their types must be predefined, but business needs often require extending column descriptions; using MySQL's JSON data type bridges the gap between relational and non‑relational storage, offering a more flexible architecture.

JSON Data Type

JSON (JavaScript Object Notation) is mainly used for data exchange between internet services. MySQL supports the RFC 7159 JSON specification, providing two main types: JSON objects and JSON arrays. An example JSON object storing image information:

{
  "Image": {
    "Width": 800,
    "Height": 600,
    "Title": "View from 15th Floor",
    "Thumbnail": {
      "Url": "http://www.example.com/image/481989943",
      "Height": 125,
      "Width": 100
    },
    "IDs": [116, 943, 234, 38793]
  }
}

The object can describe various fields such as width, height, title, and supports integers, strings, and nested arrays.

Another example shows a JSON array containing two objects:

[
  {
    "precision": "zip",
    "Latitude": 37.7668,
    "Longitude": -122.3959,
    "Address": "",
    "City": "SAN FRANCISCO",
    "State": "CA",
    "Zip": "94107",
    "Country": "US"
  },
  {
    "precision": "zip",
    "Latitude": 37.371991,
    "Longitude": -122.026020,
    "Address": "",
    "City": "SUNNYVALE",
    "State": "CA",
    "Zip": "94085",
    "Country": "US"
  }
]

JSON is not just a large string; it is a native type with its own storage format, indexable fields, and optimizations unavailable to plain VARCHAR columns. It also eliminates the need to pre‑define columns, allowing unlimited field expansion.

JSON support starts from MySQL 5.7, while MySQL 8.0 improves update‑log performance. For production use, MySQL 8.0 is strongly recommended.

Practical Table Design – User Login

JSON is suitable for relatively static data that changes infrequently. A user login table can store multiple login methods in a single JSON column:

DROP TABLE IF EXISTS UserLogin;
CREATE TABLE UserLogin (
    userId BIGINT NOT NULL,
    loginInfo JSON,
    PRIMARY KEY(userId)
);

SET @a = '{
  "cellphone" : "13918888888",
  "wxchat" : "破产码农",
  "QQ" : "82946772"
}';
INSERT INTO UserLogin VALUES (1, @a);

SET @b = '{
  "cellphone" : "15026888888"
}';
INSERT INTO UserLogin VALUES (2, @b);

Querying specific fields can be done with JSON_EXTRACT or the shorter ->> operator:

SELECT userId,
       loginInfo->>"$.cellphone" AS cellphone,
       loginInfo->>"$.wxchat" AS wxchat
FROM UserLogin;

To index a JSON field, create a virtual column and a unique index:

ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");
ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);

Explain shows the optimizer using the new index.

Practical Table Design – User Profile (Tags)

A tag definition table stores possible tags:

CREATE TABLE Tags (
    tagId BIGINT AUTO_INCREMENT,
    tagName VARCHAR(255) NOT NULL,
    PRIMARY KEY(tagId)
);

Users can be associated with tags using a JSON array:

DROP TABLE IF EXISTS UserTag;
CREATE TABLE UserTag (
    userId BIGINT NOT NULL,
    userTags JSON,
    PRIMARY KEY(userId)
);
INSERT INTO UserTag VALUES (1, '[2,6,8,10]');
INSERT INTO UserTag VALUES (2, '[3,10,12]');

MySQL 8.0.17+ supports Multi‑Valued Indexes on JSON arrays, enabling fast searches:

ALTER TABLE UserTag ADD INDEX idx_user_tags ((CAST(userTags->"$" AS UNSIGNED ARRAY)));

Examples of querying:

-- Users who have tag 10 ("常看电影")
SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$");

-- Users with both tags 2 and 10
SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]');

-- Users with any of tags 2, 3, or 10
SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]');

Summary

Use MySQL 8.0.17+ for the best JSON performance and Multi‑Valued Index support.

JSON eliminates the need for predefined columns and provides rich descriptive capability.

Avoid storing clearly relational data (e.g., balance, name, ID) in JSON.

Prefer JSON for static or rarely updated data.