JSON data type (preview)
Applies to:
Azure SQL Database
The new native json data type that 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 in 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 Work with 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 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 support was first introduced in SQL Server 2016 (13.x), mostly in the form of JSON functions. The new native json type was introduced in 2024, first on Azure SQL platforms.
json is available under all database compatibility levels.
Note
Currently, the json type is available in preview on Azure SQL Database.
Function support
All JSON functions support the json type with no code changes or usage difference necessary.
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 type.
The json 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.
For more information, see Data type conversion.
Compatibility
The json 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 data type column.
Creation of alias type using CREATE TYPE
for the json type isn't allowed. This is same behavior as xml type.
Using SELECT ... INTO
with the JSON type will create a table with the JSON type.
Limitations
The behavior of
CAST ( ... AS JSON)
returns a json type, but the sp_describe_first_result_set system stored procedure doesn't correct return the json data type. Therefore, many data access clients and driver will see a varchar or nvarchar data type.- Currently, TDS >= 7.4 (with UTF-8) support will see varchar(max) with
Latin_General_100_bin2_utf8
. - Currently, TDS < 7.4 support will see nvarchar(max) with database collation.
- Currently, TDS >= 7.4 (with UTF-8) support will see varchar(max) with
Currently, the
OPENJSON()
function doesn't accept the json type, currently that is an implicit conversion. Explicitly convert to nvarchar(max) first.
Related content
प्रतिक्रिया
https://aka.ms/ContentUserFeedback.
जल्द आ रहा है: 2024 के दौरान हम सामग्री के लिए फीडबैक तंत्र के रूप में GitHub मुद्दों को चरणबद्ध तरीके से समाप्त कर देंगे और इसे एक नई फीडबैक प्रणाली से बदल देंगे. अधिक जानकारी के लिए, देखें:के लिए प्रतिक्रिया सबमिट करें और देखें