How to retrieve JSON data from MySQL?

MySQL

JSON

Data Retrieval

Database Queries

SQL

How to retrieve JSON data from MySQL?

Master System Design with Codemia

Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.

Start Practicing Learn More

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.

How to retrieve JSON data from MySQL?

Master System Design with Codemia

Introduction

Create a Table with JSON Data

Extract JSON Fields in SELECT Queries

Filter Rows by JSON Content

Use Generated Columns for Indexing

Flatten JSON Arrays with JSON_TABLE

Retrieve JSON in Python Application Code

Handle Missing Keys and Null Values

Common Pitfalls

Summary

Flatten JSON Arrays with `JSON_TABLE`