Working with JSON in T-SQL: FOR JSON, OPENJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY and Indexable JSON

Version note: The SQL queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10) with the series' Bookstore database; the PostgreSQL comparisons were run on PostgreSQL 18.7.

Guess First: Reading a 5000-Character Value with JSON_VALUE

A book record from a supplier has a 5000-character blurb (blurb). We read the title and the blurb with JSON_VALUE. What comes back?

DECLARE @doc NVARCHAR(MAX) = CONCAT(N'{"isbn":"9798900000268","title":"Dune","blurb":"', REPLICATE(CAST(N'x' AS NVARCHAR(MAX)), 5000), N'"}');
SELECT JSON_VALUE(@doc, '$.title') AS title, JSON_VALUE(@doc, '$.blurb') AS blurb, LEN(JSON_VALUE(@doc, '$.blurb')) AS blurb_len;

title  blurb  blurb_len
-----  -----  ---------
Dune   NULL   NULL

No error, no warning, just NULL. JSON_VALUE returns its result as nvarchar(4000); if the value is longer than 4000 characters, the default "lax" mode quietly returns NULL. To tell whether the value is missing from the JSON or simply does not fit, you have to put strict at the start of the path:

DECLARE @doc NVARCHAR(MAX) = CONCAT(N'{"isbn":"9798900000268","title":"Dune","blurb":"', REPLICATE(CAST(N'x' AS NVARCHAR(MAX)), 5000), N'"}');
SELECT JSON_VALUE(@doc, 'strict $.blurb') AS blurb;

Msg 13625, Level 16, State 1, Line 2
String value in the specified JSON path would be truncated.

The right tool for long values is the WITH schema of OPENJSON, where you choose the column type yourself:

DECLARE @doc NVARCHAR(MAX) = CONCAT(N'{"isbn":"9798900000268","title":"Dune","blurb":"', REPLICATE(CAST(N'x' AS NVARCHAR(MAX)), 5000), N'"}');
SELECT LEN(blurb) AS blurb_len FROM OPENJSON(@doc) WITH (blurb NVARCHAR(MAX) '$.blurb');

blurb_len
---------
5000

This post covers the JSON core of T-SQL: turning a query result into JSON with FOR JSON, opening JSON into rows with OPENJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY and ISJSON, writing JSON that comes from the application into tables in a single round trip, and indexing JSON. Most of these functions have been around since SQL Server 2016; JSON_OBJECT/JSON_ARRAY from post 17 and the native json type and JSON_ARRAYAGG from post 18 were added on top of them. The examples run on the database of Bookstore, the online bookstore used as the shared example across this site's series.

FOR JSON: Turning a Query Result into JSON

The order detail endpoint of the Bookstore API returns an order, its customer and its items as one JSON document. Instead of building it in the application with three queries and a mapping layer, you can produce it in one query:

SELECT o.order_id AS id, o.status, o.ordered_at AS orderedAt,
       c.first_name + ' ' + c.last_name AS [customer.name], c.email AS [customer.email],
       (SELECT b.title, oi.quantity, oi.unit_price AS unitPrice
        FROM dbo.order_items AS oi
        JOIN dbo.books AS b ON b.book_id = oi.book_id
        WHERE oi.order_id = o.order_id
        ORDER BY oi.order_item_id
        FOR JSON PATH) AS items
FROM dbo.orders AS o
JOIN dbo.customers AS c ON c.customer_id = o.customer_id
WHERE o.order_id = 370
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"id":370,"status":"Shipped","orderedAt":"2026-06-11T15:03:40","customer":{"name":"Annie Easley","email":"annie.easley@example.com"},"items":[{"title":"And Then There Were None","quantity":1,"unitPrice":430.00},{"title":"One Thousand and One Nights","quantity":1,"unitPrice":370.50},{"title":"Crime and Punishment","quantity":1,"unitPrice":200.00}]}

In FOR JSON PATH, the column aliases define the structure. A dotted alias such as [customer.name] produces a nested object ("customer":{...}). The correlated subquery we use for the items returns an array with its own FOR JSON PATH, and the outer query embeds it as JSON, not as a string; the value produced by FOR JSON is marked as "already JSON". WITHOUT_ARRAY_WRAPPER removes the [ ] around a single-row result, so the API returns an object directly. The date is written in ISO 8601 format and the DECIMAL values as unquoted numbers; the types carry over from the column types.

For NULL values the default is not to write the key at all. If your API contract wants the key to be always there, add INCLUDE_NULL_VALUES; ROOT wraps the result in a named object:

SELECT book_id AS id, title, subtitle FROM dbo.books WHERE book_id IN (26, 28) ORDER BY book_id FOR JSON PATH;
SELECT book_id AS id, title, subtitle FROM dbo.books WHERE book_id IN (26, 28) ORDER BY book_id FOR JSON PATH, INCLUDE_NULL_VALUES, ROOT('books');

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
---------------------------------------------------------
[{"id":26,"title":"Dune"},{"id":28,"title":"Foundation"}]

(1 rows affected)
JSON_F52E2B61-18A1-11d1-B105-00805F49916B
---------------------------------------------------------------------------------------------------
{"books":[{"id":26,"title":"Dune","subtitle":null},{"id":28,"title":"Foundation","subtitle":null}]}

Code on the Angular side that reads book.subtitle sees undefined or null in either case, but contract tests and typed clients notice the difference. FOR JSON AUTO, on the other hand, derives the structure from the joins and uses the table alias as the key of the nested arrays:

SELECT b.book_id, b.title, a.last_name
FROM dbo.books AS b
JOIN dbo.book_authors AS ba ON ba.book_id = b.book_id
JOIN dbo.authors AS a ON a.author_id = ba.author_id
WHERE b.book_id IN (26, 42)
ORDER BY b.book_id, ba.author_order
FOR JSON AUTO;

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
--------------------------------------------------------------------------------------------------------------------------------------------------------
[{"book_id":26,"title":"Dune","a":[{"last_name":"Herbert"}]},{"book_id":42,"title":"Good Omens","a":[{"last_name":"Pratchett"},{"last_name":"Gaiman"}]}]

The authors sit under the key "a", because we gave the authors table the alias a. AUTO is handy for a quick look, but you do not want the shape of an API response to depend on an alias name or on the join order. Use PATH and explicit aliases in production code.

The column name JSON_F52E2B61-18A1-11d1-B105-00805F49916B is a hint too: it is a fixed name, and once the result grows it no longer fits in one row. Let's ask for the whole catalog as JSON:

SELECT book_id AS id, isbn, title, list_price AS price FROM dbo.books ORDER BY book_id FOR JSON PATH;

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
...
(3 rows affected)

sqlcmd showed three rows: 2033, 2033 and 1781 characters. SQL Server sends a long FOR JSON result to the client in chunks; SSMS joins them in the grid and shows them as one row, while ExecuteScalar in ADO.NET returns only the first chunk, that is, the first 2033 characters of a broken JSON. You either read all rows and concatenate them, or assign the result to a variable and return it as a single value:

DECLARE @j NVARCHAR(MAX) = (SELECT book_id AS id, isbn, title, list_price AS price FROM dbo.books ORDER BY book_id FOR JSON PATH);
SELECT LEN(@j) AS json_length;

json_length
-----------
5847

A FOR JSON used as a subquery is not split: all 5847 characters are in one nvarchar(max) value. SELECT (SELECT ... FOR JSON PATH) AS books_json gives the same result and puts a meaningful name on the column.

OPENJSON: Opening JSON into Rows

The other direction is OPENJSON. Called without a schema, it returns one row for each key of an object or each element of an array:

DECLARE @order NVARCHAR(MAX) = N'{"id":370,"status":"Shipped","customer":{"name":"Annie Easley"},"items":[{"bookId":46,"quantity":1},{"bookId":25,"quantity":2}],"gift":true,"note":null}';
SELECT [key], value, type FROM OPENJSON(@order);

key       value                                                    type
--------  -------------------------------------------------------  ----
id        370                                                      2
status    Shipped                                                  1
customer  {"name":"Annie Easley"}                                  5
items     [{"bookId":46,"quantity":1},{"bookId":25,"quantity":2}]  4
gift      true                                                     3
note      NULL                                                     0

The type column gives the JSON type as a number: 0 null, 1 string, 2 number, 3 boolean, 4 array, 5 object. value is always text; both 370 and Shipped are nvarchar. In real work you mostly use the WITH schema: you write the type and JSON path of each column, take a nested array as raw JSON with AS JSON, and open it one level further with CROSS APPLY. A batch of two orders:

DECLARE @orders NVARCHAR(MAX) = N'[
  {"id":501,"customerId":1,"items":[{"bookId":26,"quantity":1},{"bookId":42,"quantity":2}]},
  {"id":502,"customerId":3,"items":[{"bookId":60,"quantity":1}]}
]';
SELECT o.id AS order_id, o.customer_id, i.book_id, i.quantity
FROM OPENJSON(@orders) WITH (
         id          INT           '$.id',
         customer_id INT           '$.customerId',
         items       NVARCHAR(MAX) '$.items' AS JSON
     ) AS o
CROSS APPLY OPENJSON(o.items) WITH (book_id INT '$.bookId', quantity INT '$.quantity') AS i
ORDER BY o.id, i.book_id;

order_id  customer_id  book_id  quantity
--------  -----------  -------  --------
501       1            26       1
501       1            42       2
502       3            60       1

AS JSON can only be used on an NVARCHAR(MAX) column. Keys in JSON are case-sensitive: had you written the path as '$.customerid', you would get no error, just a NULL column. As in post 9, CROSS APPLY ties each order's items to its own row; if you also want to see orders without items, use OUTER APPLY.

JSON_VALUE, JSON_QUERY, ISJSON and Path Modes

There are two functions for reading a single value, and they differ in what kind of value they return:

DECLARE @order NVARCHAR(MAX) = N'{"id":370,"status":"Shipped","customer":{"name":"Annie Easley"},"items":[{"bookId":46,"quantity":1},{"bookId":25,"quantity":2}],"gift":true,"note":null}';
SELECT JSON_VALUE(@order, '$.customer.name') AS customer_name, JSON_VALUE(@order, '$.items[1].quantity') AS second_qty,
       JSON_VALUE(@order, '$.items') AS items_as_value, JSON_QUERY(@order, '$.items') AS items_as_query,
       JSON_VALUE(@order, '$.missing') AS missing, ISJSON(@order) AS is_json;

customer_name  second_qty  items_as_value  items_as_query                                           missing  is_json
-------------  ----------  --------------  -------------------------------------------------------  -------  -------
Annie Easley   2           NULL            [{"bookId":46,"quantity":1},{"bookId":25,"quantity":2}]  NULL     1

JSON_VALUE returns only scalars (string, number, boolean); for a path that points to an array or an object it returns NULL. JSON_QUERY is the opposite: it returns objects and arrays, and NULL for a scalar. Array indexes start at zero; $.items[1] is the second item. A missing key is also NULL in lax mode. So in lax mode NULL can mean four different things: the value really is null, the key is missing, the path points to something of the wrong kind, or the value exceeds 4000 characters. When you want one of these to be an error, use strict:

DECLARE @order NVARCHAR(MAX) = N'{"id":370,"status":"Shipped"}';
SELECT JSON_VALUE(@order, 'strict $.missing') AS missing_strict;

Msg 13608, Level 16, State 2, Line 2
Property cannot be found on the specified JSON path.

ISJSON returns 1/0 depending on whether the text is valid JSON; since SQL Server 2022 a second argument also lets you ask for the expected type (ISJSON(@x, OBJECT), ARRAY, SCALAR). If you store JSON in nvarchar, a CHECK (ISJSON(payload) = 1) constraint stops broken data at the door; with the native json type the type itself does that check (post 18).

Because the result of JSON_VALUE is always text, comparing it with a number brings implicit conversion into play. SQL Server 2025 added RETURNING for this, but only when the input is of the native json type:

DECLARE @text NVARCHAR(MAX) = N'{"price":390.50}';
SELECT JSON_VALUE(@text, '$.price' RETURNING DECIMAL(10,2)) AS price;

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'RETURNING'.

DECLARE @doc JSON = N'{"price":390.50}';
SELECT JSON_VALUE(@doc, '$.price' RETURNING DECIMAL(10,2)) AS price, JSON_VALUE(@doc, '$.price') AS price_text,
       SQL_VARIANT_PROPERTY(JSON_VALUE(@doc, '$.price'), 'BaseType') AS text_type;

price   price_text  text_type
------  ----------  ---------
390.50  390.50      nvarchar

On an nvarchar variable RETURNING is a syntax error; the same text in a variable of type JSON came back as DECIMAL(10,2). Without RETURNING, the call still returns nvarchar even for json input.

JSON_MODIFY: Changing a Document in Place

JSON_MODIFY updates, adds or deletes a single path in a JSON text and returns the new text. On a cart:

DECLARE @cart NVARCHAR(MAX) = N'{"customerId":1,"items":[{"bookId":26,"quantity":1}]}';
SET @cart = JSON_MODIFY(@cart, '$.items[0].quantity', 2);
SET @cart = JSON_MODIFY(@cart, 'append $.items', JSON_QUERY(N'{"bookId":42,"quantity":1}'));
SET @cart = JSON_MODIFY(@cart, '$.coupon', 'SUMMER10');
SELECT @cart AS cart;
SET @cart = JSON_MODIFY(@cart, '$.coupon', NULL);
SELECT @cart AS without_coupon;
SELECT JSON_MODIFY(N'{"items":[]}', 'append $.items', N'{"bookId":42,"quantity":1}') AS escaped;

cart
----------------------------------------------------------------------------------------------------
{"customerId":1,"items":[{"bookId":26,"quantity":2},{"bookId":42,"quantity":1}],"coupon":"SUMMER10"}

(1 rows affected)
without_coupon
--------------------------------------------------------------------------------
{"customerId":1,"items":[{"bookId":26,"quantity":2},{"bookId":42,"quantity":1}]}

(1 rows affected)
escaped
--------------------------------------------
{"items":["{\"bookId\":42,\"quantity\":1}"]}

Setting a value on a key that does not exist ($.coupon) adds the key in lax mode; setting it to NULL deletes the key. append adds an element to the end of an array. The last line shows the most common mistake: when the new value is a plain string, JSON_MODIFY escapes it as a JSON string, and the array receives not an object but a piece of text with escaped quotes. When you add an object or an array, wrap the value in JSON_QUERY; the result of JSON_QUERY is marked as "this is already JSON". If you try to update a missing key with a strict path you get Msg 13608, which is a way to enforce "change only what exists" in code.

Writing in a Single Round Trip with a JSON Parameter

In post 19 we said that sending a list of ids from the application as a JSON parameter is more robust than a comma-separated string. The reason: with a comma-separated string you deal with the separator, spaces and types yourself; in a JSON array the boundaries are clear and the WITH schema checks the type:

DECLARE @ids NVARCHAR(MAX) = N'[26, 42, 60]';
SELECT b.book_id, b.title, b.list_price
FROM dbo.books AS b
WHERE b.book_id IN (SELECT id FROM OPENJSON(@ids) WITH (id INT '$'))
ORDER BY b.book_id;

book_id  title            list_price
-------  ---------------  ----------
26       Dune             390.50
42       Good Omens       340.90
60       Design Patterns  1450.00

The real gain is on the write side. The checkout in the Angular bookstore-web sends the cart as JSON; let's write the order and its items in one request and one transaction. @cart is a variable here; in the application it is a parameter coming from Dapper or ADO.NET:

DECLARE @cart JSON = N'{"customerId":1,"items":[{"bookId":26,"quantity":2,"price":1.00},{"bookId":42,"quantity":1},{"bookId":60,"quantity":1}]}';
SET XACT_ABORT ON;
BEGIN TRAN;
DECLARE @new_order TABLE (order_id INT);
INSERT INTO dbo.orders (customer_id, ordered_at, status)
OUTPUT inserted.order_id INTO @new_order (order_id)
VALUES (JSON_VALUE(@cart, '$.customerId' RETURNING INT), SYSUTCDATETIME(), 'Pending');
INSERT INTO dbo.order_items (order_id, book_id, quantity, unit_price)
SELECT n.order_id, i.book_id, i.quantity, b.list_price
FROM OPENJSON(@cart, '$.items') WITH (book_id INT '$.bookId', quantity INT '$.quantity') AS i
JOIN dbo.books AS b ON b.book_id = i.book_id AND b.is_active = 1
CROSS JOIN @new_order AS n;
IF @@ROWCOUNT <> (SELECT COUNT(*) FROM OPENJSON(@cart, '$.items'))
    THROW 50002, 'Cart contains an unknown or inactive book.', 1;
SELECT oi.order_id, oi.book_id, b.title, oi.quantity, oi.unit_price
FROM dbo.order_items AS oi
JOIN dbo.books AS b ON b.book_id = oi.book_id
WHERE oi.order_id = (SELECT order_id FROM @new_order)
ORDER BY oi.book_id;
ROLLBACK;

order_id  book_id  title            quantity  unit_price
--------  -------  ---------------  --------  ----------
380       26       Dune             2         390.50
380       42       Good Omens       1         340.90
380       60       Design Patterns  1         1450.00

A few decisions are built in. The price comes from books, not from the JSON: the "price":1.00 sent by the client was ignored, because leaving the price to the browser means letting someone buy Dune for 1 TRY. The order id is taken with OUTPUT ... INTO as in post 15. The JOIN silently drops an unknown book or one that is no longer on sale; to catch that we compare the number of inserted rows with the number of items in the JSON (reading @@ROWCOUNT right after the statement, as in post 22). When I replaced the second item with book 44, which is not on sale, the script stopped with "Msg 50002, Level 16, State 1, Line 14 Cart contains an unknown or inactive book." and XACT_ABORT rolled back the order too; @@TRANCOUNT was 0. The ROLLBACK at the end of the example only keeps the database clean for the next examples. We look at THROW and transactional scripts in detail in the next post.

You can do the same job with a TVP; we covered TVPs in the Dapper series post on SQL Server-specific parameters. A TVP gives you type safety and better statistics at large volumes, but it needs a table type defined in the database and a DataTable or similar structure in the application. A JSON parameter needs no extra objects, carries nested data (order plus items) in one parameter, and the same script is easy to run by hand in SSMS. Up to a few thousand rows, both do the job.

Indexing JSON: Computed Columns and JSON Indexes

Filtering on a value inside JSON without an index means parsing the JSON in every row. Let's write 20,000 events into a table that holds Bookstore's product page events; only 20 of them are stock_low:

DROP TABLE IF EXISTS dbo.book_events;
CREATE TABLE dbo.book_events (
    event_id INT IDENTITY(1,1) CONSTRAINT PK_book_events PRIMARY KEY,
    payload  NVARCHAR(MAX) NOT NULL CONSTRAINT CK_book_events_payload CHECK (ISJSON(payload) = 1)
);
INSERT INTO dbo.book_events (payload)
SELECT CONCAT(N'{"type":"', CASE WHEN g.value % 1000 = 0 THEN 'stock_low' ELSE 'viewed' END,
              N'","bookId":', 1 + g.value % 77, N',"at":"2026-06-', FORMAT(1 + g.value % 30, '00'), N'"}')
FROM GENERATE_SERIES(1, 20000) AS g;
SELECT JSON_VALUE(payload, '$.type') AS event_type, COUNT(*) AS events FROM dbo.book_events GROUP BY JSON_VALUE(payload, '$.type');

(20000 rows affected)
event_type  events
----------  ------
viewed      19980
stock_low   20

SET STATISTICS IO ON;
SELECT COUNT(*) AS low_stock_events FROM dbo.book_events WHERE JSON_VALUE(payload, '$.type') = 'stock_low';
SET STATISTICS IO OFF;

low_stock_events
----------------
20

(1 rows affected)
Table 'book_events'. Scan count 1, logical reads 276, ...

The whole table was read for 20 rows: 276 pages. The classic fix is to put the searched expression into a computed column and index it:

ALTER TABLE dbo.book_events ADD event_type AS JSON_VALUE(payload, '$.type');
CREATE INDEX IX_book_events_event_type ON dbo.book_events (event_type);

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IX_book_events_event_type' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.

SET STATISTICS IO ON;
SELECT COUNT(*) AS low_stock_events FROM dbo.book_events WHERE JSON_VALUE(payload, '$.type') = 'stock_low';
SET STATISTICS IO OFF;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT COUNT(*) AS low_stock_events FROM dbo.book_events WHERE JSON_VALUE(payload, '$.type') = 'stock_low';
GO
SET SHOWPLAN_TEXT OFF;

low_stock_events
----------------
20

(1 rows affected)
Table 'book_events'. Scan count 1, logical reads 3, ...
  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1004],0)))
       |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
            |--Index Seek(OBJECT:([sqlseries_ext_23].[dbo].[book_events].[IX_book_events_event_type]), SEEK:([sqlseries_ext_23].[dbo].[book_events].[event_type]=N'stock_low') ORDERED FORWARD)

We did not change the query; the optimizer saw that the JSON_VALUE(payload, '$.type') expression in the WHERE is the same as the computed column's expression and used the index: 3 logical reads instead of 276. The computed column is not persisted and takes no space in the table; its value lives only in the index. The warning comes from JSON_VALUE returning nvarchar(4000): if a type longer than 1700 bytes arrives, the insert fails. Microsoft Learn recommends casting the column to a narrow type; but then the query has to write the same CAST. When I defined the column as CAST(JSON_VALUE(payload, '$.type') AS VARCHAR(30)) the warning went away, but the query with the bare JSON_VALUE could not find the index and read 276 pages again; WHERE event_type = 'stock_low' or the same expression with the CAST went down to 3 pages. The rule: the expression you index and the expression you write in the query must be identical.

In SQL Server 2025 the second option is a native json column with CREATE JSON INDEX. According to Microsoft Learn both are generally available (GA) in 2025; a JSON index requires a clustered primary key on the table, and one index covers several paths in the document. Let's copy the same events into a table of type json and index it:

DROP TABLE IF EXISTS dbo.book_events_json;
CREATE TABLE dbo.book_events_json (
    event_id INT NOT NULL CONSTRAINT PK_book_events_json PRIMARY KEY,
    payload  JSON NOT NULL
);
INSERT INTO dbo.book_events_json (event_id, payload) SELECT event_id, payload FROM dbo.book_events;
CREATE JSON INDEX JX_book_events_json_payload ON dbo.book_events_json (payload) FOR ('$.type');
GO
SET STATISTICS IO ON;
SELECT COUNT(*) AS by_json_value FROM dbo.book_events_json WHERE JSON_VALUE(payload, '$.type') = 'stock_low';
SELECT COUNT(*) AS by_json_contains FROM dbo.book_events_json WHERE JSON_CONTAINS(payload, 'stock_low', '$.type') = 1;
SET STATISTICS IO OFF;

by_json_value
-------------
20

(1 rows affected)
Table 'book_events_json'. Scan count 1, logical reads 310, ...
by_json_contains
----------------
20

(1 rows affected)
Table 'book_events_json'. Scan count 0, logical reads 40, ...

The query with JSON_CONTAINS used the JSON index: 40 logical reads on the table and 4 on the index's own internal table. When the same condition was written as JSON_VALUE ... =, the optimizer did not choose the index and scanned the whole table (310 pages); Microsoft Learn says this form is supported too, but on my Cumulative Update 10 build the index was not used for this table. So if you move to a JSON index, write your queries with JSON_CONTAINS/JSON_PATH_EXISTS and always check the plan yourself. If you filter often on a single property, a computed column index is the predictable path that has worked since 2016. On the Entity Framework Core side, JSON columns are covered in the EF Core series posts on the new features of EF Core 7 and EF Core 10; at compatibility level 170, EF Core 10 moves these columns to the json type.

Function Summary and PostgreSQL Counterparts

I ran the expressions in the PostgreSQL column in my own PostgreSQL 18 container. PostgreSQL has two JSON types: json, which keeps the text as is, and jsonb, which stores it in binary form; jsonb is the one that can be indexed and that you use day to day.

When I built a GIN index on a jsonb column in PostgreSQL and wrote payload @> '{"type":"stock_low"}', the plan used a Bitmap Index Scan on the same 20,000 rows. Reading the 5000-character value with JSON_VALUE caused no problem there; the 4000 limit is specific to T-SQL.

In Production

Use JSON at the edges: producing the document returned to the API, receiving bulk data from the application, and storing data whose shape really varies (event payloads, integration responses, attributes that change by product type). Data that you query, join on or want constraints on belongs in columns. If you put an order's customer or amount into JSON, you lose the foreign key, the CHECK and the statistics; the Solution Patterns series post "Multilingual Data: Storing Names in Turkish and English — Translation Tables, JSON Columns and Fallback" weighs when a JSON column is the right choice through an example.

When you return a FOR JSON result to the application, watch out for chunking: take the result as a single value through a variable or in the form SELECT (... FOR JSON PATH) AS x. For JSON coming from the application, check types and counts with the WITH schema and a row count comparison, and base business rules (price, stock, active flag) on the data in the database, not on the JSON. If you keep JSON in nvarchar, add an ISJSON constraint; if you have moved to 2025, use the json type for new tables.

Common Pitfalls

• JSON_VALUE quietly returning NULL in lax mode for values longer than 4000 characters; use OPENJSON ... WITH (x NVARCHAR(MAX)) for long values.
• Trying to read an array or object with JSON_VALUE and getting NULL; that is the job of JSON_QUERY.
• Passing an object to JSON_MODIFY as a plain string and adding escaped text; wrap the value in JSON_QUERY.
• Reading a long FOR JSON result with ExecuteScalar and getting only the first 2033 characters.
• Defining the computed column with a CAST and writing a bare JSON_VALUE in the query; if the expressions differ, the index is not used.
• Getting the case of a key in a JSON path wrong; you get NULL, not an error.
• Putting data that should be relational (customer, amount, status) into a JSON column and losing foreign keys and constraints.

A Small Experiment

★☆☆ Produce all orders of one customer, each with its items, as a single document with FOR JSON PATH and ROOT('orders'). Do Grace Hopper's (customer 3) 33 orders fit in one row? Put the result into a variable and measure it with LEN.

★★☆ Change the cart script so that when the same book appears twice in the JSON, the quantities are added up (the UQ_order_items_order_book constraint on order_items does not allow two rows for the same book). See which error you get when an item with a quantity of 0 or a negative quantity arrives.

★★★ Add a computed column index for bookId on book_events as well and look at the plan of the query "stock_low events of book 18". Then write the same query on book_events_json with JSON_CONTAINS; you will need to recreate the JSON index for two paths.

With this post we have covered the JSON side of T-SQL. In the next post we move from single queries to scripts: with batches and GO, variables, IF/WHILE, TRY...CATCH and THROW, we will write a price update script that leaves no half-done work behind when something fails.