JSON in T-SQL

Status: Reviewed (deep-dive v1)
Stack note: PS collectors and Blazor can pass JSON into usp_* instead of XML/CSV. Use OPENJSON / FOR JSON — still prefer TVPs for simple id lists (SQL-21).
Goal: Lean JSON patterns for payloads without turning SQL into an app server.

Quick chooser

Need	Prefer
List of ids	TVP (SQL-21)
Nested PS probe payload	`OPENJSON` → `#temp` / staging
Return shaped DTO to Blazor	`FOR JSON PATH` from proc
Store config blob	`nvarchar(max)` + check `ISJSON`

Inbound (PS → proc)

CREATE OR ALTER PROC dbo.usp_Inventory_MergeInstancesJson
  @Payload nvarchar(max)
AS
BEGIN
  SET NOCOUNT ON;
  IF ISJSON(@Payload) <> 1 THROW 50001, N'Invalid JSON.', 1;

  INSERT dbo.SqlInstanceStage (InstanceKey, HostName, InstanceName, Version, LastSeenAt)
  SELECT
    j.InstanceKey, j.HostName, j.InstanceName, j.Version,
    COALESCE(j.LastSeenAt, SYSUTCDATETIME())
  FROM OPENJSON(@Payload)
  WITH (
    InstanceKey nvarchar(128) '$.InstanceKey',
    HostName nvarchar(128) '$.HostName',
    InstanceName nvarchar(128) '$.InstanceName',
    Version nvarchar(128) '$.Version',
    LastSeenAt datetime2(0) '$.LastSeenAt'
  ) j;

  EXEC dbo.usp_Inventory_MergeInstances;  -- SQL-19
END;

Outbound (proc → Blazor)

SELECT a.AlertId, a.Title, a.SeverityCode
FROM dbo.Alert a
WHERE a.Status = N'Open'
FOR JSON PATH, ROOT('alerts');

Or keep tabular results — Blazor often prefers grids; JSON when nested graphs help.

Habits

[ ] Validate ISJSON
[ ] Explicit WITH schema on OPENJSON
[ ] Size caps on @Payload
[ ] Don’t shred JSON in a cursor
[ ] Index real columns after import — not JSON path forever

Done when

[ ] Collector JSON path documented vs TVP/stage
[ ] No CSV-in-JSON anti-pattern for ids
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-26 JSON in T-SQL v1