Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server JSON Support: NoSQL Flexibility with SQL Power

- 29.08.26 - ErcanOPAK

📝 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'
);

Related posts:

SQL Server — TOP (1) Without ORDER BY Is Undefined

SQL Server — TOP (1) Without ORDER BY Is Undefined

SQL: Use UNION ALL Instead of UNION When Duplicates Don't Matter

SQL: Use UNION ALL Instead of UNION When Duplicates Don't Matter

SQL: Use Recursive CTE to Query Hierarchical Data (Employee Trees)

SQL: Use Recursive CTE to Query Hierarchical Data (Employee Trees)

Views: 8

Post navigation

.NET Core Configuration: Power Your App with Flexible Settings
C# Async/Await: Deep Dive into the Most Important Feature

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com