How to Handle Array Data in MySQL Using JSON (Complete Guide with Examples)

1. Introduction

The Need to Handle Array Data in MySQL

Databases typically store data based on relational design principles. However, depending on application requirements, there are cases where you may want to store multiple values in a single column. In such situations, a data structure similar to an “array” becomes useful.

For example, consider the following scenarios:

• Storing multiple tags selected by a user.
• Saving multiple image URLs for a product.
• Combining history or logs into a single field.

Benefits of Using the JSON Data Type

MySQL does not provide a direct “array type,” but by using the JSON data type, you can handle array-like data structures. The JSON type is highly flexible and offers the following advantages:

• Supports nested data structures.
• Allows easy data manipulation within queries.
• Enables management of multiple data formats within a single field.

In this article, we will introduce how to efficiently handle array data in MySQL using the JSON data type.

2. Basic Knowledge of Handling Arrays with MySQL JSON

What Is the JSON Data Type?

JSON (JavaScript Object Notation) is a lightweight and simple data interchange format. In MySQL, native JSON support was introduced in version 5.7 and later, allowing you to store and manipulate JSON-formatted data directly within the database.

Example: Below is an example of data that can be stored in a JSON column.

{
  "tags": ["PHP", "MySQL", "JSON"],
  "status": "published"
}

Advantages and Use Cases of the JSON Data Type

The main benefits of using the JSON type are as follows:

1. Flexible Data Structure: You can handle variable-length data without modifying the relational schema.
2. Efficient Data Manipulation: You can easily manipulate data using MySQL’s dedicated functions (e.g., JSON_EXTRACT, JSON_ARRAY).
3. Schema-less Design: There is no need to frequently modify the schema when application requirements change.

Example use cases:

• Assigning multiple categories to product information.
• Saving custom user settings.
• Using nested JSON data in web applications.

3. Basic JSON Array Operations

Creating a JSON Array

In MySQL, you can easily create a JSON array using the JSON_ARRAY function. Arrays are useful when storing multiple values in a single column.

Example

The following query creates a JSON array called tags.

SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;

Result:

["PHP", "MySQL", "JavaScript"]

Practical Example

The following example shows how to store a JSON array in the database using an INSERT statement.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON
);

INSERT INTO articles (tags) 
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));

Extracting Data from a JSON Array

To retrieve data stored in a JSON array, use the JSON_EXTRACT function. This function allows you to easily extract specific elements from the array.

Example

The following example retrieves the second element in the array (index starts at 0).

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;

Result:

"MySQL"

Retrieving Multiple Elements

You can also retrieve multiple elements at once.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;

Adding, Updating, and Removing Data

Adding Data to an Array

You can use the JSON_ARRAY_APPEND function to add new data to an existing array.

SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;

Result:

["PHP", "MySQL", "JavaScript"]

Updating Data in an Array

You can update a specific element in the array using the JSON_SET function.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Result:

["PHP", "Python", "JavaScript"]

Removing Data from an Array

You can remove a specific element from the array using the JSON_REMOVE function.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;

Result:

["PHP", "JavaScript"]

4. Searching and Filtering JSON Arrays

Searching for Arrays Containing Specific Data

To check whether a JSON array contains specific data, use the JSON_CONTAINS function. This function determines whether a specified value exists within the JSON array.

Example

The following example checks whether the JSON array contains “MySQL.”

SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;

Result:

1  (if present)
0  (if not present)

Practical Example: Conditional Search

To search for rows containing a specific value in a JSON array within a database table, use JSON_CONTAINS in the WHERE clause.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

This query retrieves rows where the tags column contains “MySQL.”

Getting the Length of an Array

To retrieve the number of elements in a JSON array, use the JSON_LENGTH function. This function returns the number of elements in the array and is useful for data analysis and conditional logic.

Example

The following example retrieves the number of elements in the array.

SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;

Result:

3

Practical Example: Extracting Rows That Meet a Specific Condition

To extract rows where the number of elements is greater than or equal to a specific value, use JSON_LENGTH in the WHERE clause.

SELECT * 
FROM articles
WHERE JSON_LENGTH(tags) >= 2;

This query retrieves rows where the tags column contains two or more elements.

Advanced Conditional Query Example

You can combine multiple conditions for more advanced searches. The following query searches for rows where the tags array contains “JavaScript” and has three or more elements.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"') 
  AND JSON_LENGTH(tags) >= 3;

5. Practical Examples: Using JSON Arrays in Real Use Cases

How to Store Product Categories as a JSON Array

In e-commerce sites and similar systems, a product may belong to multiple categories. In such cases, you can efficiently store category information using a JSON array.

Example: Storing Product Category Data

Below is an example of creating a JSON column named categories in a product table and storing multiple categories.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    categories JSON
);

INSERT INTO products (name, categories) 
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
       ('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));

This structure keeps the data concise even when a product belongs to multiple categories.

Query to Extract Products in a Specific Category

By leveraging the JSON data type, you can easily search for products that belong to a specific category.

Query Example

The following query retrieves all products in the “Electronics” category.

SELECT name 
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');

Result:

Laptop
Smartphone

This query makes it easy to retrieve product lists by category in a flexible way.

Example: Filtering by Price Range

Let’s look at how to store JSON data that includes pricing information and then search for products based on a price range.

Example Data

The following example stores price information per product using the JSON type.

CREATE TABLE products_with_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSON
);

INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
       ('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');

Query Example

To search for products priced at 100,000 or higher, use the JSON_EXTRACT function.

SELECT name 
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;

Result:

Laptop

Expanding JSON with JSON_TABLE and Query Example

If you want to query JSON data in a relational format, the JSON_TABLE function is very useful. This function allows you to expand a JSON array into a virtual table.

Example

The following example expands a JSON array and displays each category as a separate row.

SELECT * 
FROM JSON_TABLE(
    '["Electronics", "Computers", "Mobile Devices"]',
    '$[*]' COLUMNS(
        category_name VARCHAR(100) PATH '$'
    )
) AS categories_table;

Result:

category_name
--------------
Electronics
Computers
Mobile Devices

6. Important Considerations When Using the JSON Data Type

Performance Optimization Tips

While the JSON type is flexible, poor design can negatively impact database performance. Below are key optimization points.

1. Using Indexes

In MySQL, you cannot create an index directly on a JSON column itself, but you can create a generated column and index a specific key.

Example: Creating an Index Using a Generated Column

In the following example, the price key inside JSON data is used as an index target.

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

By using a generated column, you can significantly improve search performance on JSON data.

2. Avoid Overly Complex JSON Structures

Deeply nested JSON structures can reduce query readability and performance. When designing data, choose the simplest JSON structure possible.

Good example:

{
  "categories": ["Electronics", "Computers"],
  "price": 150000
}

Example to avoid:

{
  "product": {
    "details": {
      "price": 150000,
      "categories": ["Electronics", "Computers"]
    }
  }
}

How to Leverage Indexes

When indexing using generated columns, keep the following points in mind:

1. The generated column must be STORED.
2. Use the JSON_EXTRACT function to extract a specific key as a generated column.

For example, to extract the first element of the categories key and create an index, do the following.

ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);

The Importance of Data Validation

Because JSON data is flexible, it is also easier to store data in the wrong format. To maintain data integrity, use the following approaches.

1. Use CHECK Constraints

In MySQL 8.0 and later, you can validate JSON structure and content using CHECK constraints.

ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);

2. Application-Level Validation

When inserting data, it is recommended to validate JSON format on the application side. Programming languages such as PHP and Python can validate JSON using their standard libraries.

7. Frequently Asked Questions About Using Arrays in MySQL

Q1: Does MySQL have an array data type?

A1:MySQL does not have a direct “array data type.” However, by using the JSON type, you can handle array-like data structures. With the JSON type, you can store multiple values in one column and manipulate them through queries.

Example:

SELECT JSON_ARRAY('Value 1', 'Value 2', 'Value 3') AS array_example;

Result:

["Value 1", "Value 2", "Value 3"]

Q2: Can you create an index on JSON data?

A2:You cannot create an index directly on the JSON type itself. However, you can extract a specific key or value into a generated column and create an index on that generated column.

Example:

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

This allows you to efficiently search values inside JSON data.

Q3: Is there a size limit for JSON data?

A3:MySQL’s JSON type can store up to 4GB of data. However, using extremely large JSON documents can reduce performance, so you should design your data carefully.

Recommendations:

• Store only the minimum required data.
• Avoid deeply nested JSON structures.

Q4: How can I update a specific element inside a JSON array?

A4:You can update a specific element in an array using the JSON_SET function.

Example:

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Result:

["PHP", "Python", "JavaScript"]

Q5: Comparing the JSON Type vs. Normal Table Design

A5:While the JSON type is highly flexible, it has different characteristics compared to traditional relational database design.

8. Summary

Benefits of Using the JSON Data Type for Array Operations in MySQL

In this article, we explained the JSON data type, which is helpful when working with array-like data in MySQL. Below is a summary of the key points covered:

1. Why Use the JSON Type
   MySQL does not have a direct array type, but by using the JSON type, you can store multiple values in one column and achieve flexible data manipulation.
2. Basic JSON Operations

• We covered how to create JSON arrays, extract data, update data, and remove data.
• By using convenient functions such as JSON_ARRAY, JSON_EXTRACT, and JSON_SET, you can efficiently manipulate array data.

1. Search and Filtering

• How to search for data containing specific values using JSON_CONTAINS.
• How to retrieve the number of elements with JSON_LENGTH and perform conditional filtering.

1. Practical Examples
   Through real-world use cases such as managing product categories and filtering by price, we learned concrete ways to apply JSON arrays in applications.
2. Considerations and Optimization

• We explained how to set up indexing using generated columns and emphasized the importance of validating JSON data.

Next Steps When Using the JSON Data Type

By using the JSON type in MySQL, you can manage data more flexibly than in traditional relational database designs. However, good design and performance considerations are essential.

Topics to Learn Next:

• Using Composite Indexes
  Index design that combines JSON data with normal columns.
• Using Advanced JSON Functions
  Perform more complex operations with functions such as JSON_MERGE and JSON_OBJECT.
• Application-Level Data Handling
  How to efficiently manipulate MySQL JSON data using PHP or Python.

Summary

Through this article, you should now understand how to efficiently handle array-like data using MySQL’s JSON data type. By applying this knowledge, you can design more flexible and scalable databases.