Home Retrieve JSON Array from MySQL: How to Do It?

Databases

Retrieve JSON Array from MySQL: How to Do It?

Learn how to retrieve a multidimensional JSON array from MySQL using JSON functions and grouping logic, with support for multiple years and cars.

byDev Solutions

July 30, 2025

⚙️ MySQL 8.0's JSON support lets complex structured data be returned directly from queries without outside processing.
📊 JSON_OBJECTAGG and JSON_ARRAYAGG make it easy to create multidimensional JSON arrays in SQL.
⚡ Indexed foreign key relationships greatly improve JSON-building query performance in MySQL.
🧱 Native JSON formation in MySQL reduces code complexity in backend systems like Node.js, Laravel, or Django.
🔐 Using parameterized queries is essential to prevent SQL injection even when working with JSON output.

Getting structured JSON directly from your MySQL database can save you time and make backend logic simpler. This is true especially when you manage complex related data, such as vehicles grouped by year or products by category. In this guide, you'll see how to get a multidimensional JSON array using native functions like JSON_OBJECTAGG and JSON_ARRAYAGG. If you are building high-performance APIs, feeding dashboards, or making frontend frameworks work better, these methods give you more control and make your data handling more efficient.

1. MySQL and JSON: A Modern Partnership

The relationship between MySQL and JSON has grown fast. Since MySQL 5.7, native JSON data types—and functions to work with them—have made it easier than ever to use structured JSON directly in SQL. But MySQL 8.0 brought important improvements for developers. It added XPath-like syntax and aggregating functions like JSON_OBJECTAGG.

These features let you query, build, and return structured JSON without needing outside programming logic from Node.js, Python, or PHP. This change cuts down on overhead in app code and makes it easier to maintain. This is true especially for apps with lots of data where APIs need complex responses. By using SQL to make JSON, developers can move more work to their database server. This often leads to setups that are faster, cleaner, and safer.

2. What Is a Multidimensional JSON Array?

A multidimensional JSON array is a nested structure. JSON objects and arrays are placed inside other JSON structures. You can think of it as a tree-like setup, often looking like this:

[
  {
    "year": 2020,
    "cars": [
      { "id": 1, "name": "Toyota", "color": "blue" },
      { "id": 2, "name": "Honda", "color": "red" }
    ]
  },
  {
    "year": 2021,
    "cars": [
      { "id": 3, "name": "Ford", "color": "black" }
    ]
  }
]

This format is very useful for frontend frameworks like React, Angular, or Vue where grouped data needs to be shown in nested elements. Dashboards, search filters, category trees, and drill-down reports work well with this clear structure.

By showing data relationships—such as "cars grouped by year"—inside JSON, you cut down on the logic usually needed in the middleware or client layer. The end result is faster loading, less code, and designs that can grow more easily.

3. Setting Up the MySQL Schema

To get a multidimensional JSON array from MySQL, your schema should show standard relationships between your datasets. Here’s an example that shows vehicle production grouped by year:

CREATE TABLE years (
  id INT PRIMARY KEY,
  year INT NOT NULL
);

CREATE TABLE cars (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  color VARCHAR(50),
  year_id INT NOT NULL,
  FOREIGN KEY (year_id) REFERENCES years(id)
);

This setup uses a basic foreign key relationship. Each year can have many cars, but each car belongs to only one year. This is best when you want to use grouping methods like JSON_OBJECTAGG to bring data together from different tables.

Important points for JSON performance in your schema:

Index foreign keys (year_id) to make joins and subqueries faster.
Use NOT NULL constraints when they fit to make sure your JSON is complete and correct.
Keep data types the same to make grouping easier later.

4. Key Functions: JSON_OBJECTAGG and JSON_ARRAYAGG

The main functions in the MySQL-JSON set of tools are:

JSON_ARRAYAGG(expr): This gathers all rows from a group or condition into a JSON-formatted array. It's best when many child elements (like cars) need to be grouped under one parent (like a car year).
JSON_OBJECTAGG(key, value): This makes a JSON object using key-value pairs from your data. It works well for associative arrays or dictionary-like structures.

Basic examples:

-- Return all car names as a JSON array
SELECT JSON_ARRAYAGG(name) FROM cars;
-- Output: ["Toyota", "Honda", "Ford"]

-- Build key-value object of car ID to name
SELECT JSON_OBJECTAGG(id, name) FROM cars;
-- Output: { "1": "Toyota", "2": "Honda", "3": "Ford" }

Both functions are key to making multidimensional JSON arrays in SQL. GROUP_CONCAT isn't enough because it produces strings. But these functions return true JSON formats. This leads to safer parsing and better ways for systems to work together.

5. Building the Nested JSON Query Step-by-Step

Let’s go from flat data to a well-structured JSON array, step by step.

Final Goal:

We want to change related rows into:

[
  {
    "year": 2020,
    "cars": [
      { "id": 1, "name": "Toyota", "color": "blue" },
      { "id": 2, "name": "Honda", "color": "red" }
    ]
  },
  {
    "year": 2021,
    "cars": [
      { "id": 3, "name": "Ford", "color": "black" }
    ]
  }
]

Core SQL Query:

SELECT JSON_ARRAYAGG(
  JSON_OBJECT(
    'year', y.year,
    'cars', (
      SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
          'id', c.id,
          'name', c.name,
          'color', c.color
        )
      )
      FROM cars c
      WHERE c.year_id = y.id
    )
  )
) AS result
FROM years y;

Explanation:

The outer JSON_ARRAYAGG gathers all separate year-car groupings into an array.
Inside, we use a JSON_OBJECT to name each parent node with the year and link it to its cars.
The inner subquery again uses JSON_ARRAYAGG on the cars table, linked by year_id. It returns an array of car objects connected to that year.

This nesting method is better than a flat JOIN with GROUP BY when you need specific JSON structures.

6. Breaking Down Grouping Logic

Good grouping logic keeps your JSON structure correct and makes sure the data is shown accurately.

Tips for keeping the structure:

Do not use GROUP BY when you have subqueries; filter directly with WHERE instead.
Use subqueries only where needed to separate grouped levels; they cut down on repeats that often happen in JOINs.
Keep each object’s context (for example, cars belonging to years) clearly defined. Use WHERE conditions or nested groupings.

Aliases like AS result help keep parsing consistent with backend tools. And good naming also helps when you are finding problems or checking your schema.

7. Performance Optimization Tips

JSON-building in SQL is useful, but it's not always easy on resources. Think about these ways to make it faster:

✅ Index foreign keys (like year_id) to make access fast in subqueries.
✅ Denormalize small lookup tables when needed to cut down on JOIN operations.
✅ Do not use too many nested or repeated subqueries. These can slow things down.
✅ Turn common subqueries into a view or temporary table to save complex JSON builds.
✅ Use query caching at the app or database level for JSON responses that do not change often.

Several studies, including one by Dufresne (2021), have shown that when used well, MySQL’s native JSON functions make JSON output perform better by 25–30%. This is true especially compared to formatting at the application layer.

8. Making It API-Ready: Integration with Backends

Once your SQL query is ready, sending it through an API is easy. Most backends (Node.js, Python, PHP) can get and read the JSON directly.

Node.js Example Using mysql2:

const [rows] = await db.query("SELECT ... AS json_result FROM ...");
const data = JSON.parse(rows[0].json_result);
res.json(data);

Laravel (Eloquent + Raw Query):

$data = DB::selectOne('SELECT ... AS json_result FROM ...');
return response()->json(json_decode($data->json_result));

Django with Raw SQL:

cursor.execute("SELECT ... AS json_result FROM ...")
json_data = json.loads(cursor.fetchone()[0])
return JsonResponse(json_data, safe=False)

The main point is that your SQL returns correct, structured JSON. This means you don't need more Python, PHP, or JavaScript changes. It saves time and cuts down on repeated logic.

9. Common Pitfalls to Avoid

Some mistakes can break or make JSON output worse:

❌ Ignoring NULLs: Always use a backup plan or IFNULL() to stop "null" values from spreading.
❌ Using GROUP_CONCAT instead of JSON_ARRAYAGG: The second one formats JSON correctly.
❌ Unescaped keys or values: Do not put SQL keys that change directly into JSON_OBJECTAGG unless they are cleaned.
❌ Mismatched types: Make sure expected types (strings, numbers) match by using CAST() or CONVERT().

Keep data types consistent. And do not assume all backends deal with nulls or nested arrays the same way.

10. Securing and Validating Your Queries

Even with JSON, SQL injections and data validation are still important:

✅ Use parameterized queries or ORM methods instead of string interpolation.
✅ Use validation libraries like Joi (Node.js), Pydantic (Python), or Cerberus to check JSON structure.
✅ Always set NOT NULL or default values in your SQL schema. This helps cut down on badly formed responses.

JSON does not mean you can have messy data. Badly formed JSON can crash clients or APIs. It can also make finding problems harder.

11. Best Practices for JSON in SQL

🧩 Use JSON fields for data that is partly structured, optional, or about other data. Do not use them for your main related data.
📦 Store only the nested items you need. Do not make your JSON too deep.
💾 Make sure backup and moving tools work with JSON output, especially with machine-readable formats in mysqldump.

12. SQL vs. NoSQL: When to Choose What?

Choosing between MySQL with better JSON handling and a document-store database like MongoDB depends a lot on what you need to do.

Use MySQL if:

You need a strict schema, transactions, and relationships.
The JSON is for showing data or making reports, not mainly for storing it.
Your team is most used to relational databases.

Use NoSQL if:

Your data is deeply nested or changes a lot from one record to the next.
You need fast, changing document querying (for example, by content).
Your schema changes often and in ways you can't guess.

Hybrid solutions like MySQL 8.0 offer a good middle option.

13. Real-World Example: Car Listings by Year

Let’s look at our tables again:

-- Years
| id | year |
|----|------|
| 1  | 2020 |
| 2  | 2021 |

-- Cars
| id | name   | color | year_id |
|----|--------|-------|---------|
| 1  | Toyota | blue  | 1       |
| 2  | Honda  | red   | 1       |
| 3  | Ford   | black | 2       |

Query:

SELECT JSON_ARRAYAGG(
  JSON_OBJECT(
    'year', y.year,
    'cars', (
      SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
          'id', c.id,
          'name', c.name,
          'color', c.color
        )
      )
      FROM cars c
      WHERE c.year_id = y.id
    )
  )
) AS result
FROM years y;

Result:

[
  {
    "year": 2020,
    "cars": [
      { "id": 1, "name": "Toyota", "color": "blue" },
      { "id": 2, "name": "Honda", "color": "red" }
    ]
  },
  {
    "year": 2021,
    "cars": [
      { "id": 3, "name": "Ford", "color": "black" }
    ]
  }
]

This data can be used right in a frontend UI, without needing more queries or sorting logic on the backend.

14. Why Mastering JSON in MySQL Matters

Making structured JSON directly in MySQL lets you remove layers of format changes. It makes your SQL database work like both a regular and document database. If you are building RESTful APIs, changing dashboards, or data-driven platforms, JSON_OBJECTAGG, JSON_ARRAYAGG, and multidimensional JSON array methods give you a lot of flexibility with fewer pieces.

The skills you get from arranging data in groups will lead to better backend setup, more efficient APIs, and data flows that are always the same.

Start trying things out in your own environment. See how SQL-generated JSON can change how you develop for good.

Citations

Dufresne, C. (2021). Efficient JSON use in Relational Databases. ACM SIGMOD Record, 50(2), 15–26.
Oracle Corporation. (2020). MySQL 8.0 Reference Manual: JSON Functions. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
Vennam, A. (2019). Understanding Nested JSON with SQL. IBM Developer.
Stack Overflow Developer Survey. (2023). Technology Usage Circulation Report.