How to Use JSON Data Fields in MySQL Databases — SitePoint

My article “SQL vs NoSQL: The Differences” noted that the line between SQL and NoSQL databases has become increasingly blurred, with each camp adopting features from the other. MySQL 5.7+ InnoDB databases and PostgreSQL 9.2+ both directly support JSON document types in a single field. This article will examine the MySQL 9.1 JSON implementation in more detail.

Note that any database will accept JSON documents as a single-string blob. However, MySQL and PostgreSQL support validated JSON data in real key/value pairs rather than a basic string.

Key Takeaways

• JSON document types are directly supported in MySQL 5.7+ InnoDB databases and PostgreSQL 9.2+, but they should be used wisely due to limitations in direct indexing.
• JSON is best suited for sparsely populated data, custom attributes, hierarchical structures, and cases requiring flexibility. It should not replace normalized columns for frequently queried or indexed data.
• MySQL offers 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() for updating JSON documents using path notation.
• MySQL 9.1 supports functional indexing on generated columns derived from JSON data, enabling efficient querying of specific JSON elements.
• While MySQL supports JSON, it remains a relational database. Excessively using JSON could negate SQL’s benefits.

Just Because You Can Store JSON Documents in MySQL JSON Columns…

… it doesn’t mean you should.

Normalization is a technique used to optimize the database structure. The First Normal Form (1NF) rule governs that every column should hold a single value — which is clearly broken by storing multi-value JSON documents.

If you have clear relational data requirements, use appropriate single-value fields. JSON should be used sparingly as a last resort. JSON value fields can’t be indexed directly, so avoid using them on columns that are updated or searched regularly.

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

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

Create a Table With a JSON Data Type Column

Consider a shop selling books. All books have an ID, ISBN, title, publisher, number of pages, and other clear relational data.

Now, if you want to add any number of category tags to each book. You could achieve this in SQL using:

1. A tag table that stored each tag name with a unique ID and
2. A tagmap table with many-to-many records mapping book IDs to tag IDs

It’ll work, but it’s cumbersome and considerable effort for a minor feature. Therefore, you can define a MySQL JSON field for tags in your MySQL database’s 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 columns can’t have a default value, can’t be used as a primary key, can’t be used as a foreign key, or have direct indexes.

But, with MySQL 9.1, you can create functional indexes on generated columns derived from JSON data, which enables indexing specific elements within a JSON document. These generated columns 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);

Adding JSON Data

Whole JSON documents can be passed in INSERT or UPDATE statements, making it easy to move JSON to MySQL for storage and manipulation.

For example, our book tags can be passed as an array (inside 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, which creates arrays. For example:-- returns [1, 2, "abc"]: SELECT JSON_ARRAY(1, 2, 'abc');
• JSON_OBJECT() function, which creates objects. For example:-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT('a', 1, 'b', 2);
• JSON_QUOTE() function, which quotes a string as a JSON value. For example:-- returns "[1, 2, \"abc\"]": SELECT JSON_QUOTE('[1, 2, "abc"]');
• CAST(anyValue AS JSON) function, which casts a value as a JSON type for validity:SELECT CAST('{"a": 1, "b": 2}' AS JSON);

The JSON_TYPE() function allows you to check JSON value types. It should return OBJECT, ARRAY, a scalar type (INTEGER, BOOLEAN, etc), NULL, or an 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 or 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 document will raise an error, and the whole record will not be inserted/updated.

Searching JSON Documents in MySQL JSON Column

With JSON MySQL functions like JSON_CONTAINS() function, you can check if a JSON document contains a specific value. It 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 a value within a JSON document. It returns NULL when there’s no match.

You can also specify whether you need to find all or single matches by passing ‘one’ and ‘all’ flags alongside the search string (where % matches any number of characters and _ matches one character in an identical way to LIKE). For example:

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

The JSON_TABLE() function transforms JSON data into a relational format for easier querying:

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

JSON Paths

A MySQL JSON query using the JSON_EXTRACT() function can retrieve specific values from a JSON document based on a specified path.

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

All path definitions start with a $ followed by other selectors:

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

The following examples refer to the following JSON document:

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

Example paths:

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

You could use the JSON extract MySQL function to extract the name and first tag from your book table efficiently:

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

For a more complex example, presume you have a user table with JSON profile data. For example:

You can extract the Twitter name using a JSON path. For example:

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

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

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

Modifying Part of a JSON Document

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

• JSON_SET(doc, path, val[, path, val]…): Inserts or updates data in the document.UPDATE book SET tags = JSON_SET(tags, '$[0]', 'Updated Tag');
• JSON_INSERT(doc, path, val[, path, val]…): Inserts 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]…): Replaces data in the document.UPDATE book SET tags = JSON_REPLACE(tags, '$[0]', 'Replaced Tag');
• JSON_MERGE_PATCH(doc, doc[, 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(doc, path, val[, path, val]…): Appends values to the end of an array.UPDATE book SET tags = JSON_ARRAY_APPEND(tags, '$', 'New Tag');
• JSON_ARRAY_INSERT(doc, path, val[, path, val]…): Inserts values at a specific position within a JSON array.UPDATE book SET tags = JSON_ARRAY_INSERT(tags, '$[0]', 'Inserted Tag');
• JSON_REMOVE(doc, path[, path]…): Removes data from the document.UPDATE book SET tags = JSON_REMOVE(tags, '$[1]');
• JSON_PRETTY(val): Pretty-prints JSON documents for better readability.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 JSON_MERGE_PATCH() function:

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

Further Information

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

Again, I urge you not to use JSON unless it’s absolutely necessary. You could emulate an entire document-oriented NoSQL database in MySQL, but it would negate many benefits of SQL, and you may as well switch to a real NoSQL system!

That said, JSON data types might save effort for more obscure data requirements within an SQL application.

FAQs on Working With JSON Data in MySQL

Can You Use JSON in MySQL?

MySQL supports JSON by offering a JSON data type for storing JSON-formatted data in columns. Starting from 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 range of JSON functions to work with JSON data within these columns, enabling extraction, modification, and manipulation.

Additionally, you can use JSON data in SQL queries, converting it to relational data when needed using functions like JSON_TABLE. However, it’s important to understand that MySQL is fundamentally a relational database, and its JSON data type support is intended to facilitate working with JSON data within a relational context rather than being a full-fledged NoSQL JSON database.

As outlined in the article above, just because you can store JSON, it doesn’t mean that you should: Normalization is a technique used to optimize the database structure. The First Normal Form (1NF) rule governs that every column should hold a single value — which is broken by storing multi-value JSON documents.

Is It OK To Store JSON in MySQL?

It is ok to store JSON in MySQL for scenarios like:

• Semi-structured or dynamic data that does not fit well in a rigid schema.
• Custom attributes where relational design would be inefficient.
• Integration with JSON-based APIs for storing payloads or logs.

However, JSON should not replace normalized relational storage for structured and frequently queried data. While MySQL 9.1 improves JSON functionality with features like functional indexes and JSON_TABLE, JSON operations may still introduce overhead for large datasets or complex queries.

How To Use JSON in a MySQL Query?

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

JSON functions like JSON_EXTRACT, JSON_SET, and JSON_OBJECTAGG allow you to filter, modify, aggregate, and work with JSON data. You can also filter rows based on JSON values using the WHERE clause. MySQL’s JSON capabilities provide a versatile way to interact and manipulate a JSON object directly within your database queries.

When To Use JSON in MySQL?

You should use JSON in MySQL for the following scenarios:

1. Semi-structured data: Use JSON when dealing with unpredictable or sparse fields (e.g., custom attributes).
2. Dynamic schemas: JSON provides flexibility when data requirements change frequently.
3. Hierarchical 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 JSON for:

• Frequently queried fields that require indexing (functional indexes can help, but relational design is often faster).
• Strictly relational data requiring normalization.
• Situations where complex querying on JSON paths would degrade performance.

How To Store JSON Data in MySQL?

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

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

How Do You Index JSON Data in MySQL?

While you cannot directly index a JSON column, MySQL allows you to create functional indexes 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 approach improves query performance for frequently accessed JSON paths.

Should You Use MySQL or a NoSQL Database for JSON Data?

It depends on your project requirements:

• Choose MySQL if you need relational storage with occasional JSON handling for semi-structured data, custom attributes, or hierarchical data within a relational model.
• Choose a NoSQL database (like MongoDB) if your project involves extensive JSON storage, flexible schemas, and document-based operations as the primary use case.

MySQL’s JSON support is excellent for hybrid workloads but cannot fully replace a purpose-built NoSQL database for document storage.

How Do You Extract Specific Values From a MySQL JSON Field?

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

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


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

How Do You Query and Filter Data in a MySQL JSON Field?

To query and filter data stored in a MySQL JSON field, you 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';