Хранение документов JSON в SQL Server или базе данных SQLStore JSON documents in SQL Server or SQL Database

SQL Server и база данных SQL Azure имеют собственные функции JSON, позволяющие анализировать документы JSON с помощью стандартного языка SQL.SQL Server and Azure SQL Database have native JSON functions that enable you to parse JSON documents using standard SQL language. Вы можете хранить документы JSON в SQL Server или Базе данных SQL и запрашивать данные JSON так же, как в базе данных NoSQL.You can store JSON documents in SQL Server or SQL Database and query JSON data as in a NoSQL database. Эта статья описывает возможности хранения документов JSON в SQL Server или базе данных SQL.This article describes the options for storing JSON documents in SQL Server or SQL Database.

Формат хранения JSONJSON storage format

При проектировании хранилища прежде всего нужно решить, как хранить документы JSON в таблицах.The first storage design decision is how to store JSON documents in the tables. Доступны два варианта:There are two available options:

  • Хранилище LOB позволяет хранить документы JSON без преобразования в столбцах NVARCHAR.LOB storage - JSON documents can be stored as-is in NVARCHAR columns. Это лучший способ для быстрой загрузки и приема данных, так как скорость загрузки соответствует загрузке столбцов со строковыми значениями.This is the best way for quick data load and ingestion because the loading speed is matching loading of string columns. Этот подход может снизить производительность из-за задержек на выполнение запросов и анализа, если индексация значений JSON не выполнялась, так как при выполнении запросов необходимо проанализировать необработанные документы JSON.This approach might introduce additional performance penalty on query/analysis time if indexing on JSON values in not performed, because the raw JSON documents must be parsed while the queries are running.
  • Реляционное хранилище позволяет с помощью функций OPENJSON, JSON_VALUE или JSON_QUERYанализировать документы JSON во время их вставки в таблицу.Relational storage - JSON documents can be parsed while they are inserted in the table using OPENJSON, JSON_VALUE or JSON_QUERY functions. Фрагменты входных документов JSON могут храниться в столбцах типа данных SQL или в столбцах NVARCHAR, содержащих вложенные элементы JSON.Fragments from the input JSON documents can be stored in the SQL data type columns or in NVARCHAR columns containing JSON sub-elements. Такой подход увеличивает время загрузки, так как анализ JSON выполняется во время загрузки. Но время выполнения запросов соответствует времени выполнения традиционных запросов к реляционным данным.This approach increases the load time because JSON parsing is done during load; however, queries are matching performance of classic queries on the relational data.

Классические таблицыClassic tables

Для хранения документов JSON в SQL Server или базе данных SQL проще всего создать обычную таблицу с двумя столбцами, содержащую идентификатор и содержимое документа.The simplest way to store JSON documents in SQL Server or SQL Database is to create a two-column table that contains the ID of the document and the content of the document. Пример:For example:

create table WebSite.Logs (
    _id bigint primary key identity,
    log nvarchar(max)
);

Эта структура эквивалентна коллекциям из классических баз данных документов.This structure is equivalent to the collections that you can find in classic document databases. Первичный ключ _id — это значение с автоматическим приращением, представляющее собой уникальный идентификатор каждого документа для быстрого поиска.The primary key _id is an auto-incrementing value that provides a unique identifier for every document and enables fast lookups. Такая структура хорошо подходит для классических сценариев NoSQL, когда получение или изменение хранимого документа осуществляются по идентификатору.This structure is a good choice for the classic NoSQL scenarios where you want to retrieve a document by ID or update a stored document by ID.

Тип данных nvarchar(max) позволяет хранить документы JSON размером до 2 ГБ.The nvarchar(max) data type lets you store JSON documents that are up to 2 GB in size. Если вы уверены, что размер документов JSON не превышает 8 КБ, рекомендуем вместо NVARCHAR(max) использовать NVARCHAR(4000) для большей производительности.If you're sure that your JSON documents aren't greater than 8 KB, however, we recommend that you use NVARCHAR(4000) instead of NVARCHAR(max) for performance reasons.

В образце таблицы, созданном в предыдущем примере, предполагается, что в столбце log хранятся допустимые документы JSON.The sample table created in the preceding example assumes that valid JSON documents are stored in the log column. Если вы хотите убедиться, что в столбце log хранятся допустимые документы JSON, добавьте для столбца ограничение CHECK.If you want to be sure that valid JSON is saved in the log column, you can add a CHECK constraint on the column. Пример:For example:

ALTER TABLE WebSite.Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON(log)=1)

Каждый раз, когда кто-то вставляет или изменяет документ в таблице, это ограничение проверяет, что документ JSON имеет правильный формат.Every time someone inserts or updates a document in the table, this constraint verifies that the JSON document is properly formatted. Без этого ограничения таблица будет оптимизирована для операций вставки, так как любой документ JSON добавляется в столбец напрямую, без обработки.Without the constraint, the table is optimized for inserts, because any JSON document is added directly to the column without any processing.

При хранении в таблице документов JSON вы можете выполнять запросы к документам с помощью стандартного языка Transact-SQL.When you store your JSON documents in the table, you can use standard Transact-SQL language to query the documents. Пример:For example:

SELECT TOP 100 JSON_VALUE(log, '$.severity'), AVG( CAST( JSON_VALUE(log,'$.duration') as float))
 FROM WebSite.Logs
 WHERE CAST( JSON_VALUE(log,'$.date') as datetime) > @datetime
 GROUP BY JSON_VALUE(log, '$.severity')
 HAVING AVG( CAST( JSON_VALUE(log,'$.duration') as float) ) > 100
 ORDER BY AVG( CAST( JSON_VALUE(log,'$.duration') as float) ) DESC

Возможность использования любой функции и предложения запроса T-SQL для запросов к документам JSON является существенным преимуществом.It's a powerful advantage that you can use any T-SQL function and query clause to query JSON documents. SQL Server и база данных SQL не вводят никаких ограничений к запросам, используемым для анализа документов JSON.SQL Server and SQL Database don't introduce any constraints in the queries that you can use to analyze JSON documents. Вы можете извлекать из документа JSON значения с помощью функции JSON_VALUE и использовать их в запросе, как любые другие значения.You can extract values from a JSON document with the JSON_VALUE function and use it in the query like any other value.

Наличие полнофункционального синтаксиса запросов T-SQL является ключевым отличием SQL Server и базы данных SQL от классических баз данных NoSQL: в Transact-SQL вам, скорее всего, доступны все необходимые функции для обработки данных JSON.This ability to use rich T-SQL query syntax is the key difference between SQL Server and SQL Database and classic NoSQL databases - in Transact-SQL you probably have any function that you need to process JSON data.

ИндексыIndexes

Если окажется, что ваши запросы часто выполняют в документах поиск по определенному свойству (например, по свойству severity в документе JSON), добавьте к свойству классический индекс NONCLUSTERED, чтобы ускорить обработку запросов.If you find out that your queries frequently search documents by some property (for example, a severity property in a JSON document), you can add a classic NONCLUSTERED index on the property to speed up the queries.

Вы можете создать вычисляемый столбец, который предоставляет значения JSON из столбцов JSON по заданному пути (то есть, по пути $.severity), и создать стандартный индекс в этом столбце.You can create a computed column that exposes JSON values from the JSON columns on the specified path (that is, on the path $.severity) and create a standard index on this computed column. Пример:For example:

create table WebSite.Logs (
    _id bigint primary key identity,
    log nvarchar(max),

    severity AS JSON_VALUE(log, '$.severity'),
    index ix_severity (severity)
);

Используемый в этом примере вычисляемый столбец является несохраняемым или виртуальным столбцом, который не добавляет дополнительное место в таблицу.The computed column used in this example is a non-persisted or virtual column that doesn't add additional space to the table. Индекс ix_severity использует его для повышения производительности запросов, как показано в следующем примере.It is used by the index ix_severity to improve performance of the queries like the following example:

SELECT log
FROM Website.Logs
WHERE JSON_VALUE(log, '$.severity') = 'P4'

Важное свойство этого индекса — учет параметров сортировки.One important characteristic of this index is that it is collation-aware. Если исходный столбец NVARCHAR имеет свойство COLLATION (например, для учета регистра или японского языка), индекс расставляется в соответствии с правилами языка или правилами учета регистра, связанными со столбцом NVARCHAR.If your original NVARCHAR column has a COLLATION property (for example, case-sensitivity or Japanese language), the index is organized according to the language rules or the case sensitivity rules associated with the NVARCHAR column. Такой учет параметров сортировки может оказаться важным при разработке приложений для международного рынка, в которых нужно использовать особые языковые правила при обработке JSON-документов.This collation awareness may be an important feature if you are developing applications for global markets that need to use custom language rules when processing JSON documents.

Большие таблицы и формат columnstoreLarge tables & columnstore format

Если в вашей коллекции планируется большое количество документов JSON, рекомендуем добавить в нее индекс CLUSTERED COLUMNSTORE, как показано в следующем примере.If you expect to have a large number of JSON documents in your collection, we recommend adding a CLUSTERED COLUMNSTORE index on the collection, as shown in the following example:

create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
    _id bigint default(next value for WebSite.LogID),
    log nvarchar(max),

    INDEX cci CLUSTERED COLUMNSTORE
);

Индекс CLUSTERED COLUMNSTORE обеспечивает высокую степень сжатия данных (максимум в 25 раз), которая позволит значительно снизить требования к дисковому пространству, сократить расходы на хранение и повысить производительность операций ввода-вывода рабочей нагрузки.A CLUSTERED COLUMNSTORE index provides high data compression (up to 25x) that can significantly reduce your storage space requirements, lower the cost of storage, and increase the I/O performance of your workload. Кроме того, индексы CLUSTERED COLUMNSTORE оптимизированы для сканирования таблиц и анализа документов JSON, поэтому они могут быть наилучшим выбором для аналитики журналов.Also, CLUSTERED COLUMNSTORE indexes are optimized for table scans and analytics on your JSON documents, so this type of index may be the best option for log analytics.

В примере выше для присвоения значений столбцу _id используется объект последовательности.The preceding example uses a sequence object to assign values to the _id column. И последовательности, и идентификаторы являются приемлемыми вариантами для этого столбца.Both sequences and identities are valid options for the ID column.

Часто изменяемые документы и таблицы, оптимизированные для памятиFrequently changing documents & memory-optimized tables

Если в коллекциях ожидается множество операций изменения, вставки и удаления, вы можете хранить документы JSON в таблицах, оптимизированных для памяти.If you expect a large number of update, insert, and delete operations in your collections, you can store your JSON documents in memory-optimized tables. Оптимизированные для памяти коллекции JSON всегда хранят данные в памяти, что позволяет избежать нагрузки, связанной с операциями ввода-вывода в хранилище.Memory-optimized JSON collections always keep data in-memory, so there is no storage I/O overhead. Кроме того, эти коллекции абсолютно неблокируемые, то есть действия с документами не препятствуют никаким другим операциям.Additionally, memory optimized JSON collections are completely lock-free - that is, actions on documents do not block any other operation.

Единственное, что вам нужно сделать для преобразования классической коллекции в коллекцию, оптимизированную для памяти, — это указать параметр with (memory_optimized=on) после определения таблицы, как показано в следующем примере.The only thing that you have to do convert a classic collection to a memory-optimized collection is to specify the with (memory_optimized=on) option after the table definition, as shown in the following example. После этого вы получите оптимизированную для памяти версию коллекции JSON.Then you have a memory-optimized version of the JSON collection.

create table WebSite.Logs (
  _id bigint identity primary key nonclustered,
  log nvarchar(4000)
) with (memory_optimized=on)

Таблица, оптимизированная для памяти, — лучший вариант для часто изменяемых документов.A memory-optimized table is the best option for frequently changing documents. При их внедрении также учитывайте производительность.When you are considering memory-optimized tables, also consider performance. Если возможно, используйте в своих оптимизированных для памяти коллекциях NVARCHAR(4000) вместо NVARCHAR(max) для документов JSON, так как это может значительно увеличить производительность.Use NVARCHAR(4000) instead of NVARCHAR(max) for the JSON documents in your memory-optimized collections, if possible, because it may drastically improve performance.

Как и с классическими таблицами, вы можете добавлять индексы в поля, предоставляемые в оптимизированных для памяти таблицах, с помощью вычисляемых столбцов.As with classic tables, you can add indexes on the fields that you are exposing in memory-optimized tables by using computed columns. Пример:For example:

create table WebSite.Logs (

  _id bigint identity primary key nonclustered,
  log nvarchar(4000),

  severity AS cast(JSON_VALUE(log, '$.severity') as tinyint) persisted,
  index ix_severity (severity)

) with (memory_optimized=on)

Чтобы обеспечить максимальную производительность, приведите значение JSON к наименьшему возможному типу, который можно использовать для хранения значения свойства.To maximize performance, cast the JSON value to the smallest possible type that can be used to hold the value of the property. В примере выше используется tinyint.In the preceding example, tinyint is used.

Вы также можете поместить запросы SQL, которые изменяют документы JSON, в хранимые процедуры, чтобы воспользоваться преимуществами компиляции в машинный код.You can also put SQL queries that update JSON documents in stored procedures to get the benefit of native compilation. Пример:For example:

CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
WITH SCHEMABINDING, NATIVE_COMPILATION

AS BEGIN
    ATOMIC WITH (transaction isolation level = snapshot,  language = N'English')

    UPDATE WebSite.Logs
    SET log = JSON_MODIFY(log, @Property, @Value)
    WHERE _id = @Id;

END

Эта скомпилированная в машинный код процедура принимает запрос и создает .DLL-код, который выполняет запрос.This natively compiled procedure takes the query and creates .DLL code that runs the query. Она является самым быстрым способом для создания запросов и изменения данных.A natively compiled procedure is the faster approach for querying and updating data.

ЗаключениеConclusion

Собственные функции JSON в SQL Server и базе данных SQL позволяют работать с документами JSON так же, как в базах данных NoSQL.Native JSON functions in SQL Server and SQL Database enable you to process JSON documents just like in NoSQL databases. Каждая база данных (реляционная или NoSQL) обладает рядом преимуществ и недостатков в обработке данных JSON.Every database - relational or NoSQL - has some pros and cons for JSON data processing. Основное преимущество хранения документов JSON в SQL Server или базе данных SQL — это полная поддержка языка SQL.The key benefit of storing JSON documents in SQL Server or SQL Database is full SQL language support. Вы можете использовать широкие возможности языка Transact-SQL для обработки данных и настройки множества параметров хранения (от индексов columnstore для высокой степени сжатия и быстрого анализа до оптимизированных для памяти таблиц, обеспечивающих обработку без блокирования).You can use the rich Transact-SQL language to process data and to configure a variety of storage options (from columnstore indexes for high compression and fast analytics to memory-optimized tables for lock-free processing). Кроме того, вам доступны обширные возможности обеспечения безопасности и оптимизации под различные рынки, которые можно легко переносить в сценарии NoSQL.At the same time, you get the benefit of mature security and internationalization features which you can easily reuse in your NoSQL scenario. Изложенные выше причины являются веским доводом в пользу хранения документов JSON в SQL Server или базе данных SQL.The reasons described in this article are excellent reasons to consider storing JSON documents in SQL Server or SQL Database.

Дополнительные сведения о JSON в SQL Server и базе данных SQL AzureLearn more about JSON in SQL Server and Azure SQL Database

Видео МайкрософтMicrosoft videos

Наглядные инструкции по встроенной поддержке JSON в SQL Server и базе данных SQL Azure см. в следующих видео.For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos: