How to Parse JSON in MariaDB

Galaxy Glossary

How do I parse JSON data in MariaDB?

Extract values from JSON columns or strings with JSON_EXTRACT and related helpers.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

What does JSON_EXTRACT do?

JSON_EXTRACT() reads a JSON document and returns the value found at the supplied path. It works on JSON, LONGTEXT, or VARCHAR columns that contain valid JSON and supports multiple path arguments in one call.

How to parse JSON from a column?

Use JSON_EXTRACT(column, '$.path') or the shorthand column->'$.path'. Wrap the call with JSON_UNQUOTE() when you want plain text instead of a JSON value.

Example: get customer city

SELECT JSON_UNQUOTE(address->'$.city') AS city FROM Customers WHERE id = 1; returns “Berlin” when the address column stores {"city":"Berlin","zip":"10115"}.

How to parse JSON from a string literal?

Call JSON_EXTRACT('{"name":"Laptop","spec":{"ram":16}}', '$.spec.ram'); This returns 16 without any table reference, useful for quick tests.

How to filter rows by JSON content?

Combine JSON_EXTRACT in WHERE. Example: SELECT * FROM Products WHERE JSON_EXTRACT(attributes,'$.color') = '"red"'; Add a generated column color GENERATED ALWAYS AS (JSON_UNQUOTE(attributes->'$.color')) STORED and index it for speed.

Best practices for JSON parsing

Validate input with JSON_VALID(), use generated columns to index frequent paths, avoid wildcard paths that disable indexes, and keep JSON documents small to prevent full-row scans.

Why How to Parse JSON in MariaDB is important

How to Parse JSON in MariaDB Example Usage


-- Extract a product’s color and stock from a JSON column
SELECT 
  JSON_UNQUOTE(attributes->'$.color')  AS color,
  JSON_EXTRACT(attributes,'$.stock')   AS stock
FROM Products
WHERE id = 42;

How to Parse JSON in MariaDB Syntax


JSON_EXTRACT(json_doc, path[, path ...])
    -- Returns JSON value at path

json_doc->'$.path'
    -- Shorthand for JSON_EXTRACT(json_doc,'$.path')

JSON_UNQUOTE(JSON_EXTRACT(json_doc,'$.path'))
    -- Returns plain text

JSON_VALID(json_doc)
    -- 1 if json_doc is valid JSON, 0 otherwise

Common Mistakes

Using an incorrect path like '$.city' on a nested document produces NULL. Fix by specifying the full path, e.g., '$.address.city'.
Comparing raw JSON_EXTRACT output to unquoted text returns no rows because the result is still JSON. Wrap the call in JSON_UNQUOTE() or compare to a quoted value.

Frequently Asked Questions (FAQs)

Is JSON_EXTRACT slower than normal columns?

Yes, because the function must parse the JSON at runtime. Use generated columns and indexes when you query the same path often.

Can I update a single JSON key?

Yes. Use JSON_SET(column,'$.key', new_value). The function returns an updated JSON document that you can write back with UPDATE.

Does MariaDB store JSON in a binary format?

No. Unlike MySQL, MariaDB stores JSON as plain LONGTEXT, so size and validation depend on your application layer.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.