Launch Deal · 50% off 1st month ·

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_EXISTS and JSON_CONTAINS on 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 JSON or JSON_OBJECT turns 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_VALUE to return an array or object (it returns NULL; use JSON_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 OPENJSON without WITH and 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?

Report issue

Log in to save your progress.

Sources