JSON Functions in MySQL 8.0+: JSON_EXTRACT, JSON_SET, and JSON_ARRAYAGG

Not every piece of data fits neatly into fixed columns — a product might have a wildly varying set of attributes, or an API response might need to be stored exactly as received. MySQL 8.0 introduced a native JSON data type along with dedicated functions for storing, querying, and modifying JSON documents directly inside relational tables, bridging the gap between rigid schemas and flexible, semi-structured data.

What Are MySQL's JSON Functions?

MySQL's JSON functions let you store JSON documents in a native JSON column type and then extract, modify, search, and aggregate values within that JSON directly inside SQL queries, without needing to parse the JSON in application code first. JSON_EXTRACT retrieves a value, JSON_SET modifies one, and JSON_ARRAYAGG builds a JSON array from query results.

What You'll Learn

• Extract specific values from a JSON column using JSON_EXTRACT and the -> operator.
• Modify values within a JSON document using JSON_SET.
• Aggregate query results into a JSON array using JSON_ARRAYAGG.
• Recognize appropriate use cases for JSON columns versus traditional normalized columns.

Key Terms to Know

• JSON data type: A native MySQL 8.0+ column type for storing and validating JSON documents.
• JSON_EXTRACT: Retrieves a value from a JSON document at a specified path.
• JSON_SET: Inserts or updates a value within a JSON document at a specified path.
• JSON_ARRAYAGG: Aggregates values from multiple rows into a single JSON array.

Storing and Extracting JSON with JSON_EXTRACT

A column defined as attributes JSON can store a document like {"color": "red", "size": "M", "weight_kg": 0.5}. JSON_EXTRACT(attributes, '$.color') retrieves just the color value from that document, using a JSON path expression starting with $ to represent the document's root.

MySQL also provides the shorthand -> operator as an alternative to JSON_EXTRACT, so attributes->'$.color' is equivalent to JSON_EXTRACT(attributes, '$.color'). The ->> operator additionally unquotes the result, returning a plain string instead of a quoted JSON string value, which is usually what you actually want for display or comparison purposes.

Modifying JSON Documents with JSON_SET

JSON_SET(attributes, '$.size', 'L') returns a new JSON document with the size key updated to 'L', leaving every other key in the document unchanged — note that JSON_SET returns the modified document as a value; to actually persist the change, it needs to be combined with an UPDATE statement, such as UPDATE products SET attributes = JSON_SET(attributes, '$.size', 'L') WHERE product_id = 10;.

If the specified path doesn't already exist in the document, JSON_SET adds it as a new key; this differs from the related JSON_REPLACE function, which only updates existing keys and leaves the document unchanged if the path doesn't already exist.

Aggregating Rows into JSON with JSON_ARRAYAGG

JSON_ARRAYAGG(product_name) combined with GROUP BY collects all the product_name values within each group into a single JSON array, such as turning multiple rows of products within the same category into one row containing a JSON array of all those product names for that category — useful when an API response needs nested array-style data rather than flat rows.

A related function, JSON_OBJECTAGG, similarly aggregates rows into a JSON object (key-value pairs) rather than an array, useful when the aggregated structure needs named keys rather than just an ordered list.

Visual Summary

Picture a row in a table as a structured form with fixed, labeled blanks, plus one special attached envelope that can hold an arbitrarily shaped note inside it (the JSON column). JSON_EXTRACT is like reaching into that envelope and pulling out one specific labeled detail from the note without unfolding the whole thing. JSON_SET is like reaching in and editing just one specific detail on the note, then sealing it back up. JSON_ARRAYAGG is like collecting one detail from many different envelopes across many rows and stapling them all together into a single new list.

Core JSON Functions

Function / Operator	Purpose	Example
JSON_EXTRACT(doc, path)	Retrieve a value at a path	JSON_EXTRACT(attrs, '$.color')
-> / ->>	Shorthand for extract (quoted / unquoted)	attrs->'$.color' / attrs->>'$.color'
JSON_SET(doc, path, value)	Insert or update a value	JSON_SET(attrs, '$.size', 'L')
JSON_ARRAYAGG(expr)	Aggregate values into a JSON array	JSON_ARRAYAGG(product_name)

SQL Example

-- Table with a JSON column for flexible product attributes
CREATE TABLE products (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(150) NOT NULL,
  attributes JSON
);

-- Extracting a specific attribute, unquoted with ->>
SELECT product_name, attributes->>'$.color' AS color
FROM products;

-- Updating one attribute inside the JSON document
UPDATE products
SET attributes = JSON_SET(attributes, '$.size', 'L')
WHERE product_id = 10;

-- Aggregating product names per category into a JSON array
SELECT category, JSON_ARRAYAGG(product_name) AS product_names
FROM products
GROUP BY category;

The table definition shows a JSON column alongside normal typed columns. The second query extracts just the color attribute from each product's JSON document, using the unquoted ->> shorthand for a clean string result. The third updates a single attribute within the JSON document for one specific product. The fourth collects every product name within each category into a single JSON array per category row.

Real-World Examples

• E-commerce platforms use JSON columns for product attributes that vary wildly by category (screen size for TVs, fabric type for clothing) without needing a separate sparse column for every possible attribute.
• API-backed applications use JSON columns to cache an external API's raw response alongside normalized fields extracted from it for querying.
• Analytics platforms use JSON_ARRAYAGG to shape relational query results into nested JSON structures expected by a frontend or reporting tool.
• Configuration-heavy SaaS platforms use JSON columns to store flexible, evolving per-customer settings without requiring a schema migration every time a new setting is introduced.

Best Practices and Pro Tips

• Use JSON columns specifically for genuinely variable or sparse attributes, not as a substitute for proper normalized columns and relationships — overusing JSON can make filtering, indexing, and reporting significantly harder.
• Use the ->> operator (rather than -> or JSON_EXTRACT alone) when you need a clean, unquoted string for comparison or display, since -> and JSON_EXTRACT return a quoted JSON value by default.
• Consider MySQL's generated columns combined with an index on a frequently queried JSON path if performance on JSON lookups becomes a bottleneck, since raw JSON columns aren't indexed by default the way regular columns are.

Common Mistakes to Avoid

• Using a JSON column for data that's actually always present and well-structured, when proper normalized columns and tables would be simpler to query, validate, and index.
• Forgetting that JSON_SET (and JSON_EXTRACT) return a value rather than modifying the table directly — actually persisting a change requires wrapping the call in an UPDATE statement.
• Using -> or JSON_EXTRACT directly for comparisons without realizing the result is a quoted JSON value, which can cause string comparisons to unexpectedly fail; ->> is usually the safer choice for this.
• Storing deeply nested or highly variable JSON structures without any consistent path conventions, making queries difficult to write and maintain over time.

Interview Questions

Q1. What is the difference between the -> and ->> operators in MySQL's JSON functions?

-> (equivalent to JSON_EXTRACT) returns the extracted value as a quoted JSON value. ->> additionally unquotes the result, returning it as a plain string, which is usually more convenient for display or comparison.

Q2. Does JSON_SET modify the table directly, or just return a value?

JSON_SET only returns a new, modified JSON document as a value; it does not modify the table on its own. To persist the change, it must be used within an UPDATE statement's SET clause.

Q3. What does JSON_ARRAYAGG do, and how is it typically used?

JSON_ARRAYAGG aggregates values from multiple rows into a single JSON array, typically combined with GROUP BY, useful for shaping flat relational rows into nested array structures often needed by APIs or reporting tools.

Q4. When is it appropriate to use a JSON column instead of normal relational columns?

JSON columns are appropriate for genuinely variable or sparse data, such as product attributes that differ wildly between categories. They're not a good substitute for well-defined, consistently-structured data, which is better served by normal columns and proper relational design.

Practice MCQs

1. Which operator returns an unquoted string when extracting a value from a JSON column?

1. ->
2. ->>
3. JSON_SET
4. JSON_ARRAYAGG

Answer: B. ->>

Explanation: ->> unquotes the extracted JSON value, returning it as a plain string, unlike -> which returns a quoted JSON value.

2. What must JSON_SET be combined with to actually persist a change to a table?

1. Nothing, it updates automatically
2. An UPDATE statement
3. A CREATE TABLE statement
4. JSON_EXTRACT

Answer: B. An UPDATE statement

Explanation: JSON_SET only returns a modified JSON value; persisting that change to the actual table requires using it within an UPDATE statement's SET clause.

3. What does JSON_ARRAYAGG(product_name) combined with GROUP BY produce?

1. A single product name per row
2. A JSON array of product names for each group
3. A count of products per group
4. An error, since JSON_ARRAYAGG requires no grouping

Answer: B. A JSON array of product names for each group

Explanation: JSON_ARRAYAGG collects all matching values within each group into a single JSON array, producing one row per group containing that array.

Quick Revision Points

• JSON_EXTRACT (and -> shorthand) retrieves a value at a JSON path; ->> additionally unquotes it.
• JSON_SET inserts or updates a value at a path, returning a new document; must be combined with UPDATE to persist.
• JSON_ARRAYAGG aggregates values across rows into a JSON array, typically with GROUP BY.
• JSON columns suit genuinely variable/sparse data, not a substitute for proper relational design.

Conclusion

• MySQL's JSON functions bridge flexible, semi-structured data with the reliability of a relational database.
• JSON_EXTRACT/->/->> and JSON_SET cover the most common read and write needs for JSON columns.
• JSON_ARRAYAGG is a powerful tool for reshaping relational results into nested structures for APIs and reports.

MySQL 8.0's JSON functions — JSON_EXTRACT (and its -> / ->> shorthand), JSON_SET, and JSON_ARRAYAGG — allow genuinely variable or semi-structured data to live inside a relational table without sacrificing SQL's querying power. Used judiciously, alongside proper normalized columns for well-defined data, JSON columns offer real flexibility for attributes that don't fit a fixed schema. The next lesson previews window functions — ROW_NUMBER and RANK — which will be covered in full depth later in the course, in Module 13.