MySQL JSON Data Type Tutorial

What is the MySQL JSON Data Type?

The JSON data type in MySQL stores JSON documents as a binary format that is optimized for quick access and validation.

Unlike storing JSON as plain text, the JSON data type ensures that only valid JSON documents are stored, preventing malformed data.

• Introduced in MySQL 5.7.8
• Stores JSON in a compact binary format
• Validates JSON on insert and update
• Supports indexing for faster queries

Creating Tables with JSON Columns

To use JSON data type, define a column with the JSON type when creating or altering a table.

This allows you to insert JSON documents directly into that column.

Querying JSON Data

MySQL provides a rich set of JSON functions to extract and manipulate data inside JSON documents.

You can use the -> and ->> operators or JSON_EXTRACT() function to access JSON values.

• Use JSON_EXTRACT(json_doc, path) to get JSON values.
• Use -> operator to extract JSON values as JSON.
• Use ->> operator to extract JSON values as text.

Manipulating JSON Data

MySQL allows you to modify JSON documents using functions like JSON_SET(), JSON_REMOVE(), and JSON_REPLACE().

These functions return a new JSON document with the modifications applied.

• JSON_SET(json_doc, path, value) adds or updates a value.
• JSON_REMOVE(json_doc, path) removes a key or element.
• JSON_REPLACE(json_doc, path, value) replaces an existing value.

Indexing JSON Columns

To improve query performance on JSON data, you can create indexes on generated columns extracted from JSON fields.

MySQL does not support direct indexing of JSON columns, but generated columns can be indexed.

• Create a generated column that extracts a JSON value.
• Index the generated column for faster lookups.