?️ MySQL JSON Indexing

? Quick Overview

MySQL supports JSON data type, allowing structured data storage inside columns. JSON indexing in MySQL means extracting values from JSON objects or arrays using path expressions and built-in JSON functions.

? Key Concepts

JSON columns store structured data in key–value format
JSON paths start with $
Objects use keys, arrays use numeric indexes
Functions like JSON_EXTRACT() and -> operator are used

? Syntax / Theory

JSON_EXTRACT(json_col,'$.key') → Access object value
json_col->'$.key' → Shorthand extraction
json_col->>'$.key' → Extract as plain text
$.array[index] → Access array element

? Code Examples

? View Code Example

// Creating table with JSON column
CREATE TABLE students (
id INT PRIMARY KEY,
data JSON
);

// Inserting JSON data
INSERT INTO students VALUES
(1,'{"name":"Rahul","age":22,"skills":["HTML","CSS","SQL"]}');

? View Code Example

// Extracting JSON object value
SELECT data->>'$.name' AS student_name
FROM students;

// Extracting array element using index
SELECT data->>'$.skills[1]' AS second_skill
FROM students;

? View Code Example

// Accessing nested JSON using JSON_EXTRACT
SELECT JSON_EXTRACT(data,'$.age') AS student_age
FROM students;

? Live Output / Explanation

Output

Rahul
CSS
22

The JSON path expression navigates through keys and array indexes to fetch the required values from the JSON column.

? Interactive Playground

Test your JSON path skills live!

Database Row (JSON Column)

SQL Path Expression

Extraction Result

✅ Tips & Best Practices

Always validate JSON using JSON_VALID()
Use ->> to avoid quoted output
Create generated columns for indexing JSON values
Keep JSON structure consistent across rows

? Try It Yourself

Create a table with a JSON column for employee details
Store name, salary, and an array of skills
Extract the first skill and salary using JSON paths