19 November 2026

SQL Server 2025 JSON_CONTAINS for Beginners: Search Student Skills

  Asp.Net CS By Example       19 November 2026

A student profile may store a short list of skills as JSON. When you need everyone who knows SQL, searching the text with LIKE '%SQL%' can match the wrong characters and ignores the JSON structure. SQL Server 2025 adds JSON_CONTAINS so you can ask whether a JSON path contains a particular value.

Search the array, not the text

Student JSON → $.skills[*] → JSON_CONTAINS(..., 'SQL', ...) → Matching students

Before you run the query

Use a disposable practice database on SQL Server 2025 (17.x). Earlier SQL Server releases do not have this function. This exercise stores JSON in an nvarchar(max) column and checks that each inserted document is valid JSON. The function can search JSON text as well as the newer native json type.

Create the sample students and search

Run this script once in your practice database. The SQL/JSON path $.skills[*] means “look at each item in the skills array.”

-- Run only in a disposable SQL Server 2025 (17.x) practice database.
CREATE TABLE dbo.StudentProfiles
(
    StudentId int NOT NULL PRIMARY KEY,
    StudentName nvarchar(80) NOT NULL,
    SkillsJson nvarchar(max) NOT NULL
        CONSTRAINT CK_StudentProfiles_SkillsJson CHECK (ISJSON(SkillsJson) = 1)
);

INSERT INTO dbo.StudentProfiles (StudentId, StudentName, SkillsJson)
VALUES
    (1, N'Asha',  N'{"skills":["SQL","Python"]}'),
    (2, N'Ravi',  N'{"skills":["React","Node.js"]}'),
    (3, N'Meena', N'{"skills":["SQL","Power BI"]}');

SELECT StudentName
FROM dbo.StudentProfiles
WHERE JSON_CONTAINS(SkillsJson, N'SQL', '$.skills[*]') = 1
ORDER BY StudentName;

The result should list Asha and Meena, in that order. Ravi has React and Node.js but no SQL entry. Running the full script a second time will fail because the table already exists; use a new practice database or a new table name for another run.

How JSON_CONTAINS answers the question

SkillsJson is the document to inspect. N'SQL' is the value to find. '$.skills[*]' restricts the search to items in the skills array. The function returns 1 for a match and 0 when it does not match. It can return NULL when an argument is null or the path is absent, so the = 1 predicate keeps only definite matches.

Always pass an explicit path in this SQL Server 2025 example. The documentation shows an optional path in the general syntax, but its current limitations say the path is required. Arrays also need the wildcard in the path, as shown here.

Practice

  1. Search for N'Python'. Which student appears?
  2. Add a fourth student with a JSON skills array, then predict the result before querying.
  3. Change the path to a missing property and observe how the result changes.

Continue with the SQL learning path. For syntax and limitations, see Microsoft's JSON_CONTAINS reference and SQL Server JSON overview.

logoblog

Thanks for reading SQL Server 2025 JSON_CONTAINS for Beginners: Search Student Skills

Previous
« Prev Post

No comments:

Post a Comment

Please do not enter any spam link in the comment box.