JSON Data Type

Introduction

MySQL 8 stores flexible JSON documents in table columns with validation on insert. JSON functions extract and update nested fields—useful for settings, metadata, and semi-structured data without exploding every key into columns. This chapter creates JSON columns, queries with JSON_* functions, and touches generated columns for indexing.

Prerequisites

• Creating Tables and Data Types
• SELECT Queries Basics

Create Table With JSON

CREATE TABLE user_profiles (
  user_id INT UNSIGNED NOT NULL PRIMARY KEY,
  settings JSON NOT NULL,
  CONSTRAINT fk_profiles_user
    FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB;

Insert valid JSON:

INSERT INTO user_profiles (user_id, settings) VALUES
  (1, JSON_OBJECT(
    'theme', 'dark',
    'notifications', JSON_OBJECT('email', true, 'sms', false),
    'tags', JSON_ARRAY('sql', 'mysql')
  ));

Invalid JSON fails:

INSERT INTO user_profiles (user_id, settings)
VALUES (2, '{not json}');

Extract Values

SELECT
  user_id,
  JSON_UNQUOTE(JSON_EXTRACT(settings, '$.theme')) AS theme
FROM user_profiles;

Shorthand -> and ->> (unquoted):

SELECT
  user_id,
  settings->>'$.theme' AS theme,
  settings->'$.notifications.email' AS email_on
FROM user_profiles;

Code explanation:

• $ is root; dot path navigates objects; [0] indexes arrays

Modify JSON

UPDATE user_profiles
SET settings = JSON_SET(
  settings,
  '$.theme', 'light',
  '$.notifications.email', false
)
WHERE user_id = 1;

Remove key:

UPDATE user_profiles
SET settings = JSON_REMOVE(settings, '$.tags')
WHERE user_id = 1;

Merge object:

UPDATE user_profiles
SET settings = JSON_MERGE_PATCH(settings, '{"locale": "en-US"}')
WHERE user_id = 1;

Search in JSON

Members contain value:

SELECT user_id
FROM user_profiles
WHERE JSON_CONTAINS(settings->'$.tags', '"mysql"');

Path exists:

WHERE JSON_CONTAINS_PATH(settings, 'one', '$.notifications.sms');

Generated Column and Index

Index JSON field via stored generated column:

ALTER TABLE user_profiles
  ADD COLUMN theme VARCHAR(20)
    AS (settings->>'$.theme') STORED,
  ADD INDEX idx_profiles_theme (theme);

Query uses indexed column:

SELECT user_id FROM user_profiles WHERE theme = 'dark';

Code explanation:

• STORED materializes value; VIRTUAL computes on read—index requires stored or virtual supported

JSON vs Normalized Columns

Use JSON for optional/evolving attributes; core entities stay relational.

When most of your data is document-shaped (nested arrays, varying fields per row), a dedicated document database may fit better—MongoDB JSON/BSON basics. MySQL JSON keeps relational reporting and FOREIGN KEY on the same row.

FAQ

JSON max size?

Limited by max_allowed_packet and row size.

Compare JSON documents?

JSON_CONTAINS, not = for deep compare always.

Array loop in SQL?

JSON_TABLE (8.0)—flatten JSON array to rows—advanced.

ORM mapping?

Hibernate/JPA JSON types map to column—validation in app too.

Pretty print?

JSON_PRETTY(settings) in SELECT for debugging.

MariaDB JSON?

Similar functions—test portability if multi-DB.