How to Use JSON Data Fields in MySQL Databases

This article explores the details of JSON implementation in MySQL 9.1, and continues the argument that the boundaries between SQL and NoSQL databases are blurred in the previous article "SQL vs NoSQL: The Differences", both of which are learning from each other's features. Both MySQL 5.7 InnoDB database and PostgreSQL 9.2 directly support storing JSON document types in a single field.

It should be noted that any database can store JSON documents as a single string blob. However, MySQL and PostgreSQL support storing validated JSON data as real key-value pairs instead of simple strings.

Key Points

• MySQL 5.7 InnoDB database and PostgreSQL 9.2 directly support JSON document types, but should be used with caution due to the limitation of direct indexing.
• JSON is best for storing sparse data, custom properties, hierarchies, and situations where flexibility is required. It should not replace normalized columns that frequently query or index data.
• MySQL provides various functions to create, verify, search and modify JSON objects. These functions include JSON_ARRAY(), JSON_OBJECT(), JSON_QUOTE(), JSON_TYPE(), JSON_VALID(), JSON_CONTAINS(), JSON_SEARCH(), and JSON_SET() and JSON_MERGE_ for updating JSON documents using path notation PATCH(), etc. function.
• MySQL 9.1 supports function indexing of generated columns derived from JSON data, allowing efficient query of specific JSON elements.
• Although MySQL supports JSON, it is still a relational database. Overuse of JSON may offset the advantages of SQL.

Storing JSON documents in MySQL JSON column does not mean you should do this

Normalization is a technology used to optimize database structure. The first normal formula (1NF) rule stipulates that each column should hold a single value—and storing multi-valued JSON documents obviously violates this rule.

If your data has clear relational data requirements, please use the appropriate single-value field. JSON should be used with caution as a last resort. JSON value fields cannot be indexed directly, so avoid using them on frequently updated or searched columns.

Function indexing of generated columns derived from JSON data allows you to index certain parts of JSON objects, thereby improving query performance.

That is, JSON has some good use cases for sparse data or custom properties.

Create a table with JSON data type column

Consider a store selling books. All books have ID, ISBN, title, publisher, page count and other clear relational data.

Now, if you want to add as many category tags to each book. You can implement it in SQL using the following method:

1. A tag table that stores each tag name and its unique ID;
2. A tag mapping table containing many to many records, mapping the book ID to the tag ID.

This method works, but for a secondary function it is too cumbersome and requires considerable effort. Therefore, you can define a MySQL JSON field for the tag in the book table of the MySQL database:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

MySQL JSON columns cannot have default values, cannot be used as primary keys, cannot be used as foreign keys, and cannot have direct indexes.

However, with MySQL 9.1, you can create function indexes on generated columns derived from JSON data, which enables indexing specific elements in JSON documents. These generated columns can be virtual or stored and indexed as auxiliary indexes.

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

Add JSON data

The entire JSON document can be passed in an INSERT or UPDATE statement, making it easy to move JSON to MySQL for storage and operation.

For example, our book tags can be passed as an array (within a string):

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

You can also create JSON using the following functions:

• JSON_ARRAY() function, used to create arrays. For example: -- Return [1, 2, "abc"]: SELECT JSON_ARRAY(1, 2, 'abc');
• JSON_OBJECT() function, used to create objects. For example: -- Return {"a": 1, "b": 2}: SELECT JSON_OBJECT('a', 1, 'b', 2);
• JSON_QUOTE() function, used to reference strings as JSON values. For example: -- Return "[1, 2, "abc"]": SELECT JSON_QUOTE('[1, 2, "abc"]');
• CAST(anyValue AS JSON) function to convert values ​​to JSON types to ensure validity: SELECT CAST('{"a": 1, "b": 2}' AS JSON);

JSON_TYPE() function allows you to check the type of JSON value. It should return OBJECT, ARRAY, scalar type (INTEGER, BOOLEAN, etc.), NULL or error. For example:

-- 返回ARRAY：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回OBJECT：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回错误：
SELECT JSON_TYPE('{"a": 1, "b": 2');

JSON_VALID() function returns 1 if JSON is valid, otherwise 0 will be returned:

-- 返回1：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回1：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回0：
SELECT JSON_TYPE('{"a": 1, "b": 2');

Attempting to insert an invalid JSON document will throw an error and the entire record will not be inserted/updated.

Search for JSON documents in MySQL JSON column

Using MySQL JSON functions such as JSON_CONTAINS() function, you can check whether the JSON document contains a specific value. Returns 1 when a match is found. For example:

-- 所有带有“JavaScript”标签的书籍：
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

JSON_SEARCH() function returns the path to the value in the JSON document. Returns NULL when there is no match.

You can also specify whether you need to find all matches or a single match by passing the "one" and "all" flags and search strings (where % matches any number of characters, _ matches one character like LIKE) . For example:

-- 所有标签以“Java”开头的书籍：
SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

JSON_TABLE() function converts JSON data into relational formats to make it easier to query:

SELECT * 
FROM JSON_TABLE(
    '[{"tag": "SQL"}, {"tag": "JSON"}]', 
    '$[*]' COLUMNS (tag VARCHAR(50) PATH '$.tag')
) AS tags_table;

JSON path

MySQL JSON query using the JSON_EXTRACT() function can retrieve specific values ​​from JSON documents based on the specified path.

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

All path definitions start with $ followed by other selectors:

• A period followed by a name, such as $.website
• [N], where N is the position in the zero index array
• .[*]All members of wildcard evaluation object
• [*]Wildcard evaluation array all members
• prefix**suffix wildcard evaluates all paths that start with a named prefix and end with a named suffix

The following example refers to the following JSON document:

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

Path example:

• $.a Return 1
• $.c Return [3, 4]
• $.c[1] Return 4
• $.d.e Return 5
• $**.e Return [5]

You can use JSON to extract MySQL functions to effectively extract the name and first tag from your book table:

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

For more complex examples, suppose you have a user table containing JSON configuration file data. For example:

id	name	profile
1	Craig	{ "email": ["craig@email1.com", "craig@email2.com"], "twitter": "@craigbuckler" }
2	SitePoint	{ "email": [], "twitter": "@sitepointdotcom" }

You can use the JSON path to extract the Twitter name. For example:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

You can use the JSON path in the WHERE clause to return only users with a Twitter account:

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

Modify part of JSON document

There are several MySQL functions that can modify a part of a JSON document using path notation. These functions include:

• JSON_SET(doc, path, val[, path, val]…): Insert or update data in the document. UPDATE book SET tags = JSON_SET(tags, '$[0]', 'Updated Tag');
• JSON_INSERT(doc, path, val[, path, val]…): Insert data into the document without overwriting existing values. UPDATE book SET tags = JSON_INSERT(tags, '$[0]', 'New Tag');
• JSON_REPLACE(doc, path, val[, path, val]…): Replace data in the document. UPDATE book SET tags = JSON_REPLACE(tags, '$[0]', 'Replaced Tag');
• JSON_MERGE_PATCH(doc, doc[, doc]…): Merge two or more JSON documents and replace existing keys with the values ​​of subsequent documents. UPDATE book SET tags = JSON_MERGE_PATCH(tags, '["technical"]') WHERE JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;
• JSON_ARRAY_APPEND(doc, path, val[, path, val]…): Append the value to the end of the array. UPDATE book SET tags = JSON_ARRAY_APPEND(tags, '$', 'New Tag');
• JSON_ARRAY_INSERT(doc, path, val[, path, val]…): Insert values ​​at a specific location in the JSON array. UPDATE book SET tags = JSON_ARRAY_INSERT(tags, '$[0]', 'Inserted Tag');
• JSON_REMOVE(doc, path[, path]…): Delete data from the document. UPDATE book SET tags = JSON_REMOVE(tags, '$[1]');
• JSON_PRETTY(val): Beautify JSON documents to make them easier to read. SELECT JSON_PRETTY('{"name": "SitePoint", "tags": ["MySQL", "JSON"]}');

For example, if you want to add a "technical" tag to any book that already has a "JavaScript" tag, you can use the JSON_MERGE_PATCH() function:

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

More information

MySQL documentation provides detailed information about MySQL JSON data types and related JSON functions.

Remind again, do not use JSON unless absolutely necessary. You can simulate the entire document-oriented NoSQL database in MySQL, but this will offset many of the benefits of SQL, and you might as well switch directly to a real NoSQL system!

That is, for the more vague data requirements in SQL applications, JSON data types may save some effort.

Frequently Asked Questions about Using JSON Data in MySQL

Can you use JSON in MySQL?

MySQL supports JSON by providing a JSON data type, which is used to store data in JSON format in columns. Starting with MySQL 5.7.8, you can create tables with JSON columns, allowing you to insert, update, and query JSON data using SQL. MySQL provides a series of JSON functions to process JSON data in these columns, so that JSON data can be extracted, modified, and manipulated.

In addition, you can use JSON data in SQL queries and convert it into relational data using functions such as JSON_TABLE when needed. However, it is important to understand that MySQL is fundamentally a relational database with JSON data type support designed to facilitate the processing of JSON data in a relational context rather than becoming a full-fledged NoSQL JSON database.

As mentioned above, just because you can store JSON does not mean you should do this: Normalization is a technique for optimizing database structure. The first normal formula (1NF) rule states that each column should hold a single value—and storing multi-valued JSON documents violates this rule.

Is it OK to store JSON in MySQL?

Storing JSON in MySQL is OK if:

• Semi-structured or dynamic data, not suitable for strict patterns.
• Custom properties, where relationship design is inefficient.
• Integrated with JSON API for storing payloads or logs.

However, JSON should not replace the normalized relational storage of structured and frequently query data. While MySQL 9.1 improves JSON functionality with features such as function indexing and JSON_TABLE, JSON operations can still cause overhead for large datasets or complex queries.

How to use JSON in MySQL query?

You can use JSON in MySQL queries by using MySQL's JSON function. These functions enable you to extract, manipulate, and query JSON data stored in JSON format strings in JSON columns or databases. To access JSON data in a JSON column, use the -> operator followed by the path to the desired JSON element.

JSON_EXTRACT, JSON_SET, and JSON_OBJECTAGG and other JSON functions allow you to filter, modify, aggregate and process JSON data. You can also use the WHERE clause to filter rows based on JSON values. MySQL's JSON functionality provides a flexible way to interact and manipulate JSON objects directly in database queries.

When should I use JSON in MySQL?

You should use JSON in MySQL under the following circumstances:

1. Semi-structured data: Use JSON when handling unpredictable or sparse fields such as custom properties.
2. Dynamic Mode: JSON provides flexibility when data requirements change frequently.
3. Hydraft or nested data: JSON supports data with parent-child relationships or arrays.
4. API Integration: Store payloads, responses, or logs as JSON documents.

However, avoid using JSON when:

• Frequently queried fields that require indexing (function indexing can help, but relationship design is usually faster).
• Strict relationship data that is standardized is required.
• Complex queries on JSON paths can degrade performance.

How to store JSON data in MySQL?

To store JSON data in MySQL, you have two main options. First, you can use the JSON data type introduced in MySQL to create a table with JSON columns. This method provides structured storage and better query performance for JSON data.

Alternatively, you can store JSON data as text in the regular VARCHAR or TEXT columns. This method applies when it is primarily necessary to store and retrieve JSON data without complex database operations.

How to index JSON data in MySQL?

Although you can't index JSON columns directly, MySQL allows you to create function indexes on generated columns derived from JSON values.

For example, to index the first element of the JSON array:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

This method improves query performance for frequently accessed JSON paths.

For JSON data, should you use MySQL or NoSQL database?

It depends on your project requirements:

• If you need relational storage, occasionally processing semi-structured data, custom properties, or hierarchical data in relational models, select MySQL.
• If your project involves a lot of JSON storage, flexible schemas and document-based operations as its main use cases, choose NoSQL databases (such as MongoDB).

MySQL's JSON support is ideal for hybrid workloads, but it is not a complete replacement for NoSQL databases specifically for document storage.

How to extract specific values ​​from MySQL JSON field?

To extract a specific value from the MySQL JSON field, use the JSON_EXTRACT() function or the abbreviation -> operator.

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

How to query and filter data in MySQL JSON fields?

To query and filter data stored in MySQL JSON fields, you can use functions such as JSON_CONTAINS() and JSON_SEARCH(). You can also use JSON_EXTRACT() to retrieve specific values ​​for further filtering.

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

The above is the detailed content of How to Use JSON Data Fields in MySQL Databases. For more information, please follow other related articles on the PHP Chinese website!