Master MySQL JSON: Create, Query, and Update JSON Fields with Ease

Create Table with JSON Field

MySQL now supports JSON documents. You can define a column of type json and use built‑in functions to query and modify JSON data.

CREATE TABLE `article` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `tags` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Insert Data

Insert a row containing a JSON array into the tags column.

INSERT INTO `article` (`title`, `tags`)
VALUES ('体验 Mysql JSON', '["Mysql", "Database"]');

Query JSON Data

Two simple queries using JSON functions:

-- Find articles with the tag "Mysql"
SELECT * FROM `article`
WHERE JSON_CONTAINS(tags, '["Mysql"]');

-- Find articles whose tags start with "Data"
SELECT * FROM `article`
WHERE JSON_SEARCH(tags, 'one', 'Data%') IS NOT NULL;

The JSON_SEARCH function takes three arguments: the document to search, the search scope ('one' for the first match, 'all' for all matches), and the search pattern.

JSON Path Example

Use JSON_EXTRACT with a JSON path to retrieve a specific field.

SELECT JSON_EXTRACT('{"id": 1, "name": "mysql"}', '$.name');

The result is mysql. JSON paths start with $. Examples:

{
  "num": 123,
  "arr": [1, 2],
  "obj": {"a": 3, "b": 4}
}
$.num          // 123
$.arr          // [1, 2]
$.arr[1]       // 1
$.obj.a        // 3
$**.b          // 4

Query Using JSON Path Operator

SELECT tags->"$[0]" AS 'tag' FROM `article`;

Update JSON Data

Add a new tag "dev" only to rows that already contain "Mysql" and do not yet have "dev".

UPDATE `article`
SET tags = JSON_MERGE(tags, '["dev"]')
WHERE JSON_SEARCH(tags, 'one', 'dev') IS NULL
  AND JSON_SEARCH(tags, 'one', 'Mysql') IS NOT NULL;

Result: the "dev" tag is successfully added (see image).

Replace the tag "Mysql" with "Mysql 5.7.13":

UPDATE `article` SET tags = JSON_SET(tags, '$[0]', 'Mysql 5.7.13');

Other Useful JSON Functions

JSON_INSERT(doc, path, val[, path, val]...)

– Insert data. JSON_REPLACE(doc, path, val[, path, val]...) – Replace existing data. JSON_ARRAY_APPEND(doc, path, val[, path, val]...) – Append to an array. JSON_REMOVE(doc, path[, path]...) – Remove data at the specified path.

Overall, MySQL's JSON capabilities provide a smooth way to work with document‑style data directly inside a relational database.