JSON_CONTAINS in SQL Server 2025: Searching Inside JSON Arrays

JSON_CONTAINS checks whether a JSON document holds a value at a path you choose. It is new in SQL Server 2025. It answers a common question in one call: does this array contain spicy? Let’s try it on a small menu table.

What the Function Does

The call is JSON_CONTAINS(document, value, path). The path starts with $, which means the root of the document. The mark [*] means every element of an array. The result is 1 when the value is found and 0 when it isn’t. When there is nothing to look in, the result is NULL.

The first argument must be the json data type. I ran everything here on SQL Server 2025, and the function needed no preview setting. The next query reads the database scoped setting that switches preview features on. In my test database it was OFF.

IF DB_ID(N'SqlJsonContainsDemo') IS NULL CREATE DATABASE SqlJsonContainsDemo;
GO
USE SqlJsonContainsDemo;
GO
SELECT name, value FROM sys.database_scoped_configurations WHERE name = N'PREVIEW_FEATURES';

A value of 0 means OFF, and every query below ran with it. If a future build moves the function into preview, that setting is the one to check.

Build the Test

The table holds five menu items. Each has a json column with tags, sizes, a stock count and a prep station. One item has no tags at all, and one has no document. Real data looks like that, so the test should too.

DROP TABLE IF EXISTS dbo.Dishes;
CREATE TABLE dbo.Dishes
(
    DishID int IDENTITY(1,1) PRIMARY KEY,
    DishName nvarchar(60) NOT NULL,
    Details json NULL
);
INSERT INTO dbo.Dishes (DishName, Details) VALUES
(N'Paneer Wrap', N'{"tags":["vegetarian","spicy","quick"],"sizes":[6,12],"stock":12,"prep":{"station":"oven","minutes":15}}'),
(N'Lentil Soup', N'{"tags":["vegan","mild"],"note":"not spicy","sizes":[8],"stock":0,"prep":{"station":"stove","minutes":30}}'),
(N'Mango Lassi', N'{"tags":["Sweet","cold"],"sizes":[12,16],"stock":30,"prep":{"station":"blender","minutes":5}}'),
(N'Chickpea Salad', N'{"sizes":[8],"stock":8,"prep":{"station":"counter","minutes":10}}'),
(N'Daily Special', NULL);

The json type brings a second benefit: it checks the text when you insert it. A document with a stray comma never reaches the table, so the search later runs on clean data. This statement tries to add a broken row.

INSERT INTO dbo.Dishes (DishName, Details) VALUES (N'Broken Row', N'{"tags":["spicy",}');

Msg 13609, Level 16, State 9, Line 1
JSON text is not properly formatted. Unexpected character '}' is found at position 17.

Nothing was inserted, so the five rows stay as they were. The error names the character and its position, which makes the typo easy to find in a long document.

Search an Array

First, a plain variable. The query checks one array three ways. One value is there, one isn’t, and one path doesn’t exist.

DECLARE @doc json = N'{"tags":["vegan","spicy"]}';
SELECT JSON_CONTAINS(@doc, 'spicy', '$.tags[*]') AS found,
       JSON_CONTAINS(@doc, 'mild', '$.tags[*]') AS missing,
       JSON_CONTAINS(@doc, 'spicy', '$.notes[*]') AS bad_path;

The third column is the one to remember. A path that doesn’t exist returns NULL, not 0. That detail decides how you write filters, and we come back to it.

Now the data type. A text variable, even a big nvarchar(max), is rejected. Cast it to json first. Better, store the column as json from the start, as the table above does.

DECLARE @text nvarchar(max) = N'{"tags":["vegan","spicy"]}';
SELECT JSON_CONTAINS(@text, 'spicy', '$.tags[*]');
GO
SELECT JSON_CONTAINS(CAST(N'{"tags":["vegan","spicy"]}' AS json), 'spicy', '$.tags[*]') AS after_cast;

Msg 8116, Level 16, State 1, Line 2
Argument data type nvarchar(max) is invalid for argument 1 of json_contains function.

Search the Table

With the json column, the search reads like plain English. The first query shows the raw result for every row. The second keeps only the matching rows.

SELECT DishName, JSON_CONTAINS(Details, 'spicy', '$.tags[*]') AS has_spicy FROM dbo.Dishes;

SELECT DishName FROM dbo.Dishes WHERE JSON_CONTAINS(Details, 'spicy', '$.tags[*]') = 1;

Chickpea Salad has no tags property, so the path is missing and the answer is NULL. Daily Special has no document, so it is NULL as well.

Positions, Nested Values and Combined Tests

The path can name a fixed position, such as $.tags[2], which checks only the third tag. It can also walk into a nested object, such as $.prep.station. And since each call returns a number, you can join calls with AND and OR.

SELECT DishName FROM dbo.Dishes WHERE JSON_CONTAINS(Details, 'quick', '$.tags[2]') = 1;

SELECT DishName FROM dbo.Dishes WHERE JSON_CONTAINS(Details, 'stove', '$.prep.station') = 1 AND JSON_CONTAINS(Details, 'vegan', '$.tags[*]') = 1;

SELECT DishName FROM dbo.Dishes WHERE JSON_CONTAINS(Details, 'cold', '$.tags[*]') = 1 OR JSON_CONTAINS(Details, 'quick', '$.tags[*]') = 1;

Compare this with a text search. Searching the raw document with LIKE finds the word anywhere, including in a note or a property name. Lentil Soup carries the note “not spicy”, so a LIKE search for spicy returns it. That is a false match, and JSON_CONTAINS never makes it because it looks only at the path you gave.

SELECT DishName FROM dbo.Dishes WHERE CAST(Details AS nvarchar(max)) LIKE N'%spicy%';

Numbers, Text and Case

JSON keeps numbers and text apart, and so does the function. The number 12 matches the stock value, while the text ’12’ doesn’t. Inside an array of numbers, 12 matches again.

SELECT JSON_CONTAINS(Details, 12, '$.stock') AS number_12,
       JSON_CONTAINS(Details, '12', '$.stock') AS text_12,
       JSON_CONTAINS(Details, 12, '$.sizes[*]') AS in_sizes
FROM dbo.Dishes
WHERE DishID = 1;

Text follows the collation. My test database ignores case, so ‘sweet’ finds the tag Sweet on Mango Lassi. Adding a case-sensitive collation to the search value turns that match off.

SELECT JSON_CONTAINS(Details, 'sweet', '$.tags[*]') AS database_default,
       JSON_CONTAINS(Details, 'sweet' COLLATE Latin1_General_100_CS_AS, '$.tags[*]') AS case_sensitive
FROM dbo.Dishes
WHERE DishID = 3;

The function also accepts a fourth argument that must be 0 or 1. In every test I ran, both values gave the same answers, so I leave it out.

The Older OPENJSON Way

Before this function, you opened the array with OPENJSON, which turns each element into a row. Then you checked those rows. OPENJSON has worked since SQL Server 2016. The query below finds the same spicy dish as before.

SELECT d.DishName
FROM dbo.Dishes AS d
WHERE EXISTS (SELECT 1 FROM OPENJSON(d.Details, '$.tags') AS t WHERE t.value = N'spicy');

The two ways split when you ask the opposite question: which dishes are not spicy? Look at both answers.

SELECT DishName FROM dbo.Dishes WHERE JSON_CONTAINS(Details, 'spicy', '$.tags[*]') = 0;

SELECT d.DishName
FROM dbo.Dishes AS d
WHERE NOT EXISTS (SELECT 1 FROM OPENJSON(d.Details, '$.tags') AS t WHERE t.value = N'spicy');

OPENJSON returns no rows for a missing path, so NOT EXISTS counts those dishes as not spicy. JSON_CONTAINS returns NULL for them, and a filter on 0 drops them. Neither answer is wrong. They answer different questions, and you should choose on purpose.

You could say OPENJSON is enough, since it runs on older servers and you already know it. Fair point. If your servers are older than SQL Server 2025, it is your only option. On 2025, the new function is shorter and easier to read.

You could also say a LIKE search is the quickest to type. Fair point, and for a throwaway check in a query window it is fine. In code that stays in production, the path makes your intent clear. A reader sees at once that you want the tags array, not the word anywhere.

A Simple Rule

Store searchable JSON in a json column, and write the path with the leading $. Use [*] to search every element of an array. Without the $, the call fails with a path format error.

Decide what a NULL result means before you write the filter. The function returns NULL for a missing path and for a missing document. For “not spicy”, use ISNULL when missing tags should count as a yes, and leave it out when they shouldn’t. Also match the type: 12 is not ’12’.

When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlJsonContainsDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlJsonContainsDemo;

JSON_CONTAINS is not a text search, it is a typed lookup at a path.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.