Working with JSON in SQL Server 2016
JSON is now one of the most used data formats in development. Most modern services return information in the form of JSON. JSON is also the preferred format for storing structured information in files, for example. Since a lot of data is used in JSON format, JSON support in SQL Server especially becomes relevant in order to be able to exchange data with other services.
JSON has become one of the most requested features added in SQL Server 2016. Next, we will look at the main mechanisms for working with JSON in this article.
Functions for working with JSON in SQL Server allow you to analyze and query JSON data, convert JSON to a relational view, and export the result of a SQL query as JSON.
')
If you have JSON, you can get data from there or check it for validity using the built-in functions JSON_VALUE, JSON_QUERY and ISJSON. To change the data, the JSON_MODIFY function can be used. For more advanced use, the OPENJSON function allows you to convert an array of JSON objects into a set of strings. Then you can run any SQL query on this set. Finally, there is a FOR JSON construct that converts the result of the query to JSON.
Let's look at some simple examples. In the following code, we define a text variable in which there will be JSON:
Now we can get individual values or objects from JSON using JSON_VALUE and JSON_QUERY:
This query will return “Basic”, “Bristol” and [“Sport”, “Water polo”]. The JSON_VALUE function returns a scalar value from JSON (that is, a string, a number, a boolean value) that is located along the “path” specified by the second parameter. JSON_QUERY returns an object or array (in our example, it is an array of tags) along a “path”. The built-in JSON functions use JavaScript-like syntax to refer to values and objects as the second parameter.
The OPENJSON function allows you to access an array inside JSON and return the elements of this array:
This example returns string values from an array of tags. In addition, OPENJSON can return any complex object.
Finally, the FOR JSON construct can format any result of executing a SQL query in JSON:
Consider these features in more detail.
In SQL Server, JSON is stored as text. You can use the NVARCHAR type for this. In the following example, we will store JSON in the InfoJson field:
In SQL Server 2016, you can combine regular columns (FirstName and LastName in the example) and columns with JSON (InfoJSON in the example) in the same table. You can also combine JSON columns with columns with spatial data (spatial columns) and XML. Unlike only relational or only document-oriented repositories, you can choose storage principles to achieve greater development flexibility.
Although JSON is stored in text columns, it is not just plain text. SQL Server has a mechanism for optimizing the storage of text columns using various compression mechanisms, such as UNICODE compression, which can save up to 50% of the size. You can also store JSON in tables with columnstore indexes or compress them explicitly using the built-in COMPRESS function, which uses the GZip algorithm.
JSON is fully compatible with any SQL Server component that works with the NVARCHAR type. In the example above, JSON is stored in the OLTP (Hekaton) table in memory, which offers super-performance. You can store JSON in regular tables, use columnstore indexes or FILESTREAM. You can also load it from Hadoop using Polybase tables, read from the file system, work with it in Azure SQL, use replication, etc. If you combine tables that store JSON with other SQL Server features, such as temporary table security or row-level security, you can discover powerful features that are not available in existing document-oriented DBMSs.
If you want to ensure the validity of the stored JSON, you can add a validation check using the restrictions and ISJSON functions:
At runtime, your requests will not work if JSON is formatted incorrectly.
Since JSON is represented as text, there is no need to change something in your applications. You can work with JSON as with ordinary strings. JSON can be loaded using ORM as a string and sent to a JavaScript client application. Any data extraction utilities will also work.
SQL Server 2016 offers several functions for handling JSON:
These functions use “paths” in JSON to refer to values or objects. Examples:
When using the JSON_MODIFY function, additional modifiers can be used in the path parameter. In general, the “path” syntax looks like:
If you specify the append modifier, the new value will be added to the array referenced by json_path. The lax modifier sets the mode of operation in which it does not matter whether a property exists or not. If not, it will be added. When using strict, if there is no property, an error will be generated.
The dollar sign ($) refers to the entire JSON object (similar to the root node "/" in XPath). You can add any property after the “$” to refer to the elements of the object. Consider a simple example:
This query returns the first and last names from the regular columns, the social number and the skills array from the JSON column. The results are filtered by the condition that the InfoJSON column must contain valid JSON and the Type value in the JSON column is “Student”. As you already understood, you can use values from JSON in any part of the request (sorting, grouping, etc.).
The OPENJSON function returns a table that defines an array of objects, iterates through the array, and displays each element of the array in a row.
Input Data (JSON):
SQL query:
Result
In the example above, we determined where we would look for an array of JSON that we process (that is, the $ .Orders path), which columns we return, and where in the JSON objects we find the values that we return as cells.
OPENJSON can be used in any query when working with data. As in the example, we can convert the JSON array from the orders variable to the rowset and then insert them into a regular table:
The 4 columns of the returned OPENJSON data set are defined using the WITH clause. OPENJSON will try to find the Number, Date, Customer, and Quantity properties in each JSON object and convert the values to columns in the resulting dataset. By default, if the property is not found, NULL will be returned. Suppose the orders variable contains the following JSON:
As you can see, the conversion from JSON to the relational form is very simple. All you need to do is define the column names and types, and OPENJSON will find the properties in JSON that correspond to the columns. This example uses simple sibling JSON, but OPENJSON can work with complex nested objects.
OPENJSON can also be used when you need to combine relational data and JSON in the same query. Suppose that the JSON array from the previous example is stored in the OrdersJson column. The following query returns the normal and JSON fields:
OPENJSON processes the array in each cell and returns one row for each JSON object in the array. The CROSS APPLY OPENJSON syntax is used to combine table rows with JSON data.
Although the values in JSON are stored as text, you can index them as normal values in columns. You can use nonclustered or full text indexes.
If you want to create an index on any JSON property that is often used in queries, you can create a calculated column that refers to the desired property, then create a regular index on this field. In the following example, we optimize queries that filter rows using the $ .Company property from the InfoJSON column:
SQL Server provides a hybrid model in which you can combine regular columns and values from JSON in a single index.
Since JSON is just text, you can use a full-text index. Full-text indexes can be created on an array of values. You create a full-text index on a column that contains an array of JSON, or you can create a calculated column that references the array and add a full-text index to this column:
A full-text index is useful if you need to optimize queries that look for any value in a JSON array:
This request will return lines from Person, where the email address array contains „john@mail.microsoft.com“. The full-text index has no special rules for parsing JSON. It divides the array using delimiters (double quotes, commas, and square brackets) and indexes the values in the array. A full-text index applies to arrays of numbers or strings. If you have more complex objects in JSON, the full-text index is not applicable, as it cannot distinguish keys from values.
In general, the same principles for creating indexes can be applied to ordinary columns or JSON columns.
In SQL Server, it is possible to convert relational data to JSON using the FOR JSON construct. If you are familiar with the FOR XML construct, then you already practically know FOR JSON.
Initial data
SQL query
Resulting json
When you add FOR JSON to the end of a SELECT query, SQL Server formats the result as JSON. Each row will be represented as one JSON object, the values from the cells will be JSON values, and the column names will be used as keys. There are 2 types of FOR JSON constructs:
JSON functions in SQL Server allow you to query and analyze data as JSON, as well as convert it to a relational view and vice versa. This allows SQL Server to be integrated into external systems that send or receive JSON without additional conversions.
SQL Server also offers a hybrid storage model when you combine relational data and JSON data in one table. This model allows for a trade-off between high speed data access and flexible application development.
In addition, you can index values in JSON as ordinary columns, and also convert relational data to JSON with FOR JSON and vice versa, using OPENJSON.
JSON has become one of the most requested features added in SQL Server 2016. Next, we will look at the main mechanisms for working with JSON in this article.
Short review
Functions for working with JSON in SQL Server allow you to analyze and query JSON data, convert JSON to a relational view, and export the result of a SQL query as JSON.
')
If you have JSON, you can get data from there or check it for validity using the built-in functions JSON_VALUE, JSON_QUERY and ISJSON. To change the data, the JSON_MODIFY function can be used. For more advanced use, the OPENJSON function allows you to convert an array of JSON objects into a set of strings. Then you can run any SQL query on this set. Finally, there is a FOR JSON construct that converts the result of the query to JSON.
Let's look at some simple examples. In the following code, we define a text variable in which there will be JSON:
DECLARE @json NVARCHAR(4000) SET @json = N'{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }' Now we can get individual values or objects from JSON using JSON_VALUE and JSON_QUERY:
SELECT JSON_VALUE(@json, '$.type') as type, JSON_VALUE(@json, '$.info.address.town') as town, JSON_QUERY(@json, '$.info.tags') as tags This query will return “Basic”, “Bristol” and [“Sport”, “Water polo”]. The JSON_VALUE function returns a scalar value from JSON (that is, a string, a number, a boolean value) that is located along the “path” specified by the second parameter. JSON_QUERY returns an object or array (in our example, it is an array of tags) along a “path”. The built-in JSON functions use JavaScript-like syntax to refer to values and objects as the second parameter.
The OPENJSON function allows you to access an array inside JSON and return the elements of this array:
SELECT value FROM OPENJSON(@json, '$.info.tags') This example returns string values from an array of tags. In addition, OPENJSON can return any complex object.
Finally, the FOR JSON construct can format any result of executing a SQL query in JSON:
SELECT object_id, name FROM sys.tables FOR JSON PATH Consider these features in more detail.
JSON data storage in SQL Server
In SQL Server, JSON is stored as text. You can use the NVARCHAR type for this. In the following example, we will store JSON in the InfoJson field:
CREATE TABLE Person ( Id int IDENTITY PRIMARY KEY NONCLUSTERED, FirstName nvarchar(100) NOT NULL, LastName nvarchar(100) NOT NULL, InfoJson nvarchar(max) ) WITH (MEMORY_OPTIMIZED=ON) In SQL Server 2016, you can combine regular columns (FirstName and LastName in the example) and columns with JSON (InfoJSON in the example) in the same table. You can also combine JSON columns with columns with spatial data (spatial columns) and XML. Unlike only relational or only document-oriented repositories, you can choose storage principles to achieve greater development flexibility.
Although JSON is stored in text columns, it is not just plain text. SQL Server has a mechanism for optimizing the storage of text columns using various compression mechanisms, such as UNICODE compression, which can save up to 50% of the size. You can also store JSON in tables with columnstore indexes or compress them explicitly using the built-in COMPRESS function, which uses the GZip algorithm.
JSON is fully compatible with any SQL Server component that works with the NVARCHAR type. In the example above, JSON is stored in the OLTP (Hekaton) table in memory, which offers super-performance. You can store JSON in regular tables, use columnstore indexes or FILESTREAM. You can also load it from Hadoop using Polybase tables, read from the file system, work with it in Azure SQL, use replication, etc. If you combine tables that store JSON with other SQL Server features, such as temporary table security or row-level security, you can discover powerful features that are not available in existing document-oriented DBMSs.
If you want to ensure the validity of the stored JSON, you can add a validation check using the restrictions and ISJSON functions:
ALTER TABLE Person ADD CONSTRAINT [Content should be formatted as JSON] CHECK ( ISJSON( InfoJSON )> 0 ) At runtime, your requests will not work if JSON is formatted incorrectly.
Since JSON is represented as text, there is no need to change something in your applications. You can work with JSON as with ordinary strings. JSON can be loaded using ORM as a string and sent to a JavaScript client application. Any data extraction utilities will also work.
Built-in JSON handling
SQL Server 2016 offers several functions for handling JSON:
- ISJSON (jsonText) checks the validity of JSON for compliance with the specification. With this function you can impose restrictions on columns containing JSON
- JSON_VALUE (jsonText, path) parses jsonText and highlights individual values along a specific “path” (see examples below)
- JSON_QUERY (jsonText, path) parses jsonText and highlights objects or arrays along a specific “path” (see examples below)
- JSON_MODIFY (jsonText, path, newValue) changes the value of a property by a certain “path (see examples below)
These functions use “paths” in JSON to refer to values or objects. Examples:
'$' // JSON '$.property1' // property1 JSON '$[4]' // 5- ( 0) '$.property1.property2.array1[5].property3.array2[15].property4' // '$.info."first name"' // "first name" . (, ..), When using the JSON_MODIFY function, additional modifiers can be used in the path parameter. In general, the “path” syntax looks like:
[append] [lax | strict] $ .json_path
If you specify the append modifier, the new value will be added to the array referenced by json_path. The lax modifier sets the mode of operation in which it does not matter whether a property exists or not. If not, it will be added. When using strict, if there is no property, an error will be generated.
The dollar sign ($) refers to the entire JSON object (similar to the root node "/" in XPath). You can add any property after the “$” to refer to the elements of the object. Consider a simple example:
SELECT Id, FirstName, LastName, JSON_VALUE(InfoJSON, '$.info."social security number"') as SSN, JSON_QUERY(InfoJSON, '$.skills') as Skills FROM Person AS t WHERE ISJSON( InfoJSON ) > 0 AND JSON_VALUE(InfoJSON, '$.Type') = 'Student' This query returns the first and last names from the regular columns, the social number and the skills array from the JSON column. The results are filtered by the condition that the InfoJSON column must contain valid JSON and the Type value in the JSON column is “Student”. As you already understood, you can use values from JSON in any part of the request (sorting, grouping, etc.).
JSON conversion to relational view - OPENJSON
The OPENJSON function returns a table that defines an array of objects, iterates through the array, and displays each element of the array in a row.
Example
Input Data (JSON):
{ "Orders": [ { "Order": { "Number": "S043659", "Date": "2011-05-31T00:00:00" }, "Account": "Microsoft", "Item": { "Price": 59.99, "Quantity": 1 } }, { "Order": { "Number": "S043661", "Date": "2011-06-01T00:00:00" }, "Account": "Nokia", "Item": { "Price": 24.99, "Quantity": 3 } } ] } SQL query:
SELECT * FROM OPENJSON(@json, N'$.Orders') WITH ( Number VARCHAR(200) N'$.Order.Number', Date DATETIME N'$.Order.Date', Customer VARCHAR(200) N'$.Account', Quantity INT N'$.Item.Quantity' ) Result
| Number | Date | Customer | Quantity -------------------------------------------------- -------- | S043659 | 2011-05-31 00: 00: 00.000 | Microsoft | one | S043661 | 2011-06-01 00: 00: 00.000 | Nokia | 3
In the example above, we determined where we would look for an array of JSON that we process (that is, the $ .Orders path), which columns we return, and where in the JSON objects we find the values that we return as cells.
OPENJSON can be used in any query when working with data. As in the example, we can convert the JSON array from the orders variable to the rowset and then insert them into a regular table:
INSERT INTO Orders(Number, Date, Customer, Quantity) SELECT Number, Date, Customer, Quantity OPENJSON (@orders) WITH ( Number varchar(200), Date datetime, Customer varchar(200), Quantity int ) AS OrdersArray The 4 columns of the returned OPENJSON data set are defined using the WITH clause. OPENJSON will try to find the Number, Date, Customer, and Quantity properties in each JSON object and convert the values to columns in the resulting dataset. By default, if the property is not found, NULL will be returned. Suppose the orders variable contains the following JSON:
'[ {"Number":1, "Date": "8/10/2012", "Customer": "Adventure works", "Quantity": 1200}, {"Number":4, "Date": "5/11/2012", "Customer": "Adventure works", "Quantity": 100}, {"Number":6, "Date": "1/3/2012", "Customer": "Adventure works", "Quantity": 250}, {"Number":8, "Date": "12/7/2012", "Customer": "Adventure works", "Quantity": 2200} ]' As you can see, the conversion from JSON to the relational form is very simple. All you need to do is define the column names and types, and OPENJSON will find the properties in JSON that correspond to the columns. This example uses simple sibling JSON, but OPENJSON can work with complex nested objects.
OPENJSON can also be used when you need to combine relational data and JSON in the same query. Suppose that the JSON array from the previous example is stored in the OrdersJson column. The following query returns the normal and JSON fields:
SELECT Id, FirstName, LastName, Number, Date, Customer, Quantity FROM Person CROSS APPLY OPENJSON (OrdersJson) WITH ( Number varchar(200), Date datetime, Customer varchar(200), Quantity int ) AS OrdersArray OPENJSON processes the array in each cell and returns one row for each JSON object in the array. The CROSS APPLY OPENJSON syntax is used to combine table rows with JSON data.
JSON data indexing
Although the values in JSON are stored as text, you can index them as normal values in columns. You can use nonclustered or full text indexes.
If you want to create an index on any JSON property that is often used in queries, you can create a calculated column that refers to the desired property, then create a regular index on this field. In the following example, we optimize queries that filter rows using the $ .Company property from the InfoJSON column:
ALTER TABLE Person ADD vCompany AS JSON_VALUE(InfoJSON, '$.Company') CREATE INDEX idx_Person_1 ON Person(vCompany) SQL Server provides a hybrid model in which you can combine regular columns and values from JSON in a single index.
Since JSON is just text, you can use a full-text index. Full-text indexes can be created on an array of values. You create a full-text index on a column that contains an array of JSON, or you can create a calculated column that references the array and add a full-text index to this column:
ALTER TABLE Person ADD vEmailAddresses AS JSON_QUERY(InfoJSON, '$.Contact.Emails') CREATE FULLTEXT INDEX ON Person(vEmailAddresses) KEY INDEX PK_Person_ID ON jsonFullTextCatalog; A full-text index is useful if you need to optimize queries that look for any value in a JSON array:
SELECT PersonID, FirstName,LastName,vEmailAddresses FROM Person WHERE CONTAINS(vEmailAddresses, 'john@mail.microsoft.com') This request will return lines from Person, where the email address array contains „john@mail.microsoft.com“. The full-text index has no special rules for parsing JSON. It divides the array using delimiters (double quotes, commas, and square brackets) and indexes the values in the array. A full-text index applies to arrays of numbers or strings. If you have more complex objects in JSON, the full-text index is not applicable, as it cannot distinguish keys from values.
In general, the same principles for creating indexes can be applied to ordinary columns or JSON columns.
Export data to JSON - FOR JSON
In SQL Server, it is possible to convert relational data to JSON using the FOR JSON construct. If you are familiar with the FOR XML construct, then you already practically know FOR JSON.
Example
Initial data
| Number | Date | Customer | Price | Quantity | -------------------------------------------------- ------------------ | S043659 | 2011-05-31 00: 00: 00.000 | Microsoft | 59.99 | 1 | | S043661 | 2011-06-01 00: 00: 00.000 | Nokia | 24.99 | 3 |
SQL query
SELECT Number AS [Order.Number], Date AS [Order.Date], Customer AS [Account], Price AS 'Item.UnitPrice', Quantity AS 'Item.Qty' FROM SalesOrder FOR JSON PATH, ROOT('Orders') Resulting json
{ "Orders": [ { "Order": { "Number": "S043659", "Date": "2011-05-31T00:00:00" }, "Account": "Microsoft", "Item": { "UnitPrice": 59.99, "Qty": 1 } }, { "Order": { "Number": "S043661", "Date": "2011-06-01T00:00:00" }, "Account": "Nokia", "Item": { "UnitPrice": 24.99, "Qty": 3 } } ] } When you add FOR JSON to the end of a SELECT query, SQL Server formats the result as JSON. Each row will be represented as one JSON object, the values from the cells will be JSON values, and the column names will be used as keys. There are 2 types of FOR JSON constructs:
- FOR JSON PATH allows you to define a JSON structure on output using column names. If you use dot-separated names as synonyms for columns, the JSON properties will follow the naming conventions. This is similar to FOR XML PATH where you can specify a path separated by slashes.
- FOR JSON AUTO automatically creates nested arrays based on the hierarchy of the tables in the query. Looks like FOR XML AUTO.
Conclusion
JSON functions in SQL Server allow you to query and analyze data as JSON, as well as convert it to a relational view and vice versa. This allows SQL Server to be integrated into external systems that send or receive JSON without additional conversions.
SQL Server also offers a hybrid storage model when you combine relational data and JSON data in one table. This model allows for a trade-off between high speed data access and flexible application development.
In addition, you can index values in JSON as ordinary columns, and also convert relational data to JSON with FOR JSON and vice versa, using OPENJSON.
Source: https://habr.com/ru/post/317166/
All Articles