Inspired by a project I recently worked on, I decided to write a short post on a subject many people will find useful, that is how SQL Server 2016 handles JSON. This post shows also some simple examples of how SQL Server 2016 can query and extract values from JSON strings.
1 – About JSON
JSON (JavaScript Object Notation) is a data-interchange format. It can be easily read and written and can be easily parsed and generated by machines. JSON is a text format that is completely language independent and uses conventions programmers of the C-family are familiar with.
JSON is built on two structures:
-
a collection of name/value pairs;
-
an ordered list of values.
Most, if not all the modern programming languages support these data structures. Compared to XML, JSON is less verbose, smaller, simpler and someone claims better in CPU performance. Yet, while XML is a mark-up language, JSON is only a data format, they are often linked as they are typically used to accomplish the same task, data interchange.
2 – JSON in SQL Server 2016
This is in short what can be said about how SQL Server 2016 Community Technology Preview (CTP3) handles JSON.
– SQLS 2016 CTP3 offers built-in JSON support, but not native JSON data type and this is an important difference with XML. SQLS 2016 keeps storing JSON data as NVARCHAR or, more often, as NVARCHAR(MAX).
– While with SQLS 2016 CTP2 we were able just to format and export data as JSON string, with CTP3 we can also extract values from JSON text, load JSON text in tables, index properties in JSON text…
– Some useful functions: JSON_VALUE extracts a scalar value from a JSON string; JSON_QUERY instead extracts an object or an array; OPENJSON allows you to convert JSON data into a relational format (rows and columns) and, after that, you can query this data by using T-SQL, as SQL Server does not provide a custom query language for querying JSON; FOR JSON formats query results as JSON text.
3 – Some examples
SQL Server 2016 does not have custom JSON indexes but, however, for complex queries it is possible to optimize performances by using standard non-clustered indexes, full-text search indexes or creating indexed computed columns.
JSON_VALUE
The first example is very simple and shows the JSON_VALUE function extracting scalar values from within variable containing JSON text. The syntax is:
JSON_VALUE(expression, path)
where expression is the column/variable name and path specifies the path of the value to extract.
The variable has been set to NVARCHAR(MAX).
The query extracts the four values from the JSON text.
JSON_QUERY
When it comes to entire objects (in curly braces) or arrays (in square brackets), the right function is JSON_QUERY. The syntax is:
JSON_QUERY(expression, path)
where expression is the column/variable name and path specifies the path of the object or array to extract. If the value is not an object or an array, JSON_QUERY returns NULL.
In this example we extract the entire array contained in the column.
The query returns 31465 rows each one containing the JSON array.
OPENJSON
OPENJSON can be considered as a table-valued function able to parse JSON text and to return objects and properties in JSON as rows and columns.
For instance, let’s use OPENJSON to transform a JSON string into a relation format.
SQL Server 2016 will return four rows, one row for each property, and three columns (key, value, type). The type column will change depending on the type (string, number, Boolean, array, object).
SQL Server 2016 allows you also to import content from JSON files into relational tables combining OPENJSON and OPENROWSET.
FOR JSON
This function enables you to export data from SQL Server as JSON or to format result of a query as JSON. There are two modes, very similar to XML:
– FOR JSON PATH, with full control over the output format;
– FOR JSON AUTO, automatically formatted.
4 – Conclusion
Compared to the past, with SQL Server 2016 CTP3 now it is a lot easier to deal with JSON, a format that keeps increasing its popularity. The tools offered by SQL Server 2016 allow us to work with JSON without thinking too often that this is not a native data type as it happens in other DBMSs (PostgreSQL). A lot has been done, a few relevant improvements have been provided, the features are relatively easy to use and then, being positive, it is better than no support at all.
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr