Master MySQL JSON: Create, Query, Update, and Index JSON Data in MySQL 5.7+

Overview

Since MySQL 5.7.8, the database supports a native JSON data type, allowing storage and querying of JSON documents while still retaining relational features. The article demonstrates practical operations such as table creation, data insertion, updates, indexing, and provides a reference of all JSON functions.

Creating a Table with a JSON Column

CREATE TABLE table_name (
    id INT NOT NULL AUTO_INCREMENT,
    json_col JSON,
    PRIMARY KEY(id)
);

The column json_col is declared with the JSON type.

Inserting Simple JSON Data

INSERT INTO table_name (json_col)
VALUES ('{"City": "Galle", "Description": "Best damn city in the world"}');

Note that the JSON document is wrapped in single quotes because the JSON syntax itself uses double quotes.

Inserting Complex JSON (Array) Data

INSERT INTO table_name (json_col)
VALUES ('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}');

This example stores a JSON object that contains an array under the variations key.

Updating JSON Values

UPDATE myjson
SET dict = JSON_ARRAY_APPEND(dict, '$.variations', 'scheveningen')
WHERE id = 2;

The $.variations path selects the variations array, and JSON_ARRAY_APPEND adds a new element.

Querying Updated Data

SELECT * FROM myjson;

The result shows the variations array now contains "scheveningen" as the fourth element.

Creating an Index on a JSON Field

MySQL cannot index a JSON column directly, but you can generate a virtual column that extracts a scalar value and index that column.

CREATE TABLE jemp (
    c JSON,
    g INT GENERATED ALWAYS AS (c->"$.id") VIRTUAL,
    INDEX i (g)
);

INSERT INTO jemp (c) VALUES
    ('{"id": "1", "name": "Fred"}'),
    ('{"id": "2", "name": "Wilma"}'),
    ('{"id": "3", "name": "Barney"}'),
    ('{"id": "4", "name": "Betty"}');

SELECT c->>"$.name" AS name FROM jemp WHERE g > 2;

The generated column g extracts the id field, allowing an index i to speed up queries that filter on g.

Converting a String to JSON

SELECT CAST('[1,2,3]' AS JSON);
SELECT CAST('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}' AS JSON);

These statements illustrate how to cast a literal string into a JSON value.

Reference: All MySQL JSON Functions

JSON_APPEND()
JSON_ARRAY()
JSON_ARRAY_APPEND()
JSON_ARRAY_INSERT()
JSON_CONTAINS()
JSON_CONTAINS_PATH()
JSON_DEPTH()
JSON_EXTRACT()
JSON_INSERT()
JSON_KEYS()
JSON_LENGTH()
JSON_MERGE()   -- deprecated synonym for JSON_MERGE_PRESERVE()
JSON_MERGE_PRESERVE()
JSON_OBJECT()
JSON_QUOTE()
JSON_REMOVE()
JSON_REPLACE()
JSON_SEARCH()
JSON_SET()
JSON_TYPE()
JSON_UNQUOTE()
JSON_VALID()

Each function provides specific capabilities for creating, modifying, querying, and validating JSON documents stored in MySQL.