#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

MySQL JSON Data Type: Store and Query JSON

An online store sells kurtas with sizes, shoes with a waterproof flag and bottles with a capacity in ml. Giving every possible attribute its own column leaves most cells NULL. The JSON column type, available since MySQL 5.7.8, lets each row carry its own set of attributes. MySQL still validates the JSON, lets you query inside it and can index parts of it. This tutorial covers storing, reading, filtering, updating and indexing JSON, and when not to use it.

Store JSON

CREATE TABLE products (
  id    INT PRIMARY KEY,
  name  VARCHAR(40),
  attrs JSON
);

INSERT INTO products VALUES
(1,'Cotton kurta', '{"brand":"Weavers","sizes":["S","M","L"],"price":799,"care":{"wash":"cold"}}'),
(2,'Running shoes','{"brand":"Stride","sizes":["8","9","10"],"price":2499,"waterproof":false}'),
(3,'Steel bottle', '{"brand":"Hydra","capacity_ml":750,"price":549}');

Invalid JSON is rejected, which a plain TEXT column would never do:

INSERT INTO products VALUES (4,'Broken','{"brand": "X", }');
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 15 in value for column 'products.attrs'.

Read values: -> versus ->>

SELECT name,
       attrs->'$.brand'      AS brand_json,
       attrs->>'$.brand'     AS brand_text,
       attrs->>'$.care.wash' AS wash
FROM products;
namebrand_jsonbrand_textwash
Cotton kurta"Weavers"Weaverscold
Running shoes"Stride"StrideNULL
Steel bottle"Hydra"HydraNULL

-> is short for JSON_EXTRACT() and returns a JSON value, with its quotes. ->> also unquotes it (JSON_UNQUOTE(JSON_EXTRACT(...))), which is what you want for display and for comparing with plain strings. The path starts with $, and dots step into nested objects. A path that does not exist returns NULL rather than an error.

Filter on JSON contents

SELECT name, attrs->>'$.price' AS price
FROM products
WHERE CAST(attrs->>'$.price' AS UNSIGNED) < 1000
ORDER BY CAST(attrs->>'$.price' AS UNSIGNED);
nameprice
Steel bottle549
Cotton kurta799

Cast before comparing numbers. Otherwise you compare text, and '2499' sorts before '549'. To test whether an array holds a value, either form works:

SELECT name FROM products WHERE JSON_CONTAINS(attrs->'$.sizes', '"M"');
SELECT name FROM products WHERE 'M' MEMBER OF (attrs->'$.sizes');   -- 8.0.17+
name
Cotton kurta

Note the inner quotes in '"M"': JSON_CONTAINS expects a JSON value, and a JSON string needs quotes.

Inspect structure

SELECT name,
       JSON_LENGTH(attrs->'$.sizes')                   AS n_sizes,
       JSON_KEYS(attrs)                                 AS keys_list,
       JSON_CONTAINS_PATH(attrs, 'one', '$.waterproof') AS has_wp
FROM products;
namen_sizeskeys_listhas_wp
Cotton kurta3["care", "brand", "price", "sizes"]0
Running shoes3["brand", "price", "sizes", "waterproof"]1
Steel bottleNULL["brand", "price", "capacity_ml"]0

The key order is not the order you inserted. MySQL normalises JSON when storing it (keys sorted by length, then alphabetically), so never rely on key order.

Turn a JSON array into rows with JSON_TABLE

SELECT p.name, s.size
FROM products p,
     JSON_TABLE(p.attrs, '$.sizes[*]' COLUMNS (size VARCHAR(5) PATH '$')) AS s
ORDER BY p.id, s.size;
namesize
Cotton kurtaL
Cotton kurtaM
Cotton kurtaS
Running shoes10
Running shoes8
Running shoes9

JSON_TABLE (MySQL 8.0) turns a JSON array into relational rows that you can join, group or count. The bottle has no sizes, so it produces no rows. The sizes sort as text ('10' before '8') because the column was declared VARCHAR.

Update part of a document

UPDATE products SET attrs = JSON_SET(attrs, '$.price', 749, '$.on_sale', true) WHERE id = 1;
UPDATE products SET attrs = JSON_REMOVE(attrs, '$.care') WHERE id = 1;
UPDATE products SET attrs = JSON_ARRAY_APPEND(attrs, '$.sizes', 'XL') WHERE id = 1;
SELECT attrs FROM products WHERE id = 1;
attrs
{"brand": "Weavers", "price": 749, "sizes": ["S", "M", "L", "XL"], "on_sale": true}

JSON_SET replaces existing keys and adds missing ones. JSON_INSERT only adds, and JSON_REPLACE only replaces. Because you do not rewrite the whole document from the application, two programs editing different keys are less likely to overwrite each other's changes.

Index a JSON field

A JSON column cannot be indexed directly. Expose the field as a generated column and index that:

ALTER TABLE products
  ADD COLUMN brand VARCHAR(30) GENERATED ALWAYS AS (attrs->>'$.brand') VIRTUAL,
  ADD INDEX idx_brand (brand);

EXPLAIN SELECT name FROM products WHERE brand = 'Stride';
typepossible_keyskeyrefrows
refidx_brandidx_brandconst1

The plan now uses idx_brand. MySQL 8.0.17 also added multi-valued indexes for arrays (INDEX ((CAST(attrs->'$.sizes' AS CHAR(5) ARRAY)))), which speed up MEMBER OF and JSON_CONTAINS. For more on how indexes work, see MySQL indexes explained.

Build JSON from rows

SELECT JSON_OBJECT('id', id, 'name', name) AS obj FROM products WHERE id = 2;
SELECT JSON_ARRAYAGG(name) AS all_names FROM products;
obj
{"id": 2, "name": "Running shoes"}
all_names
["Cotton kurta", "Running shoes", "Steel bottle"]

This is handy when an API wants JSON straight from the database.

When JSON is the wrong choice

A common middle ground is regular columns for the core fields plus one JSON column for optional, varying attributes, as in the products table above. For choosing the other column types, see the MySQL data types guide.

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →