JSON Functions, OPENJSON, JSON Construction, Indexing Strategies, and API Workloads

Learning outcomes

ServiceHub receives partner API payloads containing flexible attributes that do not justify a new relational column for every optional field. A team stores raw text and uses string searches; another stores JSON but never validates it; a third wants to adopt SQL Server 2025 native json columns and JSON indexes immediately because “2025 is GA.” The correct design separates stable relational keys from semi-structured payloads, stable JSON functions from SQL Server 2025 preview features, and syntax validity from business-schema validity.

1. JSON is a representation, not a replacement for relational invariants

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab06') IS NULL EXEC(N'CREATE SCHEMA lab06 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab06.ApiInbox;GOCREATE TABLE lab06.ApiInbox(  message_id bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_lab06_ApiInbox PRIMARY KEY,  source_system varchar(30) NOT NULL,  received_at datetime2(3) NOT NULL CONSTRAINT DF_lab06_ApiInbox_Received DEFAULT SYSUTCDATETIME(),  payload nvarchar(max) NOT NULL,  CONSTRAINT CK_lab06_ApiInbox_IsJson CHECK (ISJSON(payload)=1));INSERT lab06.ApiInbox(source_system,payload) VALUES('partnerA',N'{"workOrderId":5001,"priority":3,"customer":{"tier":"gold"},"tags":["urgent","hvac"]}'),('partnerA',N'{"workOrderId":5002,"priority":1,"customer":{"tier":"standard"},"tags":["inspection"]}');GO

ISJSON protects syntactic JSON validity. It does not prove that workOrderId exists, is unique, is a number, or refers to a real ServiceHub work order. Those are business constraints that belong in relational columns, typed extraction, validation code, or a defined ingestion contract.

2. Scalar, object, and rowset extraction are different operations

JSON_VALUE extracts a scalar value. JSON_QUERY returns an object or array fragment. OPENJSON turns JSON into rows; with an explicit WITH clause it also converts selected paths to declared SQL types. Prefer typed extraction at ingestion/query boundaries so failures and nullability are deliberate.

USE ServiceHubLab;GOSELECT message_id,       TRY_CONVERT(bigint, JSON_VALUE(payload,'$.workOrderId')) AS work_order_id,       TRY_CONVERT(int, JSON_VALUE(payload,'$.priority')) AS priority,       JSON_VALUE(payload,'$.customer.tier') AS customer_tier,       JSON_QUERY(payload,'$.tags') AS tags_jsonFROM lab06.ApiInboxORDER BY message_id;GOSELECT i.message_id, j.[value] AS tagFROM lab06.ApiInbox AS iCROSS APPLY OPENJSON(i.payload,'$.tags') AS jORDER BY i.message_id,j.[key];GO

The array query uses OPENJSON because a collection is naturally a rowset. Do not split JSON arrays with string functions. If a required path is missing or has the wrong type, decide whether ingestion should reject the message, quarantine it, or store a nullable result; silent coercion is an application policy, not a JSON feature.

3. OPENJSON WITH is a typed ingestion boundary

USE ServiceHubLab;GOSELECT i.message_id, x.work_order_id, x.priority, x.customer_tierFROM lab06.ApiInbox AS iCROSS APPLY OPENJSON(i.payload)WITH(  work_order_id bigint '$.workOrderId',  priority int '$.priority',  customer_tier nvarchar(20) '$.customer.tier') AS xORDER BY i.message_id;GO

This schema is explicit and reviewable. It also prevents downstream queries from repeatedly guessing lengths and types. OPENJSON is available at database compatibility level 130 or higher; the course baseline is 170, so the mandatory lab satisfies that prerequisite.

4. FOR JSON defines an API serialization contract

FOR JSON PATH serializes a rowset. Column aliases can create nested property paths, and options control behaviors such as null inclusion and outer array wrapping. The database can be an effective serializer for bounded API shapes, but large unbounded documents can increase CPU, memory, network payload, and coupling. Keep pagination and field ownership explicit.

USE ServiceHubLab;GOSELECT TOP (2)       TRY_CONVERT(bigint,JSON_VALUE(payload,'$.workOrderId')) AS [workOrder.id],       TRY_CONVERT(int,JSON_VALUE(payload,'$.priority')) AS [workOrder.priority],       JSON_VALUE(payload,'$.customer.tier') AS [customer.tier]FROM lab06.ApiInboxORDER BY message_idFOR JSON PATH, ROOT('messages');GO

The outer SELECT determines which rows and their order; FOR JSON formats that result. If the API requires stable pagination, apply the deterministic ordering rules from Chapter 05. JSON serialization does not create consistency across independent requests.

5. Index the property you actually search, with an appropriate SQL type

For long-established SQL Server JSON designs, a computed column can expose a frequently queried scalar property and an ordinary index can target that expression. Cast/extract to the narrowest correct SQL type rather than indexing an unconstrained text expression. This gives the optimizer ordinary relational statistics and access paths while retaining the original payload.

USE ServiceHubLab;GOALTER TABLE lab06.ApiInboxADD work_order_id AS TRY_CONVERT(bigint,JSON_VALUE(payload,'$.workOrderId'));GOCREATE INDEX IX_lab06_ApiInbox_WorkOrderIdON lab06.ApiInbox(work_order_id);GOSELECT message_id,source_system,work_order_idFROM lab06.ApiInboxWHERE work_order_id=5002;GO

Capture the actual plan at realistic scale. Two-row lab data may still scan because scanning is cheaper. The lesson proves indexability and schema design, not a fabricated performance win. A computed index also adds write/storage/statistics maintenance, so create it for measured search patterns rather than every possible JSON path.

6. SQL Server 2025 native JSON and JSON indexes are preview on SQL Server

SQL Server 2025 adds a native binary json type, and current Microsoft documentation marks that type as preview for SQL Server 2025. CREATE JSON INDEX is likewise preview and currently requires, among other documented restrictions, a clustered primary key on the table. Feature availability differs from Azure SQL Database/Managed Instance, so “works in Azure” does not automatically mean the on-prem SQL Server 2025 feature is GA.

-- OPTIONAL PREVIEW ONLY: verify current docs/build and enable/use preview features as required.-- Do not deploy this snippet as a production-default recommendation.CREATE TABLE lab06.JsonPreviewExample(  id bigint NOT NULL PRIMARY KEY CLUSTERED,  doc json NOT NULL);-- CREATE JSON INDEX IX_JsonPreviewExample_Doc--   ON lab06.JsonPreviewExample(doc) FOR ('$.workOrderId');

Preview status can change after this lesson is published. Re-check Microsoft Learn, release notes, servicing notes, and any preview-enable requirements on the exact build before testing. The mandatory course path remains functional without the preview type/index.

7. Production judgment and chapter cleanup

Use JSON for attributes whose shape genuinely benefits from semi-structured storage, but keep stable identifiers, relationships, security boundaries, and high-value predicates relational when that improves integrity and operability. Validate syntax, define business schema rules, bound document size, protect sensitive fields, and monitor parse/extraction CPU plus computed-index write cost. Avoid logging entire sensitive payloads by default.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab06.JsonPreviewExample;DROP TABLE IF EXISTS lab06.ApiInbox;DROP TABLE IF EXISTS lab06.ExternalTicket;DROP TABLE IF EXISTS lab06.TechnicianMetric;DROP TABLE IF EXISTS lab06.InvoiceLine;DROP TABLE IF EXISTS lab06.WorkEvent;GOIF SCHEMA_ID(N'lab06') IS NOT NULL   AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE schema_id=SCHEMA_ID(N'lab06'))  EXEC(N'DROP SCHEMA lab06;');GO

Chapter 06 completes the advanced query-shaping layer: windows analyze detail rows, advanced GROUP BY produces multiple grains, PIVOT reshapes controlled dimensions, OUTPUT/UPSERT logic handles writes under concurrency, and JSON bridges semi-structured API payloads. Chapter 07 moves into transactions, locking, row versioning, isolation, and deadlocks—the mechanisms that explain the concurrency tradeoffs introduced here.

Authoritative references

• JSON data in SQL Server — JSON storage/query overview
• ISJSON — syntax validation
• JSON_VALUE — scalar path extraction
• OPENJSON — rowset and typed shredding
• FOR JSON — result serialization
• Index JSON data — computed-column indexing approach
• json data type — SQL Server 2025 native type and current preview status
• CREATE JSON INDEX — SQL Server 2025 preview indexing requirements
• SQL Server 2025 build versions — servicing baseline