SQL Server 2025 (preview) working with JSON

 

Working with JSON in SQL Server

SQL Server allows you to store JSON text in database tables and use built-in functions to query or modify it using standard T-SQL.

1. Extract Values from JSON

You can extract values using:

JSON_VALUE: Returns a scalar value (e.g., string, number).

JSON_QUERY: Returns an object or array.

E.g.

SELECT JSON_VALUE(jsonCol, '$.info.address.Town') AS Town

FROM People

WHERE ISJSON(jsonCol) > 0;

SELECT JSON_QUERY(jsonCol, '$.info.skills') AS Skills

FROM People;

2. Modify JSON Data

Use JSON_MODIFY to update parts of a JSON string.

E.g

DECLARE @json NVARCHAR(MAX);

SET @json = '{"city":"Paris"}';

SET @json = JSON_MODIFY(@json, '$.city', 'London');

SELECT @json AS ModifiedJson;

-- Result: {"city":"London"}

3. Convert JSON to Rowset

Use OPENJSON to transform JSON arrays into tabular data.

E.g

DECLARE @json NVARCHAR(MAX) = N'[

  {"id": 1, "name": "Alice"},

  {"id": 2, "name": "Bob"}

]';

SELECT *

FROM OPENJSON(@json)

WITH (

    id INT,

    name NVARCHAR(100)

);

4. Validate JSON

E.g

Checks if a string is valid JSON (returns 1 if true).

SELECT ISJSON('{"name":"value"}') AS IsJson; -- Returns 1

SELECT ISJSON('invalid') AS IsJson;          -- Returns 0

5. Combined JSON

SELECT 

  Name,

  JSON_VALUE(jsonCol, '$.info.address.Town') AS Town,

  JSON_QUERY(jsonCol, '$.info.skills') AS Skills

FROM People

WHERE ISJSON(jsonCol) = 1

  AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade';

Cheers

Samitha

SQL Sever Introduces Copilot (preview)

 Copilot in SSMS helps you ask questions about your database and environment, and assists in writing T-SQL with AI. It doesn’t retain or use your data for training. Copilot works with SQL Server, Azure SQL Database, Azure SQL Managed Instance, and SQL Database in Fabric. Query execution respects your login permissions—if you lack access to certain objects, Copilot can generate queries but cannot execute them.

read here for more information.

Cheers

Samitha

C# 14 Extension members

 C# 14 introduces new syntax for defining extension members, expanding on traditional extension methods. Key additions include:

• Extension properties and indexers alongside extension methods.

• Ability to define static extension members, making them appear as if they are static members of the extended type

Two Types of Extension Blocks:

Instance-like extension members (e.g., sequence.IsEmpty):

 

extension<TSource>(IEnumerable<TSource> source)

{

    public bool IsEmpty => !source.Any();

    public TSource this[int index] => source.Skip(index).First();

    public IEnumerable<TSource> Where(Func<TSource, bool> predicate) { ... }

}

Static-like extension members (e.g., IEnumerable<int>.Identity):

 

extension<TSource>(IEnumerable<TSource>)

{

    public static IEnumerable<TSource> Combine(IEnumerable<TSource> first, IEnumerable<TSource> second) { ... }

    public static IEnumerable<TSource> Identity => Enumerable.Empty<TSource>();

}

These features allow more natural syntax and better integration with existing types by simulating both instance and static members via extensions.

Cheers

Samitha

ASP.NET Core changes in .NET 9 MapStaticAssets

 MapStaticAssets is a new feature that enhances the delivery of static assets in web applications. It is intended as a  replacement for UseStaticFiles.  

MapStaticAssets offers key advantages over UseStaticFiles, including:

• Build-time compression:

  • Uses gzip during development and gzip + Brotli during publish, minimizing asset sizes.

• Content-based ETags:

• Generates ETags from the SHA-256 hash of each file’s content, ensuring browsers only re-download files when their content changes

Read more here.

Cheers

Samitha

EF Core Vs Dapper

 Both Dapper and EF Core are excellent ORM options suitable for projects of any size. The choice between them largely depends on personal or organizational preferences. Dapper appeals to those who prefer writing raw SQL queries, while EF Core is favored by those who prefer working with LINQ. Each has its own strengths and weaknesses

You can learn more on Dapper vs EF Core here.

Cheers

Samitha

C# 14 New feature for Null conditional assignment

The preview of .NET 10 and the upcoming C# version introduces a potential new feature: null-conditional assignment. This aims to simplify code where you assign a value to a property only if the object isn't null.

Currently, you'd write:

if (classObj is not null)

{

  classObj .Property = 100;

}

With the new feature, this can be shortened to:

classObj ?.Property = 100;

This makes the code more concise and readable when dealing with potentially null objects.

Cheers,

Samitha

EF Core 10 Support for .NET 10 LeftJoin and RightJoin operators

 In earlier versions of EF Core, performing a LEFT JOIN in LINQ required a complex combination of SelectMany, GroupJoin, and DefaultIfEmpty.

With .NET 10, a new built-in LeftJoin method simplifies this process. It allows developers to write cleaner and more intuitive LINQ queries, and EF Core fully supports it in database queries.

Example:

var studentDepartments = context.Students

    .LeftJoin(

        inner: context.Departments,

        outerKeySelector: student => student.DepartmentID,

        innerKeySelector: department => department.ID,

        resultSelector: (student, department) => new

        {

            FirstName = student.FirstName,

            LastName = student.LastName,

            Department = department.Name ?? "[NONE]"

        });

Cheers

Samitha