1.3 — JSON Columns, JSON Indexes and JSON Functions
DP-800 Domain 1 · Skill 1.3
What You Need to Know
Modern SQL platforms treat JSON as a first-class citizen: a native json data type, JSON indexes, and a full set of functions to build, query and shred JSON. DP-800 expects you to pick the right function for each task.
The json data type
CREATE TABLE dbo.Orders (
OrderID int IDENTITY PRIMARY KEY, -- a JSON index needs a clustered primary key
Payload json NOT NULL
);
- Stored in an optimized binary format: it's validated on insert, faster to query than text, and smaller.
- Available in Azure SQL Database, SQL database in Fabric, Azure SQL Managed Instance (always-up-to-date) and SQL Server 2025.
JSON indexes
CREATE JSON INDEX ix_orders_payload ON dbo.Orders (Payload)
FOR ('$.customer.id', '$.status'); -- only the paths you filter on
-- or WITH (OPTIMIZE_FOR_ARRAY_SEARCH = ON) when you search inside arrays
- They speed up predicates such as
JSON_VALUE,JSON_PATH_EXISTSandJSON_CONTAINSon the indexed paths. - The table needs a clustered primary key. You can index the whole document or only chosen paths.
- Before JSON indexes existed, the pattern was a computed column on
JSON_VALUE(...)with a regular index on it, and you still see that pattern.
Functions to know
| Task | Function | Example |
|---|---|---|
| Read one scalar | JSON_VALUE |
JSON_VALUE(Payload, '$.customer.id') |
| Read an object or array | JSON_QUERY |
JSON_QUERY(Payload, '$.items') |
| Check that a path exists | JSON_PATH_EXISTS |
JSON_PATH_EXISTS(Payload, '$.discount') = 1 |
| Check that a value is contained | JSON_CONTAINS |
JSON_CONTAINS(Payload, 'gold', '$.tags') |
| Validate | ISJSON |
ISJSON(@text) = 1 |
| Shred into rows and columns | OPENJSON with a WITH schema |
CROSS APPLY OPENJSON(Payload, '$.items') WITH (sku varchar(20), qty int) |
| Build an object | JSON_OBJECT |
JSON_OBJECT('id': OrderID, 'total': Total) |
| Build an array | JSON_ARRAY |
JSON_ARRAY(1, 2, 3) |
| Aggregate rows into an array | JSON_ARRAYAGG |
JSON_ARRAYAGG(Sku ORDER BY Sku) |
| Aggregate key/value pairs into an object | JSON_OBJECTAGG |
JSON_OBJECTAGG(Code : Qty) |
| Modify a value | JSON_MODIFY |
JSON_MODIFY(Payload, '$.status', 'shipped') |
| Turn a result set into JSON | FOR JSON PATH |
SELECT ... FOR JSON PATH, ROOT('orders') |
-- One row per order line from the JSON array, joined to relational data
SELECT o.OrderID, li.sku, li.qty
FROM dbo.Orders AS o
CROSS APPLY OPENJSON(o.Payload, '$.items')
WITH (sku varchar(20) '$.sku', qty int '$.qty') AS li
WHERE JSON_VALUE(o.Payload, '$.status') = 'open';
When to use JSON
- Semi-structured attributes that vary by row (product specifications, telemetry payloads, API responses).
- Keep frequently filtered, joined or constrained values in real columns, and the rest in JSON.
- JSON is also the bridge to language models:
FOR JSONorJSON_OBJECTturns rows into prompt context (lesson 3.6).
Key Concept: JSON_VALUE reads scalars and JSON_QUERY reads fragments. OPENJSON ... WITH shreds JSON into rows. JSON_OBJECT and JSON_ARRAYAGG build JSON. The json type plus JSON indexes make all of it fast.
Exam Traps
- Using
JSON_VALUEto return an array or object (it returns NULL; useJSON_QUERY) - Creating a JSON index on a table without a clustered primary key
- Storing a constantly filtered key only inside JSON with no index
- Using
OPENJSONwithoutWITHand then wondering why every value is an nvarchar key/value pair
Check your understanding
Practice Scenario
The json column Payload contains an items array, for example [{"sku":"A1","qty":2},{"sku":"B7","qty":1}]. You need one result row per array element, with typed sku and qty columns, next to the OrderID. Which approach should you use?
Log in to save your progress.
Sources
- JSON data type, Microsoft Learn
- CREATE JSON INDEX, Microsoft Learn
- JSON data in SQL Server, Microsoft Learn
- OPENJSON (Transact-SQL), Microsoft Learn
- JSON_ARRAYAGG (Transact-SQL), Microsoft Learn