SQL Lab — JSON and Semi-Structured Data (Hard)

🖥️

SQL Terminal

📚

Reference

📌 OBJECTIVES — NO HINTS

○Inspect JSON structure (JSON_KEYS, JSON_TYPE)

○Extract values with JSON_EXTRACT / ->

○Create virtual generated column from JSON path

○Index the generated column

○Rewrite slow JSON report as generated-column query

○Use JSON_TABLE for array flattening

○Validate: report query under 100ms

0 / 7 complete

SQL TERMINAL — JSON AND SEMI-STRUCTURED DATA (HARD)

MySQL 8.0 — Lab environment loaded JSON and Semi-Structured Data (Hard) — Hard tier · No syntax hints provided ⚠ This lab tests independent problem-solving. Reference window available. ──────────────────────────────

mysql>

COMMAND REFERENCE

AVAILABLE COMMANDS

SELECT JSON_KEYS(metadata) FROM transactions LIMIT 1;
SELECT metadata->'$.merchant.name', metadata->'$.risk_score' FROM transactions LIMIT 5;
ALTER TABLE transactions ADD COLUMN risk_score DECIMAL(5,2) AS (metadata->'$.risk_score') VIRTUAL;
CREATE INDEX idx_risk ON transactions(risk_score);
SELECT merchant_name, AVG(risk_score) FROM transactions GROUP BY merchant_name HAVING AVG(risk_score) > 0.7;
SELECT jt.* FROM transactions, JSON_TABLE(metadata, '$.tags[*]' COLUMNS(tag VARCHAR(50) PATH '$')) AS jt;

Type keywords from the command list. The terminal will recognise your intent even if the exact syntax differs.

MISSION BRIEFING

JSON COLUMNS — CRESTVIEW FINTECH · HARD ⭐⭐⭐

SCENARIO

Crestview FinTech stores transaction metadata as JSON blobs in a single metadata column. Analysts cannot query inside them and performance is terrible. You must extract the key paths, create generated columns for the hot fields, index the generated columns, and rewrite the JSON-heavy reports as efficient SQL.

OBJECTIVES

Inspect JSON structure (JSON_KEYS, JSON_TYPE)
Extract values with JSON_EXTRACT / ->
Create virtual generated column from JSON path
Index the generated column
Rewrite slow JSON report as generated-column query
Use JSON_TABLE for array flattening
Validate: report query under 100ms

HARD MODE RULES

No step-by-step guidance. Use the Reference window for command syntax only. You must determine the approach, the correct columns and order, and verify your own results.

Display Mode