Using ARRAY and JSON for Multi-Value Storage in SQL

Using ARRAY Data Types in PostgreSQL and JSON in MySQL

Write a SQL query to store and retrieve multiple values in a single column, using ARRAY in PostgreSQL and JSON in MySQL.

Solution:

-- PostgreSQL
CREATE TABLE Employees (
    EmployeeID INT,
    Skills TEXT[]
);

INSERT INTO Employees (EmployeeID, Skills)
VALUES (1, ARRAY['SQL', 'Python', 'Java']);

SELECT EmployeeID, Skills
FROM Employees
WHERE 'Python' = ANY(Skills);

-- MySQL
CREATE TABLE Employees (
    EmployeeID INT,
    Skills JSON
);

INSERT INTO Employees (EmployeeID, Skills)
VALUES (1, '["SQL", "Python", "Java"]');

SELECT EmployeeID, Skills
FROM Employees
WHERE JSON_OVERLAPS(Skills, '"Python"');

Explanation:

• Purpose of the Query :
• The goal is to demonstrate how to store and retrieve multiple values using platform-specific data types.
• Key Components :
• ARRAY (PostgreSQL): Stores multiple values in a single column.
• JSON (MySQL): Stores semi-structured data for flexibility.
• Why Compare Data Types?:
• Understanding these differences ensures efficient schema design across platforms.
• It supports modern application requirements for flexible data storage.
• Real-World Application :
• In skill tracking systems, arrays or JSON columns store employee skills.

Additional Notes:

• Use ARRAY in PostgreSQL for structured lists and JSON in MySQL for flexibility.
• Test queries on all target platforms to ensure consistent results.
• Important Considerations:
• Validate JSON or array data to avoid parsing errors.

For more Practice: Solve these Related Problems:

• Write a SQL query to store and retrieve multiple product tags, using ARRAY in PostgreSQL and JSON in MySQL.
• Write a SQL query to store and retrieve multiple customer preferences, using ARRAY in PostgreSQL and JSON in MySQL.
• Write a SQL query to store and retrieve multiple order items, using ARRAY in PostgreSQL and JSON in MySQL.
• Write a SQL query to store and retrieve multiple employee certifications, using ARRAY in PostgreSQL and JSON in MySQL.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise:Writing a Query That Uses SEQUENCE in PostgreSQL and IDENTITY in SQL Server.
Next SQL Exercise: Writing a Query that uses INTERSECT in PostgreSQL and INNER JOIN in MySQL.