{"id":102912,"date":"2024-08-23T03:01:23","date_gmt":"2024-08-23T03:01:23","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=102912"},"modified":"2026-03-09T14:13:08","modified_gmt":"2026-03-09T14:13:08","slug":"effective-strategies-for-storing-and-parsing-json-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/effective-strategies-for-storing-and-parsing-json-in-sql-server\/","title":{"rendered":"Store & Parse JSON in SQL Server: Strategy Guide"},"content":{"rendered":"\n

SQL Server stores JSON data in VARCHAR or NVARCHAR columns (or, starting in 2024 with Azure SQL Database, in a native JSON data type). To work with JSON effectively: validate documents with ISJSON() before storage, read values with JSON_VALUE() and JSON_QUERY(), convert JSON arrays to table rows with OPENJSON(), and write relational data back to JSON with FOR JSON.<\/p>\n\n\n\n

For better query performance, create computed columns on frequently accessed JSON properties and index them. For storage efficiency, compress large JSON documents using COMPRESS()\/DECOMPRESS(). This guide covers the complete JSON storage lifecycle in SQL Server – from validation and storage architecture to indexing, compression, and the new native JSON data type.<\/p>\n\n\n\n

Read also:<\/strong>
XML storage and parsing strategies for SQL Server<\/a>
Storing and parsing JSON in SQL Server<\/a>
Generating HTML from SQL Server using FOR XML PATH<\/a>
Using the FOR XML clause for query output<\/a>
CHAR and VARCHAR data type considerations<\/a><\/p>\n\n\n\n

Introduction<\/h2>\n\n\n\n

Like XML, JSON is an open standard storage format for data, metadata, parameters, or other unstructured or semi-structured data. Because of its heavy usage in applications today, it inevitably will make its way into databases where it will need to be stored, compressed, modified, searched, and retrieved.<\/p>\n\n\n\n

Even though a relational database is not the ideal place to store and manage less structured data, application requirements can oftentimes override an \u201coptimal\u201d database design. There is a convenience in having JSON data close to related relational data and architecting its storage effectively from the start can save significant time and resources in the future.<\/p>\n\n\n\n

This article delves into how JSON is stored in SQL Server and the different ways in which it can be written, read, and maintained.<\/p>\n\n\n\n

JSON and Strings are Not the Same!<\/h2>\n\n\n\n

Data stored as JSON will likely enter and exit a database in the JSON format. While stored in SQL Server, JSON should be queried in its native format.<\/p>\n\n\n\n

For most data engineers and application developers, working with strings is fast, easy, and second nature. Functions such as SUBSTRING<\/code>, LEFT<\/code>, TRIM<\/code>, and CHARINDEX<\/code> are well-documented and standard across many platforms. For this reason, it is a common mistake when working with unstructured or semi-structured data to convert it to a VARCHAR<\/code>\/NVARCHAR<\/code> format and using string-manipulation to work with it.<\/p>\n\n\n\n

Because JSON is stored in SQL Server in VARCHAR<\/code>\/NVARCHAR<\/code> formats, it is even easier to use this strategy than when working with XML, which is stored in XML-typed columns.<\/p>\n\n\n\n

There are performance benefits and cost-savings when using the built-in JSON functionality. This article will focus on using SQL Server\u2019s JSON functionality and omit demonstrations that work around it.<\/p>\n\n\n\n

Lastly, be sure to only use JSON where it is needed by an application. Only store JSON data in SQL Server when required by an application and do not parse it unless component data from within it is required and cannot be obtained in an easier fashion.<\/p>\n\n\n\n

Starting in 2024, in Azure SQL Database, JSON can be stored natively using the newly-provided JSON data type<\/a>. This will be demonstrated later in this article. For now, in on-premises SQL Server, JSON is stored in VARCHAR<\/code>\/NVARCHAR<\/code> columns. In both scenarios, JSON functions may be used to validate, read, and write the underlying data as JSON.<\/p>\n\n\n\n

Note that native JSON support is likely to be added to SQL Server in the future, but as of the writing of this article, there is currently no timeline available for that feature. It is also very likely that the techniques provided here will still be useful, much like the XML functionality that operates on string data is<\/a>.<\/em><\/p>\n\n\n\n

A Quick Note on SQL Server Version<\/h2>\n\n\n\n

JSON support was introduced with SQL Server 2016. This equates to compatibility level 130. If any JSON functions are used in older versions of SQL Server, they will fail with an error that they are not found.<\/p>\n\n\n\n

Note that JSON functions are available, even if a database is in a compatibility level less than 130, but SQL Server itself must be on at least SQL Server 2016 for this to work. For example, if any of the demonstrations in this article are run on compatibility level 100, but on SQL Server 2022, they would work. While I cannot think of any reason to do this, I suspect someone out there can!<\/p>\n\n\n\n

Validating JSON Documents<\/h2>\n\n\n\n

The ideal use-case for JSON in the database is to be written and read in-place, without any complex I\/O requirements to be managed along the way. Whether its use is this simple or not, reading JSON from the database can be accomplished relatively easily.<\/p>\n\n\n\n

JSON can be stored and parsed from scalar values, such as parameters or locally declared variables, or it can be written and read to a column within a table. These first examples work with a simple variable that describes some (somewhat) hypothetical metadata about people:<\/p>\n\n\n\n

DECLARE @PersonInfo VARCHAR(MAX) =\n'{ \"PersonInfo\":\n\t{\n\t\t\"City\": \"Albany\",\n\t\t\"State\": \"New York\",\n\t\t\"SpiceLevel\": \"Extreme\",\n\t\t\"FavoriteSport\": \"Baseball\",\n\t\t\"Skills\": [\"SQL\", \"Baking\", \"Running\", \"Minecraft\"]\n\t}\n}';<\/pre>\n\n\n\n
This JSON document describes a single person using a list of attributes. Note the syntax used in creating the document as it is important to create JSON that is well-formed with valid syntax. Forgetting a detail such as a quotation mark or comma can render the JSON impossible to validate or read using SQL Server\u2019s built in functions.<\/p>\n\n\n\n
For this reason, if there is any question at all as to the validity of JSON that is to be read, validate it first. This may be done using the ISJSON()<\/code> function, like this:<\/p>\n\n\n\n
--code works in conjunction with previous code.\nSELECT ISJSON(@PersonInfo) AS IsValidJSON;<\/pre>\n\n\n\n
The result is either a zero if invalid, one if valid, and NULL<\/code> if the value of the input, or in the case, @PersonInfo<\/code> is NULL<\/code>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
This is a great way to avoid unexpected errors. If zero is returned, then code can be executed to handle that scenario in whatever way makes the most sense. For example, if the T-SQL should throw an error, then this would be a simple way to do so:<\/p>\n\n\n\n
--code works in conjunction with previous code.\nDECLARE @IsValidJSON BIT\nSELECT @IsValidJSON = ISJSON(@PersonInfo);\nIF @IsValidJSON = 0\nBEGIN\n\tDECLARE @msg nvarchar(200) = 'The JSON entered is not' + \n             ' valid. Please investigate and resolve' + \n             ' this problem!'--broken up to format for screen\n\tRAISERROR(@msg, 16, 1);\n\tRETURN\nEND<\/pre>\n\n\n\n
If the JSON had not been well-formed, then the error would have been thrown immediately and RETURN<\/code> would ensure that the calling process would end immediately. Alternatively, the error could be handled less viciously with a log entry, quiet skipping of invalid data, or some other method that doesn\u2019t end processing immediately.<\/p>\n\n\n\n
JSON can be written as a set of values, as well, like this:<\/p>\n\n\n\n
DECLARE @PersonInfo VARCHAR(MAX) =\n'{ \"PersonInfo\":\n\t{\n\t\t\"FirstName\": \"Edward\",\n\t\t\"LastName\": \"Pollack\",\n\t\t\"City\": \"Albany\",\n\t\t\"State\": \"New York\",\n\t\t\"SpiceLevel\": \"Extreme\",\n\t\t\"FavoriteSport\": \"Baseball\",\n\t\t\"Skills\": [\"SQL\", \"Baking\", \"Running\", \"Minecraft\"]\n\t},\n\t{\n\t\t\"FirstName\": \"Edgar\",\n\t\t\"LastName\": \"Codd\",\n\t\t\"City\": \"Fortuneswell\",\n\t\t\"State\": \"Dorset\",\n\t\t\"SpiceLevel\": \"Medium\",\n\t\t\"FavoriteSport\": \"Flying\",\n\t\t\"Skills\": [\"SQL\", \"Computers\", \"Flying\", \n                             \"Normalizing Data Models\"]\n\t}\n}';<\/pre>\n\n\n\n
The following is a small table that includes a JSON-containing string column:<\/p>\n\n\n\n
CREATE TABLE dbo.PersonInfo\n(    PersonId INT NOT NULL IDENTITY(1,1)\n\t\tCONSTRAINT PK_PersonInfo PRIMARY KEY CLUSTERED,\n     FirstName VARCHAR(100) NOT NULL,\n     LastName VARCHAR(100) NOT NULL,\n     PersonMetadata VARCHAR(MAX) NOT NULL\n);<\/pre>\n\n\n\n
In addition to the two examples already provided of person data, a few additional rows will be inserted into the table:<\/p>\n\n\n\n
INSERT INTO dbo.PersonInfo\n\t(FirstName, LastName, PersonMetadata)\nVALUES\n('Thomas', 'Edison',\n'{ \"PersonInfo\":\n\t{\n\t\t\"City\": \"Milan\",\n\t\t\"State\": \"Ohio\",\n\t\t\"SpiceLevel\": \"Mild\",\n\t\t\"FavoriteSport\": \"Reading\",\n\t\t\"Skills\": [\"Technology\", \"Business\", \n                     \"Communication\"]\n\t}\n}')\n,\n('Nikola', 'Tesla',\n'{ \"PersonInfo\":\n\t{\n\t\t\"City\": \"Smiljan\",\n\t\t\"State\": \"Croatia\",\n\t\t\"SpiceLevel\": \"Hot\",\n\t\t\"FavoriteSport\": \"Inventing\",\n\t\t\"Skills\": [\"Lighting\", \"Electricity\", \n                     \"X-Rays\", \"Motors\"]\n\t}\n}')\n,\n('Edward', 'Pollack',\n'{ \"PersonInfo\":\n\t{\n\t\t\"City\": \"Albany\",\n\t\t\"State\": \"New York\",\n\t\t\"SpiceLevel\": \"Extreme\",\n\t\t\"FavoriteSport\": \"Baseball\",\n\t\t\"Skills\": [\"SQL\", \"Baking\", \"Running\", \"Minecraft\"]\n    }\n}'),\n('Edgar','Codd',\n'{ \"PersonInfo\":\n    {\n\t\t\"City\": \"Fortuneswell\",\n\t\t\"State\": \"Dorset\",\n\t\t\"SpiceLevel\": \"Medium\",\n\t\t\"FavoriteSport\": \"Flying\",\n\t\t\"Skills\": [\"SQL\", \"Computers\", \"Flying\", \n                     \"Normalizing Data Models\"]\n  }\n}')<\/pre>\n\n\n\n
The validity of each JSON document can be verified similarly to the scalar example from earlier:<\/p>\n\n\n\n
SELECT\n\t*\nFROM dbo.PersonInfo\nWHERE ISJSON(PersonInfo.PersonMetadata) = 1;<\/pre>\n\n\n\n
Four rows are returned, confirming that each JSON document is valid:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Reading JSON Documents<\/h2>\n\n\n\n
Data can be read from JSON documents by either brute-force string searching or by using native JSON functions. Generally speaking, using native JSON functions will result in more reliable results and better performance. This is especially significant if using the native JSON data type in Azure SQL Database as it automatically offers performance improvements for native operations that are forfeited if string-searching is used instead.<\/p>\n\n\n\n
Full-Text Indexing can be used on JSON columns that are typed as VARCHAR<\/code> or NVARCHAR<\/code>, though this is not advisable unless you already use Full-Text Indexing and\/or all other search solutions have been exhausted.<\/p>\n\n\n\n
It is important to repeat here: JSON documents are ideally stored as pass-through data for basic writes and reads. Since JSON columns cannot be natively indexed: searching, updating, and otherwise complex parsing will be potentially slow and challenging to code.<\/p>\n\n\n\n
All demonstrations here use native functions and do not show string manipulation as a way to parse JSON data.<\/p>\n\n\n\n
The JSON_VALUE<\/code> function can be used to return a value from a JSON document. It can also be used for filtering, grouping, etc\u2026The following example returns the values for City<\/code> and State<\/code> for each row in the table:<\/p>\n\n\n\n
SELECT\n\tPersonId,\n\tFirstName,\n\tLastName,\n\tJSON_VALUE(PersonMetadata, '$.PersonInfo.City') AS PersonCity,\n\tJSON_VALUE(PersonMetadata, '$.PersonInfo.State') AS PersonState\nFROM dbo.PersonInfo;<\/pre>\n\n\n\n
The results are what we expect, knowing the data in the table:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
If the path requested in JSON_VALUE <\/code>doesn\u2019t exist, NULL<\/code> will be returned. There will not be an error or indication of why NULL<\/code> is returned, though. Similarly, JSON_VALUE<\/code>, when evaluated against NULL<\/code> will simply return NULL<\/code>, with no further fanfare. The following code provides an example of this:<\/p>\n\n\n\n
SELECT\n\tPersonId,\n\tFirstName,\n\tLastName,\n\tJSON_VALUE(PersonMetadata, '$.PersonInfo.VideoGamePreference') \n                             AS PersonVideoGamePreference\nFROM dbo.PersonInfo;<\/pre>\n\n\n\n
The results show the expected NULL<\/code> for the JSON_VALUE<\/code> expression:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
If the inputs to a JSON expression like this may not be valid, ensure that they are tested (if needed) to catch bad data, since there will not be an error or other indication of a problem (other than all NULL<\/code> values returned).<\/p>\n\n\n\n
An expression like JSON_VALUE<\/code> can be placed in the WHERE<\/code> clause for a query. Keep in mind that this will result in an index\/table scan on a relatively large wide column value to be fully evaluated. Therefore, be sure to include additional filters if the table is particularly large, or if queries get slow.<\/p>\n\n\n\n
The following example shows a query that returns all rows for people with a City<\/code> defined as Albany:<\/p>\n\n\n\n
SELECT\n      *\nFROM dbo.PersonInfo\nWHERE ISJSON(PersonMetadata) = 1\nAND JSON_VALUE(PersonMetadata, '$.PersonInfo.City') = 'Albany';<\/pre>\n\n\n\n
The results show a single row returned that matches the filter:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
If there is a need to test whether a path exists within a JSON document, the function JSON_PATH_EXISTS<\/code> may be used. It will return a 1 if the path exists, 0 if it does not, or NULL<\/code> if the input it NULL<\/code>. Like JSON_VALUE<\/code>, this function does not return errors, regardless of whether the path exists or not. The following query can provide a test as to whether a person has a path exists for VideoGamePreference<\/code>:<\/p>\n\n\n\n
SELECT\n    PersonId,\n    FirstName,\n    LastName,\n    JSON_PATH_EXISTS(PersonMetadata,'$.PersonInfo.VideoGamePreference') \n                                            AS PersonVideoGamePreference\nFROM dbo.PersonInfo;<\/pre>\n\n\n\n
The results show that the path doesn\u2019t exist for any of our examples:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Remove the .VideoGamePreference<\/code> from the string and you will see that $.PersonInfo <\/code>does exist. This can be a good way to understand how the paths work in JSON documents.<\/p>\n\n\n\n
This can also be used in the WHERE<\/code> clause to ensure that only rows are returned that include a given path, or that values are only returned when a specified path exists.<\/p>\n\n\n\n
Updating JSON Documents<\/h2>\n\n\n\n
While JSON documents can be updated en masse, that can be an expensive process if the documents are large, or if there are downstream calculations made based on documents. Being able to tactically make changes when and where needed can reduce IO and be a faster and more efficient way to update, add, or remove paths. Similarly, they may also be a need to modify documents after being read.<\/p>\n\n\n\n
The simplest way to make changes to a JSON document is using the JSON_MODIFY<\/code> function. This allows properties to be added, removed, or updated with relative ease. For example, the following query updates the City<\/code> and State<\/code> columns for any matching a specific City\/State filter:<\/p>\n\n\n\n
SELECT\n\tPersonId,\n\tFirstName,\n\tLastName,\n\tJSON_MODIFY(JSON_MODIFY(PersonMetadata, \n              '$.PersonInfo.City', 'London'), \n              '$.PersonInfo.State', 'London') \n                                AS UpdatedDocument\nFROM dbo.PersonInfo\nWHERE JSON_VALUE(PersonMetadata, '$.PersonInfo.City') = 'Fortuneswell'\nAND JSON_VALUE(PersonMetadata, '$.PersonInfo.State') = 'Dorset';<\/pre>\n\n\n\n
The result is an updated document with London replacing Fortuneswell:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Note that this was not an UDPATE<\/code> statement and that JSON_MODIFY<\/code> does not write to the underlying table. It modifies the string value to include the new data you are asking it to. To permanently make this change to the table, the SELECT<\/code> needs to be adjusted to an UPDATE<\/code>, like this:<\/p>\n\n\n\n
UPDATE PersonInfo\n\tSET PersonMetadata = \n\tJSON_MODIFY(JSON_MODIFY(PersonMetadata, \n                        '$.PersonInfo.City', 'London'), \n                        '$.PersonInfo.State', 'London')\nFROM dbo.PersonInfo\nWHERE JSON_VALUE(PersonMetadata, '$.PersonInfo.City') = 'Fortuneswell'\n  AND JSON_VALUE(PersonMetadata, '$.PersonInfo.State') =  'Dorset';<\/pre>\n\n\n\n
This query can be used to validate the result:<\/p>\n\n\n\n
SELECT\n\t*\nFROM dbo.PersonInfo\nWHERE JSON_VALUE(PersonMetadata, '$.PersonInfo.City') = 'London';<\/pre>\n\n\n\n
The result shows that the change has now been made permanent:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Updating JSON List Elements<\/h3>\n\n\n\n
A list may be updated with new elements by using the append<\/code> option for JSON_MODIFY<\/code>. This example adds the skill \u201cMustaches\u201d to the list for a specific person:<\/p>\n\n\n\n
SELECT\n\tPersonId,\n\tFirstName,\n\tLastName,\n\tJSON_MODIFY(PersonMetadata, \n                'append $.PersonInfo.Skills', 'Mustaches')\nFROM dbo.PersonInfo\nWHERE FirstName = 'Nikola'\nAND LastName = 'Tesla';<\/pre>\n\n\n\n
The results confirm that the list has been returned with the added element:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Like before, the underlying document has not been updated, but could be if the SELECT<\/code> is adjusted into an UPDATE<\/code>, like this:<\/p>\n\n\n\n
UPDATE PersonInfo\n\tSET PersonMetadata = \n           JSON_MODIFY(PersonMetadata, 'append $.PersonInfo.Skills', \n                                                           'Mustaches')\nFROM dbo.PersonInfo\nWHERE FirstName = 'Nikola'\nAND LastName = 'Tesla';<\/pre>\n\n\n\n
Querying JSON List Elements<\/h3>\n\n\n\n
We can query to confirm the existence of the new skill in the list by using OPENJSON<\/code>:<\/p>\n\n\n\n
SELECT\n\t*\nFROM dbo.PersonInfo\nCROSS APPLY OPENJSON(PersonMetadata, '$.PersonInfo.Skills')\nWHERE [value] = 'Mustaches';<\/pre>\n\n\n\n
This query creates a full list of people with one row per person and skill. Removing the WHERE<\/code> clause shows the full output of the CROSS APPLY,<\/code> which in effect normalizes the data in the skills column to another table, just like if you were joining to a PersonInfoSkills<\/code> table:<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The filter checks the [value] column returned by OPENJSON<\/code> and returns only the rows that are filtered for.<\/p>\n\n\n\n
The following syntax can also be used to search for one or many list elements:<\/p>\n\n\n\n
SELECT\n     *\nFROM dbo.PersonInfo\nWHERE 'Mustaches' IN \n         (SELECT [value] \n          FROM OPENJSON(PersonMetadata, '$.PersonInfo.Skills'));<\/pre>\n\n\n\n
Whether mustaches are a skill is up for grabs, but there are at least easy ways to search for them within a JSON list!<\/p>\n\n\n\n
Read also:<\/strong> Consuming and querying JSON strings in SQL Server<\/a><\/p>\n\n\n\n
Removing a JSON Property<\/h3>\n\n\n\n
A property may be deleted by setting it to NULL<\/code>. The following example shows the State<\/code> attribute being removed from the document:<\/p>\n\n\n\n
SELECT\n\tPersonId,\n\tFirstName,\n\tLastName,\n\tJSON_MODIFY(PersonMetadata, '$.PersonInfo.State', NULL)\nFROM dbo.PersonInfo;<\/pre>\n\n\n\n
The results show that State<\/code> no longer exists for any rows in the table:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
A property can be renamed, if needed. This can be helpful when system or product names change, or simply to correct a mistake within a document. This cannot be accomplished in a single step, though, and is done via a deletion and an insertion, like this:<\/p>\n\n\n\n
SELECT\n\tPersonId,\n\tFirstName,\n\tLastName,\n\tJSON_MODIFY(JSON_MODIFY(PersonMetadata, \n                     '$.PersonInfo.State', NULL), \n                      '$.PersonInfo.Region', \n               JSON_VALUE(PersonMetadata, \n                      '$.PersonInfo.State'))\nFROM dbo.PersonInfo;<\/pre>\n\n\n\n
Note that to maintain the existing value, JSON_VALUE<\/code> is used to set the value for the new attribute equal to its previous value. The result shows that \u201cState\u201d<\/code> has been renamed to \u201cRegion\u201d<\/code>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The new Region<\/code> attribute is appended to the end of the document and retains its original value prior to the rename.<\/p>\n\n\n\n
The final example shows how updating JSON can become complex and difficult to read in T-SQL. If the application that manages this data can perform updates as it would update a value to any other column, the result would be simpler code and likely code that is more efficient. Since JSON is not relational data, even simple operations such as an attribute rename can become convoluted.<\/p>\n\n\n\n
The longer and more complex a query is, the more likely it is for a mistake to be made when writing or modifying it. Consider this when deciding how documents will be updated, and what application will be used to do so.<\/p>\n\n\n\n
Indexing JSON Documents<\/h2>\n\n\n\n
While no native indexing exists (yet) for JSON columns, hope is not lost! If a JSON document is often searched based on a specific attribute, then a computed column may be added that evaluates that attribute. Once a computed column exists, it may be indexed, just like any other computed column.<\/p>\n\n\n\n
Consider the example earlier of a JSON document that contains a variety of attributes. Let\u2019s say that a very common search occurs against the City<\/code> attribute. To begin to solve this problem, a computed column will be created on the PersonInfo<\/code> table that isolates City<\/code><\/p>\n\n\n\n
ALTER TABLE dbo.PersonInfo \n ADD City AS JSON_VALUE(PersonMetadata, '$.PersonInfo.City');<\/pre>\n\n\n\n
The resulting column shows that it is set equal to the value of the City<\/code> attribute.<\/p>\n\n\n\n
SELECT *\nFROM  dbo.PersonInfo;<\/pre>\n\n\n\n
Now there is a new City<\/code> column added.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
While the new column is convenient, it provides no performance help as it is just an embedded query against the JSON document. Indexing it provides the ability to search it:<\/p>\n\n\n\n
CREATE NONCLUSTERED INDEX IX_PersonInfo_CityJSON\nON dbo.PersonInfo (City ASC);<\/pre>\n\n\n\n
This executes successfully, but does return a warning message:<\/p>\n\n\n\n
Warning! The maximum key length for a nonclustered index<\/code>is 1700 bytes. The index 'IX_PersonInfo_CityJSON' has<\/code>maximum length of 8000 bytes. For some combination of<\/code> large values, the insert\/update operation will fail.<\/code><\/p>\n\n\n\n
Viewing the column\u2019s definition shows that it indeed inherited a biggie-sized column length:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
That column size is unnecessarily massive and can both impact performance, as well as confuse developers who are guaranteed to scratch their heads at a City that can be 4000 multi-byte characters long. To address this, we will remove the index and column and recreate it with a minor adjustment:<\/p>\n\n\n\n
DROP INDEX IX_PersonInfo_CityJSON ON dbo.PersonInfo;\nALTER TABLE dbo.PersonInfo DROP COLUMN City;\nALTER TABLE dbo.PersonInfo \nADD City AS\nCAST(JSON_VALUE(PersonMetadata, '$.PersonInfo.City') \n                                     AS VARCHAR(100));<\/pre>\n\n\n\n
The new column includes a CAST<\/code> that forces the column length to be VARCHAR(100)<\/code>. Assuming that City<\/code> does not require double-byte characters and will always be under 100 characters, then this is a beneficial change. The new column size can be quickly validated, like before:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
With that problem out of the way, an index can be placed on the column, this time without a warning message:<\/p>\n\n\n\n
CREATE NONCLUSTERED INDEX IX_PersonInfo_CityJSON\nON dbo.PersonInfo (City ASC);<\/pre>\n\n\n\n
The final test of this indexed computed column is to select a row using a simple JSON search and then the indexed City<\/em> column:<\/p>\n\n\n\n
SELECT\n\tCOUNT(*)\nFROM dbo.PersonInfo\nWHERE JSON_VALUE(PersonMetadata, '$.PersonInfo.City') = 'Albany';\nSELECT\n\tCOUNT(*)\nFROM dbo.PersonInfo\nWHERE City = 'Albany';<\/pre>\n\n\n\n
Note the difference in execution plans:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The search against the unindexed JSON column requires a table scan as the function must be evaluated against every JSON doc in the table prior to returning results. Alternatively, the search against the City<\/em> column can use the newly created index and a seek to get its result.<\/p>\n\n\n\n
An alternative to this strategy would be for the application that uses this table to persist the City<\/code> column itself. Instead of a computed column, the City<\/code> could be maintained completely by the application, ensuring the correct value is updated whenever the JSON document is created, updated, or deleted. This would be less convoluted but would require that the application be solely responsible for always maintaining the column. Depending on the app, this may or may not be a simple request.<\/p>\n\n\n\n
Consider the cost of maintaining a persisted computed column when implementing a performance change like this. The cost for improving search performance for a column is that write operations against the JSON document will be slightly slower as each update will need to re-evaluate the definition for the computed column and update the index. Adding many columns like this can quickly become expensive and cause write operations to both perform slower and be more likely to introduce contention. Carefully weigh the importance and frequency of a search query against the IO needed to maintain a persisted column with its value.<\/p>\n\n\n\n
Compressing JSON<\/h2>\n\n\n\n
There is no unique way to compress a JSON column, regardless of whether it is stored in VARCHAR<\/code>\/NVARCHAR<\/code> format, or in native JSON format.<\/p>\n\n\n\n
The option available for compressing JSON documents is to use the COMPRESS<\/code> and DECOMPRESS<\/code> functions, which use the Gzip compression algorithm to decrease the size of text columns that are stored as off-row data. These functions are not unique to JSON and are also used to compress large text or binary data that row\/page compression will not handle by default.<\/p>\n\n\n\n
It is important to note that compression has ramifications that go beyond simply reducing the table\u2019s size and therefore the size of the data file for its database. Compressed data remains compressed in memory and is only decompressed at runtime, when needed by SQL Server. If you are running SQL Server in Web Edition, or if backup compression is unavailable, then compressing data translates to compressing data within backups. This results in smaller backup files. Therefore, data compression can save storage space in multiple locations, as well as memory.<\/p>\n\n\n\n
Schema changes are required to use COMPRESS<\/code> and DECOMPRESS<\/code> as the output of compression is a VARBINARY<\/code> data type, and not a VARCHAR<\/code>\/NVARCHAR<\/code> data type. This can be observed by selecting JSON data and compressing it prior to displaying it:<\/p>\n\n\n\n
SELECT\n\tPersonId,\n\tFirstName,\n\tLastName,\n\tCOMPRESS(PersonMetadata) AS PersonMetadataCompressed\nFROM dbo.PersonInfo<\/pre>\n\n\n\n
The result of this query is a VARBINARY<\/code> string that is visually meaningless:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
To use compression via this method, there are some basic steps to follow:<\/p>\n\n\n
\n
    \n
  1. Ensure the table has a VARBINARY(MAX)<\/code> column available to store the compressed data.<\/li>\n\n\n\n
  2. When writing data to the table, use COMPRESS<\/code> on the string data prior to inserting it into the VARBINARY<\/code> column.<\/li>\n\n\n\n
  3. When reading data from the table, use DECOMPRESS<\/code> on the binary data.<\/li>\n\n\n\n
  4. When the data is ready to be used, cast the VARBINARY<\/code> output of DECOMPRESS<\/code> as the VARCHAR<\/code>\/NVARCHAR<\/code> that the column is intended to be.<\/li>\n<\/ol>\n<\/div>\n\n\n
    To begin the walkthrough of this process, a new table will be created that uses a VARBINARY(MAX)<\/code> column, instead of a VARCHAR(MAX)<\/code>.<\/p>\n\n\n\n
    CREATE TABLE dbo.PersonInfoCompressed\n(\tPersonId INT NOT NULL IDENTITY(1,1)\n\t\tCONSTRAINT PK_PersonInfoCompressed \n                             PRIMARY KEY CLUSTERED,\n\tFirstName VARCHAR(100) NOT NULL,\n\tLastName VARCHAR(100) NOT NULL,\n\tPersonMetadata VARBINARY(MAX) NOT NULL\n);<\/pre>\n\n\n\n
    The PersonMetadata<\/code> column is now a VARBINARY(MAX)<\/code> column now, rather than VARCHAR(MAX)<\/code>.<\/p>\n\n\n\n
    With this table created, it can be populated similarly to before:<\/p>\n\n\n\n
    INSERT INTO dbo.PersonInfoCompressed\n\t(FirstName, LastName, PersonMetadata)\nVALUES\n('Edward', 'Pollack',\nCOMPRESS('{ \"PersonInfo\":\n\t{\n\t\t\"City\": \"Albany\",\n\t\t\"State\": \"New York\",\n\t\t\"SpiceLevel\": \"Extreme\",\n\t\t\"FavoriteSport\": \"Baseball\",\n\t\t\"Skills\": [\"SQL\", \"Baking\", \n                     \"Running\", \"Minecraft\"]\n\t}\n}')),\n('Edgar', 'Codd',\nCOMPRESS('{ \"PersonInfo\":\n\t{\n\t\t\"City\": \"Fortuneswell\",\n\t\t\"State\": \"Dorset\",\n\t\t\"SpiceLevel\": \"Medium\",\n\t\t\"FavoriteSport\": \"Flying\",\n\t\t\"Skills\": [\"SQL\", \"Computers\", \n                     \"Flying\", \"Normalizing Data Models\"]\n\t}\n}'));\n\t\nINSERT INTO dbo.PersonInfoCompressed\n\t(FirstName, LastName, PersonMetadata)\nVALUES\n('Thomas', 'Edison',\nCOMPRESS('{ \"PersonInfo\":\n\t{\n\t\t\"City\": \"Milan\",\n\t\t\"State\": \"Ohio\",\n\t\t\"SpiceLevel\": \"Mild\",\n\t\t\"FavoriteSport\": \"Reading\",\n\t\t\"Skills\": [\"Technology\", \"Business\", \n                     \"Communication\"]\n\t}\n}')),\n('Nikola', 'Tesla',\nCOMPRESS('{ \"PersonInfo\":\n\t{\n\t\t\"City\": \"Smiljan\",\n\t\t\"State\": \"Croatia\",\n\t\t\"SpiceLevel\": \"Hot\",\n\t\t\"FavoriteSport\": \"Inventing\",\n\t\t\"Skills\": [\"Lighting\", \"Electricity\", \n                     \"X-Rays\", \"Motors\"]\n\t}\n}'));<\/pre>\n\n\n\n
    The big difference is that each JSON document is wrapped in a call to the COMPRESS<\/code> function. This is now a necessary step, and attempting to insert the VARCHAR<\/code> JSON data directly into the VARBINARY<\/code> column will result in a conversion error. Selecting data from the table will show the same results as the compression test in the previous demonstration.<\/p>\n\n\n\n
    To read data from this column, it needs to have the DECOMPRESS<\/code> function applied, like this:<\/p>\n\n\n\n
    SELECT\n\tPersonId,\n\tFirstName,\n\tLastName,\n\tDECOMPRESS(PersonMetadata) AS PersonMetadata\nFROM dbo.PersonInfoCompressed\nWHERE LastName = 'Tesla';<\/pre>\n\n\n\n
    The results show the decompressed VARBINARY<\/code> output:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    To convert this into meaningful data, it needs to be converted to its intended VARCHAR<\/code> format:<\/p>\n\n\n\n
    SELECT\n\tCAST(DECOMPRESS(PersonMetadata) AS VARCHAR(MAX)) \n                                        AS PersonMetadata\nFROM dbo.PersonInfoCompressed\nWHERE LastName = 'Tesla';<\/pre>\n\n\n\n
    This time, the output is the JSON document, as it was originally inserted:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    There is value in keeping the JSON document compressed for as long as possible, prior to decompressing and converting to a string that can be read or parsed. The longer it is compressed, the longer computing resources are saved in moving it around.<\/p>\n\n\n\n
    Because the compressed JSON document is stored in VARBINARY<\/code> format, there is no simple way to search the column that will not involve a table scan. This is because all rows need to be decompressed and converted to a string first, prior to searching. If there is a frequent need to search the compressed JSON column, consider persisting the search column as a separate indexed column.<\/p>\n\n\n\n
    Note that COMPRESS<\/code> and DECOMPRESS<\/code> are deterministic and will always yield the same results. There is no random element to the algorithm, nor does the compression algorithm\/results change for different versions or editions of SQL Server.<\/p>\n\n\n\n
    Read also:<\/strong> Temporary tables for staging parsed JSON results<\/a><\/p>\n\n\n\n
    The JSON Data Type in Azure SQL Database<\/h2>\n\n\n\n
    Azure SQL Database added native JSON support in May 2024, allowing columns and variables to be declared as JSON, rather than VARCHAR<\/code> or NVARCHAR.<\/code> This support is available regardless of database compatibility level. Native support provides significantly improved performance as the document is stored in an already-parsed format. This reduces its storage size, IO needed to read and write JSON data, and allows for efficient compression.<\/p>\n\n\n\n
    As a bonus, no code changes are required to take advantage of the native JSON data type. A column\u2019s data type may be altered from VARCHAR\/NVARCHAR<\/code> to JSON and existing functions will continue to work. As always, it is important to test schema changes before deploying to a production environment, but the ability to improve JSON performance without code changes is hugely beneficial to any organization managing JSON documents in SQL Server.<\/p>\n\n\n\n
    These demos are executed against a small test database in Azure SQL Database that contains the default test objects from Microsoft.<\/em><\/p>\n\n\n\n
    Consider the simple example from earlier in this article, rewritten slightly to use the native JSON data type:<\/p>\n\n\n\n
    -- In Azure SQL Database\nDECLARE @PersonInfo JSON =\n'[\n\t{\n\t\t\"FirstName\": \"Edward\",\n\t\t\"LastName\": \"Pollack\",\n\t\t\"City\": \"Albany\",\n\t\t\"State\": \"New York\",\n\t\t\"SpiceLevel\": \"Extreme\",\n\t\t\"FavoriteSport\": \"Baseball\",\n\t\t\"Skills\": [\"SQL\", \"Baking\", \"Running\", \"Minecraft\"]\n\t},\n\t{\n\t\t\"FirstName\": \"Edgar\",\n\t\t\"LastName\": \"Codd\",\n\t\t\"City\": \"Fortuneswell\",\n\t\t\"State\": \"Dorset\",\n\t\t\"SpiceLevel\": \"Medium\",\n\t\t\"FavoriteSport\": \"Flying\",\n\t\t\"Skills\": [\"SQL\", \"Computers\", \"Flying\", \n                     \"Normalizing Data Models\"]\n\t}\n]';\nSELECT ISJSON(@PersonInfo);\n<\/pre>\n\n\n\n
    This code executes exactly the same way as it did earlier, returning a \u201c1\u201d, indicating that this is valid JSON. All the other code in the chapter will work the same way as well, just in a more efficient manner internally.<\/p>\n\n\n\n
    For columns that use the native JSON data type, constraints may be placed against them, just like any other column data type. While ISJSON()<\/code> may be used in the column\u2019s definition, allowing JSON to only be stored if it is valid and well-formed, this check is already performed as part of the JSON data type. An attempt to insert JSON that is not properly formed will result in an error.<\/p>\n\n\n\n
    The following is a simple table that contains a JSON column:<\/p>\n\n\n\n
    CREATE TABLE dbo.JSONTest\n(\tID INT NOT NULL IDENTITY(1,1) \n         CONSTRAINT PK_JSONTest PRIMARY KEY CLUSTERED,\n\tDocName VARCHAR(50) NOT NULL,\n\tJSONDocument JSON NOT NULL \n        CONSTRAINT CK_JSONTest_Check_FirstName\n            CHECK (JSON_PATH_EXISTS(JSONDocument, \n                                     '$.FirstName') = 1));\n<\/pre>\n\n\n\n
    Note the CHECK<\/code> constraint on the JSONDocument<\/code> column. This constraint will check the document to ensure that FirstName<\/code> is present, and if not, the document will not be allowed. For example, the following T-SQL will result in an error:<\/p>\n\n\n\n
    INSERT INTO dbo.JSONTest\n\t(DocName, JSONDocument)\nVALUES\n(   'A name entry',\n    '{\n\t\t\"LastName\": \"Pollack\",\n\t\t\"City\": \"Albany\",\n\t\t\"State\": \"New York\",\n\t\t\"SpiceLevel\": \"Extreme\",\n\t\t\"FavoriteSport\": \"Baseball\",\n\t\t\"Skills\": [\"SQL\", \"Baking\", \"Running\", \"Minecraft\"]\n\t}');\n\n<\/pre>\n\n\n\n
    The error is the standard check constraint failure error:<\/p>\n\n\n\n
    Msg 547, Level 16, State 0, Line 158<\/code><\/p>\n\n\n\n
    The INSERT statement conflicted with the CHECK constraint \"CK_JSONTest_Check_FirstName\". The conflict occurred in database \"EdTest\", table \"dbo.JSONTest\", column 'JSONDocument'.<\/code><\/p>\n\n\n\n
    The statement has been terminated.<\/code><\/p>\n\n\n\n
    The ability to embed JSON functions into check constraints can help to add firm data validation to documents before they enter the database. This can be exceptionally valuable in scenarios where an application is unable to ensure different criteria up-front.<\/p>\n\n\n\n
    There are no native indexes supported (yet) for JSON data types, though a JSON column may be part of the INCLUDE<\/code> columns in an index definition. The JSON data type is compatible as a stored procedure parameter, in triggers, views, and as a return type in functions.<\/p>\n\n\n\n
    If using Azure SQL Database and JSON, then the native data type is an easy way to improve performance and data integrity.<\/p>\n\n\n\n
    Read also:
    <\/strong>    Azure SQL\u2019s native JSON data type
    <\/a>    CHAR vs VARCHAR data type considerations<\/a><\/p>\n\n\n\n
    Conclusion<\/h2>\n\n\n\n
    JSON documents can be stored in SQL Server and accessed similarly to data stored in any other typed column using specialized JSON functions. This provides applications the ability to store JSON in SQL Server alongside relational data when needed.<\/p>\n\n\n\n
    While there are many considerations for how to store, index, compress, and manage JSON data that differ from traditional data types, the ability to maintain this data in SQL Server allows applications to manage it conveniently, rather than needing another storage location for it.<\/p>\n\n\n\n
    The JSON data type is a game-changer for these applications as it allows JSON documents to be stored natively in SQL Server, where they can be stored and read far more efficiently than as a string (or compressed string) column. While this feature is only available in Azure SQL Database as of June 2024, it is very likely to be available in future versions of SQL Server. Similarly, native JSON indexing is also a likely future feature addition. If this is an important feature to your development team, then keep an eye out for its availability in the near future.<\/p>\n\n\n\n
    Lastly, consider how and why JSON is stored in a relational database. Part of planning good data architecture is understanding the WHY and HOW behind data decisions, as well as what the future of that data will be. The ideal scenario for JSON is to be stored, maybe compressed, but filtered and manipulated as little as possible. While this may not always be possible, it is important to remember that a relational database engine is not optimized for document storage. Even with a native JSON data type, compression, and other (really cool) trickery, it is still a bit of an edge-case that should be managed carefully.<\/p>\n\n\n\n
    Thanks for reading, and hopefully this information helps you manage JSON documents more efficiently in the world of SQL Server!<\/p>\n\n\n\n
    \n    
    \n        
    \n            
    \n                
    Fast, reliable and consistent SQL Server development…<\/h3>\n                
    \n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
    \n    
    FAQs: How to store and parse JSON in SQL Server<\/h2>\n\n                        
    1. How do you store JSON in SQL Server?<\/h3>\n            
    \n                
    In on-premises SQL Server (2016+), store JSON in NVARCHAR(MAX) or VARCHAR(MAX) columns – there is no native JSON data type. Use ISJSON() as a CHECK constraint to validate that only well-formed JSON documents are inserted. In Azure SQL Database (2024+), you can use the native JSON data type, which provides built-in validation and more efficient storage. For large documents, consider COMPRESS() to reduce storage footprint, and create computed columns with indexes on frequently queried JSON properties.<\/p>\n            <\/div>\n                    
    2. What is the difference between JSON_VALUE and JSON_QUERY in SQL Server?<\/h3>\n            
    \n                
    JSON_VALUE extracts a scalar value (string, number, boolean) from a JSON document and returns it as NVARCHAR(4000). JSON_QUERY extracts a JSON object or array and returns it as NVARCHAR(MAX), preserving the JSON structure. Use JSON_VALUE for leaf-level properties like names or prices; use JSON_QUERY for nested objects or arrays that need to remain as JSON.<\/p>\n            <\/div>\n                    
    3. How do you index JSON data in SQL Server?<\/h3>\n            
    \n                
    Create a computed column that extracts the JSON property with JSON_VALUE(), then add a standard index on that computed column. For example: ALTER TABLE Orders ADD CustomerName AS JSON_VALUE(OrderData, ‘$.customer.name’); CREATE INDEX IX_CustomerName ON Orders(CustomerName). The computed column can be persisted for better performance. This approach works for both NVARCHAR-stored JSON and the native JSON data type.<\/p>\n            <\/div>\n                    
    4. Should you store JSON in SQL Server or a document database?<\/h3>\n            
    \n                
    Store JSON in SQL Server when the JSON data is closely related to relational data in the same database, when the application needs ACID transactions across JSON and relational data, or when JSON is primarily stored and retrieved without complex querying. Use a document database (MongoDB, Cosmos DB) when JSON is the primary data model, when you need flexible schemas across large datasets, or when you need specialized JSON indexing and query capabilities beyond what SQL Server provides.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
    Learn how to store, validate, index, and compress JSON in SQL Server. Covers ISJSON, OPENJSON, JSON_VALUE, computed columns, the Azure SQL native JSON data type, and storage architecture best practices.…<\/p>\n","protected":false},"author":329827,"featured_media":103460,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143531],"tags":[4880,4151],"coauthors":[101655],"class_list":["post-102912","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-t-sql-programming-sql-server","tag-json","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102912","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/329827"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=102912"}],"version-history":[{"count":11,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102912\/revisions"}],"predecessor-version":[{"id":109067,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102912\/revisions\/109067"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/103460"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=102912"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=102912"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=102912"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=102912"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}