JSON for SQL Server

JSON for SQL Server. Part 1

In the last few years, JSON has positioned itself as a standard data exchange format between services, although XML is still widely used. In the SQL Server 2016, Microsoft implemented JSON support directly in the database engine, and the capabilities of data manipulation are increasing in each following version.

The word comes as an abbreviation from JavaScript Object Notation, and it represents an open-standard format in a form of human-readable key-value pairs; it is language-independent. It is often used in application configurations, RESTful web services, and NoSQL databases like CouchDB and MongoDB.

Popular development languages, including JavaScript, natively support generation and consumption of JSON without serialization, which gives it flexibility while keeping self-description without the need for schema, which is a requirement in XML.

JSON Basics

JSON text content is a sequence of tokens containing code points that conform to the JSON value grammar. Values can be either primitive (strings, numbers, booleans, or nulls) or complex (objects or arrays).

A JSON object is defined as a collection of “zero or more” key-value pairs named object members that are written in paired braces. Keys and values are separated with a single colon, and objects are separated by a comma. The key is a string, and the value can be any primitive or complex data type. A JSON array is an ordered list of zero or more values separated by commas and surrounded by square brackets.

Since JSON is designed to be as lightweight as possible, it supports only four primitive data types – numbers (double-precision float), string (Unicode text surrounded by double-quotes), true/false (boolean values that must be written in lowercase), and nulls. There is no dedicated “date” type – they are represented as strings. In JSON, strings are sequences wrapped with quotation marks, and all characters must be placed within them, except for escaped characters.

JSON basic structure

Getting SQL Server Data in JSON Format

When we start working with JSON in SQL Server, we usually first have to retrieve tabular data in this format. Microsoft first implemented a FOR JSON clause in SQL Server 2017 – this clause can be natively used with the SELECT statement, similarly to FOR XML that we use for retrieving data in XML format.

FOR JSON allows for two methods to select from:

FOR JSON AUTO – output will be formatted according to the SELECT statement structure

FOR JSON PATH – output will be formatted according to the user-defined structure, allowing you to use nested objects and properties

Whichever model you choose, SQL Server will extract relational data in SELECT statements. It will automatically convert the database data types to JSON types and implement character escape rules. Finally, it will format the output according to explicitly or implicitly defined formatting rules.

With FOR JSON AUTO, the output format is controlled by the design of the SELECT statement. Thus, using this mode requires a database table or view.

We get the following error message:

Msg 13600, Level 16, State 1, Line 4

FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name.

Now we show how SQL Server automatically generates JSON data. First, it is as output in Management Studio, and then formatted in a text editor:

Each row in the original result set is created as a flat property structure. If you compare this to standard XML, you will see much less text. It is because the table names do not appear in the JSON output.

The difference in size becomes important when you start to use the ELEMENTS option in XML instead of the default RAW value. To demonstrate this, we use the SELECT statement that compares the data length in bytes of XML and JSON output:

As we can see from the query results, XML element size is around 65% bigger than JSON size. On the other hand, when expressed as XML attributes, JSON and XML are about the same.

The output of using FOR JSON AUTO is a flat structure with single-level properties. If this is not sufficient for your needs, you need to use the FOR JSON PATH extension.

FOR JSON PATH allows you to keep complete control of the JSON output by creating wrapper objects and using complex properties. The final result is presented as a JSON objects array. This extension will use the alias/column name to define the key name in the output. If an alias contains dots, it will create a nested object.

Extending the previous example, we want to present FirstName and LastName columns as nested properties of the new PersonName column. We do it by adding an alias to do the columns that we nest and use dot syntax to get the proper output:

If we do not change default settings, the NULL values won’t be included in the results. If your statement joins multiple tables in one query, the output will be a flat list where FOR JSON PATH nests each column according to the defined column alias.

FOR JSON PATH extensions don’t need databases table, as we can in the following example:

Tabular Data Conversion with JSON

To use JSON with relational data or work with tables, you need to map this data with JSON and import it into the database tables.

If you are using SQL Server 2016 or later, you can use the OPENJSON function. This is a new rowset table-valued function added to the database engine. It returns an object that can be used as a view or table.

It converts JSON objects/properties pairs to rows/columns combinations, accepting two input parameters: Expression (UNICODE-based JSON text) and Path (JSON path expression, optional argument, used to specify a fragment of input expression).

If your database is not at a compatibility level of 130 or more, you will get the following exception when trying to use the OPENJSON function:

Msg 208, Level 16, State 1, Line 78

Invalid object name ‘OPENJSON’.

If you do not specify the schema for returned results, it will create a table containing three columns:

Key (name of property or index of element, column type is NOT NULL VARCHAR(4000));

Value (value of property or index of element, column type is NOT NULL NVARCHAR(MAX));

Type (JSON data type of value, column type is TINYINT).

OPENJSON will return one table, where first-level properties will be rows, and each row will be one JSON property or array element. To demonstrate this, we will use sample JSON data and provide it as a string to see the output:

If the input data is in the incorrect format, the following error will be displayed:

In this example, we returned only first-level properties. If we wish to return complex values of JSON documents (objects and arrays), we need to specify a path argument. See the next example with returning the PersonName element:

Again, if JSON is not properly formatted, the SQL Server engine will throw an exception. Let’s make an intended mistake in the same JSON text – we’ll omit one quotation sign next to FirstName:

A frequent problem is loading data in a comma-separated-value format, and we can use OPENJSON to help us with this:

There is an easier way to work with the JSON code – modern technologies brought us numerous tools. For instance, the dbForge SQL Complete allows you to add this plugin to SSMS and then view the JSON code directly in a dedicated Data Viewer window.

Conclusion

Thus, we have covered the basics of JSON and its usage in SQL Server, demonstrating the concepts with examples. There are more advanced topics of JSON handling left, and we’ll explore them thoroughly in the next article.

Josip Saban

An experienced database developer and software manager with more than 15 years in the field, both in corporate and start-up environments. Josip has strong problem-solving skills and a proven track-record of successfully implementing strategic solutions to meet changing business needs, combined with the ability to build key relationships and communicate well with stakeholders, most of it in finance industry. Focused on Microsoft tool stack, specifically SQL Server, especially in data warehouse modelling and development. from version 2000 to latest versions. He is interested in database architect and engineering management positions, with strong affinity to data-driven industries.