SQL Server: Storing JSON Documents in the Database Engine

Generally, there are three different ways in which data presented in a particular format can be stored in relational form:

  • As “raw” documents
  • Decomposed into relational columns
  • Using native storage

If you store data presented in a particular format as a large object (LOB), an exact copy of the data is stored. In this case, data is stored “raw”—that is, in its character string form. The raw form allows you to insert documents very easily. The retrieval of such a document is very efficient if you retrieve the entire document. To retrieve parts of the documents, you need to create special types of indices.

To decompose data presented in a particular format into separate columns of one or more tables, you can use its schema. In this case, the hierarchical structure of the document is preserved, while order among elements is ignored.

NOTE The decomposition of JSON documents into separate columns is not possible, because JSON is (generally) a schema-less format.

Native storage means that documents are stored in their parsed form. In other words, the document is stored in an internal representation (Infoset, for instance) that preserves the content of the data.

Using native storage makes it easy to query information based on the structure of the document. On the other hand, reconstructing the original form of the document is difficult, because the created content may not be an exact copy of the document.

In the case of JSON, the Database Engine supports only the first form of storage, as raw documents. Therefore, JSON objects can be stored as values of the NVARCHAR data type.

NOTE The best way to store data is to use native storage. We can hope that the implementation of JSON objects as raw documents is just Microsoft’s first step toward fully integrating JSON in the Database Engine.

Example 29.2 creates a table with a JSON document and inserts six rows in the table.

Example 29.2

USE sample;

CREATE TABLE json_table

(id INT PRIMARY KEY IDENTITY, person_and_friends NVARCHAR(2000));

— Insert a couple of rows

INSERT INTO json_table (person_and_friends) VALUES

(N'{“info”:{“who”: “Fred” ,”where”: “Microsoft” ,

“friends”:[{“name”:”Lili”,”rank”:5}, {“name”:”Hank”,”rank”:7}]}}’);

INSERT INTO json_table (person_and_friends) VALUES

(N'{“info”:{“who”: “Tom”, “where”: “IBM”, “friends”: [ { “name”:

  “Sharon”, “rank”: 2}, {“name”: “Monty”, “rank”: 3} ] }}’);

INSERT INTO json_table (person_and_friends) VALUES

(N'{“info”:{“who”:”Jack”, “friends”: [ { “name”: “Connie” } ] }}’);

INSERT INTO json_table (person_and_friends) VALUES

(N'{“info”:{“who”:”Joe”,”friends”:[{“name”:”Doris”},{“rank”:1}]}}’)

INSERT INTO json_table (person_and_friends) VALUES

(N’ {“info”: {“who”:”Mabel”,

  “where”:”PostgresSQL”,”friends”:[{“name”:”Buck”,”rank”: 6}]}}’);

INSERT INTO json_table (person_and_friends) VALUES

(N’ {“info”:{“who”: “Louise”, “where”: “Hanna” }}’);

Example 29.2 creates a table called jason_table with a person_and_friends column, which contains JSON documents and is therefore specified using the NVARCHAR data type. The INSERT statements insert six JSON objects, the structure of which is identical to the JSON object presented in Example 29.1.

Because JSON documents are stored using the NVARCHAR data type, it is not necessary to support a customized JSON index. In other words, this data type is a standard data type, and columns specified using the NVARCHAR type can be indexed using the B-tree indices.

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *