How to Parse JSON in MySQL

How do I parse JSON in MySQL?

Use MySQL’s JSON functions and operators to extract, transform, and filter data stored in JSON columns or strings.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why parse JSON in MySQL?

Parsing JSON lets you keep flexible schemas while still querying individual attributes efficiently. You can filter rows, join tables, and build reports without exporting data to an application layer.

What syntax does MySQL offer for JSON parsing?

Use JSON_EXTRACT(), ->, and ->> to fetch JSON values. JSON_UNQUOTE(), JSON_SET(), and JSON_REPLACE() modify or cleanse data. The JSON_TABLE() function turns JSON arrays into relational rows.

How do I pull a customer’s city from a JSON column?

Store customer addresses in Customers.address (JSON). Retrieve the city:

SELECT id, name, address->>"$.city" AS city FROM Customers WHERE address->>"$.country" = 'US';

How do I extract nested array values?

When an order’s metadata JSON contains an items array, use:

SELECT JSON_EXTRACT(metadata, '$.items[0].product_id') AS first_item FROM Orders WHERE id = 42;

How can I flatten JSON arrays for analytics?

JSON_TABLE() converts each element into a row, perfect for aggregations.

SELECT o.id AS order_id, jt.product_id, jt.quantity FROM Orders o, JSON_TABLE(o.metadata, '$.items[*]' COLUMNS( product_id INT PATH '$.product_id', quantity INT PATH '$.quantity')) AS jt;

Best practices for JSON parsing

Index frequently parsed paths with virtual generated columns. Validate JSON on insert to avoid malformed data. Prefer ->> for strings to skip quoting.

Common mistakes and fixes

Using the wrong operator: -> returns quoted JSON; cast or use ->> for plain text.

Ignoring nulls: If the key is missing, the result is NULL; coalesce when necessary (COALESCE(field, default)).

Need quick reference?

Remember: -> JSON, ->> text, JSON_TABLE rows. Index paths you query often and keep JSON small for performance.

Why How to Parse JSON in MySQL is important

How to Parse JSON in MySQL Example Usage


-- List out-of-stock products stored in a JSON column
SELECT p.id, p.name
FROM Products p
WHERE p.details->>'$.stock_status' = 'out_of_stock';

How to Parse JSON in MySQL Syntax


JSON_EXTRACT(json_doc, path[, path]...)
json_doc->'$.path'  -- JSON value (keeps quotes)
json_doc->>'$.path' -- Text value (no quotes)
JSON_TABLE(json_doc, path COLUMNS (...))

-- Ecommerce examples
-- 1. Extract a customer’s signup year
SELECT JSON_EXTRACT(metadata, '$.signup_year')
FROM Customers;

-- 2. Get total_amount from an Orders JSON column
SELECT order_json->>'$.totals.amount' AS total_amount
FROM Orders;

Common Mistakes

Using -> when you need plain text: -> returns JSON, so numeric comparisons fail. Fix by switching to ->> or casting.
Forgetting to index parsed paths: repeated JSON_EXTRACT calls without virtual columns slow queries. Create generated columns and index them.