Practice Now
MySQL
JSON
Data Retrieval
Database Queries
SQL

How to retrieve JSON data from MySQL?

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Databases

Introduction

MySQL JSON columns let you store flexible document-style data while still querying with SQL. The important part is choosing the right retrieval method for your use case: display, filtering, or aggregation. Good JSON retrieval uses path extraction functions, proper typing, and indexes for frequently queried keys.

Create a Table with JSON Data

Start with a reproducible sample table.

sql
1CREATE TABLE orders (
2    id BIGINT PRIMARY KEY AUTO_INCREMENT,
3    customer_id BIGINT NOT NULL,
4    payload JSON NOT NULL,
5    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
6);
7
8INSERT INTO orders (customer_id, payload)
9VALUES
10(1001, '{"status":"paid","total":49.95,"shipping":{"city":"Toronto","method":"express"}}'),
11(1002, '{"status":"pending","total":18.50,"shipping":{"city":"Montreal","method":"standard"}}'),
12(1003, '{"status":"paid","total":120.00,"shipping":{"city":"Toronto","method":"standard"}}');

Now you can query nested values without denormalizing every key into separate columns.

Extract JSON Fields in SELECT Queries

Use JSON_EXTRACT or shorthand operators.

sql
1SELECT
2    id,
3    JSON_EXTRACT(payload, '$.status') AS status_json,
4    payload->'$.shipping.city' AS city_json,
5    payload->>'$.shipping.method' AS shipping_method_text
6FROM orders;

Notes:

  • -> returns JSON value.
  • ->> returns unquoted text.
  • JSON_UNQUOTE(JSON_EXTRACT(...)) is equivalent to ->>.

For numeric operations, cast extracted values explicitly.

sql
1SELECT
2    id,
3    CAST(payload->>'$.total' AS DECIMAL(10,2)) AS total_amount
4FROM orders;

Filter Rows by JSON Content

JSON path filters are common in APIs and dashboards.

sql
1SELECT id, customer_id
2FROM orders
3WHERE payload->>'$.status' = 'paid'
4  AND payload->>'$.shipping.city' = 'Toronto';

This is readable, but performance can degrade on large tables without indexes.

Use Generated Columns for Indexing

For hot query paths, extract JSON fields into generated columns and index them.

sql
1ALTER TABLE orders
2ADD COLUMN status_text VARCHAR(20)
3    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.status'))) STORED,
4ADD COLUMN shipping_city VARCHAR(100)
5    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.shipping.city'))) STORED,
6ADD INDEX idx_orders_status_city (status_text, shipping_city);

Now filtering by status and city can use standard B-tree indexes.

Flatten JSON Arrays with JSON_TABLE

When payload contains arrays, JSON_TABLE is useful for relational querying.

sql
1CREATE TABLE invoices (
2    id BIGINT PRIMARY KEY AUTO_INCREMENT,
3    doc JSON NOT NULL
4);
5
6INSERT INTO invoices (doc)
7VALUES ('{"lines":[{"sku":"A1","qty":2},{"sku":"B9","qty":1}]}');
8
9SELECT i.id, jt.sku, jt.qty
10FROM invoices i,
11JSON_TABLE(
12    i.doc,
13    '$.lines[*]'
14    COLUMNS (
15        sku VARCHAR(20) PATH '$.sku',
16        qty INT PATH '$.qty'
17    )
18) AS jt;

This turns JSON arrays into tabular rows for joins and aggregation.

Retrieve JSON in Python Application Code

When application logic needs full document parsing, fetch and decode JSON.

python
1import json
2import mysql.connector
3
4conn = mysql.connector.connect(
5    host="localhost",
6    user="app",
7    password="secret",
8    database="shop"
9)
10cur = conn.cursor()
11cur.execute("SELECT id, payload FROM orders ORDER BY id")
12
13for order_id, payload in cur.fetchall():
14    data = json.loads(payload)
15    status = data.get("status")
16    city = data.get("shipping", {}).get("city")
17    print(order_id, status, city)
18
19cur.close()
20conn.close()

Prefer SQL-side extraction for filtering and sorting, and app-side parsing for complex business logic.

Handle Missing Keys and Null Values

JSON documents are often non-uniform. Query defensively so missing keys do not break reporting logic.

sql
1SELECT
2    id,
3    COALESCE(payload->>'$.shipping.city', 'unknown') AS city,
4    COALESCE(CAST(payload->>'$.total' AS DECIMAL(10,2)), 0.00) AS total_amount
5FROM orders;

You can also validate key existence for quality checks:

sql
SELECT id
FROM orders
WHERE JSON_CONTAINS_PATH(payload, 'one', '$.status', '$.shipping.city') = 0;

These checks are helpful in ingestion pipelines where payload shape may drift over time.

Common Pitfalls

  • Filtering by JSON keys without generated-column indexes on large tables.
  • Comparing numeric values as strings due to missing casts.
  • Fetching entire payload when only one key is needed.
  • Storing inconsistent JSON shapes and assuming all keys always exist.
  • Mixing SQL extraction and app parsing without clear ownership of validation.

Summary

  • Use JSON_EXTRACT, ->, and ->> for targeted JSON retrieval.
  • Cast extracted values to correct SQL types for comparisons and math.
  • Add generated columns plus indexes for frequently filtered keys.
  • Use JSON_TABLE to query array elements relationally.
  • Keep retrieval strategy clear between database and application layers.
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.