Advanced Analytical JSON Queries0%
Table Partitioning Principles: Range, List, Hash, Key

Advanced Analytical JSON Queries

Advanced14 min read•Updated: 2026-09-12
Study Materials

Advanced Analytical JSON Queries

Enterprise applications frequently encounter scenarios where semi-structured JSON payloads must be converted into flat relational tables for analytics, or flat relational rows must be aggregated into hierarchical JSON payloads for REST APIs.

MySQL 8.0 bridges this gap using two powerhouse features:

  1. 1
    JSON Aggregation Functions (JSON_ARRAYAGG, JSON_OBJECTAGG)
  2. 2
    JSON_TABLE(): The JSON-to-Relational Table function.

1. Relational-to-JSON Aggregation

Instead of performing messy client-side grouping to assemble hierarchical JSON API responses:

SQL
-- Assemble a complete Customer Profile with nested orders array in 1 query!
SELECT
c.customer_id,
c.first_name,
c.email,
JSON_ARRAYAGG(
JSON_OBJECT(
'order_id', o.order_id,
'total', o.total_amount,
'order_date', o.order_date
)
) AS order_history
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.email;

2. JSON_TABLE(): Transforming JSON into Flat Relational Tables

JSON_TABLE() is a revolutionary table function that parses JSON text and renders it as an inline virtual relational table inside a FROM clause.

SQL
SELECT jt.*
FROM product_catalogs p,
JSON_TABLE(
p.attributes,
'$.tags[*]' COLUMNS (
tag_index FOR ORDINALITY,
tag_name VARCHAR(50) PATH '$'
)
) AS jt;

Output:

The JSON array ["gaming", "portable", "vr-ready"] is unrolled into three separate relational rows!

3. Combining JSON_TABLE with Window Functions

Once JSON data is unrolled via JSON_TABLE, you can execute full analytical window functions against it:

SQL
-- Unroll order line items from a raw JSON payload and rank products by revenue
WITH UnrolledItems AS (
SELECT
jt.product_sku,
jt.quantity,
jt.unit_price,
(jt.quantity * jt.unit_price) AS line_total
FROM api_payload_logs log,
JSON_TABLE(
log.raw_payload,
'$.items[*]' COLUMNS (
product_sku VARCHAR(50) PATH '$.sku',
quantity INT PATH '$.qty',
unit_price DECIMAL(10, 2) PATH '$.price'
)
) AS jt
)
SELECT
product_sku,
line_total,
DENSE_RANK() OVER (ORDER BY line_total DESC) AS revenue_rank,
ROUND((line_total / SUM(line_total) OVER()) * 100, 2) AS pct_of_total_cart
FROM UnrolledItems;

Multiple Choice Questions

1. Which function transforms tabular SQL rows into a single JSON array of objects?

A. GROUP_CONCAT() B. JSON_ARRAYAGG() C. JSON_MERGE() D. JSON_EXPORT() Answer: B Explanation: JSON_ARRAYAGG() aggregates values or JSON_OBJECT expressions across grouped rows into a unified JSON array.

2. What is the primary purpose of the JSON_TABLE() function in MySQL 8.0?

A. Creates a table with only JSON columns B. Transforms a JSON document or array into virtual relational rows and columns in the FROM clause C. Backs up JSON data to disk D. Converts CSV files into JSON Answer: B Explanation: JSON_TABLE() unrolls JSON structures into standard tabular rows and columns for querying with standard SQL clauses.

3. In a JSON_TABLE column definition, what does FOR ORDINALITY do?

A. Automatically increments an auto-number sequence (1, 2, 3...) representing array index position B. Sorts strings in alphabetical order C. Formats dates into ISO 8601 D. Sets the primary key Answer: A Explanation: FOR ORDINALITY generates a 1-based sequential row counter indicating the position of each unrolled array item.

4. Can window functions be combined with data unrolled through JSON_TABLE()?

A. No, JSON_TABLE prevents windowing B. Yes, rows generated by JSON_TABLE behave as standard relational tables and support all window functions C. Only in MySQL 9.0 D. Only with ROW_NUMBER() Answer: B Explanation: JSON_TABLE outputs standard relational rows that integrate seamlessly with CTEs, joins, and window functions.

5. Which function creates key-value JSON objects from two relational columns (e.g., config_key, config_value)?

A. JSON_OBJECTAGG() B. JSON_PAIR() C. JSON_MAP() D. JSON_BIND() Answer: A Explanation: JSON_OBJECTAGG(key_col, val_col) aggregates key-value pairs from rows into a JSON dictionary object.

Next Lesson

Table Partitioning Principles: Range, List, Hash, Key

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
Indexing JSON via Virtual Generated ColumnsTable Partitioning Principles: Range, List, Hash, Key

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext