My article “SQL vs. NoSQL: The Difference” points out that the lines between SQL and NoSQL databases are becoming increasingly blurred, with each camp adopting the capabilities of the other. Both the MySQL 5.7+ InnoDB database and PostgreSQL 9.2+ directly support the JSON file type in a single column. This article examines the MySQL 9.1 JSON implementation in more detail.

Please note that any database will accept JSON files as single string blobs. However, MySQL and PostgreSQL support authenticated JSON data in real key/value pairs rather than basic strings.

focus

• The MySQL 5.7+ InnoDB database and PostgreSQL 9.2+ directly support the JSON file type, but due to the limitations of direct indexing, they should be used wisely.
• JSON is best suited for sparsely populated data, custom properties, hierarchies, and situations where flexibility is required. It should not replace normalized columns for frequently queried or indexed data.
• MySQL provides a variety of functions to create, validate, search, and modify JSON objects. These include JSON_ARRAY(), JSON_OBJECT(), JSON_QUOTE(), JSON_TYPE(), JSON_VALID(), JSON_CONTAINS(), JSON_SEARCH(), and functions like JSON_SET() and JSON_MERGE_PATCH() Used to update JSON files using path notation.
• MySQL 9.1 supports functional indexing of generated columns derived from JSON data, enabling efficient queries on specific JSON elements.
• Although MySQL supports JSON, it is still a relational database. Overuse of JSON may negate the advantages of SQL.

Just because you can store JSON files in MySQL JSON columns…

…that doesn’t mean you should do it.

Normalization is a technique used to optimize the structure of a database. The first normal form (1NF) rule states that each column should hold a single value, and storing a multi-valued JSON file obviously breaks this rule.

If you have explicit relational data requirements, use appropriate single-value fields. As a last resort, JSON should be used with caution. JSON value fields are not directly indexable, so avoid using them on fields that are regularly updated or searched.

Functional indexing on generated columns derived from JSON allows you to index parts of the JSON object, improving query performance.

That said, there are good JSON use cases for sparsely populated data or custom properties.

Create a table with JSON data type columns

Consider a store that sells books. All books have ID, ISBN, title, publisher, page number and other clear relationship information.

Now, if you want to add as many category tags as you want for each book. You can achieve this in SQL using the following methods:

1. one Label A table that stores the name and unique ID of each tag
2. one labeled map Table with many-to-many records mapping book IDs to tag IDs

It will work, but for a minor feature it’s cumbersome and requires considerable effort. Therefore, you can define MySQL JSON fields for tags in the MySQL database Book table:

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 fields cannot have default values, be used as primary keys, cannot be used as foreign keys, or have direct indexes.

However, with MySQL 9.1 you can build Functional indicators exist generated columns Derived from JSON data, specific elements in the JSON file can be indexed. These generated fields can be virtual or stored and indexed as secondary 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

Entire JSON document can be passed in insert or renew statement, you can easily move JSON to MySQL for storage and manipulation.

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"]'
);

JSON can also be created with these:

• JSON_ARRAY() function, used to create arrays. For example:-- returns [1, 2, "abc"]: SELECT JSON_ARRAY(1, 2, 'abc');
• JSON_OBJECT() function, used to create objects. For example:-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT('a', 1, 'b', 2);
• JSON_QUOTE() function, quotes a string as a JSON value. For example:-- returns "[1, 2, "abc"]": SELECT JSON_QUOTE('[1, 2, "abc"]');
• CAST(anyValue AS JSON) function, converts the value to JSON type to ensure validity:SELECT CAST('{"a": 1, "b": 2}' AS JSON);

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

SELECT JSON_TYPE('[1, 2, "abc"]');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

The JSON_VALID() function returns 1 if the JSON is valid, 0 otherwise:

SELECT JSON_TYPE('[1, 2, "abc"]');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

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

Search JSON documents in MySQL JSON columns

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

SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

The JSON_SEARCH() function returns the path to the value in the JSON file. When there is no match, it returns NULL.

You can also specify whether you want to find all or a single match by passing the “one” and “all” flags next to the search string (where % matches any number of characters and _ matches one character in the same way as LIKE). For example:

SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

The JSON_TABLE() function converts JSON data into relational format for easy querying:

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

JSON path

MySQL JSON queries using the JSON_EXTRACT() function can retrieve specific values ​​from a JSON file based on a specified path.

SELECT JSON_EXTRACT('{"id": 1, "website": "SitePoint"}', '$.website');

All path definitions begin with $, followed by other selectors:

• A period followed by a name, such as $.website
• [N] where N is the position in the zero-indexed array
• this.[*] Wildcard evaluates all members of the object
• this [*] Wildcard counts all members of an array
• prefix**suffix wildcard evaluates to all paths starting with a named prefix and ending with a named suffix

The following examples refer to the following JSON files:

{
  "a": ,
  "b": 2,
  "c": [, ],
  "d": {
    "e": ,
    "f": 6
  }
}

Example path:

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

You can use the JSON extract MySQL function to efficiently extract the name and first tag from the books table:

SELECT
  title, tags->"$[0]" AS `tag1`
FROM `book`;

For a more complex example, assume you have a user Table containing JSON profile data. For example:

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

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;

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

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

Modify part of a JSON document

There are several MySQL functions that modify portions of a JSON file using path notation. These include:

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

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

UPDATE book
SET tags = JSON_MERGE_PATCH(tags, '["technical"]')
WHERE JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

More information

The MySQL documentation provides detailed information about the MySQL JSON data type and associated JSON functions.

Again, don’t use JSON unless absolutely necessary. You could emulate an entire file-oriented NoSQL database in MySQL, but that would negate many of the benefits of SQL, and you might as well switch to a real NoSQL system!

That said, the JSON data type may save work on more obscure data requirements in SQL applications.

Frequently Asked Questions about using JSON data in MySQL

Can I use JSON in MySQL?

MySQL supports JSON by providing a JSON data type for storing JSON-formatted data in columns. Starting with MySQL 5.7.8, you can create tables that contain 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 to extract, modify and operate.

Additionally, you can use JSON data in SQL queries and convert it into relational data when needed using functions such as JSON_TABLE. However, it is important to understand that MySQL is a relational database at heart, and its JSON data type support is designed to facilitate the use of JSON data in a relational context, rather than being a full-blown NoSQL JSON database.

As mentioned in the article above, just because you can store JSON, it doesn’t mean you should: Normalization is a technique used to optimize the structure of a database. The first normal form (1NF) rule states that each column should hold a single value, but storing a multi-valued JSON file breaks this rule.

Can JSON be stored in MySQL?

JSON can be stored in MySQL for the following scenarios:

• Semi-structured or dynamic data This doesn’t lend itself well to strict patterns.
• Custom properties Relational design is inefficient.
• Integrate with JSON-based API Used to store payload or logs.

However, JSON should no Replace normalized relational storage of structured and frequently queried data. Although MySQL 9.1 improves JSON functionality through features such as functional indexes and JSON_TABLE, JSON operations may still incur the overhead of large data sets or complex queries.

How to use JSON in MySQL queries?

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

JSON functions such as JSON_EXTRACT, JSON_SET, and JSON_OBJECTAGG let you filter, modify, aggregate, and work with JSON data. You can also use the WHERE clause to filter rows based on JSON values. MySQL’s JSON functionality provides a common way to interact and manipulate JSON objects directly in database queries.

When to use JSON in MySQL?

In MySQL, you should use JSON in the following scenarios:

1. semi-structured data: Use JSON when dealing with unpredictable or sparse fields (such as custom attributes).
2. dynamic mode: JSON provides flexibility when data needs change frequently.
3. Hierarchical or nested data: JSON supports data with parent-child relationships or arrays.
4. API integration: Store payload, response or logs as JSON file.

However, avoid using JSON:

• Frequently queried fields need to be indexed (functional indexes can help, but relational designs are usually faster).
• Standardized, strictly relational data is required.
• Situations where complex queries on JSON paths will reduce 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 containing JSON columns. This method provides structured storage and better query performance for JSON data.

Alternatively, you can store the JSON data as text in a regular VARCHAR or TEXT column. This method is suitable when you primarily need to store and retrieve JSON data without complex database operations.

How to index JSON data in MySQL?

Although you cannot index JSON columns directly, MySQL allows you to create Functional indicators On generated columns derived from JSON values.

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

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

This method improves query performance for frequently accessed JSON paths.

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

This depends on your project requirements:

• Choose MySQL If you need relational storage, you will occasionally need to JSON process semi-structured data, custom attributes, or hierarchical data in the relational model.
• Choose a NoSQL database (eg MongoDB) If your project involves extensive JSON storage, flexible schemas, and file-based operations as primary use cases.

MySQL’s JSON support is great for mixed workloads, but it cannot completely replace a dedicated NoSQL database for file storage.

How to extract specific value from MySQL JSON field?

Extract a specific value from a MySQL JSON fieldsusing the JSON_EXTRACT() function or abbreviation -> operator.

SELECT JSON_EXTRACT(tags, '$[0]') AS first_tag FROM book;


SELECT tags->'$[0]' AS first_tag FROM book;

How to query and filter data in MySQL JSON fields?

Queries and filters are stored in MySQL JSON fieldsyou can use functions like JSON_CONTAINS() and JSON_SEARCH(). You can also use JSON_EXTRACT() to retrieve specific values ​​for further filtering.

SELECT * FROM book 
WHERE JSON_CONTAINS(tags, '["JavaScript"]');


SELECT * FROM book 
WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;


SELECT * FROM book 
WHERE JSON_EXTRACT(tags, '$[0]') = 'JavaScript';