Working with JSON in SQL Server 2016

Original author: Jovan Popovic
  • Transfer
  • Tutorial
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 becomes especially 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. Later in this article, we will look at the basic mechanisms for working with JSON.

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 an SQL query as JSON.



If you have JSON, you can get the data from there or check it for validity using the built-in functions JSON_VALUE, JSON_QUERY and ISJSON. The JSON_MODIFY function can be used to change data. For more advanced use, the OPENJSON function allows you to convert an array of JSON objects into a set of strings. Then you can execute any SQL query on this set. Finally, there is a FOR JSON construct that converts the result of the request to JSON.

Let's look at a few simple examples. In the following code, we will 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, number, Boolean value), which is located along the "path" specified by the second parameter. JSON_QUERY returns an object or an array (in our example, an array of tags) along the "path". JSON built-in functions use JavaScript-like syntax to access values ​​and objects as a 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

Let's consider these functions in more detail.

JSON data storage in SQL Server


In SQL Server, JSON is stored as text. You can use type NVARCHAR 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 one table. You can also combine JSON columns with spatial columns and XML. Unlike only relational or document-based storages, 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 text column storage using various compression mechanisms, such as UNICODE compression, which save up to 50% in 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 component of SQL Server that works with the NVARCHAR type. In the example above, JSON is stored in an OLTP (Hekaton) table in memory, which offers super performance. You can store JSON in regular tables, use columnstore indexes or FILESTREAM. You can also download 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 might find powerful features that are not available in existing document-oriented DBMSs.

If you want to ensure the validity of stored JSON, you can add a validation check using the ISJSON constraint and function:

ALTER TABLE Person
ADD CONSTRAINT [Content should be formatted as JSON]
 CHECK ( ISJSON( InfoJSON )> 0 )

At runtime, your requests will not work if the JSON is formatted incorrectly.

Because JSON is presented in the form of text, there is no need to change something in your applications. You can work with JSON as normal strings. JSON can be loaded using ORM as a string and sent to a client-side JavaScript application. Any data extraction utility will also work.

Built-in Functions for JSON Processing


SQL Server 2016 offers several features for handling JSON:

  • ISJSON (jsonText) checks the validity of JSON against the specification. With this function, you can impose restrictions on columns containing JSON
  • JSON_VALUE (jsonText, path) parses jsonText and selects individual values ​​by a specific "path" (see examples below)
  • JSON_QUERY (jsonText, path) parses jsonText and selects objects or arrays according to a specific "path" (see examples below)
  • JSON_MODIFY (jsonText, path, newValue) changes the value of a property in a specific "path (see, examples below)

These functions use “paths” in JSON to access 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 syntax of the “path” looks like:

[append] [lax | strict] $ .json_path

When specifying the append modifier, the new value will be added to the array referenced by json_path. The lax modifier sets the operation mode in which it does not matter if the property exists or not. If it is not, then 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 "$" 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 name from regular columns, social number and skill array from JSON column. 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.).

Converting JSON 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 | 1
| S043661 | 2011-06-01 00: 00: 00.000 | Nokia | 3


In the example above, we determined where we will look for the JSON array that we are processing (i.e. along the $ .Orders path), which columns we return and where in the JSON objects are the values ​​that we return as cells.

OPENJSON can be used in any request when working with data. As in the example, we can convert the JSON array from the orders variable to a set of rows and then paste 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 dataset 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. Assume that the following JSON is contained in the orders variable:

'[
   {"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, converting from JSON to relational form is very simple. All that is needed is to determine the column names and types, and OPENJSON will find the properties in JSON that correspond to the columns. This example uses simple, single-level 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 the JSON array from the previous example is stored in the OrdersJson column. The following query will return regular 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 will process the array in each cell and return 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 regular values ​​in columns. Nonclustered or full-text indexes can be used.

If you need to create an index on some JSON property that is often used in queries, you can create a computed column that references 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.

Because JSON is just text, you can use the 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 a JSON array, or you can create a computed column that references an array and add a full-text index on that 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 a value in a JSON array:

SELECT PersonID, FirstName,LastName,vEmailAddresses
FROM Person
WHERE CONTAINS(vEmailAddresses, 'john@mail.microsoft.com')

This query will return strings from Person, where the array of email addresses contains “john@mail.microsoft.com”. The full-text index does not have special JSON parsing rules. It divides the array using delimiters (double quotes, commas, square brackets) and indexes the values ​​in the array. The full-text index applies to arrays of numbers or strings. If you have more complex objects in JSON, a full-text index is not applicable, since it cannot distinguish keys from values.

In general, the same principles for creating indexes can be applied to regular columns or JSON columns.

Export data to JSON - FOR JSON


SQL Server has the ability 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 at the end of a SELECT query, SQL Server formats the result as JSON. Each row will be represented as a single 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 the JSON structure in the output using column names. If you use dot-separated names as synonyms for columns, JSON properties will follow 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 tables in the query. Similar to FOR XML AUTO.

Conclusion


JSON functions in SQL Server allow you to query and analyze data in the form of JSON, as well as convert it to a relational view and vice versa. This allows you to integrate SQL Server 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 provides a compromise between high data access speed and flexible application development capabilities.

In addition, you can index values ​​in JSON like regular columns, and also convert relational data to JSON using FOR JSON and vice versa using OPENJSON.

Only registered users can participate in the survey. Please come in.

Are you already using JSON support in SQL Server?

  • 12.4% yes 20
  • 73.9% not, but I want to try 119
  • 13.6% not and not planning 22

Also popular now: