? JSON Functions

? Quick Overview

MySQL provides powerful JSON functions to store, read, update, and manipulate JSON data directly inside database columns. These functions are widely used in modern applications where flexible schema and nested data structures are required.

? Key Concepts

• JSON is stored using the JSON data type
• Access JSON using path expressions like $.key
• Functions allow read, update, search, and validation
• Indexes can be created on generated JSON values

? Syntax / Theory

• JSON_EXTRACT(json_doc, path)
• -> and ->> operators
• JSON_OBJECT(), JSON_ARRAY()
• JSON_SET(), JSON_REPLACE(), JSON_REMOVE()

? Code Examples

-- Creating table with JSON column
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
profile JSON
);

-- Inserting JSON data
INSERT INTO employees VALUES
(1,'Amit','{"city":"Pune","skills":["SQL","Python"],"experience":5}');

-- Reading JSON value using arrow operator
SELECT profile->>'$.city' AS city FROM employees;

-- Extracting array from JSON
SELECT JSON_EXTRACT(profile,'$.skills') FROM employees;

-- Updating JSON key value
UPDATE employees
SET profile = JSON_SET(profile,'$.city','Mumbai')
WHERE id = 1;

? Live Output / Explanation

? Interactive Example / Diagram

Think of JSON as a document inside a column where each key behaves like a mini-column. Paths like $.skills[0] directly access array values.

? Use Cases

• Storing user preferences
• Dynamic form data
• API responses storage
• Configuration and metadata