56_JSON_Native_Type.sql

/**************************************************************
 * SQL Server 2025 JSON Native Data Type Tutorial
 * Description: This script demonstrates the native JSON data type
 *              introduced in SQL Server 2025 (17.x). It covers:
 *              - Creating tables with JSON columns
 *              - Storing JSON data in native binary format
 *              - Using the modify() method for in-place updates
 *              - JSON aggregate functions (JSON_OBJECTAGG, JSON_ARRAYAGG)
 *              - Performance benefits over varchar/nvarchar storage
 *              - Indexing strategies for JSON columns
 *              - Conversion and compatibility considerations
 **************************************************************/

-------------------------------------------------
-- Region: 1. Introduction and Setup
-------------------------------------------------
USE master;
GO

/*
  Create a test database for our JSON examples.
*/
IF DB_ID('JSONNativeDemo') IS NOT NULL
BEGIN
    ALTER DATABASE JSONNativeDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE JSONNativeDemo;
END
GO

CREATE DATABASE JSONNativeDemo;
GO

USE JSONNativeDemo;
GO

-- Set compatibility level for SQL Server 2025 (17.x)
ALTER DATABASE JSONNativeDemo SET COMPATIBILITY_LEVEL = 170;
GO

-------------------------------------------------
-- Region: 2. Creating Tables with Native JSON Type
-------------------------------------------------
/*
  The native JSON data type stores JSON in an optimized binary format.
  Benefits:
  - More efficient reads (already parsed)
  - More efficient writes (update individual values without full document access)
  - More efficient storage (optimized compression)
  - UTF-8 encoding by default (Latin1_General_100_BIN2_UTF8)
*/

-- 2.1 Create a table with a JSON column
CREATE TABLE dbo.Products
(
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    ProductDetails JSON NOT NULL
);
GO

-- 2.2 Create a table with JSON and constraints
CREATE TABLE dbo.Orders
(
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    OrderData JSON NOT NULL
        CHECK (JSON_PATH_EXISTS(OrderData, '$.items') = 1),
    OrderDate DATETIME2 DEFAULT SYSDATETIME()
);
GO

-- 2.3 Create a table with JSON and default value
CREATE TABLE dbo.UserSettings
(
    UserID INT PRIMARY KEY,
    UserName NVARCHAR(50) NOT NULL,
    Settings JSON DEFAULT ('{"theme": "light", "language": "en"}')
);
GO

-------------------------------------------------
-- Region: 3. Inserting JSON Data
-------------------------------------------------
/*
  Insert JSON data just like with varchar/nvarchar.
  The database automatically converts and stores it in native binary format.
*/

-- 3.1 Insert product data with JSON details
INSERT INTO dbo.Products (ProductName, ProductDetails)
VALUES 
    ('Laptop', '{"brand": "TechCorp", "specs": {"cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}, "price": 1299.99}'),
    ('Mouse', '{"brand": "Peripherals Inc", "specs": {"type": "wireless", "dpi": 1600}, "price": 29.99}'),
    ('Monitor', '{"brand": "DisplayTech", "specs": {"size": "27 inch", "resolution": "4K", "refresh": "144Hz"}, "price": 499.99}');
GO

-- 3.2 Insert order data
INSERT INTO dbo.Orders (CustomerName, OrderData)
VALUES 
    ('Alice Johnson', '{"items": [{"product": "Laptop", "quantity": 1, "price": 1299.99}], "total": 1299.99, "status": "pending"}'),
    ('Bob Smith', '{"items": [{"product": "Mouse", "quantity": 2, "price": 29.99}, {"product": "Monitor", "quantity": 1, "price": 499.99}], "total": 559.97, "status": "shipped"}');
GO

-- 3.3 Insert user settings
INSERT INTO dbo.UserSettings (UserID, UserName, Settings)
VALUES 
    (1, 'john.doe', '{"theme": "dark", "language": "en", "notifications": true}'),
    (2, 'jane.smith', DEFAULT);
GO

-------------------------------------------------
-- Region: 4. Querying JSON Data
-------------------------------------------------
/*
  Query JSON data using standard JSON functions.
  No syntax changes needed from varchar/nvarchar approach.
*/

-- 4.1 Extract scalar values from JSON
SELECT 
    ProductID,
    ProductName,
    JSON_VALUE(ProductDetails, '$.brand') AS Brand,
    JSON_VALUE(ProductDetails, '$.price') AS Price,
    JSON_VALUE(ProductDetails, '$.specs.cpu') AS CPU
FROM dbo.Products;
GO

-- 4.2 Extract JSON objects
SELECT 
    ProductID,
    ProductName,
    JSON_QUERY(ProductDetails, '$.specs') AS Specifications
FROM dbo.Products;
GO

-- 4.3 Check for JSON path existence
SELECT 
    ProductID,
    ProductName,
    CASE 
        WHEN JSON_PATH_EXISTS(ProductDetails, '$.specs.cpu') = 1 
        THEN 'Has CPU Info'
        ELSE 'No CPU Info'
    END AS CPUInfoStatus
FROM dbo.Products;
GO

-- 4.4 Use JSON in WHERE clause
SELECT 
    OrderID,
    CustomerName,
    JSON_VALUE(OrderData, '$.status') AS OrderStatus
FROM dbo.Orders
WHERE JSON_VALUE(OrderData, '$.status') = 'pending';
GO

-------------------------------------------------
-- Region: 5. Modifying JSON Data with modify() Method
-------------------------------------------------
/*
  The modify() method (preview in SQL Server 2025) allows in-place updates.
  This is more efficient than replacing the entire JSON document.
*/

-- 5.1 Update a specific value in JSON
-- Change the order status from 'pending' to 'processing'
UPDATE dbo.Orders
SET OrderData.modify('$.status', 'processing')
WHERE OrderID = 1;
GO

-- Verify the update
SELECT OrderID, CustomerName, JSON_VALUE(OrderData, '$.status') AS Status
FROM dbo.Orders
WHERE OrderID = 1;
GO

-- 5.2 Update a nested value
UPDATE dbo.Products
SET ProductDetails.modify('$.specs.ram', '32GB')
WHERE ProductID = 1;
GO

-- Verify the update
SELECT ProductID, ProductName, JSON_VALUE(ProductDetails, '$.specs.ram') AS RAM
FROM dbo.Products
WHERE ProductID = 1;
GO

-- 5.3 Update numeric values
UPDATE dbo.Products
SET ProductDetails.modify('$.price', 1199.99)
WHERE ProductID = 1;
GO

-------------------------------------------------
-- Region: 6. JSON Aggregate Functions (SQL Server 2025)
-------------------------------------------------
/*
  SQL Server 2025 introduces new JSON aggregate functions:
  - JSON_OBJECTAGG: Creates a JSON object from key-value pairs
  - JSON_ARRAYAGG: Creates a JSON array from values
*/

-- 6.1 JSON_OBJECTAGG - Create JSON object from aggregation
-- Create a JSON object mapping ProductID to ProductName
SELECT JSON_OBJECTAGG(ProductID: ProductName) AS ProductsMap
FROM dbo.Products;
GO

-- 6.2 JSON_ARRAYAGG - Create JSON array from aggregation
-- Create an array of all product names
SELECT JSON_ARRAYAGG(ProductName) AS ProductNames
FROM dbo.Products;
GO

-- 6.3 Combine with other aggregations
SELECT 
    JSON_VALUE(OrderData, '$.status') AS Status,
    COUNT(*) AS OrderCount,
    JSON_ARRAYAGG(CustomerName) AS Customers
FROM dbo.Orders
GROUP BY JSON_VALUE(OrderData, '$.status');
GO

-------------------------------------------------
-- Region: 7. Converting to/from JSON Type
-------------------------------------------------
/*
  You can convert between JSON and string types using CAST/CONVERT.
*/

-- 7.1 Convert JSON to NVARCHAR
DECLARE @jsonData JSON = '{"name": "Test", "value": 123}';

SELECT CAST(@jsonData AS NVARCHAR(MAX)) AS JsonAsString;
GO

-- 7.2 Convert NVARCHAR to JSON
DECLARE @stringData NVARCHAR(MAX) = '{"name": "Test", "value": 456}';

SELECT CAST(@stringData AS JSON) AS StringAsJson;
GO

-- 7.3 Use in functions and stored procedures
CREATE OR ALTER PROCEDURE dbo.ProcessJsonOrder
    @orderJson JSON
AS
BEGIN
    SELECT 
        JSON_VALUE(@orderJson, '$.customer') AS Customer,
        JSON_VALUE(@orderJson, '$.total') AS Total;
END;
GO

-- Test the procedure
DECLARE @testOrder JSON = '{"customer": "John Doe", "total": 299.99}';
EXEC dbo.ProcessJsonOrder @testOrder;
GO

-------------------------------------------------
-- Region: 8. Indexing JSON Data
-------------------------------------------------
/*
  JSON columns can be included in indexes or used in filtered indexes.
  Cannot be used as a key column.
*/

-- 8.1 Create an index with JSON column as included column
CREATE NONCLUSTERED INDEX IX_Products_Brand
ON dbo.Products (ProductID)
INCLUDE (ProductDetails);
GO

-- 8.2 Create a computed column for indexing
ALTER TABLE dbo.Products
ADD ProductBrand AS JSON_VALUE(ProductDetails, '$.brand') PERSISTED;
GO

CREATE NONCLUSTERED INDEX IX_Products_ComputedBrand
ON dbo.Products (ProductBrand);
GO

-- 8.3 Use filtered index with JSON
CREATE NONCLUSTERED INDEX IX_Orders_PendingStatus
ON dbo.Orders (OrderID)
WHERE JSON_VALUE(OrderData, '$.status') = 'pending';
GO

-------------------------------------------------
-- Region: 9. Performance Comparison
-------------------------------------------------
/*
  Compare performance between native JSON and NVARCHAR storage.
*/

-- 9.1 Create comparison tables
CREATE TABLE dbo.ProductsVarchar
(
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    ProductDetails NVARCHAR(MAX) NOT NULL
);
GO

-- Insert same data into both tables
INSERT INTO dbo.ProductsVarchar (ProductName, ProductDetails)
SELECT ProductName, CAST(ProductDetails AS NVARCHAR(MAX))
FROM dbo.Products;
GO

-- 9.2 Compare query performance
-- Native JSON type
SET STATISTICS TIME ON;
SELECT JSON_VALUE(ProductDetails, '$.price') AS Price
FROM dbo.Products;

-- NVARCHAR storage
SELECT JSON_VALUE(ProductDetails, '$.price') AS Price
FROM dbo.ProductsVarchar;
SET STATISTICS TIME OFF;
GO

-------------------------------------------------
-- Region: 10. Advanced JSON Operations
-------------------------------------------------
/*
  Combine native JSON type with other SQL Server features.
*/

-- 10.1 Use with OPENJSON
SELECT P.ProductID, P.ProductName, Items.*
FROM dbo.Orders O
CROSS APPLY OPENJSON(CAST(O.OrderData AS NVARCHAR(MAX)), '$.items')
WITH (
    product NVARCHAR(100) '$.product',
    quantity INT '$.quantity',
    price DECIMAL(10,2) '$.price'
) AS Items;
GO

-- 10.2 Combine with JSON_MODIFY (still useful for complex updates)
UPDATE dbo.Orders
SET OrderData = JSON_MODIFY(
    CAST(OrderData AS NVARCHAR(MAX)), 
    '$.deliveryDate', 
    CAST(SYSDATETIME() AS NVARCHAR(50))
)
WHERE OrderID = 1;
GO

-- 10.3 Use in triggers
CREATE OR ALTER TRIGGER tr_Orders_AuditChanges
ON dbo.Orders
AFTER UPDATE
AS
BEGIN
    IF UPDATE(OrderData)
    BEGIN
        PRINT 'Order data was modified';
        -- Log changes or perform additional processing
    END
END;
GO

-------------------------------------------------
-- Region: 11. Size Limitations
-------------------------------------------------
/*
  The native JSON data type has size limitations:
  - Maximum size: 2GB
  - Maximum unique keys: 32K
  - Maximum key string size: 7998 bytes
  - Maximum string value size: ~536MB
  - Maximum properties per object: 65535
  - Maximum array elements: 65535
  - Maximum nesting levels: 128
*/

-- 11.1 Check JSON document size
SELECT 
    ProductID,
    ProductName,
    DATALENGTH(ProductDetails) AS JsonSizeInBytes
FROM dbo.Products;
GO

-------------------------------------------------
-- Region: 12. Cleanup
-------------------------------------------------
/*
  Optional: Clean up the demo database.
*/
-- USE master;
-- GO
-- DROP DATABASE IF EXISTS JSONNativeDemo;
-- GO