{"id":106060,"date":"2025-05-07T06:34:00","date_gmt":"2025-05-07T06:34:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=106060"},"modified":"2026-03-12T15:49:06","modified_gmt":"2026-03-12T15:49:06","slug":"techniques-to-query-azure-sqls-new-json-datatype","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/techniques-to-query-azure-sqls-new-json-datatype\/","title":{"rendered":"Azure SQL Native JSON Data Type: Query Techniques"},"content":{"rendered":"\n

Azure SQL Database introduced a native JSON data type that stores JSON documents more efficiently than VARCHAR(MAX) and enables native CHECK constraints for JSON validation. Alongside the new data type, Azure SQL added JSON_ARRAYAGG (aggregate JSON values into an array), JSON_OBJECTAGG (aggregate key-value pairs into an object), and JSON_OBJECT (construct JSON objects from arguments) – all ANSI SQL-standard functions. <\/p>\n\n\n\n

These features are confirmed for SQL Server 2025, making the techniques in this article directly applicable to on-premises SQL Server in the near future. This guide covers creating JSON columns, adding validation constraints, and using the new aggregation functions for practical JSON construction scenarios.<\/p>\n\n\n\n

The new JSON field type and new functions in Azure SQL brings a new world of possibilities for JSON manipulation in SQL Databases. These features are slated to be a part of SQL Server 2025 as well.<\/p>\n\n\n\n

Let\u2019s analyze some practical uses of these features.<\/p>\n\n\n\n

Read also: <\/strong>Storing and parsing JSON in SQL Server with VARCHAR\/NVARCHAR<\/a><\/p>\n\n\n\n

Creating and using a JSON column<\/h2>\n\n\n\n

Before this new field type, JSON data was typically stored in varchar(max)<\/code> columns. There are many features to use with JSON values stored in varchar(max)<\/code> columns and variables, but storing JSON as regular strings is still limited.<\/p>\n\n\n\n

The built-in JSON type expands the possibilities. Using an actual JSON column, it becomes easier to build constraints related to JSON columns, for example.<\/p>\n\n\n\n

Let’s see how to create a table and insert records using this new type.<\/p>\n\n\n\n

DROP TABLE IF EXISTS dbo.testOrders;\nCREATE TABLE dbo.testOrders (\n    order_id int NOT NULL IDENTITY,\n    order_info JSON NOT NULL\n);\nINSERT INTO dbo.testOrders (order_info)\nVALUES ('\n{\n    \"OrderNumber\": \"S043659\",\n    \"Date\":\"2022-05-24T08:01:00\",\n    \"AccountNumber\":\"AW29825\",\n    \"Price\":59.99,\n    \"Quantity\":1\n}'), ('\n{\n    \"OrderNumber\": \"S043661\",\n    \"Date\":\"2022-05-20T12:20:00\",\n    \"AccountNumber\":\"AW73565\",\n    \"Price\":24.99,\n    \"Quantity\":3\n}');\nselect * \nfrom testOrders;<\/pre>\n\n\n\n
The output from the SELECT<\/code> statement will look like this. If you grab the value from the JSON columns, you will see it looks just like the input.<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The format does not make a difference when you insert the data, if you change the first of the inputs to one long string, like the following:<\/p>\n\n\n\n
VALUES ('{\"OrderNumber\": \"S043659\", \"Date\":\"2022-05-24T08:01:00\",\n          \"AccountNumber\":\"AW29825\",\"Price\":59.99,\"Quantity\":1}')<\/pre>\n\n\n\n
There will be no difference between the values you see in the structures and output.<\/p>\n\n\n\n
Creating a CHECK Constraint for a JSON type<\/h2>\n\n\n\n
One of many advantages of having a native JSON type is that you can use it to validate the structure (or values) in the JSON data. Consider the table in the example above: How could we prevent inserted records with bad JSON values based on our requirements?<\/p>\n\n\n\n
For example, how can we prevent a JSON order without the attribute Price to be inserted? The creation of a constraint is much easier than it was before. :<\/p>\n\n\n\n
alter table testOrders\n  add constraint chkPrice \n     CHECK (JSON_PATH_EXISTS(order_info, '$.Price') = 1) <\/pre>\n\n\n\n
As a result, we have an actual JSON validation when data is inserted or modified. Any attempt to insert poorly formed order structures will be blocked, as illustrated in the image below:<\/p>\n\n\n\n
Try to execute this code:<\/p>\n\n\n\n
INSERT INTO dbo.testOrders (order_info)\nVALUES ('\n{\n    \"OrderNumber\": \"S0436663\",\n    \"Date\":\"2022-05-24T08:01:00\",\n    \"AccountNumber\":\"AW29827\",\n    \"Quantity\":1\n}')<\/pre>\n\n\n\n
And it will not create the data, but will fail with an error similar to the following:<\/p>\n\n\n\n
Msg 547, Level 16, State 0, Line 1<\/code><\/p>\n\n\n\n
The INSERT statement conflicted with the CHECK constraint \"chkPrice\". The conflict occurred in database \"Testing\", table \"dbo.testOrders\", column 'order_info'.<\/code><\/p>\n\n\n\n
In this way, we can ensure our data will be way more consistent by using JSON fields and constraints.<\/p>\n\n\n\n
Methods to Query the Data<\/h2>\n\n\n\n
There are at least two different methods to query the data: using the FOR JSON<\/code> or using the JSON_ARRAYAGG<\/code> aggregation function. FOR JSON<\/code> is not new, it was introduced in SQL Server 2016.<\/p>\n\n\n\n
JSON_ARRAYAGG<\/code>, on the other hand, is one of the new JSON functions released in Azure SQL. It is an aggregation function, such as SUM<\/code>, MAX<\/code> or MIN<\/code>. This means this function can aggregate the content of a field among many records and generate a JSON array as a result. As with every aggregation function, this can also be done according to the use of a GROUP BY<\/code>.<\/p>\n\n\n\n
It\u2019s important to compare the new function with previously existing features to generate JSON data and in this way discover the new possibilities this function brings to us. Let’s analyze how both work and understand what was already available and what are the new features.<\/p>\n\n\n\n
Let’s create a table for a customers and orders type scenario:<\/p>\n\n\n\n
DROP TABLE IF EXISTS dbo.testJSON;\nCREATE TABLE testJSON\n    (Id int identity(1,1) not null,\n     CustomerName varchar(50),\n     Orders JSON)<\/pre>\n\n\n\n
The example is based on the AdventureworksLT<\/em> database. This database can be provisioned as a sample database when provisioning an Azure SQL Database<\/strong> on Azure.<\/p>\n\n\n\n
Editor note: it is a relatively painless operation if you have any experience with Azure SQL Databases. You can find more details here about restoring the database to your environment: AdventureWorks Sample Databases<\/a><\/em><\/p>\n\n\n\n
This database has customers, order headers and order details. We can join these records and transform them into a JSON document for each customer.<\/p>\n\n\n\n
We can use FOR JSON<\/code> to create a JSON result and CROSS APPLY<\/code> to get the orders for each customer. We need to use INSERT<\/code>\/<\/strong>SELECT<\/code> to insert the records in the test table.<\/p>\n\n\n\n
This is the code to fill the test table, it will create data with arrays in the Orders column:<\/p>\n\n\n\n
insert into testJSON(Id, CustomerName, Orders)\n   select FirstName + ' ' + LastName as CustomerName, t.* \n   from SalesLT.Customer  c\n        cross apply \n          (select (select ProductID, UnitPrice, \n                          OrderQty, so.SalesOrderID \n                   from SalesLT.SalesOrderDetail so\n        inner join\n          SalesLT.SalesOrderHeader SOH\n          on so.SalesOrderID=SOH.SalesOrderID\n          where SOH.CustomerID = c.CustomerID \n                            for JSON auto) as product) t\n       where t.product is not null<\/pre>\n\n\n\n
Take a look at the data:<\/p>\n\n\n\n
select *\nfrom  testJson;<\/pre>\n\n\n\n
And what you will see is that there is a square bracket that denotes an array of values:<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
After creating the test table, it\u2019s time to query it with our new functions. Let’s suppose we want to retrieve all the orders, together. The two methods, FOR JSON<\/code> and JSON_ARRAYAGG<\/code> allow this. What we need to discover is if there is any difference in the result.<\/p>\n\n\n\n
Using the FOR JSON and Analyzing the Result<\/h3>\n\n\n\n
Let\u2019s analyze the FOR JSON<\/strong> method. This query below is an example of this method:<\/p>\n\n\n\n
select (\n        select Orders \n        from testJSON for JSON auto) as Orders<\/pre>\n\n\n\n
This query has two SELECT<\/code> statements, one inside another. If we don’t build in this way, the JSON can be truncated. We can use JSON Hero website<\/a> to analyze the result of the query. Just save the results to a file and import it into the tool to analyze it. FOR JSON<\/code> joins the content from all the records in an array.<\/p>\n\n\n\n
Each record has an array, because this is the original content inside the field Orders<\/code>, resulting in an array of arrays, or close to this.<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
As you may notice in the image above, what we have is not exactly an array of array. The FOR JSON<\/code> creates an additional element in the result: It creates an object wrapping each record in the structure.<\/p>\n\n\n\n
In this way, the result becomes like this:<\/p>\n\n\n
\n