🔹 JSON kya hota hai? (Quick recap)
JSON (JavaScript Object Notation) ek lightweight text format hota hai data exchange ke liye.
Example:
{
"empId": 101,
"name": "Amit",
"skills": ["SQL", "Python"],
"address": { "city": "Delhi", "pin": 110001 }
}
🔹 SQL Server me JSON ka support
SQL Server JSON ko as a data type store nahi karta
✔ JSON plain NVARCHAR column me store hota hai
✔ SQL Server JSON ko parse, read, modify kar sakta hai
❌ JSON ke liye native data type nahi hai (PostgreSQL jaisa nahi)
🔹 Kaunse SQL Server versions JSON support karte hain?
| SQL Server Version | JSON Support |
|---|---|
| SQL Server 2014 ❌ | ❌ Not supported |
| SQL Server 2016 | ✅ Supported (first time) |
| SQL Server 2017 | ✅ |
| SQL Server 2019 | ✅ |
| SQL Server 2022 | ✅ |
| Azure SQL DB | ✅ |
👉 Minimum required: SQL Server 2016+
🔹 JSON Store karna (Basic Example)
CREATE TABLE Employees (
EmpID INT,
EmpData NVARCHAR(MAX)
)
INSERT INTO Employees VALUES
(1, '{
"name":"Rohit",
"age":30,
"skills":["SQL","Azure"],
"address":{"city":"Mumbai","pin":400001}
}')
🔹 1️⃣ ISJSON() – JSON valid hai ya nahi
Definition: JSON format valid hai ya nahi check karta hai
SELECT ISJSON(EmpData) FROM Employees
✔ Returns 1 → valid JSON
❌ Returns 0 → invalid JSON
🔹 2️⃣ JSON_VALUE() – Scalar value read karna
Definition: JSON se single value nikalta hai
SELECT
JSON_VALUE(EmpData, '$.name') AS Name,
JSON_VALUE(EmpData, '$.skills[1]') AS skill,
JSON_VALUE(EmpData, '$.address.city') AS City
FROM Employees⚠ Limitation:
- Max 4000 characters
- Scalar value return karta hai ✔
- Array ya object return ❌
🔹 3️⃣ JSON_QUERY() – Object / Array nikalna
Definition: JSON ka object ya array read karta hai
SELECT
JSON_QUERY(EmpData, '$.skills') AS Skills,
JSON_QUERY(EmpData, '$.address') AS Address
FROM Employees
✔ Array / Object return
❌ Scalar value return nahi karta
🔹 JSON_VALUE vs JSON_QUERY (Interview favorite)
| Feature | JSON_VALUE | JSON_QUERY |
|---|---|---|
| Scalar value | ✅ | ❌ |
| Object / Array | ❌ | ✅ |
| Max length | 4000 chars | NVARCHAR(MAX) |
🔹 4️⃣ OPENJSON() – JSON ko table me convert karna
Definition: JSON ko row & column format me tod deta hai
Simple example:
SELECT E.*,A.*
FROM Employees E
CROSS APPLY OPENJSON(E.EmpData) A
Structured extraction (best practice): All Time
SELECT E.*, A.*
FROM Employees E
CROSS APPLY OPENJSON(E.EmpData)
WITH (
Name NVARCHAR(100) '$.name',
Age INT '$.age',
City NVARCHAR(50) '$.address.city'
) AS A;
✔ Yeh ETL aur reporting ke liye sabse powerful hai
Array ko rows me convert karna
SELECT skill
FROM Employees
CROSS APPLY OPENJSON(EmpData, '$.skills')
WITH (skill NVARCHAR(50) '$')
Full JSON Output
SELECT
E.EmpID,
A.Name,
A.Age,
S.value AS Skill
FROM Employees E
CROSS APPLY OPENJSON(E.EmpData)
WITH (
Name NVARCHAR(100) '$.name',
Age INT '$.age',
Skills NVARCHAR(MAX) '$.skills' AS JSON
) AS A
CROSS APPLY OPENJSON(A.Skills) AS S;
--Note:- Comma seperate SKill as per yours choice.
🔹 5️⃣ JSON_MODIFY() – JSON update karna
Definition: JSON ke andar value update karta hai
UPDATE Employees
SET EmpData = JSON_MODIFY(EmpData, '$.age', 31)
WHERE EmpID = 1
Array me value add:
SET EmpData = JSON_MODIFY(EmpData, 'append $.skills', 'PowerBI')
🔹 Indexing JSON data (Performance ke liye)
SQL Server JSON index directly allow nahi karta,
lekin Computed Column + Index bana sakte ho:
ALTER TABLE Employees
ADD City AS JSON_VALUE(EmpData, '$.address.city')
CREATE INDEX IX_Employees_City ON Employees(City)
🔥 Yeh production-level best practice hai