JSON, APPLY and relational conversion

Bridge flexible and queryable data.

UnderstandStudy an examplePractise

Understand the concept

In SQL Server 2019, JSON is generally stored as nvarchar text. ISJSON validates syntax; JSON_VALUE extracts a scalar and JSON_QUERY extracts objects or arrays. OPENJSON turns JSON into rows and needs compatibility level 130 or above. An explicit WITH schema defines types and missing-value handling.

CROSS APPLY evaluates a correlated table expression and discards left rows with no result. OUTER APPLY preserves those rows with NULLs. TOP 1 needs deterministic ordering. Flexibility does not justify hiding all relational data inside JSON: consider integrity, indexing and joins. FOR JSON does not replace API contract validation.

WHEN TO USE IT

Use JSON integration and correlated table expressions.

COMMON PITFALL

Confusing CROSS APPLY and OUTER APPLY on empty results.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT JSON_VALUE(N'{"name":"Ada"}', '$.name') AS Name;

Extracts a scalar field.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Return orders as a JSON array.

Show the solution and explanation

Worked solution

SELECT OrderId, CustomerId, Status FROM lab.SalesOrder ORDER BY OrderId FOR JSON PATH;

The result is JSON text rather than a normal row grid.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.