The Complete Practical Guide to Storing, Querying, Indexing, and Producing JSON with T-SQL

Read before you buy: the PDF here is a free sample — the first 20 pages of the 496-page book (payments, sales and reviews on this demo site are sample data). Get the full e-book at thenexbooks on Gumroad.

JSON is everywhere — and sooner or later it lands in your SQL Server database.

Web and mobile apps send it, payment and shipping providers post it to your webhooks, partners exchange it, and AI assistants call tools with JSON arguments. SQL Server and Azure SQL give you a powerful set of tools to work with it: ISJSON, JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, JSON_MODIFY, the SQL Server 2022 constructors, and the native json type. Used well, they let you keep strong relational guarantees where they matter and flexible documents where they help. Used carelessly, they produce slow, unindexable, unvalidated data.

This book shows you how to do it well — from your first JSON_VALUE to indexed, secure, production-grade JSON workloads.

• 496 pages · 28 chapters + 4 appendices
• Every JSON function and clause explained with realistic T-SQL
• Shredding nested arrays with OPENJSON and CROSS APPLY
• Building API-ready documents with FOR JSON, JSON_OBJECT, and JSON_ARRAY
• Indexing JSON with computed columns, the native json type, and query tuning
• Bulk loading files, JSON parameters, EF Core, Dapper, Node.js, and Python
• Auditing, security, privacy, testing, and operations
• 2 complete case studies, including an AI shopping assistant
• Version notes throughout: SQL Server 2016, 2022, 2025, and Azure SQL — preview features clearly labeled

Table Of Contents

Part I — Foundations

• Ch 1 · Why JSON in SQL Server: Relational Meets Document
• Ch 2 · JSON Essentials for SQL Developers
• Ch 3 · Storing JSON: nvarchar(max), varchar(max), and the Native json Type
• Ch 4 · Validating JSON: ISJSON, CHECK Constraints, and Data Quality
• Ch 5 · JSON Path Expressions in Depth: Lax, Strict, Arrays, and Escaping

Part II — Reading JSON

• Ch 6 · Extracting Values: JSON_VALUE and JSON_QUERY
• Ch 7 · Shredding JSON with OPENJSON: The Default Schema
• Ch 8 · OPENJSON with Explicit Schemas and CROSS APPLY
• Ch 9 · Nested Arrays, Hierarchies, and Multi-Level Shredding
• Ch 10 · Existence, Membership, and Search: JSON_PATH_EXISTS, JSON_CONTAINS, and Patterns

Part III — Producing and Changing JSON

• Ch 11 · FOR JSON PATH: Shaping Query Output
• Ch 12 · FOR JSON AUTO and Output Options: ROOT, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
• Ch 13 · Constructing JSON: JSON_OBJECT, JSON_ARRAY, JSON_OBJECTAGG, and JSON_ARRAYAGG
• Ch 14 · Building API-Ready Documents from Relational Data
• Ch 15 · Modifying JSON: JSON_MODIFY and the json Type's modify Method

Part IV — Performance, Indexing, and Modeling

• Ch 16 · Indexing JSON with Computed Columns
• Ch 17 · The Native json Type and JSON Indexes in Depth
• Ch 18 · Query Tuning for JSON Workloads
• Ch 19 · Bulk Loading and Exporting JSON: OPENROWSET, BULK INSERT, bcp, and Files
• Ch 20 · Hybrid Data Modeling: Columns, JSON, or Both?

Part V — Application Integration

• Ch 21 · JSON Parameters: Passing Sets and Documents to Stored Procedures
• Ch 22 · JSON from Application Code: .NET, EF Core, Dapper, Node.js, and Python
• Ch 23 · Auditing, Change Capture, and Event Payloads with JSON
• Ch 24 · Security, Privacy, and Governance for JSON Data
• Ch 25 · JSON in Azure SQL and the Cloud: REST Endpoints, Data API builder, and Integrations

Part VI — Production and Case Studies

• Ch 26 · Testing, Schema Evolution, and Operating JSON in Production
• Ch 27 · Case Study: The Orbit Store Product Catalog with Flexible Attributes
• Ch 28 · Case Study: The Orbit Store Order API and AI Shopping Assistant

Appendices

• App A · T-SQL JSON Cheat Sheet
• App B · Templates and Function Reference
• App C · Production Checklist
• App D · Glossary

WHO IT IS FOR
Database developers, backend and full-stack developers, DBAs, and architects who work with SQL Server, Azure SQL Database, or Azure SQL Managed Instance and want to use JSON correctly and fast.

FORMAT
PDF (A4), 496 pages, English, clickable table of contents and PDF bookmarks. 1st Edition, 2026. Published by thenexbooks.

This book is independent and is not affiliated with or endorsed by Microsoft.

Book details