JSON in SQL Server: FOR JSON, OPENJSON and JSON_VALUE – SQL Server | Extra 3

Published / updated4 Oct 2026

Introduction

Web and mobile apps talk to servers in JSON (JavaScript Object Notation): plain text made of names and values, such as {"fullName": "Aarav Sharma"}. The SMS Web API you will build in Module 10 sends and receives JSON.

SQL Server can create JSON from tables, and read JSON back into rows. This lesson shows both directions, using real SMS screens.

By the end of this lesson, you will be able to:

Read JSON: objects, arrays, and nesting.
Turn query results into nested JSON with FOR JSON PATH.
Turn a JSON array into rows with OPENJSON and save them.
Check and read JSON with ISJSON, JSON_VALUE, and JSON_QUERY.
Decide when JSON belongs in the database and when it does not.

Why this matters

The teachers' mobile app marks attendance for a whole section and sends it in one message. The parent app shows a profile with the student's class and both guardians.

Without JSON support, the app would make one call per student, or the database would send flat rows that the API must reshape. Knowing JSON in SQL Server lets you choose the simplest path, and read JSON already stored in your tables, such as the Lesson 23 audit log.

The concept

Example: JSON in five minutes

Part	Looks like	Means
Object	`{ "name": "Aarav" }`	A set of name and value pairs, like one row
Array	`[ 1, 2, 3 ]`	An ordered list, like several rows
String	`"Class 10"`	Text, always in double quotes
Number	`92.5`	No quotes
true / false / null	`true`	Yes, no, and "no value"

Values can contain other objects and arrays, so one JSON document can hold a student and their guardians together. Think of a school ID card with a folded list of guardians clipped to it: one card, everything in one place.

JSON is text. In SQL Server, it is stored in an NVARCHAR(MAX) column or variable.

Example: the four tools

Tool	Direction	Use
`FOR JSON PATH`	Rows to JSON	Build an API-ready response
`OPENJSON`	JSON to rows	Save a JSON array into a table
`JSON_VALUE`	JSON to one value	Read one number or text
`ISJSON`	Check	Returns 1 if the text is valid JSON

JSON_QUERY is like JSON_VALUE, but returns a whole object or array instead of one value.

Apply it to the SMS

The script builds Aarav's profile, saves 10-A's attendance from a JSON array, tests bad input, and reads the audit log. Every change is rolled back.

Show extras/e3-json.sql

extras/e3-json.sql
-- SQL Extra 3: JSON in SQL Server.
-- Part 1 only reads. Parts 2 to 4 change data inside transactions that are rolled back.
USE SchoolDb;
GO

SET NOCOUNT ON;
GO

-- 1. FOR JSON PATH: one student's profile, shaped like an API response.
DECLARE @Profile NVARCHAR(MAX) = (
    SELECT
        s.Id AS id,
        s.AdmissionNumber AS admissionNumber,
        s.FirstName + N' ' + s.LastName AS fullName,
        s.DateOfBirth AS dateOfBirth,
        c.Name AS [currentClass.className],   -- a dot in the alias makes a nested object
        sec.Name AS [currentClass.section],
        e.RollNumber AS [currentClass.rollNumber],
        (
            SELECT
                g.FirstName + N' ' + g.LastName AS name,
                sg.Relationship AS relationship,
                g.Phone AS phone,
                sg.IsPrimaryContact AS isPrimaryContact
            FROM dbo.StudentGuardians AS sg
            JOIN dbo.Guardians AS g ON g.Id = sg.GuardianId
            WHERE sg.StudentId = s.Id
            ORDER BY sg.IsPrimaryContact DESC, g.Id
            FOR JSON PATH                     -- a subquery with FOR JSON makes an array
        ) AS guardians
    FROM dbo.Students AS s
    JOIN dbo.Enrollments AS e ON e.StudentId = s.Id AND e.AcademicYearId = 2
    JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
    JOIN dbo.Classes AS c ON c.Id = sec.ClassId
    WHERE s.AdmissionNumber = 'ADM-2017-0001'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER       -- one object, not an array of one
);

SELECT @Profile AS StudentProfileJson;

-- Read values back out of the JSON text.
SELECT
    ISJSON(@Profile) AS IsValidJson,
    JSON_VALUE(@Profile, '$.fullName') AS FullName,
    JSON_VALUE(@Profile, '$.currentClass.className') AS ClassName,
    JSON_VALUE(@Profile, '$.guardians[0].name') AS PrimaryContact,
    JSON_QUERY(@Profile, '$.currentClass') AS CurrentClassObject;

-- OPENJSON without a schema: one row per top-level property.
SELECT [key], [type]
FROM OPENJSON(@Profile);
GO

-- 2. OPENJSON with a schema: the mobile app sends 10-A's attendance for one day.
DECLARE @AttendanceDate DATE = '2026-07-06';
DECLARE @Json NVARCHAR(MAX) = N'[
    {"enrollmentId": 13, "status": "Present"},
    {"enrollmentId": 14, "status": "Present"},
    {"enrollmentId": 15, "status": "Late", "remarks": "Bus delay"},
    {"enrollmentId": 16, "status": "Absent"}
]';

SELECT ISJSON(@Json) AS IsValidJson;

SELECT EnrollmentId, Status, Remarks
FROM OPENJSON(@Json)
WITH (
    EnrollmentId INT           '$.enrollmentId',
    Status       VARCHAR(7)    '$.status',
    Remarks      NVARCHAR(200) '$.remarks'     -- missing in most rows, so NULL
);

BEGIN TRANSACTION;

INSERT INTO dbo.AttendanceRecords (EnrollmentId, AttendanceDate, Status, Remarks)
SELECT j.EnrollmentId, @AttendanceDate, j.Status, j.Remarks
FROM OPENJSON(@Json)
WITH (
    EnrollmentId INT           '$.enrollmentId',
    Status       VARCHAR(7)    '$.status',
    Remarks      NVARCHAR(200) '$.remarks'
) AS j;

SELECT @@ROWCOUNT AS RowsInserted;

SELECT st.FirstName, ar.Status, ar.Remarks
FROM dbo.AttendanceRecords AS ar
JOIN dbo.Enrollments AS e ON e.Id = ar.EnrollmentId
JOIN dbo.Students AS st ON st.Id = e.StudentId
WHERE ar.AttendanceDate = @AttendanceDate
  AND e.SectionId = (SELECT SectionId FROM dbo.Enrollments WHERE Id = 13)
ORDER BY e.RollNumber;

ROLLBACK TRANSACTION;
GO

-- 3. Bad input: broken JSON is caught by ISJSON; a bad value is caught by the CHECK constraint.
SELECT ISJSON(N'[{"enrollmentId": 13, "status": "Present",}]') AS TrailingCommaIsValid;

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO dbo.AttendanceRecords (EnrollmentId, AttendanceDate, Status)
    SELECT j.EnrollmentId, '2026-07-06', j.Status
    FROM OPENJSON(N'[{"enrollmentId": 13, "status": "Present"}, {"enrollmentId": 14, "status": "Holiday"}]')
    WITH (EnrollmentId INT '$.enrollmentId', Status VARCHAR(7) '$.status') AS j;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    SELECT ERROR_NUMBER() AS ErrorNumber, LEFT(ERROR_MESSAGE(), 80) AS ErrorMessage;
END CATCH;
GO

-- 4. JSON already in the SMS: the Lesson 23 audit trigger stores old and new marks as JSON.
BEGIN TRANSACTION;

UPDATE dbo.Marks SET MarksObtained = 90 WHERE Id = 1;  -- Aarav, Mathematics, was 92

SELECT
    a.OldValues,
    a.NewValues,
    JSON_VALUE(a.OldValues, '$.MarksObtained') AS OldMark,
    JSON_VALUE(a.NewValues, '$.MarksObtained') AS NewMark
FROM dbo.AuditLogs AS a
WHERE a.TableName = 'Marks'
  AND a.RecordId = 1;

ROLLBACK TRANSACTION;
GO

Example: a nested profile with FOR JSON PATH

Part 1 returns this text, formatted here for reading:

Output
{
  "id": 1,
  "admissionNumber": "ADM-2017-0001",
  "fullName": "Aarav Sharma",
  "dateOfBirth": "2011-04-12",
  "currentClass": { "className": "Class 10", "section": "A", "rollNumber": 1 },
  "guardians": [
    { "name": "Rajesh Sharma", "relationship": "Father", "phone": "9876540101", "isPrimaryContact": true },
    { "name": "Neha Sharma", "relationship": "Mother", "phone": "9876540102", "isPrimaryContact": false }
  ]
}

Three rules made this shape:

You write	You get
Column alias `admissionNumber`	The property name, in the camelCase style APIs use
Alias with a dot, `[currentClass.section]`	A nested object `currentClass`
A subquery ending in `FOR JSON PATH`	An array, here `guardians`

WITHOUT_ARRAY_WRAPPER returns one object instead of an array with one item. The bit column IsPrimaryContact became true and false, and the date became "2011-04-12".

Example: FOR JSON escapes text for you

Names can contain characters that have a meaning in JSON, such as double quotes. A guardian entered as Rajesh "Raju" Sharma would break hand-made JSON:

Example
SELECT N'Rajesh "Raju" Sharma' AS name
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Output
{"name":"Rajesh \"Raju\" Sharma"}

FOR JSON added a backslash before each quote, so the result is still valid JSON. That is why you never build JSON by joining strings with +.

Example: reading values back

IsValidJson	FullName	ClassName	PrimaryContact
1	Aarav Sharma	Class 10	Rajesh Sharma

The path $.currentClass.className starts at the root ($) and walks down. Array positions start at 0, so $.guardians[0].name is the first guardian.

JSON_QUERY(@Profile, '$.currentClass') returned the whole object, {"className":"Class 10","section":"A","rollNumber":1}. JSON_VALUE on the same path would return NULL, because it only returns single values.

To change one value inside JSON text, use JSON_MODIFY. It returns a new copy with the change:

Example
DECLARE @Class NVARCHAR(MAX) = N'{"className":"Class 10","section":"A","rollNumber":1}';
SELECT JSON_MODIFY(@Class, '$.section', 'B') AS AfterModify;

AfterModify
`{"className":"Class 10","section":"B","rollNumber":1}`

Example: OPENJSON without a schema

OPENJSON(@Profile) alone lists the top-level properties and their types:

key	type
id	2 (number)
admissionNumber	1 (string)
fullName	1 (string)
dateOfBirth	1 (string)
currentClass	5 (object)
guardians	4 (array)

This is handy when you receive JSON and are not sure what is inside. Before you write the WITH schema for a new message, run OPENJSON on a real sample first, and build the schema from what it shows.

Example: saving an array with OPENJSON

Part 2 receives 10-A's attendance for 6 July as a JSON array. OPENJSON ... WITH names each column, its type, and its path:

EnrollmentId	Status	Remarks
13	Present	NULL
14	Present	NULL
15	Late	Bus delay
16	Absent	NULL

A property missing from an item, like remarks, simply becomes NULL. One INSERT ... SELECT then saves all four rows: RowsInserted is 4, and the check query shows Aarav, Diya, Kabir, and Meera with their statuses.

One statement means all or nothing. Four separate calls from the app could fail halfway and leave the section half marked.

Example: bad input

Part 3 tests two kinds of bad input:

Problem	What catches it	Result
A trailing comma: `"Present",}`	`ISJSON`	Returns 0: not valid JSON
A status of `"Holiday"`	`CK_AttendanceRecords_Status`	Error 547; neither row is saved

JSON does not bypass your rules. Constraints, foreign keys, and triggers check every row that OPENJSON produces, exactly as with any other insert.

Example: JSON already in the SMS

The Lesson 23 audit trigger stores old and new marks as JSON. Part 4 changes Aarav's Mathematics mark from 92 to 90, then reads the audit row:

OldValues	NewValues	OldMark	NewMark
`{"MarksObtained":92.00,"IsAbsent":false}`	`{"MarksObtained":90.00,"IsAbsent":false}`	92.00	90.00

Storing changed values as JSON lets one AuditLogs table serve every table, whatever its columns. JSON_VALUE gets the details back out when someone asks "who changed this mark?"

Example: JSON column or normal columns?

Store as normal columns when	Store as JSON when
You filter, join, or sort by the value	The shape varies, like audit details
A constraint must check it	You store it and give it back whole
Many reports read it	It is extra data you rarely query

The SMS tables use normal columns for everything the school searches. JSON is for the edges: API responses, uploads, and logs.

SQL Server 2025 adds a native json data type. This course targets SQL Server 2022, so it uses NVARCHAR(MAX), which works in both.

Example: database JSON or C# JSON?

In Module 10, the Web API turns C# objects into JSON automatically. So who should build the JSON: SQL Server or C#?

Situation	Usual choice
Normal API endpoints, such as a student profile	C# builds the JSON from objects
A large, nested report read straight from the database	`FOR JSON` can be simpler and faster
Saving many rows sent by an app in one call	`OPENJSON` in a procedure, or C# with a bulk insert
Reading audit details stored as JSON	`JSON_VALUE` in SQL

Most teams build JSON in C#, because the API controls its own output format. Knowing the SQL side helps you read existing systems, and gives you a fast option when you need one.

Common mistakes

Building JSON by gluing strings together. One quote in a name breaks it. Fix: use FOR JSON, which escapes text correctly.
Using JSON_VALUE for an object or array. It returns NULL. Fix: use JSON_QUERY.
Forgetting WITHOUT_ARRAY_WRAPPER. The API returns [{...}] instead of {...}. Fix: add it when the query returns one object.
Trusting incoming JSON. Fix: check ISJSON, and keep constraints on the table.
Storing searchable data as JSON. Filters on it are slow and unchecked. Fix: use normal columns for anything you filter or join on.
Wrong letter case in a path. JSON paths are case-sensitive: '$.EnrollmentId' does not find "enrollmentId", so OPENJSON returns NULL for every row, and the insert fails on the NOT NULL column. Fix: copy the property names exactly from a real sample of the JSON the app sends.

In real projects

From 16 years of .NET projects

On a school app, the attendance screen first sent one request per student. On a bad mobile network, a teacher's 50 taps became 50 requests, and some failed silently.

We changed it to one JSON array per section, saved by one INSERT ... OPENJSON in a transaction. The section was either fully marked or not at all, and the teacher saw a clear error instead of missing rows.

The lesson I took away: JSON is a transport format. We still kept every value in proper columns, with proper constraints.

Best practices

Use FOR JSON PATH with camelCase aliases for API-shaped output.
Use OPENJSON ... WITH and explicit types when saving JSON input.
Validate with ISJSON, and let constraints check every value.
Save a whole JSON array in one statement, inside a transaction.
Keep searchable data in columns; use JSON for flexible or pass-through data.

Recap

JSON is text made of objects, arrays, and values; SQL Server stores it as NVARCHAR(MAX).
FOR JSON PATH turns rows into JSON; dots in aliases nest, subqueries make arrays.
OPENJSON ... WITH turns a JSON array into typed rows.
JSON_VALUE reads one value; JSON_QUERY reads an object or array.
ISJSON checks the text; constraints still check the data.

Homework

Library module task: Build a student's library card as JSON: the student's Id and name, plus an issues array with the book title, copy number, dates, and fine. Keep "returnedOn": null for books not yet returned.

A correct result looks like: for Advika (Id 7), one issue of The Jungle Book, copy 1, issued on 6 July, due on 20 July, with "returnedOn": null.

Show the solution

library-assignment/e3-library-json.sql
-- Library homework, Extra 3: a student's library card as JSON, with every book issued to them.
USE SchoolDb;
GO

SET NOCOUNT ON;
GO

DECLARE @StudentId INT = 7;  -- Advika, who still has a book

DECLARE @Card NVARCHAR(MAX) = (
    SELECT
        s.Id AS studentId,
        s.FirstName + N' ' + s.LastName AS studentName,
        (
            SELECT
                b.Title AS title,
                bc.CopyNumber AS copyNumber,
                bi.IssuedOn AS issuedOn,
                bi.DueOn AS dueOn,
                bi.ReturnedOn AS returnedOn,
                bi.FineAmount AS fine
            FROM dbo.BookIssues AS bi
            JOIN dbo.BookCopies AS bc ON bc.Id = bi.BookCopyId
            JOIN dbo.Books AS b ON b.Id = bc.BookId
            WHERE bi.StudentId = s.Id
            ORDER BY bi.IssuedOn
            FOR JSON PATH, INCLUDE_NULL_VALUES   -- keep "returnedOn": null for open issues
        ) AS issues
    FROM dbo.Students AS s
    WHERE s.Id = @StudentId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

SELECT @Card AS LibraryCardJson;
SELECT JSON_VALUE(@Card, '$.issues[0].title') AS FirstBookTitle;
GO

The output, formatted for reading:

Output
{
  "studentId": 7,
  "studentName": "Advika Sharma",
  "issues": [
    { "title": "The Jungle Book", "copyNumber": 1, "issuedOn": "2026-07-06", "dueOn": "2026-07-20", "returnedOn": null, "fine": 0.00 }
  ]
}

Without INCLUDE_NULL_VALUES, FOR JSON leaves NULL properties out completely.

Quiz

Which tool turns query results into JSON?
1. OPENJSON
2. FOR JSON PATH
3. JSON_VALUE
4. ISJSON
Show answer
Answer: B. FOR JSON PATH FOR JSON PATH goes from rows to JSON; OPENJSON goes the other way.
In FOR JSON PATH, what does the alias [currentClass.section] create?
1. A column named currentClass.section
2. A nested object currentClass with a property section
3. An array
4. An error
Show answer
Answer: B. A nested object currentClass with a property section A dot in the alias nests the property inside an object.
JSON_VALUE(@j, '$.currentClass') returns NULL. Why?
1. The path is wrong
2. currentClass is an object, and JSON_VALUE returns single values only
3. JSON is case-insensitive
4. ISJSON is 0
Show answer
Answer: B. currentClass is an object, and JSON_VALUE returns single values only Use JSON_QUERY for objects and arrays.
An OPENJSON insert includes one row with status 'Holiday'. What happens?
1. That row is skipped
2. The CHECK constraint fails, and no rows from the statement are saved
3. Holiday is saved
4. It becomes NULL
Show answer
Answer: B. The CHECK constraint fails, and no rows from the statement are saved Constraints check every row, and one statement is all or nothing.
Which data should stay in normal columns rather than JSON?
1. Audit details with varying shapes
2. Values you filter, join, or sort by
3. A response sent straight to the app
4. Rarely used extra data
Show answer
Answer: B. Values you filter, join, or sort by Columns give you indexes, constraints, and fast filters.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is JSON, and how does SQL Server store it? concept

JSON is a text format of objects (name and value pairs), arrays, and values, used by web and mobile apps. SQL Server 2022 stores it as text in NVARCHAR(MAX); SQL Server 2025 adds a native json type.

See the example · Permalink

F What do FOR JSON PATH and OPENJSON do? concept

FOR JSON PATH turns query results into JSON text. OPENJSON does the opposite: it turns JSON text into rows, which you can select or insert into a table.

See the example · Permalink

F How do you check whether a string is valid JSON? concept

ISJSON(@text) returns 1 for valid JSON and 0 otherwise. A trailing comma, for example, makes it return 0.

See the example · Permalink

Mid (3-5 yrs)

M How do you return nested JSON, such as a student with a guardians array? coding

Use FOR JSON PATH. A dot in a column alias, like [currentClass.section], creates a nested object. A correlated subquery that ends with FOR JSON PATH creates an array. Add WITHOUT_ARRAY_WRAPPER to return a single object.

See the example · Permalink

M What is the difference between JSON_VALUE and JSON_QUERY? concept

JSON_VALUE returns a single value, such as a number or text. JSON_QUERY returns an object or array. JSON_VALUE on an object returns NULL, which is a common surprise.

See the example · Permalink

M How do you insert rows from a JSON array? coding

Use INSERT ... SELECT ... FROM OPENJSON(@json) WITH (Column TYPE '$.path', ...). The WITH clause names each column, its type, and its path; missing properties become NULL. Paths are case-sensitive.

See the example · Permalink

Senior (6-9 yrs)

S The attendance app sends one request per student and some fail on weak networks. How would you redesign it? scenario

Send the whole section as one JSON array and save it with one INSERT ... OPENJSON (or a bulk insert from C#) inside a transaction. The section is then saved completely or not at all, and the app can retry safely because the unique key prevents duplicates.

How to answer: Explain the failure mode (partial saves), the new design (one atomic call), and the safety net (validation, constraints, unique keys for idempotent retries). Mention a clear error message for the teacher.

See the example · Permalink

S When should data be stored as JSON instead of normal columns? concept

Use JSON for data with a varying shape that you store and return whole, such as audit details or extra settings. Use normal columns for anything you filter, join, sort, or must check with constraints.

How to answer: Give both sides with examples, then the cost of getting it wrong: slow, unchecked filters on JSON, or many sparse columns. Mention computed columns with indexes for the occasional JSON filter.

See the example · Permalink

Architect / Lead (10+ yrs)

A Should JSON for an API be built in SQL Server or in the application? design

Usually in the application: the API controls its own response format, and C# serializes objects automatically. FOR JSON is a good option for large nested reports read straight from the database, and OPENJSON for saving many rows sent in one call.

How to answer: Weigh separation of concerns, performance, testability, and team skills. Show you would pick per case and keep one consistent default.

See the example · Permalink

Resources

Code for this lesson: git tag extra-3-json (code repository coming soon)
Files: extras/e3-json.sql, library-assignment/e3-library-json.sql
Video: coming soon

What's next

You can now move data in and out as JSON. Next: making SQL Server remember every version of a row automatically, so you can ask what a fee was on any past date. Extra 4: Temporal tables

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: JSON in five minutes​

Example: the four tools​

Apply it to the SMS​

Example: a nested profile with FOR JSON PATH​

Example: FOR JSON escapes text for you​

Example: reading values back​

Example: OPENJSON without a schema​

Example: saving an array with OPENJSON​

Example: bad input​

Example: JSON already in the SMS​

Example: JSON column or normal columns?​

Example: database JSON or C# JSON?​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: JSON in five minutes

Example: the four tools

Apply it to the SMS

Example: a nested profile with FOR JSON PATH

Example: FOR JSON escapes text for you

Example: reading values back

Example: OPENJSON without a schema

Example: saving an array with OPENJSON

Example: bad input

Example: JSON already in the SMS

Example: JSON column or normal columns?

Example: database JSON or C# JSON?

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next