JSON Data – Complete Conceptual Guide (SQL Server Perspective)

Lesson 81 of 96

🔹 JSON kya hota hai? (Quick recap)

JSON (JavaScript Object Notation) ek lightweight text format hota hai data exchange ke liye.

Example:

HTML 
{
  "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 VersionJSON 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)

Markdown 
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

CSS 
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

HTML 
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

HTML 
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)

FeatureJSON_VALUEJSON_QUERY
Scalar value✅❌
Object / Array❌✅
Max length4000 charsNVARCHAR(MAX)

🔹 4️⃣ OPENJSON() – JSON ko table me convert karna

Definition: JSON ko row & column format me tod deta hai

Simple example:

CSS 
SELECT E.*,A.*
FROM Employees E
CROSS APPLY OPENJSON(E.EmpData) A

Structured extraction (best practice): All Time

Markdown 
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

HTML 
SELECT skill
FROM Employees
CROSS APPLY OPENJSON(EmpData, '$.skills')
WITH (skill NVARCHAR(50) '$')

Full JSON Output

SQL 
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

SQL 
UPDATE Employees
SET EmpData = JSON_MODIFY(EmpData, '$.age', 31)
WHERE EmpID = 1

Array me value add:

HTML 
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:

SQL 
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

On This Page