📝 Key Insights: SQL Server supports JSON natively. Store, query, and transform JSON data like NoSQL, with full SQL power.
Need both relational and document flexibility? SQL JSON support gives you both.
🚀 Store JSON Data
-- ✅ Table with JSON column
CREATE TABLE Orders
(
Id INT IDENTITY(1,1) PRIMARY KEY,
CustomerId INT NOT NULL,
OrderDate DATETIME2 NOT NULL,
OrderData NVARCHAR(MAX) CONSTRAINT IsValidJSON CHECK (ISJSON(OrderData) = 1)
);
-- ✅ Insert JSON data
INSERT INTO Orders (CustomerId, OrderDate, OrderData)
VALUES (
1,
GETUTCDATE(),
N'{
"items": [
{ "productId": 101, "name": "Laptop", "price": 1299.99, "quantity": 1 },
{ "productId": 102, "name": "Mouse", "price": 29.99, "quantity": 2 }
],
"shipping": {
"address": "123 Main St",
"city": "New York",
"state": "NY",
"zip": "10001",
"method": "Express"
},
"discount": 0.1,
"notes": "Handle with care"
}'
);
🔍 Query JSON Data
-- ✅ Extract values from JSON
SELECT
Id,
CustomerId,
OrderDate,
JSON_VALUE(OrderData, '$.discount') AS Discount,
JSON_VALUE(OrderData, '$.shipping.method') AS ShippingMethod,
JSON_QUERY(OrderData, '$.items') AS Items
FROM Orders;
-- ✅ Query JSON arrays
SELECT
Id,
item.productId,
item.name,
item.price,
item.quantity
FROM Orders
CROSS APPLY OPENJSON(OrderData, '$.items')
WITH (
productId INT '$.productId',
name NVARCHAR(100) '$.name',
price DECIMAL(18,2) '$.price',
quantity INT '$.quantity'
) AS item;
-- ✅ Filter by JSON values
SELECT *
FROM Orders
WHERE JSON_VALUE(OrderData, '$.shipping.state') = 'NY';
-- ✅ Update JSON values
UPDATE Orders
SET OrderData = JSON_MODIFY(OrderData, '$.status', 'Shipped')
WHERE Id = 1;
-- ✅ Create JSON from relational data
SELECT
Id,
CustomerId,
OrderDate,
(
SELECT
JSON_QUERY(OrderData, '$.items') AS items,
JSON_QUERY(OrderData, '$.shipping') AS shipping,
JSON_VALUE(OrderData, '$.discount') AS discount,
'Shipped' AS status
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS OrderData
FROM Orders;
💡 Expert Tip: Index JSON
-- ✅ Create computed column for JSON value
ALTER TABLE Orders
ADD ShippingState AS JSON_VALUE(OrderData, '$.shipping.state');
-- ✅ Index computed column
CREATE INDEX IX_Orders_ShippingState ON Orders(ShippingState);
-- ✅ Create index on JSON array values
CREATE TABLE OrdersItems
(
Id INT IDENTITY(1,1) PRIMARY KEY,
OrderId INT,
ProductId INT,
Quantity INT,
Price DECIMAL(18,2)
);
-- ✅ Use openjson to insert into relational table
INSERT INTO OrdersItems (OrderId, ProductId, Quantity, Price)
SELECT
Id,
productId,
quantity,
price
FROM Orders
CROSS APPLY OPENJSON(OrderData, '$.items')
WITH (
productId INT '$.productId',
quantity INT '$.quantity',
price DECIMAL(18,2) '$.price'
);

