Unlock MySQL JSON: Design, Index, and Query Strategies for Dynamic Data

Relational structured storage requires predefined columns, which can be limiting when business needs evolve; using MySQL's JSON data type removes this restriction and enables flexible schema within a relational database.

The most common mistake is treating JSON as a simple string, but JSON is a native type with its own storage format, indexing capabilities, and the ability to store complex objects and arrays.

JSON Data Type

MySQL supports the RFC 7159 JSON specification, providing two main structures: JSON objects and JSON arrays.

{
  "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 example shows how a JSON object can describe an image's width, height, title, thumbnail details, and an array of IDs.

[
  {
    "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 can be indexed on specific fields, eliminating the need for heavy ALTER TABLE operations when adding new columns.

JSON support starts from MySQL 5.7; MySQL 8.0 improves performance for JSON updates and adds full support for functional indexes.

Practical Business Table Design

User Login Design

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 the JSON fields can be done with JSON_EXTRACT / JSON_UNQUOTE or the shorter ->> operator:

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

Creating a generated column and a functional index enables fast look‑ups on JSON fields:

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

User Profile Design

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

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, allowing efficient searches:

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

Examples of querying with JSON functions:

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

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

-- Users whose tag set overlaps with [2,3,10]
SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]');

Summary

Use MySQL 8.0.17 or later for optimal JSON performance and multi‑valued index support.

JSON columns eliminate the need for predefined columns, offering descriptive, flexible data storage.

Avoid storing clearly relational data (e.g., balance, name, ID) as JSON; keep such fields as regular columns.

JSON is best suited for static or infrequently updated data.