JSON data type

Applies to: SQL Server 2025 (17.x) Preview Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric Preview

The json data type stores JSON documents in a native binary format.

The json type provides a high-fidelity storage of JSON documents optimized for easy querying and manipulation, and provides the following benefits over storing JSON data as varchar or nvarchar:

• More efficient reads, as the document is already parsed
• More efficient writes, as the query can update individual values without accessing the entire document
• More efficient storage, optimized for compression
• No change in compatibility with existing code

The json type internally stores data using UTF-8 encoding, Latin1_General_100_BIN2_UTF8. This behavior matches the JSON specification.

For more information on querying JSON data, see JSON data in SQL Server.

Sample syntax

The usage syntax for the json type is similar to all other SQL Server data types in a table.

column_name JSON [ NOT NULL | NULL ] [CHECK ( constraint_expression ) ] [ DEFAULT ( default_expression ) ]

The json data type can be used in column definition contained in a CREATE TABLE statement. For example:

CREATE TABLE Orders
(
    order_id INT,
    order_details JSON NOT NULL
);

Constraints can be specified as part of the column definition. For example:

CREATE TABLE Orders
(
    order_id INT,
    order_details JSON NOT NULL
        CHECK (JSON_PATH_EXISTS(order_details, '$.basket') = 1)
);

Feature availability

JSON function support was first introduced in SQL Server 2016 (13.x). The native json type was introduced in Azure SQL Database and Azure SQL Managed Instance, and is also available in SQL Server 2025 (17.x) Preview.

The json data type is available under all database compatibility levels.

The modify method

The json data type supports the modify method. Use modify to modify JSON documents stored in a column. The modify method has optimizations to perform in-place modifications to the data where possible, and is the preferred way to modify a JSON document in a json column.

For JSON strings, if the new value is less than or equal to the existing value, then in-place modification is possible.

For JSON numbers, if the new value is of the same type, or within the range of the existing value, then in-place modification is possible.

DROP TABLE IF EXISTS JsonTable;

CREATE TABLE JsonTable
(
    id INT PRIMARY KEY,
    d JSON
);

INSERT INTO JsonTable (id, d)
VALUES (1, '{"a":1, "b":"abc", "c":true}');

UPDATE JsonTable
SET d.modify('$.a', 14859)
WHERE id = 1;

UPDATE JsonTable
SET d.modify('$.b', 'def')
WHERE id = 1;

Function support

All JSON functions support the json data type with no code changes or usage difference necessary.

• OPENJSON currently doesn't support the json data type on some platforms. For more information, see Limitations.

For a complete list of JSON functions, see JSON functions.

Indexes

There are no special index types for JSON data.

The json type can't be used as key column in a CREATE INDEX statement. However, a json column can be specified as an included column in an index definition. Additionally, a json column can appear in the WHERE clause of a filtered index.

Conversion

Explicit conversion using CAST or CONVERT from the json type can be done to char, nchar, varchar, and nvarchar types. All implicit conversions aren't allowed, similar to the behavior of xml. Similarly, only char, nchar, varchar, and nvarchar can be explicitly converted to the json data type.

The json data type can't be used with the sql_variant type or assigned to a sql_variant variable or column. This restriction similar to varchar(max), varbinary(max), nvarchar(max), xml, and CLR-based data types.

You can convert existing columns like varchar(max) to json using ALTER TABLE. Similar to the xml data type, you can't convert a json column to any of the string or binary types using ALTER TABLE.

For more information, see Data type conversion (Database Engine).

Compatibility

The json data type can be used as a parameter or return type in a user-defined function, or the parameter of a stored procedure. The json type is compatible with triggers and views.

Currently, the bcp tool's native format contains the json document as varchar or nvarchar. You must specify a format file to designate a json column.

Creation of alias type using CREATE TYPE for the json data type isn't allowed. This behavior is the same as the xml data type.

Using SELECT ... INTO with the json data type creates a table with the json type.

Limitations

The behavior of CAST ( ... AS JSON) returns a json data type, but the sp_describe_first_result_set system stored procedure doesn't correctly return the json data type. Therefore, many data access clients and driver see a varchar or nvarchar data type.

• Currently, TDS >= 7.4 (with UTF-8) sees varchar(max) with Latin_General_100_bin2_utf8.
• Currently, TDS < 7.4 sees nvarchar(max) with database collation.

Currently, the OPENJSON() function doesn't accept the json data type in some platforms. Currently, it's an implicit conversion. Explicitly convert to nvarchar(max) first.

• In SQL Server 2025 (17.x) Preview, the OPENJSON() function does support json. For more information, see Key JSON capabilities in SQL Server 2025.

Related content

• Store JSON documents
• JSON data in SQL Server