How To Store Array In Mysql Column

In this tutorial, we will learn how to store an array in a MySQL column using JSON data type. This is useful when you have a collection of values that you want to store in a single column instead of using multiple rows.

By using the JSON data type, you can store complex data structures in a single MySQL column, simplifying your database schema and improving query performance.

Step 1: Create a MySQL table with a JSON column

To store an array in a MySQL column, you need to use the JSON data type. We will create a simple table named products with a JSON column named attributes.

1
2
3
4
5
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  attributes JSON
);

Step 2: Insert an array into the JSON column

You can use the INSERT statement to insert a row with an array into the JSON column. The array is represented as a JSON array, with square brackets [...] enclosing the list of values.

For example, let’s insert a product with the following attributes:

  • Size: M
  • Color: Blue
  • Brand: XYZ
1
2
INSERT INTO products (name, attributes)
VALUES ('Product 1', '["M", "Blue", "XYZ"]');

Step 3: Query the JSON column

You can use the JSON functions provided by MySQL to query the array stored in the JSON column.

For example, to get the products with the size “M” and the color “Blue”, you can use the following query:

1
2
3
SELECT * FROM products
WHERE JSON_EXTRACT(attributes, '$[0]') = 'M'
AND JSON_EXTRACT(attributes, '$[1]') = 'Blue';

Alternatively, you can use the -> operator with the shorthand syntax:

1
2
3
SELECT * FROM products
WHERE attributes -> '$[0]' = '"M"'
AND attributes -> '$[1]' = '"Blue"';

Output:

+----+-----------+----------------------+
| id |    name   |      attributes      |
+----+-----------+----------------------+
| 1  | Product 1 | ["M", "Blue", "XYZ"] |
+----+-----------+----------------------+

Step 4: Update the array in the JSON column

You can use the JSON_SET, JSON_REPLACE, and JSON_REMOVE functions to update the array stored in the JSON column.

For example, let’s update the size of the product with id 1 to “L”:

1
2
3
UPDATE products
SET attributes = JSON_REPLACE(attributes, '$[0]', 'L')
WHERE id = 1;

Step 5: Remove the array from the JSON column

If you want to remove the entire array from the JSON column, you can set the column value to NULL:

1
2
3
UPDATE products
SET attributes = NULL
WHERE id = 1;

Full Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  attributes JSON
);
 
INSERT INTO products (name, attributes)
VALUES ('Product 1', '["M", "Blue", "XYZ"]');
 
SELECT * FROM products
WHERE JSON_EXTRACT(attributes, '$[0]') = 'M'
AND JSON_EXTRACT(attributes, '$[1]') = 'Blue';
 
UPDATE products
SET attributes = JSON_REPLACE(attributes, '$[0]', 'L')
WHERE id = 1;
 
UPDATE products
SET attributes = NULL
WHERE id = 1;

Conclusion

In this tutorial, we learned how to store an array in a MySQL column using JSON data type. By using this technique, you can greatly simplify your database schema and improve query performance when dealing with collections of values. Make sure to learn about the different JSON functions provided by MySQL to efficiently work with JSON data in your database.